$ guides / pgbouncer / pgbouncer-sv-idle-zero ▌

Operations Guides

PgBouncer sv_idle at zero: no headroom and one slow query from a cascade

Your PgBouncer dashboards look green. cl_waiting is zero, maxwait is zero, no clients are queuing, no errors in the log. But SHOW POOLS tells a different story: sv_idle is 0 and sv_active equals pool_size. Every server connection in the pool is checked out. Nothing is waiting yet, but nothing is available either.

This is the “looks green, is actually yellow” state, and it is one of the most dangerous steady states a connection pooler can sit in. The next request that arrives while all connections are busy queues immediately. There is no buffer, no graceful degradation. PgBouncer’s saturation curve is cliff-edge: below 100% utilization, assignment latency is effectively zero; at 100%, latency jumps to unbounded FIFO queuing.

If your monitoring only watches cl_waiting, you will never see this state. The first signal you get is the incident itself: waiters appear, maxwait climbs, application timeouts fire, retries multiply the queue. This article is about catching the condition before that happens, figuring out why headroom disappeared, and getting it back.

What this means

Each PgBouncer pool (one per (database, user) pair) holds server connections in several states: sv_active (executing for a client), sv_idle (connected to PostgreSQL, immediately reusable), sv_used (idle but used at least once, still considered good), sv_tested (running server_check_query or server_reset_query), and sv_login (authenticating). The sum across these states is bounded by pool_size, plus whatever the reserve pool adds.

sv_idle = 0 with sv_active = pool_size means every slot is checked out and the ready reserve is empty. In transaction pooling mode, sv_idle is not waste: it is the inventory that absorbs the next burst. When inventory hits zero, the system has no shock absorber left. A single slow query, one lock wait, one idle-in-transaction session, and the pool tips from “fully utilized” to “queuing” with no warning in between.

flowchart TD
  A[sv_idle trending toward zero over days] --> B[sv_idle = 0, sv_active = pool_size]
  B --> C{Next request arrives}
  C -->|a connection frees in time| D[assigned instantly, state persists]
  C -->|all connections busy| E[cl_waiting = 1, maxwait starts]
  E --> F[application timeout fires]
  F --> G[client retries, new waiter joins queue]
  G --> H[queue grows faster than it drains: cascade]
  D --> B

The important distinction: this is not yet pool exhaustion. It is the precondition for pool exhaustion. Pool exhaustion is the cascade after the cliff. Zero headroom is standing at the edge of it.

Common causes

Cause	What it looks like	First thing to check
Organic traffic growth	`sv_idle / pool_size` declining slowly over days or weeks, `avg_query_time` flat	Trend of peak `sv_active` against `pool_size` over the last month
Backend queries getting slower	`avg_query_time` and `avg_xact_time` elevated above baseline, connections held longer	`SHOW STATS_AVERAGES` for query time vs baseline; PostgreSQL-side slow query sources
Idle-in-transaction sessions	`avg_xact_time` much larger than `avg_query_time` (10x or more)	`pg_stat_activity` on PostgreSQL for `idle in transaction` state
Long-running analytical or batch queries	One or a few `sv_active` connections with very old `request_time`	`SHOW SERVERS` for active connections sorted by `request_time`
Session pooling mode	`sv_active` tracks connected clients rather than concurrent work; headroom maps to session count, not query turnover	`SHOW DATABASES` for `pool_mode`; zero idle headroom may be inherent to the mode when client count sits at `pool_size`
Pool simply undersized	Zero headroom at every peak, brief `cl_waiting` blips already appearing	Peak demand vs `pool_size`; see pool utilization high
Reserve pool masking undersizing	Total server connections exceed `pool_size`, log shows “taking connection from reserve_pool”	Compare sum of `sv_*` per pool against `pool_size` from `SHOW DATABASES`

Quick checks

All commands run against the PgBouncer admin console. Adjust host, port, and user for your deployment.

# Per-pool snapshot: the state you are diagnosing
psql -h /var/run/postgresql -p 6432 -U pgbouncer pgbouncer -Atc "SHOW POOLS;"

Look at sv_active, sv_idle, cl_waiting, and maxwait per pool. You are confirming sv_idle = 0, sv_active = pool_size, cl_waiting = 0. Note which (database, user) pools are affected; it is often one pool, not all.

# Configured pool size per database
psql -h /var/run/postgresql -p 6432 -U pgbouncer pgbouncer -Atc "SHOW DATABASES;"

