Diagnostiquer la saturation et la mise en file des pools

PgBouncer multiplexes many client connections onto a small set of server connections. In transaction pooling, a server connection is borrowed only for the duration of a transaction, then returned to the pool.

A pool saturates when every server connection is busy and new client requests must wait in a queue. The queue is invisible from the database's side — Postgres just sees a steady, capped number of backends — so you must read PgBouncer's own stats to see the pressure building.

• pool_size = max server connections per (database, user) pool
• Waiting clients = demand that exceeds that ceiling

PgBouncer exposes a virtual database called pgbouncer. Connect to it with psql using a user listed in admin_users, then run SHOW commands to read its internal state.

This is your primary diagnostic surface — there is no separate dashboard required.

SHOW POOLS is the single most important command for spotting saturation. Each row is one pool, identified by database and user.

• cl_active — clients currently bound to a server connection
• cl_waiting — clients queued, waiting for a free server connection
• sv_active — server connections busy serving a transaction
• sv_idle — server connections free to be assigned

The rule of thumb: any sustained cl_waiting > 0 with sv_idle = 0 means the pool is saturated.

Compare two snapshots. A healthy pool keeps spare idle servers and an empty wait queue:

• cl_active=18 cl_waiting=0 sv_active=6 sv_idle=14 → plenty of headroom
• cl_active=20 cl_waiting=47 sv_active=20 sv_idle=0 → saturated and queueing

In the second case sv_active equals pool_size, sv_idle is zero, and 47 clients are stuck in line. Latency users feel = queue wait + actual query time.

SHOW POOLS also reports maxwait and maxwait_us — the time the oldest waiting client has been queued, in seconds and microseconds.

This is your early-warning metric. A non-zero and growing maxwait means clients are not just queued but starving. If maxwait approaches your application's statement or connection timeout, requests will start failing before users even get a response.

• maxwait = 0 → nobody is waiting right now
• maxwait = 4 and climbing → act now, the pool is too small or the DB is too slow

A single snapshot can mislead — pools fill and drain in bursts. Watch the trend by polling the admin console on an interval and logging the key columns.

From a shell you can loop psql and grep the pool you care about. Rising cl_waiting across samples confirms real saturation rather than a momentary spike.

Saturation has two root causes: too little capacity, or queries that hold server connections too long. SHOW STATS separates them.

• avg_query_time — average query duration in microseconds
• avg_xact_time — average transaction duration; long transactions hog server connections
• avg_query_count — queries per second

If avg_xact_time is high, raising pool_size only delays the problem — fix the slow transactions instead.

PgBouncer tells you clients are waiting; Postgres tells you why the server connections are busy. Query pg_stat_activity on the real database to see what those backends are doing.

Long-running queries, idle-in-transaction sessions, or lock waits will pin the limited server connections and feed the PgBouncer queue.

A frequent cause of PgBouncer queueing is sessions left idle in transaction: the app opened a transaction, then stalled (waiting on an external call, a slow loop, or a bug) without committing. That server connection stays checked out and unavailable to the pool.

Hunt these down explicitly — they often explain a saturated pool whose avg_query_time looks low.

For finer detail, two more admin views drill into individual connections:

• SHOW CLIENTS — every client link; a state of waiting plus a large wait value pinpoints the starving clients
• SHOW SERVERS — every server link and which client (if any) currently owns it

Use these when SHOW POOLS shows queueing and you need to identify exactly which clients or application hosts are affected.

Once the stats point to a cause, the fix follows directly:

• High cl_waiting, low avg_xact_time → genuine capacity shortage; raise pool_size (and check Postgres max_connections has room)
• High avg_xact_time or many idle in transaction → fix the app/queries; more pool size won't help
• maxwait near app timeout → tune query_wait_timeout so clients fail fast instead of hanging
• One pool starved, others idle → consider a dedicated pool or reserve_pool_size

You read SHOW POOLS and see this row for one pool.

You can now diagnose PgBouncer pool saturation before users feel it:

• SHOW POOLS — watch cl_waiting, sv_idle, and maxwait; sustained waiting with zero idle servers means saturation
• SHOW STATS — use avg_xact_time to tell a capacity shortage from slow transactions
• pg_stat_activity — find the long-running and idle in transaction backends pinning your pool
• SHOW CLIENTS / SHOW SERVERS — drill down to the exact affected connections

Act on the cause: add capacity only when queries are fast; otherwise fix the transactions, and tune query_wait_timeout so clients fail fast instead of hanging.