Pool exhaustion and idle-in-transaction: the 3 AM failure mode

At 02:14 the PgBouncer alert fires: pool = 24, cl_waiting = 180. The app is returning 503s. pg_stat_activity shows 19 backends in state “idle in transaction” with max age 8 minutes. The pool was sized correctly this morning.

What pool exhaustion actually means

Pool exhaustion happens when every backend in the pool is checked out and no new connection can be borrowed. At the application layer: pool.acquire() times out or is rejected. At the Postgres layer: the backends behind those checked-out connections are in one of these states:

• active — running a slow query or waiting on a lock
• idle in transaction — between statements inside a BEGIN that has not COMMITted or ROLLBACKed
• idle — genuinely idle (then why is the pool full? answer: wrong pool_size setting or connection leak)

The pool is exhausted in the sense that no slot is free. The database is not exhausted — it is waiting on the application.

Four root causes

1. Long-running queries — a missing index makes a query take 5 s; at 1,000 QPS that is 5,000 connections in flight. Fix: EXPLAIN ANALYZE, add the index, paginate large scans.

2. Idle-in-transaction — the most common. An application opened a transaction (BEGIN) and did not COMMIT or ROLLBACK: an unhandled exception, an external API call inside the transaction, a missing try/finally. The backend sits “idle in transaction” holding locks, an MVCC snapshot, and a pool slot.

3. Traffic burst — demand exceeds steady-state pool sizing. Fix: reserve_pool_size in PgBouncer, autoscaling at the application layer, or queue-and-degrade (return 503 with Retry-After before pool exhaustion).

4. External dependency inside a transaction — a Stripe charge call, an S3 upload, a webhook delivery all inside BEGIN…COMMIT. The transaction holds open for the duration of the external call. If that service slows down, every in-flight transaction holds a pool slot for seconds to minutes.

Idle-in-transaction is a double failure

An idle in transaction backend holds:

1. A pool slot — visible immediately; causes pool exhaustion
2. Row locks — any locked rows cannot be written by other transactions
3. An MVCC snapshot — VACUUM cannot clean dead tuples older than this backend’s xmin; tables bloat

One bug that opens a transaction and forgets to commit on an error path can drain a 20-connection pool in seconds and cause table bloat that develops over hours.

Diagnosing pool exhaustion in five steps

The safety net: idle_in_transaction_session_timeout

Setting this Postgres GUC kills any backend that stays in idle in transaction past the threshold:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

With this set, the Stripe outage scenario from the hook becomes: Stripe timeouts cause individual transaction aborts (logged, alertable) rather than pool exhaustion. The pool survives; the application logs the 504s and retries.

This is a free safety net. Every production Postgres deployment should have it. Set it to a value above your slowest legitimate transaction (typically 30–120 s for OLTP).