Compare pool_size here against the sv_active counts from SHOW POOLS. If total server connections exceed pool_size, the reserve pool is being drawn and the base pool is chronically undersized.

# Wait time and query time averages: is the backend the reason connections are held?
psql -h /var/run/postgresql -p 6432 -U pgbouncer pgbouncer -Atc "SHOW STATS_AVERAGES;"

Compare avg_wait_time (should still be near zero in this state) against avg_query_time and avg_xact_time. If avg_xact_time is many times avg_query_time, idle-in-transaction is eating your headroom.

# Which server connections have been checked out the longest
psql -h /var/run/postgresql -p 6432 -U pgbouncer pgbouncer -Atc "SHOW SERVERS;"

Look for active connections with old request_time. Those are the connections holding pool slots. The link column ties each one back to a client.

# Rule out administrative state before treating this as an incident
psql -h /var/run/postgresql -p 6432 -U pgbouncer pgbouncer -Atc "SHOW DATABASES;" | awk -F'|' '$(NF-1)==1 || $NF==1'

In SHOW DATABASES output the last two columns are paused and disabled; this prints any database where either is set. A paused database distorts every other signal, and alerting on pool state during maintenance is a classic false positive.

How to diagnose it

Confirm the state per pool. From SHOW POOLS, identify which pools have sv_idle = 0 and sv_active = pool_size with cl_waiting = 0. A single saturated pool coexisting with healthy pools points to a workload or sizing problem scoped to one (database, user) pair, not a global event.
Establish whether this is new or chronic. If you have historical per-pool metrics, look at the sv_idle / pool_size ratio over days to weeks. A slow decline toward zero is organic growth or gradual backend degradation. A sudden drop to zero is a specific event: a deploy, a new query pattern, a batch job.
Attribute the hold time. Check avg_query_time vs avg_xact_time in SHOW STATS_AVERAGES. If both are elevated proportionally, the backend is slower and connections are held longer per unit of work. If avg_xact_time dwarfs avg_query_time, the connections are being held while doing nothing: idle-in-transaction. Confirm on the PostgreSQL side with pg_stat_activity filtered on state = 'idle in transaction'.
Find the specific holders. In SHOW SERVERS, sort active connections by request_time. One or two connections checked out for minutes while the rest turn over in milliseconds means a small number of heavy queries is consuming a large share of pool capacity. Five analytical queries in a pool of 20 is 25% of capacity gone to background work.
Check pool mode. In session pooling mode, each connected client holds a server connection until disconnect, so sv_active tracks connected clients rather than concurrent work; low or zero sv_idle can therefore be the steady state while every session slot is in use. After sessions disconnect, server_idle_timeout (default 600s) governs how long their former server connections remain idle. The headroom levers are different there: you size for concurrent sessions, not for transaction turnover.
Check for reserve pool draw. If the sum of all sv_* states exceeds pool_size, the reserve pool is active. Reserve connections only get created after a client has waited longer than reserve_pool_timeout (default 5s), so sustained reserve usage means waiters already happened and the base pool is undersized.

Metrics and signals to monitor

Signal	Why it matters	Warning sign
`sv_idle / pool_size` per pool	The headroom ratio itself; the early warning this article exists for	Trending toward zero over days; sustained zero at any time
`sv_active / pool_size` per pool	Pool utilization; at 100% the next request must queue	Above 85% sustained; see the utilization guide
`cl_waiting` per pool	Confirms whether the cliff has been reached	Any sustained non-zero value; in the zero-headroom state it is still zero, which is the trap
`maxwait`	Age of the oldest waiter; user-facing pain once queuing starts	Approaching application-side timeouts; see the maxwait guide
`avg_wait_time`	Rolling average of queuing delay; catches sub-polling-interval waits a snapshot misses	Creeping above zero during peaks when it used to be zero; see the avg_wait_time guide
`avg_query_time` / `avg_xact_time`	Tells you why connections are held: slow backend vs idle-in-transaction	Sustained 2x baseline deviation; large xact/query gap
Reserve pool draw (sum of `sv_*` > `pool_size`)	Overflow capacity in use means the base pool is undersized	Any sustained activation, not just brief spikes
`paused` / `disabled` from `SHOW DATABASES`	Context that suppresses false positives	1 on any database: treat other signals as maintenance artifacts

