Diagnose Connection Pool Exhaustion with PgBouncer

Distinguish pool exhaustion from database overload, read PgBouncer stats to find the thief, and size the pool to the database instead of to peak fear.

~30 mindatabasepostgresoperations

Task

Diagnose waiting clients and pooled connection starvation, identify which deployment consumes the pool, and right-size the configuration.

When to use this

Requests fail with timeout while acquiring a connection. The database itself is nearly idle, CPU at eight percent, yet every client waits and the pool queue grows.

Prerequisites

PgBouncer in front of PostgreSQL with admin console access
Access to per-application credentials or separate pool databases
Authority to change pool sizing and client timeouts

Steps

1. Read the live pool statistics.

psql -p 6432 pgbouncer -c "SHOW POOLS;"

Expected output: Columns cl_waiting and cl_active show hundreds of clients waiting while maxconn is small; the database is the bottleneck only if sv_active is also saturated.

2. Check which database and user consumes the pool.

psql -p 6432 pgbouncer -c "SHOW CLIENTS;" | awk '{print $2, $8}' | sort | uniq -c | sort -rn | head

Expected output: One application user owns nearly every idle client, revealing which deployment leaks or hoards connections.

3. Find connections that never return.

psql -p 6432 pgbouncer -c "SHOW CLIENTS;" | awk '($6 == "idle") {print $2}' | sort | uniq -c

Expected output: A large idle count with no waiters means clients hold connections open; a large waiting count with idle servers means the pool is undersized.

4. Bound client-side idle lifetimes.

const pool = new Pool({
  max: 10,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

Expected output: Each instance holds at most ten server connections and returns idle ones after thirty seconds, so total usage stays predictable.

5. Right-size the server-side pool and verify.

psql -p 6432 pgbouncer -c "RELOAD;" && psql -p 6432 pgbouncer -c "SHOW POOLS;"

Expected output: cl_waiting returns to zero under the same load, and sv_active stays within the limit the database can actually serve.

Troubleshooting

Problem

Prepared statements break through PgBouncer

Solution

Transaction pooling reuses server connections and breaks named prepared statements; use the binary protocol or switch the pool mode per database to session where statements must persist.

Problem

Pool raised but database now saturates

Solution

The pool was hiding concurrency the database cannot handle; lower the pool to protect the database and fix the slow queries that made clients hold connections so long.

Problem

One microservice hoards everything

Solution

Route that service to its own pgbouncer database entry with its own limits so one badly tuned client cannot starve the rest.

Limitations

SHOW stats require the stats users configuration and reset periodically to stay readable.
Transaction pooling changes session semantics; features relying on session state need session mode or redesign.

Idle database, starving clients

When every request times out acquiring a connection but the database CPU sits at eight percent, the problem is between the clients and the server: the pool. PgBouncer admin commands make the invisible queue visible in one query. cl_waiting is the count of angry clients, sv_active the count of genuinely busy server connections, and the gap between them is the story: undersized pool, hoarding client, or leaked connections.

psql -p 6432 pgbouncer -c "SHOW POOLS;"

# Key columns:
#   cl_active  cl_waiting  sv_active  sv_idle  maxused
#       3          214          20         0        20
#  -> pool exhausted while servers are the real limit

Pool to the database, not to fear

A pool larger than what the database can serve converts queueing into contention: every extra connection adds lock waits and context switching to queries that were fine at lower concurrency. The right ceiling is where throughput stops improving, visible as sv_active leveling off while latency stays flat. Bigger is not safer; bigger is slower for everyone.

Per-tenant limits stop the tragedy

One misbehaving service with a huge client-side max can occupy every server slot and starve everyone else. Give each application its own pgbouncer database entry with its own pool ceiling. Isolation turns a whole-platform outage into one noisy service waiting in its own lane.