---
title: "PostgreSQL Multi-Tenant Routing: Logical Replication Slots, PgBouncer Affinity, and the 50K Write Ceiling"
published: true
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: postgresql, architecture, api, cloud
canonical_url: https://mvpfactory.co/blog/postgresql-multi-tenant-routing-sharding
---
## What You Will Build
By the end of this tutorial, you will have a production-grade mental model — and working patterns — for multi-tenant PostgreSQL routing that scales past 50K tenants. We will cover the two isolation strategies, how to manage logical replication slots safely, how to configure PgBouncer for tenant affinity, and exactly where write amplification math forces you into horizontal sharding. Let me show you a pattern I use in every project.
## Prerequisites
- PostgreSQL 14+ in production or locally via Docker
- PgBouncer configured and running
- Familiarity with connection pooling concepts
- A multi-tenant app or one you're designing for scale
---
## Step 1 — Choose Your Isolation Strategy Early
Here is the minimal setup to get this working. You have two routing strategies at the PostgreSQL level.
| 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 |
Schema-per-tenant gives you `tenant_abc.orders` — clean isolation, simple queries. Row-level with RLS keeps operational overhead low at small scale. The right call at sub-10K tenants is a shared cluster with application-layer routing. The mistake is not planning the exit ramp.
---
## Step 2 — Manage Logical Replication Slots Correctly
Each logical replication slot retains WAL segments on the primary until its consumer acknowledges them. With per-tenant slots at 10K tenants, this becomes a disk and I/O crisis fast.
sql
-- 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';
With 1,000 active slots and a consumer lagging 30 seconds under write spikes, you accumulate gigabytes of retained WAL. The docs do not make this obvious, but **use a single slot with a WAL consumer that fans out by `tenant_id` in application code**. Never create per-tenant slots unless consumers are guaranteed low-latency.
---
## Step 3 — Configure PgBouncer for Tenant Context
Transaction-mode pooling maximises connection reuse, but multi-tenant workloads often rely on session-level state: `SET app.current_tenant_id`, `SET search_path`, or RLS context. This is why `server_reset_query` exists.
ini
pgbouncer.ini
server_reset_query = RESET ALL; SET search_path = public;
In session mode, this fires before a connection returns to the pool, clearing tenant-specific state. In transaction mode, you set context at the start of every transaction — 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 |
---
## Step 4 — Abstract Connection Routing on Day One
Build the indirection layer early, pay nothing until you need it. Your application should never hold a raw connection string.
python
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)
When you add shard 2, you update `SHARD_MAP`. No application code changes. Retrofitting shard awareness into direct connection strings is the most expensive migration you will do.
---
## Gotchas
Here is the gotcha that will save you hours.
**Write amplification will surprise you.** At 50K active tenants with an average of 10 writes/second per tenant, that is 500K writes/second through a single PostgreSQL primary. Even on high-end NVMe with `max_wal_size`, `checkpoint_completion_target`, and `synchronous_commit = off` for non-critical writes, you hit I/O saturation. Autovacuum cannot keep pace with dead tuple accumulation. Teams spend weeks chasing bad queries or missing indexes — it was never that.
**Instrument early.** 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. The practical inflection point lands between 30K and 50K write-active tenants on shared infrastructure. Past that, horizontal sharding is not optional.
---
## Conclusion
Application-level tenant routing gets you surprisingly far — schema-per-tenant to around 20K, row-level RLS to around 50K. Beyond that, the write amplification math is unambiguous. The three moves that keep this manageable: centralise your CDC consumer behind a single replication slot, instrument dead tuple accumulation before you need it, and abstract connection routing into a tenant-aware pool layer from day one.
**Further reading:**
- [PostgreSQL Logical Replication docs](https://www.postgresql.org/docs/current/logical-replication.html)
- [PgBouncer configuration reference](https://www.pgbouncer.org/config.html)
- [PostgreSQL Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)
Top comments (0)