The single most valuable addition most deployments are missing: alert on the sv_idle / pool_size ratio, not just on cl_waiting. A reasonable rule of thumb is to keep at least roughly 20% of pool_size idle at peak. That buffer absorbs bursts and keeps you off the cliff edge. Treat a sustained drop below that as a capacity ticket, not a page.

Fixes

Kill the specific holder, if there is one

If diagnosis shows one long-running query or one idle-in-transaction session is consuming the slots, canceling it on the PostgreSQL side (pg_cancel_backend, or pg_terminate_backend for a stuck session) frees the connection back to the pool immediately. Coordinate with the application team first: terminating a backend that PgBouncer is using causes the attached client to receive an error. This is expected behavior, but it is user-visible.

Fix idle-in-transaction in the application

If avg_xact_time >> avg_query_time, the fix is in application code: transactions that stay open across HTTP calls, batch loops, or user think-time hold server connections while doing nothing. Shorter transactions return connections faster and restore headroom without changing any PgBouncer setting. As a backstop, PostgreSQL’s idle_in_transaction_session_timeout can auto-terminate these sessions, at the cost of errors for the offending clients.

Address backend slowness

If avg_query_time is elevated, the pool is a symptom. The connections are held longer because PostgreSQL is slower: missing indexes, lock contention, I/O saturation. Fixing the backend restores pool turnover. Increasing pool_size in this situation only sends more concurrent load to an already struggling database.

Increase pool_size, with the PostgreSQL budget in mind

If demand genuinely exceeds capacity and the backend is healthy, raise default_pool_size or the per-database pool_size (a RELOAD applies it). Two constraints:

The sum of all pool sizes across all PgBouncer instances targeting a PostgreSQL server must stay comfortably under that server’s max_connections, leaving room for superuser access, replication, and direct connections. Keep PgBouncer’s total potential draw under about 80% of max_connections.
Bigger pools shift the bottleneck to PostgreSQL. Validate the backend can handle the additional concurrent queries before you grant them.

Session mode: resize for sessions, not turnover

In session pooling mode, sv_idle = 0 under full client load is structural. The fix is either sizing the pool to peak concurrent sessions, or evaluating whether the workload can move to transaction mode (after auditing for session-dependent features like prepared statements, temp tables, SET variables, and advisory locks).

Prevention

Alert on headroom, not just queuing. Add an alert on sv_idle / pool_size sustained near zero, and on the ratio’s multi-day trend. This is the signal that fires before users notice anything.
Trend peak utilization weekly. Track peak sv_active / pool_size per pool and extrapolate. Linear growth gives you a runway estimate: (1.0 - current_ratio) / weekly_growth in weeks until persistent queuing.
Always pair wait time with query time. avg_wait_time near zero with rising avg_xact_time means you are consuming headroom silently. By the time wait time moves, you are already at the cliff.
Exclude maintenance states. Every pool alert must check paused and disabled from SHOW DATABASES and suppress during administrative operations.
Watch reserve pool usage as a sizing defect. Brief reserve draws during spikes are the design. Regular draws mean raise the base pool.
Do not “reclaim” idle connections. High sv_idle in transaction mode is the ready reserve. Shrinking pool_size because idle connections look wasteful is how you manufacture the zero-headroom state.
Audit application transaction hygiene. Idle-in-transaction is the most common silent headroom killer in transaction mode. Catch it with the avg_xact_time vs avg_query_time gap before it shows up as saturation.

How Netdata helps

Netdata collects SHOW POOLS per pool continuously, so sv_idle, sv_active, and cl_waiting are time series rather than snapshots. The multi-day drift of sv_idle toward zero, the pattern point-in-time checks miss, becomes a visible trend line.
Because SHOW POOLS is a snapshot, brief queuing events between scrapes are easy to miss. Netdata pairs cl_waiting with avg_wait_time from SHOW STATS, so wait time injected between polls still shows up in the average.
The diagnostic pivot in this article, avg_xact_time vs avg_query_time, is a direct chart correlation: both come from the same stats source, so the idle-in-transaction gap is visible without cross-referencing tools.
Per-pool breakdowns keep one saturated (database, user) pool from hiding inside a healthy aggregate, which is exactly how zero headroom usually presents.
Alerting on the sv_idle / pool_size ratio and on sv_active / pool_size crossing 85% gives you the capacity ticket while the state is still yellow, instead of the page after cl_waiting turns red. See the monitoring checklist for the full signal set.