Scaling PostgreSQL to Terabytes: Read Replicas, PgBouncer, and Connection Pooling

1. Why PostgreSQL Needs Connection Pooling

Every PostgreSQL connection is backed by a dedicated OS process (the backend), not a lightweight thread. Forking that process, initializing its local memory structures, and attaching it to shared_buffers costs real time and RAM — typically several megabytes of resident memory per idle connection, before it has run a single query. An application server opening hundreds of connections per instance, multiplied across horizontally scaled app servers, can exhaust max_connections or, worse, the host's actual RAM long before the database is CPU or I/O bound.

The failure mode is counterintuitive: adding more connections past a certain point makes throughput worse, not better, because backends contend for the same finite CPU cores and shared_buffers locks. PostgreSQL's own documentation and most operational experience converge on a sweet spot connection count close to (2 × CPU cores) + effective spindle count for the actual query-executing connections — far lower than the hundreds or thousands an application naively wants to open.

Pooling solves this by decoupling the number of application-level logical connections from the number of physical backend processes PostgreSQL actually maintains. A pooler like PgBouncer sits between the application and PostgreSQL, multiplexing many client connections onto a much smaller set of real server connections, keeping the database's process count stable regardless of how many app instances or worker threads are opening connections upstream.

This matters more, not less, as you scale out — the moment you introduce read replicas and multiple app services, the naive connection count multiplies across every service × every replica, and without pooling you hit max_connections limits well before you hit meaningful load, forcing you into either an oversized max_connections (which risks OOM under a real traffic spike) or connection errors during routine deploys.

2. Read/Write Query Splitting with Read Replicas

PostgreSQL streaming replication ships the write-ahead log (WAL) from a primary to one or more standby servers, which replay it to stay in sync. Because this is asynchronous by default, a replica is always some distance behind the primary — usually milliseconds under normal load, but capable of growing to seconds or more under write bursts, long-running transactions on the replica, or network hiccups. Any architecture that routes reads to replicas must be built around this lag as a first-class fact, not an edge case.

The practical split is: writes (INSERT/UPDATE/DELETE, and any read that must see its own prior write) go to the primary; reads that can tolerate slightly stale data go to replicas. This works cleanly for dashboards, search indexes, reporting queries, and most GET-style API endpoints, but breaks for read-your-writes patterns — a user submitting a form and immediately being redirected to a page that reads back what they just wrote is the classic bug, where the replica hasn't caught up yet and the user sees stale or missing data.