Scaling Postgres is mostly about doing these in order — exhaust the cheap options before the expensive ones.
1 · Connection pooling (do this first)
Each Postgres connection is a backend process (~few MB). A few hundred direct connections will exhaust the server. Put PgBouncer in front:
transaction pooling mode gives the best multiplexing — connections are returned to the pool after each transaction.- Caveat: transaction mode breaks session-level features (prepared statements across calls,
SET session state, advisory locks held across statements). Know what your app relies on.
- This alone often 10×'s how many app workers a single Postgres can serve.
2 · Read replicas (scale reads)
- Stream WAL to one or more replicas; route read-only queries there, writes to the primary.
- Mind replication lag — a read immediately after a write may not see it. Route read-your-own-writes traffic to the primary, or wait for the LSN.
3 · Partitioning (scale a single big table)
- Native declarative partitioning by range (time) or list (tenant/region). Great for append-heavy tables like events, transactions, logs.
- Benefits: queries prune to relevant partitions; you can drop old data by detaching a partition instead of a massive
DELETE.
- Keep partition count reasonable — thousands of partitions hurt planning time.
4 · Vacuum & bloat
- Postgres uses MVCC; updates/deletes leave dead tuples that
autovacuum reclaims. Under heavy write load, tune autovacuum to run more aggressively or it falls behind and tables bloat.
- Watch for transaction ID wraparound warnings — that's autovacuum being unable to keep up; it can force a shutdown if ignored.
5 · Sharding (last resort)
- Only when a single primary truly can't hold the write volume or dataset. Sharding adds cross-shard query and transaction complexity.
- Options: app-level sharding by tenant, or Citus to distribute a table across nodes. Pick a shard key that keeps related data co-located and avoids hot shards.
Rule of thumb: pooling + replicas + partitioning carry you a very long way. Most teams reach for sharding far earlier than they need to.