PgBouncer maxwait high: the oldest client waiter and how close it is to timing out

You opened SHOW POOLS because an application is slow, and one column stands out: maxwait is 8, 20, maybe 90 seconds. That number is the age of the oldest client sitting in PgBouncer’s FIFO wait queue, computed from the query_start of the first waiter. It is the worst-case queuing latency any client is experiencing right now, before its query even reaches PostgreSQL.

maxwait is more actionable than the cl_waiting count. cl_waiting tells you how many clients are blocked; maxwait tells you how much it hurts. A queue of 50 clients that drains in 200ms is a burst. A queue of 3 clients where the oldest has waited 45 seconds is an incident.

The metric matters because of what happens at the end of it. PgBouncer disconnects a waiter when its wait exceeds query_wait_timeout (default 120 seconds), so maxwait is also a countdown to the oldest client being killed with an error. If your application has a shorter timeout than 120s, it gives up and retries first, and those retries join the back of the queue and make everything worse.

What this means

When all server connections in a pool are busy, new client queries enter a per-(database, user) FIFO queue. maxwait (seconds, plus maxwait_us for the microsecond remainder) measures the head of that queue. Zero means nobody is waiting. Anything sustained above zero means the pool cannot keep up with demand.

Operational heuristics that match what most OLTP applications experience:

Two nuances before you escalate:

• maxwait counts from when the query was sent, not from when the client connected. A client connected for hours in session mode contributes nothing to maxwait until it submits a query that has to queue.
• Check paused/disabled state first. During an administrative PAUSE <db>, new queries queue while existing transactions finish, so cl_waiting and maxwait spike by design. DISABLE rejects new clients but lets existing ones work. Both look alarming and are expected during maintenance. SHOW DATABASES exposes paused and disabled columns; check them before treating any queue depth as an outage.

flowchart TD
  A[maxwait rising] --> B{SHOW DATABASES: paused or disabled?}
  B -- yes --> C[Expected maintenance behavior. Do not escalate.]
  B -- no --> D{sv_active at pool_size?}
  D -- yes --> E{avg_query_time elevated?}
  E -- yes --> F[Backend slow: connections held too long. Go to PostgreSQL.]
  E -- no --> G[Pool undersized or idle-in-transaction holding slots.]
  D -- no --> H{sv_login elevated, total server connections declining?}
  H -- yes --> I[Backend connection failure: auth, network, DNS, or max_connections.]
  H -- no --> J[Check reserve pool, event loop CPU, and per-pool breakdown.]

Common causes

Quick checks

All commands below are read-only and safe to run during an incident. They assume the admin console on port 6432 with the pgbouncer admin database; adjust host, port, and user for your deployment.

# Full pool snapshot: cl_waiting, maxwait, sv_active, sv_idle, sv_login per pool
psql -h /var/run/postgresql -p 6432 -U pgbouncer pgbouncer -Atc "SHOW POOLS;"

# Maintenance context FIRST: paused and disabled columns
psql -h /var/run/postgresql -p 6432 -U pgbouncer pgbouncer -Atc "SHOW DATABASES;"

# Separate pool wait from backend latency (values in microseconds)
psql -h /var/run/postgresql -p 6432 -U pgbouncer pgbouncer -Atc "SHOW STATS_AVERAGES;"

# Per-client wait detail: who is waiting and for how long (wait, wait_us columns)
psql -h /var/run/postgresql -p 6432 -U pgbouncer pgbouncer -Atc "SHOW CLIENTS;"

# Per-server detail: which connections are stuck and how long they have been active
psql -h /var/run/postgresql -p 6432 -U pgbouncer pgbouncer -Atc "SHOW SERVERS;"

# Confirm the actual timeout the oldest waiter is racing against
psql -h /var/run/postgresql -p 6432 -U pgbouncer pgbouncer -Atc "SHOW CONFIG;" | grep -E "query_wait_timeout|pool_size|reserve_pool"

# Event loop health: admin console should answer in well under 100ms
time psql -h /var/run/postgresql -p 6432 -U pgbouncer pgbouncer -c "SHOW LISTS;" > /dev/null

