Scenario · Connections & Pooling
PgBouncer pool exhausted
A sandboxed PostgreSQL incident — investigate with your own tools, submit a fix, and get deterministic Detect / Fix / Trap scoring.
L3 · 10–15 min · runs locally in Docker
Launch
Start this scenario
Boot it in a real PostgreSQL sandbox and investigate with psql, EXPLAIN and pg_stat_statements.
ride postgres start stage-03/04-pgbouncer-pool-exhaustedPart of these paths
Show the postmortem & investigation hints spoilers
PgBouncer pool exhausted Type: incident simulation · Topic: Connections & Pooling · Level: L3 · Duration: 10–15 min Launch: ride postgres start stage-03/04-pgbouncer-pool-exhausted POSTMORTEM (root cause · how it was found · the fix · lesson) Root cause: PostgreSQL had plenty of connection slots free, but PgBouncer's transaction pool was saturated — a few clients held transactions open, occupying all the pool's server connections, so everyone else queued (cl_waiting > 0). The hang was at the pooling layer, not the database. How it was found: pg_stat_activity showed Postgres far from max_connections; PgBouncer's SHOW POOLS showed all server connections busy and clients waiting. The mitigation: free the pool by ending the transactions holding its server connections (terminate the pool_holder backends). cl_waiting then drops to 0. Lesson: "Postgres isn't full but the app can't connect" points at the pooler. Use SHOW POOLS / SHOW CLIENTS / SHOW SERVERS to diagnose; fix with an appropriate default_pool_size and short transactions — not by raising Postgres max_connections or adding indexes. INVESTIGATION HINTS (the staged path to diagnose and fix) 1. The app hangs, but PostgreSQL is NOT at max_connections — check pg_stat_activity, there's plenty of headroom. The bottleneck is one layer up, in PgBouncer. 2. Connect to the PgBouncer admin console (psql to the pgbouncer database) and run SHOW POOLS. cl_waiting > 0 with all server slots busy means the pool is saturated, not Postgres. 3. A few transactions are holding the pool's server connections open. Free them (terminate the pool_holder backends from the direct Postgres connection); SHOW POOLS cl_waiting drops to 0. The real fix is a bigger/correct pool size and short transactions.