The Connection Pool Setting That Takes Down Production at 2am

A connection pool has a fixed maximum size, and every connection a slow query holds is one that isn't available for the next request. A single query that used to take 40 milliseconds and now takes 4 seconds, because a table grew past where an index still helps, can quietly consume most of a small pool during a traffic spike. The application layer sees this as a wall of timeouts; the database sees a handful of slow queries and otherwise looks fine, which is why teams often start debugging in the wrong place.

PgBouncer's three modes trade off features against pool efficiency, and picking the wrong one for your workload is the most common misconfiguration:

• Session pooling holds a database connection for the life of a client connection. It supports everything Postgres offers, including session-level settings and prepared statements, but gives you the least multiplexing, so it needs close to one database connection per concurrent client.
• Transaction pooling returns the connection to the pool as soon as a transaction commits, which lets a small pool serve a much larger number of concurrent clients. It breaks anything that depends on session state persisting across transactions, including some ORM connection-reuse patterns.
• Statement pooling returns the connection after every single statement and cannot be used with multi-statement transactions at all.

Most web application backends belong on transaction pooling; anything that relies on session-level temp tables or advisory locks usually needs session pooling for at least that code path.

Confirm pool_size is set based on actual concurrent query count rather than a round number copied from a tutorial. Set a query timeout so one slow query cannot hold a pool slot indefinitely. Set a statement_timeout at the database level as a backstop even if the application layer also has one. Enable pool_mode logging temporarily during a load test so you can see queue depth and wait time under realistic traffic instead of guessing from a production incident after the fact.

Most connection pool incidents never show up in a load test because the test hits average traffic, not the specific combination of a slow query and a spike that caused the real incident. Run a load test that deliberately injects one artificially slow query, using a debug endpoint or a query with a forced sleep, while ramping concurrent connections toward the pool's configured maximum. Watch queue depth and timeout rate, not just average latency, since average latency looks fine right up until the pool exhausts completely.

Splitting reads to a replica reduces load on the primary but does not remove connection pool exhaustion as a failure mode, since the replica has its own pool with the same limits. Teams that move to a read replica sometimes remove monitoring from the primary's pool because attention shifted to the replica, and then get surprised when a write-heavy spike exhausts the primary's much smaller pool. Monitor both pools with the same alerting thresholds, not just the one that handles more traffic day to day.

Imagine a table that has grown steadily for two years, and a query that filters on a column which used to be covered well enough by an existing index. Past a certain row count, the query planner switches from an index scan to a sequential scan for that same query, and what used to take under a hundred milliseconds now takes several seconds under real production data volume, even though nothing in the application code changed. Because the pool has a fixed number of slots, that one query now holds a slot roughly forty times longer than before, and a traffic pattern the pool handled comfortably last quarter starts queuing and timing out this quarter. Nobody deployed anything the day it broke, which is exactly why the postmortem for this class of incident usually starts with the database, not the release log.