Multi-Tenant Patterns Explained

Multi-tenancy is the practice of serving many customers, or tenants, from shared infrastructure while keeping each tenant's data isolated from every other tenant's.

Every pattern for doing this in PostgreSQL, from a tenant_id column to a fully separate database per customer, sits somewhere on one underlying spectrum between operational simplicity and isolation strength.

This page explains that spectrum directly, so the more concrete pages in this section (shared-schema queries, RLS policies, schema-per-tenant, database-per-tenant) read as points along it rather than as unrelated techniques.

Summary

• Core Idea: Multi-tenant patterns trade operational simplicity for isolation strength, and every concrete pattern sits at a specific point on that trade-off.
• Why It Matters: Choosing too little isolation risks cross-tenant data leaks and noisy-neighbor performance problems; choosing too much isolation multiplies operational and migration cost.
• Key Concepts: tenant, shared schema, schema-per-tenant, database-per-tenant, row-level security (RLS), noisy neighbor.
• When to Use: Any SaaS product serving more than one customer from shared PostgreSQL infrastructure.
• Limitations / Trade-offs: Stronger isolation means slower cross-tenant reporting and multiplied migration and backup work.
• Related Topics: row-level security, connection pooling per tenant, schema migrations at scale, compliance-driven isolation.

Foundations

The simplest pattern, shared schema with a tenant_id column, puts every tenant's rows in the same tables, distinguished only by a tenant_id value.

CREATE TABLE projects (
  tenant_id   uuid NOT NULL REFERENCES tenants (tenant_id),
  project_id  bigint GENERATED ALWAYS AS IDENTITY,
  name        text NOT NULL,
  PRIMARY KEY (tenant_id, project_id)
);

This is the cheapest pattern to operate, because one migration, one backup, and one connection pool serve every tenant at once.

Schema-per-tenant moves one level up the spectrum by giving each tenant a dedicated PostgreSQL schema with duplicated table DDL, gaining namespace isolation at the cost of running every migration once per tenant schema.

Database-per-tenant moves further still, giving each tenant a fully separate database with independent backup, restore, and upgrade cycles, which is the strongest isolation PostgreSQL offers within a single cluster.

Every step along this spectrum, from shared schema through database-per-tenant, buys stronger blast-radius containment in exchange for more infrastructure to operate and more places for a migration to fail.

Mechanics & Interactions

Shared-schema tenancy places the entire correctness burden on every query remembering to filter by tenant_id, which is a fragile guarantee to depend on for something as serious as cross-tenant data leakage.

Row-level security (RLS) exists to make that guarantee structural instead of conventional, by attaching a policy directly to the table that PostgreSQL enforces regardless of whether the application query remembered a WHERE tenant_id = ... clause.

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
 
CREATE POLICY projects_tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

RLS is a safety net layered on top of application-level filtering, not a substitute for it, because a policy still depends on the session correctly setting app.tenant_id for every connection.

The noisy neighbor problem is the shared-schema pattern's characteristic failure mode: one tenant's unusually large workload consumes disproportionate CPU, I/O, or lock contention, degrading performance for every other tenant sharing the same tables and connection pool.

Detecting it requires per-tenant observability, such as grouping pg_stat_statements or audit logs by tenant_id, since PostgreSQL itself has no native concept of a tenant to isolate resources by.

Schema-per-tenant and database-per-tenant reduce noisy-neighbor risk structurally, because each tenant's data and, in the database-per-tenant case, connection pool, are physically separated from every other tenant's.

Advanced Considerations & Applications

Compliance requirements often force the isolation decision rather than leave it purely as a performance trade-off.

A regulated enterprise customer may contractually require that its data never shares physical storage or a connection pool with any other tenant, which effectively mandates database-per-tenant regardless of what shared-schema economics would otherwise suggest.

Many real SaaS products do not pick one pattern for the whole system; they run shared-schema plus RLS for most tenants and reserve schema-per-tenant or database-per-tenant for an enterprise tier willing to pay for that isolation.

That tiered approach means the connection-routing layer, not just the schema layer, has to know which pattern a given tenant uses, which adds real application complexity in exchange for serving both ends of the market from one product.

Connection pooling also has to be reconsidered at each point on this spectrum, since PgBouncer pools per database, not per schema, so schema-per-tenant tenants still share a pool while database-per-tenant tenants need pool-per-database or dynamic routing.

Common Misconceptions

• "Row-level security replaces the need to filter by tenant_id in queries." RLS is a safety net that catches missing filters, but relying on it alone still depends on the session correctly setting the tenant context, so application-level filtering remains the primary defense.
• "Schema-per-tenant scales to any number of tenants just as well as shared schema." Running one migration per tenant schema becomes operationally heavy well before it becomes technically impossible, which is why this pattern is usually reserved for a bounded, mid-market tier rather than thousands of self-serve tenants.
• "Database-per-tenant is only about security." It is also a scaling and isolation tool independent of compliance, letting one tenant's heavy workload be scaled or restored without touching any other tenant.
• "You have to pick one pattern for the whole product." Many production SaaS systems mix patterns by tenant tier, running shared-schema for most customers and stronger isolation only for the tenants that need or pay for it.
• "A tenant_id column is enough by itself, with no other precautions." Without RLS or equivalent enforcement, a single missing WHERE tenant_id = ... clause in any query path is a cross-tenant data leak, which is why shared-schema tenancy is considered the weakest link on this spectrum without additional safeguards.

FAQs

Related

• Multi-Tenant Basics - hands-on examples across the isolation spectrum
• Shared Schema + tenant_id - indexing and query patterns for the shared pattern
• Row-Level Security Tenancy - policy templates and enforcement details
• Schema-per-Tenant - namespace isolation trade-offs
• Database-per-Tenant - enterprise-grade isolation
• Data Modeling Foundations - the modeling process a tenancy decision fits into

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17) and PgBouncer 1.x.