The Connection Pooling Setup That Keeps Postgres From Falling Over Under Load
Prevent Postgres connection exhaustion by putting a pooler such as PgBouncer between your application and the database, using transaction pooling, and sizing the pool close to the CPU cores available to Postgres. Without pooling, each request opens its own connection, and a traffic spike can lock every request out even though the database itself is fine.
A connection pooler sits between your application and Postgres and reuses a fixed set of connections across many client requests, which is what actually prevents this. Getting the configuration wrong, though, just moves the failure mode somewhere else instead of removing it.
Why Postgres runs out of connections faster than you'd expect
Each Postgres connection is a full operating system process with its own memory overhead, which is why the default max_connections setting is usually a few hundred, not a few thousand. A service that opens a new connection per request, or per worker thread without pooling, can exhaust that ceiling with surprisingly little traffic, especially once you're running multiple application instances that each maintain their own connection pool against the same database.
The failure is also asymmetric: connection exhaustion doesn't degrade gracefully, it fails hard. There's no slow-down phase where requests just take longer; once the limit is hit, new connection attempts are refused outright, which is why this incident tends to look like a sudden full outage rather than a gradual one.
How PgBouncer changes the shape of the problem
PgBouncer holds a small pool of real connections to Postgres and multiplexes many client connections across them, so your database sees a fraction of the connection count your application layer generates. In transaction pooling mode, a connection is only held for the duration of a single transaction, then returned to the pool for the next request, which is what lets a pool of twenty real connections comfortably serve hundreds of concurrent clients.
The tradeoff is that transaction pooling mode doesn't support every Postgres feature; session level features like prepared statements or advisory locks that need to persist across transactions won't behave the same way. Know which parts of your codebase rely on those features before switching pooling modes, not after.
Sizing the pool without guessing
A pool sized too small just recreates the original problem one layer down, with requests queuing for a pooled connection instead of a database one. A pool sized too large defeats the purpose, since Postgres still has to manage that many real connections underneath. A reasonable starting point is to size the pool close to the number of CPU cores available to Postgres, then adjust based on actual queue wait time rather than a fixed formula.
Watch PgBouncer's own stats views, not just your application's error rate. A client wait time that's climbing even though your database CPU is idle usually means the pool itself is undersized, not that the database needs more resources.
Safeguards worth building in before the next spike
Set a statement timeout on the pooled connections so a single slow query can't hold a connection indefinitely and starve the rest of the pool behind it. Separate pools for different workloads, like a small pool for a background batch job and a larger one for user facing traffic, so a batch job's connection usage can't crowd out requests that need to respond in milliseconds.
Alert on pool utilization, not just on connection refusals. By the time you're seeing refused connections, clients are already failing; an alert at seventy percent pool utilization gives you time to investigate before customers notice.
Testing the failure before it happens in production
Most teams find out their pool sizing is wrong during an actual incident, which is the most expensive way to learn it. Run a load test against a staging environment that deliberately pushes past your expected peak traffic, and watch what happens to client wait time as the pool saturates, not just whether the test eventually passes.
Include a slow query in that test on purpose, a deliberately unindexed query or an artificial delay, since a single slow query holding a pooled connection is a more common trigger for exhaustion in practice than raw traffic volume alone. If your statement timeout and pool sizing can't survive that combination in staging, they won't survive it in production either.
A configuration checklist for a pooled Postgres setup:
- The pooler runs in transaction pooling mode, unless the code depends on session-level features such as prepared statements or advisory locks.
- The pool size starts close to the number of CPU cores available to Postgres and is adjusted using measured client wait time.
- A statement timeout on pooled connections stops one slow query from holding a connection indefinitely.
- Separate pools keep background batch jobs from crowding out user-facing traffic.
- A staging load test pushes past expected peak traffic, including a deliberately slow query, while watching client wait time.
What Good Looks Like
A connection pooling setup that holds under load means the pool is sized from measured wait time, statement timeouts prevent a single slow query from starving it, and alerts fire on utilization before connections start being refused.
Building The Capability (5-Stage Skill Ladder)
How to Get Started
Frequently Asked Questions
Why does connection exhaustion fail as a sudden full outage instead of gradual slowdown?
Because Postgres either has a free connection slot to hand out or it doesn't. There's no intermediate state where connections get slower; once max_connections is reached, new connection attempts are refused immediately, which is why this failure mode tends to look like everything broke at once.
Is transaction pooling mode always the right choice for PgBouncer?
It's the right default for most application traffic, but check for dependencies on session level features first, like prepared statements or advisory locks that need to persist across multiple transactions. If your codebase relies on those, session pooling mode or a targeted separate pool may be necessary for that specific traffic.
How big should our connection pool be?
Start close to the number of CPU cores available to your Postgres instance, then adjust based on measured client wait time rather than a fixed rule. A pool that's too small just moves the queuing problem in front of the pooler instead of solving it.
About the numbers
This guide doesn't quote a sourced benchmark. Figures in it are estimates or general guidance, so check them against your own numbers.
Related Guides
PgBouncer in Production: A Connection Pooling Checklist
Why Postgres runs out of connections before it runs out of CPU, and a rollout checklist for putting PgBouncer in front of it safely.
The Connection Pool Setting That Takes Down Production at 2am
Why connection pools exhaust under load, how PgBouncer's pool modes actually differ, and the four settings worth checking before your next incident.
PgBouncer Pool Sizing: A Runbook Before Your Next Deploy Storm
How to size a PgBouncer pool, pick a pooling mode, and stop connection storms during deploys from taking down your database.
Blue-Green, Canary, or Rolling: Deploying Stream Processors
A decision guide to rolling, blue-green, and canary deploys for stateful stream processors, plus the rollback plan most teams never actually test.
The Connection Pool Checklist Most Teams Skip Until an Outage
A pre-flight checklist for database connection pooling that catches the pool-exhaustion mistakes most teams only discover during a production outage.
What Actually Happens When Your Database Runs Out of Connections
A step-by-step walkthrough of how connection exhaustion happens, why adding more app servers makes it worse, and how a pooler like PgBouncer fixes it.