# Error evidence: PgBouncer exposes zero error counters via SHOW; logs are the only source
grep -E "query_wait_timeout|connect failed|login failed|reserve_pool" /var/log/pgbouncer/pgbouncer.log | tail -30

Column positions in SHOW POOLS and SHOW STATS output have changed across releases as new counters were added. Reference columns by name in any scripted check, not by field index.

How to diagnose it

1. Rule out maintenance. Run SHOW DATABASES and check paused and disabled. If the affected database is paused, the queue is intentional. Coordinate with whoever ran PAUSE on RESUME timing. Do not escalate further.

2. Confirm the queue is real and sustained. Poll SHOW POOLS a few times, 10 to 15 seconds apart. A single snapshot showing cl_waiting > 0 with maxwait under a second is a burst, especially in transaction pooling mode where the pool turns over quickly. Sustained maxwait above 5 seconds is where you start treating it as an incident.

3. Identify which pool is hurting. maxwait and cl_waiting are per (database, user). One saturated pool can sit next to many healthy ones. Note the database and user of the worst pool; everything after this is scoped to it.

4. Split pool wait from database time. In SHOW STATS_AVERAGES, compare avg_wait_time against avg_query_time for that database. High wait with low query time means PostgreSQL is fast and the pool is undersized or connections are being held idle. High query time means the backend is the root cause and PgBouncer is the messenger. This single comparison decides which half of the system you debug next.

5. Check supply vs demand. Compare sv_active in SHOW POOLS against pool_size in SHOW DATABASES. If sv_active equals pool_size and sv_idle is 0, the pool is fully saturated. If sv_active is well below pool_size while clients queue, server connections are not being created: look at sv_login, per-database current_connections vs max_connections in SHOW DATABASES, and the log for connection failures.

6. Hunt the connection holders. If sv_active is at pool_size, run SHOW SERVERS and look for active connections with the oldest request_time. The link column maps each server connection to a client; cross-reference with SHOW CLIENTS to find the source address. On the PostgreSQL side, check pg_stat_activity for idle in transaction state. The signature of idle-in-transaction starvation is avg_xact_time far larger than avg_query_time.

7. Check whether the backend is failing, not slow. If total server connections (sv_active + sv_idle + sv_used + sv_tested + sv_login) decline over successive polls while sv_login stays elevated, PgBouncer cannot establish new backend connections. Check SHOW DNS_HOSTS for stale or unresolvable addresses, test a direct connection from the PgBouncer host to PostgreSQL, and check whether PostgreSQL is at max_connections. Resizing the pool will not help this pattern.

8. Estimate time to first casualty. Compare current maxwait against query_wait_timeout from SHOW CONFIG (default 120s) and against your application’s own timeout, whichever is shorter. That gap is how long you have before clients start erroring or retrying. PgBouncer logs a query_wait_timeout event when it disconnects a waiter, so grep the log to see whether casualties have already started.

Metrics and signals to monitor

Averaging caveat: SHOW STATS averages roll over the stats period and SHOW POOLS is a point-in-time snapshot, so sub-second queue spikes between polls are invisible. That is acceptable here; sub-second queuing is not the problem this article is about.

Fixes

If the backend is slow (high avg_query_time)

The pool is a symptom. Fix the queries: find the offenders via pg_stat_activity and pg_stat_statements on PostgreSQL, and cancel or terminate the worst blockers to free server connections. Coordinate before killing anything that might be a batch job. PgBouncer-side changes only shuffle the queue.

If the pool is undersized (low avg_query_time, sv_active = pool_size)

Raise pool_size (or default_pool_size) for the affected database and run RELOAD. The arithmetic: required pool size is roughly transactions_per_second x avg_xact_time_in_seconds. Respect the ceiling: the sum of all pool sizes across all PgBouncer instances targeting one PostgreSQL must stay comfortably below max_connections, leaving room for superuser and direct connections. Do not shrink pool_size because sv_idle looks high; in transaction mode, idle connections are the burst buffer, not waste.

