Multi-tenant PostgreSQL: when sharding becomes unavoidable

Meta description: Deep dive into PostgreSQL connection-level tenant routing using logical replication slots, PgBouncer affinity, and the write amplification math that forces horizontal sharding at 50K tenants.

Tags: backend architecture microservices saas api

TL;DR

Application-level tenant routing with PostgreSQL gets you surprisingly far — until it doesn’t. Logical replication slots give you per-tenant change streams, PgBouncer’s server_reset_query handles connection affinity, but write amplification math makes horizontal sharding unavoidable around 50K active tenants. This post shows you exactly where the ceiling is and how to design past it.

The architecture most teams reach for first

In my experience building production multi-tenant systems, the instinct is always the same: start with a shared PostgreSQL cluster and route tenants at the application layer. It’s the right call at sub-10K tenants. The mistake is not planning the exit ramp.

The typical stack:

Mobile Client → API Gateway → App Server → PgBouncer → PostgreSQL Primary
                                    ↓
                            Tenant Router (schema or row-level)

Two routing strategies at the PostgreSQL level: schema-per-tenant (each tenant gets tenant_abc.orders) or row-level isolation (a tenant_id column on every table with RLS policies). Schema-per-tenant wins on isolation and query simplicity. Row-level wins on operational overhead at small scale — but “small scale” is doing a lot of work in that sentence.

Strategy	Isolation	Migration Complexity	Max Practical Tenants
Schema-per-tenant	High	High (per-tenant DDL)	~20K
Row-level + RLS	Medium	Low	~50K
Logical shard (separate DB)	Very High	Very High	Unlimited

Logical replication slots: the hidden complexity tax

What most teams get wrong about logical replication in multi-tenant systems: slots are not free.

Each logical replication slot retains WAL segments on the primary until its consumer acknowledges them. If you’re building per-tenant change data capture — for audit logs, mobile sync, or event streaming — you’re paying this cost multiplied by active slot count.

-- Creating a per-tenant logical slot
SELECT pg_create_logical_replication_slot(
  'tenant_abc_slot',
  'pgoutput'
);

-- Check WAL lag across all slots
SELECT slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_type = 'logical';

The numbers aren’t subtle. With 1,000 active logical slots and a consumer that lags by even 30 seconds under write spikes, you can accumulate gigabytes of retained WAL. At 10K tenants with per-tenant slots, this becomes a disk and I/O crisis.

Don’t create per-tenant logical slots unless your consumers are guaranteed low-latency. Use a single slot with a WAL consumer that fans out by tenant ID in application code.

PgBouncer connection affinity and `server_reset_query`

Transaction-mode pooling in PgBouncer is the default recommendation — it maximizes connection reuse. But multi-tenant workloads often rely on session-level state: SET app.current_tenant_id, SET search_path, or RLS context.

Session mode feels safe because you don’t have to think about tenant context per transaction. But you’re trading connection density for that comfort, and the tradeoff gets painful fast.

This is why server_reset_query exists:

# pgbouncer.ini
server_reset_query = RESET ALL; SET search_path = public;

In session-mode pooling, this fires before a connection returns to the pool, clearing tenant-specific state. In transaction mode, you set tenant context at the start of every transaction explicitly — more latency per query, but far higher connection density.

Pooling Mode	Tenant State Safety	Max Connections	Recommended At
Session mode	Safe via reset query	~500 server conns	<5K tenants
Transaction mode	Manual, per-txn SET	~5,000 server conns	>5K tenants

The write amplification ceiling

This is where the math gets uncomfortable.

In a row-level isolated schema, every write — even to a single tenant’s row — hits shared indexes, shared WAL, and shared autovacuum pressure. At 50K active tenants with an average of 10 writes/second per tenant, that’s 500K writes/second through a single PostgreSQL primary.

Even on high-end NVMe hardware with PostgreSQL tuned for throughput (max_wal_size, checkpoint_completion_target, synchronous_commit = off for non-critical writes), you hit I/O saturation. Autovacuum cannot keep pace with dead tuple accumulation across 50K tenants writing simultaneously. Table bloat becomes a runaway problem. I’ve seen teams hit this wall and spend weeks chasing bad queries or missing indexes. It wasn’t that.

The inflection point in practice lands between 30K and 50K write-active tenants on shared infrastructure. Past that, horizontal sharding is not optional.

The exit ramp: horizontal sharding design

Design your tenant routing layer to be shard-aware from day one, even if you start single-cluster:

def get_connection(tenant_id: str) -> Connection:
    shard_key = hash(tenant_id) % TOTAL_SHARDS
    cluster = SHARD_MAP[shard_key]  # maps to PgBouncer endpoint
    return pool.connect(cluster, tenant_id)

Your application never holds a raw connection string — it always goes through a routing layer you control. When you add shard 2, you update SHARD_MAP. No application code changes.

Before you need it

Use a single logical replication slot with application-level fan-out. Per-tenant slots create WAL retention risk that compounds with tenant count; centralize your CDC consumer and filter by tenant_id downstream.
Instrument write amplification before you hit the wall. Track pg_stat_user_tables.n_dead_tup per tenant cohort and set autovacuum alerts. The ceiling shows up in dead tuple accumulation weeks before query latency degrades.
Abstract connection routing behind a tenant-aware pool layer on day one. Retrofitting shard awareness into direct connection strings is the most expensive migration you’ll do. Build the indirection layer early, pay nothing until you need it.