PostgreSQL — Scalability

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.