Diagnosing PgBouncer cl_waiting Spikes

This guide is part of PgBouncer Metrics Monitoring. Sustained cl_waiting means a pool is too small; that case is straightforward. Spikes are harder: a pool that runs with cl_waiting = 0 for hours suddenly shows 40 waiting clients and a maxwait of three seconds, then recovers within a minute. The application sees a burst of slow requests or acquisition timeouts, and by the time anyone logs into the console the pool looks healthy again:

2026-09-14 03:00:02.114 UTC [911] LOG C-0x55d1c3a0: orders/orders_app@10.4.2.17:51122 closing because: query_wait_timeout (age=120s)
2026-09-14 03:00:02.391 UTC [911] LOG S-0x55d1b870: orders/orders_app@10.0.1.20:5432 new connection to server (from 10.0.1.9:48210)
2026-09-14 03:00:02.392 UTC [911] LOG S-0x55d1b8e8: orders/orders_app@10.0.1.20:5432 new connection to server (from 10.0.1.9:48212)

The objective is to classify the spike by what the server connections were doing at that moment, and to apply the fix that matches the cause instead of raising default_pool_size by reflex.

Rapid incident diagnosis

Get the shape of the spike from the exporter. With the Prometheus exporter in place, plot four series for the affected pool across the spike: waiting clients, active servers, servers logging in, and maximum wait.

pgbouncer_pools_client_waiting_connections{database="orders"}
pgbouncer_pools_server_active_connections{database="orders"}
pgbouncer_pools_server_login_connections{database="orders"}
pgbouncer_pools_client_maxwait_seconds{database="orders"}

Capture the console during the next spike. A short loop records what the averaged metrics hide:

while true; do
  date -u +%T
  psql -h 127.0.0.1 -p 6432 -U pgbstats pgbouncer -Atc "SHOW POOLS;" | awk -F'|' '$1=="orders"'
  psql -h 127.0.0.1 -p 6432 -U pgbstats pgbouncer -Atc "SHOW SERVERS;" | awk -F'|' '$3=="orders" {print $4}' | sort | uniq -c
  sleep 1
done >> /var/tmp/pgbouncer-spike.log

SHOW SERVERS lists every server connection with its state (active, idle, used, tested, new) and connect time.

Classify by what the servers were doing.

• All servers active, few logins, avg_xact_time jumps: a burst of long transactions — a report, a batch job, a lock wait — held every server connection. Look at pg_stat_activity for long xact_start ages at the spike time.
• Many servers in new or high sv_login, active low: server connections were being re-established. Common triggers are server_lifetime expiring for a large cohort of connections opened together (after a restart or failover), a PostgreSQL restart, or server_check_query failing on many idle connections.
• sv_active + sv_idle + sv_used well below default_pool_size, logins succeed: a cap other than the pool size was reached — max_db_connections or max_user_connections — or PostgreSQL refused connections, visible as server login failed in the log.
• Everything normal on PgBouncer, spike visible only in maxwait: the PgBouncer process itself was busy (one CPU core) or the host was starved; check process CPU at the same second.

Mathematical sizing or parameter formula

Long-transaction spikes. A burst of k transactions each lasting T seconds removes k server connections for T seconds. Clients wait if the remaining connections cannot serve normal traffic:

spike_if   (default_pool_size − k) < arrival_rate × normal_xact_time ÷ 0.8
maxwait ≈  T   (in the worst case, when k ≥ default_pool_size)

A pool of 25 serving 2,000 transactions per second at 5 ms needs about 2000 × 0.005 ÷ 0.8 = 12.5 connections. A nightly job that opens 15 transactions of 3 seconds each leaves 10 — below 12.5 — and clients queue for up to 3 seconds. The fix is not a larger pool for everyone; it is a separate pool, or a limit, for the job.

Reconnect-cohort spikes. Connections opened together expire together. After a PgBouncer restart or database failover, all default_pool_size server connections are created within a second, and server_lifetime later closes them within the same second. Each new connection costs a login:

reconnect_window ≈ cohort_size × login_time ÷ concurrent_logins

With a cohort of 25, TLS plus SCRAM authentication at 40 ms each, and logins effectively serialised through the event loop in bursts, the pool can be short of connections for several hundred milliseconds every server_lifetime. With server_lifetime = 3600, that is a small spike every hour on the hour — a pattern easy to recognise in a week of metrics.

Reserve pool sizing. A reserve pool absorbs short spikes of any cause. It opens extra server connections when a client has waited longer than reserve_pool_timeout:

reserve_pool_size ≥ k_expected − (default_pool_size − steady_state_need)

For the nightly job above: 15 − (25 − 12.5) = 2.5, so a reserve of 3–5 keeps normal traffic flowing, at the cost of that many more backend connections in the budget.

Exact remediation & configuration

For long transactions: give heavy work its own pool. Route the batch or reporting user to a database entry with its own size so it cannot drain the interactive pool:

[databases]
orders       = host=10.0.1.20 port=5432 dbname=orders
orders_batch = host=10.0.1.20 port=5432 dbname=orders pool_size=6 pool_mode=transaction

