Why Your Postgres Is Idle But Your API Is Timing Out (PgBouncer Pool Exhaustion, Explained)

Ever had an incident where latency on a trivial SELECT spikes from a few milliseconds to several seconds, CPU on the Postgres box is basically flat, and there's no slow query or missing index anywhere in sight?

That's not a database problem. That's connection pool exhaustion at the PgBouncer layer — and it's one of the most common (and most misdiagnosed) production incidents in Postgres-backed systems.

I just wrote a deep dive on this: how it happens, how to diagnose it live, and how to size your pool correctly instead of guessing. Here's the short version.

The core issue

PgBouncer sits between potentially thousands of client connections and a small, fixed number of physical Postgres backends. That works fine — until something makes transactions hold connections longer than expected: a slow downstream API call, a lock wait, a runaway transaction. When that happens, the queue of waiting clients grows unbounded, and every request behind it pays the tax, even the fast ones.

Pooling mode matters more than people think

• Session pooling — 1:1 mapping, safest for compatibility, but a pool of 100 connections supports at most 100 concurrent clients. Full stop.
• Transaction pooling — what most production setups run. Connections are freed the instant a transaction commits, so you can multiplex hundreds of clients onto a handful of backends. The catch: anything session-scoped (SET search_path, prepared statements, temp tables) becomes unreliable.
• Statement pooling — most aggressive, breaks multi-statement transactions, mostly used for read-only analytics. Transaction mode is where exhaustion gets sneaky, because the pool looks elastic. Teams assume Postgres's max_connections is the real ceiling — it isn't. The real ceiling is how many transactions your application can hold open simultaneously.

Diagnosing it live

Two commands tell you almost everything:

psql -h 127.0.0.1 -p 6432 -U pgbadmin pgbouncer -c "SHOW POOLS;"

If cl_waiting is climbing while sv_active is pinned at pool_size, that's the signature: every server slot is occupied and clients are queuing.

Then cross-reference with Postgres directly to find out what's holding those slots hostage — usually a transaction left open across a network call (a classic ORM anti-pattern), or a lock wait cascading from a migration.

The usual culprit: idle-in-transaction leakage

If your app opens a transaction, does a read, then awaits an external HTTP call before committing, that connection is unusable by anyone else for the entire duration of that call. Under load, this single pattern causes more exhaustion incidents than raw traffic volume ever does.

Sizing the pool properly

Little's Law gives a good starting point:

required_connections = arrival_rate × average_transaction_duration

But size for the tail, not the average — one slow query can distort your numbers badly. And resist the urge to just crank default_pool_size up; more Postgres backends means more memory overhead and more lock contention, not a free win.

---

I go into a lot more depth in the full article — reference PgBouncer config, the exact diagnostic queries, failover edge cases, and the security trade-offs of exposing the admin console. Worth a read if you've ever been paged for a "database is slow" incident that turned out not to be the database at all.

👉 Full article: Diagnosing PgBouncer Pool Exhaustion Under Load

Curious if others have run into the prepared-statement-caching gotcha under transaction pooling — that one bit me hard the first time I hit it. Drop your war stories below.