If idle-in-transaction clients are hoarding connections

This is an application bug, not a PgBouncer tuning problem. Identify the client via SHOW SERVERS link and SHOW CLIENTS addr, and fix the code that opens a transaction and then does non-database work. As a safety net, PostgreSQL’s idle_in_transaction_session_timeout can auto-terminate these sessions, but that changes application-visible behavior, so roll it out deliberately.

If backend connections cannot be established

Do not touch the pool size. Fix reachability: verify PostgreSQL is up and below max_connections, check credentials in PgBouncer’s auth configuration, and check SHOW DNS_HOSTS for a stale address after a failover. A RELOAD refreshes the DNS cache.

If reserve pool is absorbing the overflow regularly

The reserve pool (reserve_pool_size, default 0) exists for brief spikes: it only kicks in after a client has waited reserve_pool_timeout (default 5s). If it activates routinely, the base pool is chronically undersized. Raise pool_size instead of growing the reserve; when base plus reserve both fill, the queuing cliff is deeper.

On query_wait_timeout itself

The 120s default is far longer than most application timeouts, which means applications time out and retry long before PgBouncer ejects the waiter, amplifying the queue. Many operators lower query_wait_timeout to a value aligned with the application’s own timeout so that PgBouncer, not the application, enforces the queue limit. Tradeoff: a shorter timeout disconnects legitimate waiters during transient backend slowness instead of letting them ride it out. Change it with the application timeout in mind, not in isolation.

Prevention

• Alert on sustained maxwait, not on cl_waiting > 0. Brief queuing during bursts is normal in transaction mode and makes cl_waiting > 0 a false-positive machine. The signal that distinguishes “burst” from “stuck” is maxwait above a threshold (5s is a reasonable starting point) sustained for a minute or more, with a paused/disabled check built into the alert condition.
• Suppress alerts during administrative state. High cl_waiting with sv_active draining to zero is the exact signature of both a PAUSE and a real outage. Every PgBouncer alert must read the paused/disabled columns first.
• Track headroom, not just pain. Trend sv_active / pool_size per pool. Above 85% sustained, act before the queue forms. Zero sv_idle with zero waiters is a yellow state, not green: you are one slow query from a cascade.
• Baseline avg_wait_time and avg_xact_time. A creep from zero wait time, or a growing gap between transaction time and query time, are the early warnings that arrive weeks before the maxwait incident.
• Align timeouts across layers. Application timeout, query_wait_timeout, and load balancer timeouts in front of PgBouncer should be a deliberate stack, not three independent defaults.
• Watch capacity relationships. Sum of pool sizes versus PostgreSQL max_connections, and max_client_conn versus the file descriptor limit, are misconfigurations that turn a traffic peak into a maxwait incident.
• Remember that stats reset on restart and that SHOW commands expose no error counters. Log collection for PgBouncer is not optional if you want query_wait_timeout events and connection failures after the fact.

How Netdata helps

• Netdata collects PgBouncer pool metrics continuously through the admin console, so maxwait, cl_waiting, and per-pool server states become a time series instead of a snapshot you happened to catch during the incident.
• Per-pool breakdowns show which (database, user) pool owns the queue while the aggregate still looks healthy.
• Correlating client wait time with query time on the same dashboard makes the “pool too small” vs “database too slow” decision visual instead of a manual two-query comparison.
• The gap between transaction time and query time, tracked over weeks, surfaces idle-in-transaction creep before it saturates a pool.
• Alerting on sustained maxwait with anomaly detection on wait time catches slow-building cases that static cl_waiting thresholds miss, without paging on normal burst queuing.
• Host-level metrics (per-core CPU for the single-threaded event loop, file descriptor usage) sit next to the pooler metrics, which matters when the root cause is PgBouncer itself rather than the pools.

Related guides

• How PgBouncer actually works in production: a mental model for operators
• PgBouncer monitoring checklist: the signals every connection pooler needs
• PgBouncer monitoring maturity model: from survival to expert
• PgBouncer pool exhaustion: clients queue, wait times climb, and the retry cascade