[pgbouncer]
default_pool_size = 25
reserve_pool_size = 4
reserve_pool_timeout = 1          ; seconds a client waits before the reserve opens
max_db_connections = 40           ; total backend connections to this host across pools
query_wait_timeout = 30           ; fail waiting clients long before the application gives up

Point batch jobs at orders_batch. The interactive pool now loses nothing when the job runs, and the job queues against its own six connections.

For reconnect cohorts: spread the lifetimes and keep logins cheap. Lower server_lifetime only if needed for load-balancer or failover reasons; otherwise leave it long so cohorts expire rarely. Keep server_idle_timeout moderate so idle connections close individually over time, which breaks the cohort apart naturally:

server_lifetime = 3600
server_idle_timeout = 300
server_connect_timeout = 5
server_login_retry = 1           ; retry a failed backend login after 1 s instead of 15 s
min_pool_size = 10               ; keep a warm floor so a burst does not start from zero

min_pool_size means the pool starts rebuilding before clients arrive after a restart, rather than creating every connection on demand at the first burst.

For caps: make the limits consistent. Ensure max_db_connections is at least the sum of the pools that share the host plus the reserve, and that PostgreSQL’s max_connections exceeds max_db_connections across every PgBouncer instance:

max_db_connections ≥ Σ pool_size (per database entry to the host) + reserve_pool_size
Σ over PgBouncer instances (max_db_connections) ≤ max_connections − superuser_reserved_connections − other_clients

For a busy PgBouncer process: a single process tops out at one core; see Scaling PgBouncer Across CPU Cores with so_reuseport.

Apply configuration changes with RELOAD; on the admin console; pool sizes and timeouts take effect without disconnecting clients.

Validation & verification

Reproduce each cause in staging and watch the right columns move.

Long transactions — open more slow transactions than the pool minus steady need, while normal load runs:

for i in $(seq 1 15); do
  psql -h pgbouncer.staging -p 6432 -U orders_app orders -c "BEGIN; SELECT pg_sleep(3); COMMIT;" &
done; wait

Before the fix, cl_waiting rises and maxwait reaches about 3 seconds. After routing the job to orders_batch, the interactive pool’s cl_waiting must stay at zero while orders_batch shows the queue instead.

Reconnect cohorts — restart PgBouncer, then force the cohort to expire by temporarily setting server_lifetime = 60 and reloading. The spike at the one-minute mark must be absorbed by the reserve pool: maxwait below reserve_pool_timeout plus login time, and the log showing taking connection from reserve_pool.

Caps — lower max_db_connections below the pool sum in staging and confirm clients wait while SHOW SERVERS totals stay at the cap, then restore it.

Watch reserve usage in production. Reserve connections appearing a few times a day is healthy. Reserve connections in use most of the time means the regular pool is undersized:

journalctl -u pgbouncer --since "24 hours ago" | grep -c "taking connection from reserve_pool"

Alerting on spikes without paging on noise

Short spikes are normal in busy pools; the goal is to page when spikes hurt clients and to record the rest for review.

Page on wait duration, not waiting count. A dozen clients waiting 20 milliseconds is healthy multiplexing. One client waiting five seconds is an incident. maxwait captures the second directly:

groups:
  - name: pgbouncer-spikes
    rules:
      - alert: PgBouncerClientWaitLong
        expr: max by (database) (pgbouncer_pools_client_maxwait_seconds) > 2
        for: 1m
        labels: { severity: critical }
      - alert: PgBouncerPoolAtCeilingOften
        expr: avg_over_time((max by (database) (pgbouncer_pools_server_idle_connections) == bool 0)[1h:15s]) > 0.25
        labels: { severity: warning }
      - record: pgbouncer:spikes_per_hour
        expr: count_over_time((max by (database) (pgbouncer_pools_client_waiting_connections) > 0)[1h:15s])

The second rule warns when a pool has had no idle server connections in more than a quarter of the samples over the last hour — the condition in which the reserve pool is being called on repeatedly, indicating the regular pool is undersized. Count taking connection from reserve_pool log lines alongside it to confirm.

Record spike frequency for capacity review. The recording rule counts how many 15-second samples in each hour had waiting clients. A value that grows week over week at the same hour is the earliest sign that the pool is approaching its limit for that traffic pattern, long before any alert fires.

Annotate known causes. Nightly jobs, hourly lifetime cohorts and deploys all produce predictable spikes. Add them as dashboard annotations so on-call engineers can dismiss an expected 03:00 spike in seconds, and treat any spike without an annotation as worth a look.

Frequently Asked Questions

Related

• PgBouncer Metrics Monitoring — the parent guide to PgBouncer’s admin console columns.
• Setting Up the PgBouncer Prometheus Exporter — the series used to see spikes after the fact.
• Configuring PgBouncer Pool Size and max_client_conn — sizing the regular pool the reserve supplements.
• Interpreting pg_stat_activity Wait Events for Pool Issues — what the long transactions behind a spike were waiting on.