Building a Multitenant SaaS Database Schema in PostgreSQL

Multitenant database architectures fall into three primary design patterns: Discriminator Column (Pooled), Schema-per-Tenant (Isolated Namespaces), and Database-per-Tenant (Physical Isolation).

Discriminator Column (Pooled) stores all tenant data in shared tables tagged with a tenant_id foreign key. While offering maximum hardware efficiency and low migration complexity, it relies heavily on application-level filtering to prevent data cross-contamination.

Schema-per-Tenant creates a dedicated PostgreSQL schema (e.g., tenant_acme.orders) for each customer. It provides strong namespace boundaries and independent backup capabilities, but increases DDL migration times as tenant counts scale into thousands. For complementary database partitioning strategies, read our guide on postgres partitioning vs sharding and building a multitenant saas database schema.

To eliminate the risk of developer bugs omitting WHERE tenant_id = '...' in application queries, PostgreSQL provides native Row-Level Security (RLS). RLS enforces security policy filtering directly inside the database query engine.

When RLS is enabled on a table (ALTER TABLE orders ENABLE ROW LEVEL SECURITY), PostgreSQL automatically appends security constraints to every SELECT, UPDATE, INSERT, and DELETE query executed by non-superuser database connections.

Define an RLS policy checking a session variable: CREATE POLICY tenant_isolation_policy ON orders USING (tenant_id = current_setting('app.current_tenant_id'));. Upon checking out a database connection from a pool, the application executes SET LOCAL app.current_tenant_id = 'tenant_123', ensuring all subsequent queries are automatically scoped to that tenant.

For B2B SaaS applications serving enterprise customers with strict regulatory mandates, Schema-per-Tenant provides dedicated logical namespaces within a single PostgreSQL database.

When a tenant connects, the application sets the PostgreSQL search_path configuration parameter (SET search_path TO tenant_acme, public;). Queries referencing SELECT * FROM orders automatically resolve to tenant_acme.orders without modifying SQL statements.

However, schema migrations require executing DDL statements (ALTER TABLE, CREATE INDEX) sequentially across thousands of schema namespaces. Use schema migration tools (like Prisma, Flyway, or Liquibase) configured for parallel multi-schema iteration to prevent deployment bottlenecks.

In multi-tenant systems processing high concurrent traffic, managing PostgreSQL connection limits is a critical operational challenge. Each PostgreSQL backend connection consumes ~10MB of RAM, making un-pooled direct connections unsustainable.

Deploy PgBouncer in transaction pooling mode in front of PostgreSQL. Transaction pooling allows thousands of tenant web worker threads to share a small pool of 50-100 physical database connections.

When combining PgBouncer transaction pooling with RLS session variables, use SET LOCAL app.current_tenant_id inside explicit BEGIN ... COMMIT transaction blocks. SET LOCAL automatically resets the session variable when the transaction finishes, preventing tenant state leaking to subsequent queries on shared pooled connections.