PostgreSQL multitenancy: schema vs partition at scale

Meta description: Compare schema-per-tenant, partition-based isolation, and separate databases for PostgreSQL SaaS multitenancy. Includes pg_bouncer routing and migration strategies for 10k+ tenants.

Tags: backend saas architecture microservices api

---

TL;DR

Row-Level Security is not the only path to tenant isolation in PostgreSQL. At scale, partition pruning and schema separation outperform RLS on query latency, operational simplicity, and connection efficiency — but each strategy has a ceiling. Pick the right model for your tenant count, and make sure pg_bouncer is handling your connection pool before it becomes the thing that kills the product.

---

The problem most SaaS teams ignore until it’s too late

In my experience building production systems, teams default to a shared schema with a tenant_id column and slap RLS policies on every table. It works at 50 tenants. At 5,000, you are debugging query planner decisions at 2 AM.

RLS adds predicate evaluation overhead on every row scan. Partition pruning eliminates entire physical segments before the planner touches a single row. That is not a micro-optimization — it is a structural difference in how PostgreSQL executes the query.

Three architectures are worth considering.

---

Comparing the three isolation models

Strategy	Tenant ceiling	Query isolation	Migration complexity	Connection overhead
Shared schema + tenant_id	~500 tenants	Low (RLS required)	Low	Low
Schema-per-tenant	~1,000–2,000 tenants	High	Medium	Medium
Partition-per-tenant	~5,000–10,000 tenants	High (planner-level)	Medium	Low
Separate database	Unlimited	Complete	High	High

Schema-per-tenant

Each tenant gets its own PostgreSQL schema (tenant_acme.orders, tenant_beta.orders). The application sets search_path at connection time.

-- Connection setup per tenant
SET search_path TO tenant_acme, public;

-- Query hits only tenant_acme.orders — no predicate needed
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days';

What most teams miss: schema proliferation bloats pg_class and pg_attribute. At 2,000 schemas with 50 tables each, catalog scans slow down DDL operations significantly. Plan migrations carefully — ALTER TABLE across 2,000 schemas requires tooling, not manual SQL.

Partition-based isolation

Declarative partitioning by tenant_id lets the query planner prune irrelevant partitions before execution. With enable_partition_pruning = on (default in PostgreSQL 11+), the planner excludes non-matching partitions entirely.

-- Parent table
CREATE TABLE orders (
    id BIGSERIAL,
    tenant_id INT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    total NUMERIC
) PARTITION BY LIST (tenant_id);

-- Per-tenant partition
CREATE TABLE orders_tenant_42
    PARTITION OF orders
    FOR VALUES IN (42);

At query time, EXPLAIN ANALYZE will show Partitions excluded rather than filtered rows. That is the difference between skipping IO entirely and filtering it in memory. For read-heavy SaaS workloads, this matters.

The practical ceiling is roughly 10,000 partitions before the planner’s partition selection overhead becomes measurable. PostgreSQL 14+ improved this substantially, but it remains a real consideration.

---

The connection routing layer: pg_bouncer is not optional

Schema switching and partition routing both require that the right connection reaches the right database context. At 10,000 tenants, each with burst traffic, native PostgreSQL connections (one process per connection) will exhaust memory well before you hit query bottlenecks.

pg_bouncer in transaction-mode pooling solves this. A single pg_bouncer instance can multiplex thousands of logical client connections onto a small pool of actual PostgreSQL backends.

# pgbouncer.ini — transaction pooling for schema-per-tenant
[pgbouncer]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 25
server_reset_query = RESET ALL; SET search_path TO public;

The server_reset_query is critical. Without it, a connection released by tenant_acme retains that search_path and leaks into the next tenant’s transaction. This is the class of bug that only appears in production under load.

---

Migration strategy at scale

Schema-per-tenant migrations require automation. The pattern that holds up in production:

1. Generate migration SQL once
2. Iterate over all schemas in information_schema.schemata where schema_name LIKE 'tenant_%'
3. Execute in batches with explicit transaction boundaries per schema
4. Track completion state in a separate admin table

Never run schema migrations in the application startup path at this scale. Use a dedicated migration runner with rollback capability and per-tenant status tracking.

---

Three things worth getting right early

First, choose your isolation model before you have tenants, not after. Migrating from shared-schema to partition-based isolation at 1,000 tenants is a multi-week project. The architectural decision is cheap at day one and expensive at year two.

Second, deploy pg_bouncer in transaction mode from the start. Connection count is the first thing that breaks at scale, and retrofitting a pooler into an existing system is harder than building around it from the beginning.

Third, benchmark partition pruning with your actual tenant distribution — not a synthetic one. Uniform tenant sizes favor partitioning cleanly. Highly skewed tenants, where 10 accounts drive 80% of traffic, may need hybrid approaches: dedicated resources for large tenants, partitioned shared infrastructure for the long tail. The math looks different when your biggest customer is 50x the size of your median one.