PgBouncer avg_query_time high: reading backend slowdown through the pooler

avg_query_time in PgBouncer’s SHOW STATS output just doubled, and now cl_waiting is starting to flicker above zero. This is the classic early-warning sequence for a pool exhaustion cascade: queries take longer, server connections are held longer, pool utilization climbs, and clients begin to queue. Catching the rise at the avg_query_time stage, before cl_waiting climbs, is the difference between a quiet Tuesday fix and a paged incident.

The trap is that avg_query_time is an average, and PgBouncer exposes no percentiles. A workload of 95% 1ms queries and 5% 500ms queries averages to a harmless-looking 26ms while the slow tail is already wrecking pool utilization. This article covers what the metric actually measures, how to diagnose a real rise versus an artifact, and how to trace the slowdown through to PostgreSQL.

What this means

avg_query_time comes from SHOW STATS (and the simpler SHOW STATS_AVERAGES) and is reported in microseconds per database. It measures the time from when PgBouncer sends a query to the PostgreSQL backend until it receives the complete response. That boundary matters:

• It includes the network round trip between PgBouncer and PostgreSQL. If they run on different hosts, network latency is inside this number.
• It includes server_reset_query overhead (default DISCARD ALL) when a connection is returned in session mode.
• It does not include client wait time. Time spent queued in cl_waiting is measured separately by avg_wait_time.

The averages are computed over a rolling window controlled by stats_period (default 60 seconds). Two consequences follow: a sudden spike will not fully show in the metric for up to a minute, and short bursts within the window get smoothed out. If you are watching dashboards at sub-minute granularity, the signal lags reality.

The mechanical link to pool pressure is simple: server connections are held for the duration of each query. Double avg_query_time and you halve the effective throughput of the pool at the same pool_size. That is why this metric is a leading indicator for saturation, and why it must always be read together with avg_wait_time:

• avg_query_time high, avg_wait_time low: the database is slow, but the pool still has headroom. Fix PostgreSQL.
• avg_query_time low, avg_wait_time high: the database is fine, the pool is too small or connections are being held idle. See PgBouncer avg_wait_time high.
• Both high: backend slowdown has already propagated into queuing. You are in the early stage of a pool exhaustion cascade.

flowchart TD
  A[avg_query_time rising] --> B{avg_wait_time also high?}
  B -- No --> C[Backend slow, pool has headroom: fix PostgreSQL]
  B -- Yes --> D[Pool exhaustion cascade forming]
  C --> E[pg_stat_statements: find slow queries]
  D --> F[SHOW POOLS: sv_active vs pool_size, cl_waiting]
  D --> G[avg_xact_time vs avg_query_time gap: idle in transaction?]
  E --> H[Cancel or fix offending queries]
  F --> H
  G --> H

Common causes

Quick checks

All read-only. Run against the admin console:

# Current per-database averages (microseconds for *_time columns)
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW STATS_AVERAGES;"

Read query_time, xact_time, and wait_time together. Compare xact_time against query_time: a large gap is idle-in-transaction time, not slow SQL.

# Pool state: is utilization climbing with the query time?
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

Check sv_active against your configured pool_size, plus cl_waiting and maxwait. If sv_active is at pool_size, the query-time rise is already costing you queue.

# Which server connections are stuck on long-running work?
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW SERVERS;"

Look for active connections with old request_time. Those are the connections holding up the pool. The link column lets you trace back to the client via SHOW CLIENTS.

# Confirm runtime config that affects interpretation
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW CONFIG;" | grep -E "stats_period|query_timeout|pool_mode|server_reset_query"

query_timeout defaults to 0 (disabled), so slow queries run to completion and hold their server connection the entire time.

On the PostgreSQL side:

# Idle-in-transaction connections holding PgBouncer server slots
psql -h <postgres-host> -c "SELECT pid, state, now() - xact_start AS xact_duration, query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY xact_duration DESC;"

# Top queries by cumulative execution time (pg_stat_statements required)
psql -h <postgres-host> -c "SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;"

Column names in pg_stat_statements vary by PostgreSQL version (older versions use total_time / mean_time). Adjust if needed.

How to diagnose it

1. Baseline the deviation. Pull SHOW STATS_AVERAGES and compare query_time against your rolling baseline for that database. A sustained rise beyond 2x baseline is notable; 5x with continuing load means imminent pool exhaustion. Absolute thresholds are application-dependent: OLTP typically expects single-digit milliseconds, so interpret accordingly.

2. Rule out the average lying to you. Because PgBouncer exposes only means, check the distribution indirectly. Compare xact_time to query_time: if transaction time is 10x query time, the problem is connections held idle inside transactions, not slow SQL. Check the bytes-per-query ratio (total_sent / total_query_count): a jump means a few queries returning huge result sets are holding connections while streaming, even if the average looks moderate.

3. Split wait from query time. If wait_time is also rising, the slowdown has propagated into queuing and you are treating a cascade, not just a slow backend. Note which (database, user) pool is affected in SHOW POOLS; per-pool saturation hides inside aggregates.

4. Find the offending connections. In SHOW SERVERS, sort mentally by request_time among active connections. The oldest active request is your primary suspect. Follow link to SHOW CLIENTS to identify the source application by addr.

5. Cross-reference PostgreSQL. PgBouncer cannot tell you which queries are slow; it only sees aggregates per database. Use pg_stat_statements for the per-query breakdown and pg_stat_activity for live state (active, idle in transaction, blocked on locks). Match PgBouncer’s sv_active against PostgreSQL’s active connections to confirm the story.

6. Check host-level resources on the PostgreSQL server. If all queries slowed uniformly and no single query explains it, look at CPU, disk I/O, and autovacuum activity on the database host.

Metrics and signals to monitor

Fixes

Slow queries or plan changes on PostgreSQL

The fix lives on the database side: index the query, rewrite it, or address bloat and statistics. PgBouncer cannot help you here beyond isolating the affected database. If a single runaway query is holding pool slots and the situation is urgent, canceling it on PostgreSQL (pg_cancel_backend, or pg_terminate_backend as the heavier option) frees the server connection. Coordinate with the application owner first: the client will receive an error, and termination rolls back the transaction.

Idle in transaction

This is an application bug, not a database bug. Short term, PostgreSQL’s idle_in_transaction_session_timeout can auto-terminate offending sessions and return connections to the pool. Long term, fix the code path that opens a transaction and then does non-database work. The avg_xact_time / avg_query_time ratio is your regression detector after the fix ships.

Reset query overhead

If you are in session pooling mode and DISCARD ALL is expensive on your backend (many temp tables to clean up, for example), the cost shows up inside avg_query_time. Evaluate whether the default server_reset_query is needed for your workload, but understand the tradeoff: disabling it lets session state bleed between clients.

Protecting the pool during a backend slowdown

Setting query_timeout (disabled by default) bounds how long a query may hold a server connection before PgBouncer cancels it. This is a blunt instrument: it protects pool capacity during backend degradation but turns slow queries into failed queries. Size it against your application’s own timeout and error-handling behavior, not against the average.

Prevention

• Alert on baseline deviation, not absolutes. A sustained 2x rise in avg_query_time is the ticket-level early warning; 5x with load continuing means act now. Absolute thresholds are meaningless across workloads.
• Always pair query time with wait time in alerts and dashboards. The two together attribute latency correctly and prevent the classic misdiagnosis of blaming PostgreSQL for pool undersizing (or vice versa).
• Track the avg_xact_time / avg_query_time ratio. It is the only pooler-side signal that catches idle-in-transaction before it starves the pool.
• Track bytes per query. It surfaces slow-burn changes in result set size that averages hide.
• Keep pg_stat_statements enabled on PostgreSQL. PgBouncer will never give you per-query granularity; the database must.
• Remember the smoothing. stats_period (default 60s) delays and smooths the signal, and SHOW STATS counters reset on PgBouncer restart. Use the avg_* columns or compute deltas, and handle restarts in your tooling.

How Netdata helps

• Netdata collects PgBouncer stats through the admin console and charts avg_query_time, avg_wait_time, and avg_xact_time per database together, so the wait-versus-query attribution is visible on one screen instead of across two tools.
• Pool state (sv_active, sv_idle, cl_waiting, maxwait) is graphed alongside the latency averages, which makes the “query time rises, then utilization rises, then clients queue” cascade visible as it develops.
• Per-database breakdowns prevent a single saturated pool from hiding inside a healthy aggregate.
• Because Netdata also monitors the PostgreSQL host and the PgBouncer process itself, you can correlate a query-time rise with database CPU, disk I/O, and pg_stat_activity state without switching contexts.
• Historical retention lets you compute the rolling baseline that deviation-based alerting on avg_query_time requires.

Related guides

• PgBouncer advisory locks in transaction mode: orphaned locks and mysterious contention
• PgBouncer avg_wait_time high: the latency the pool itself is injecting
• PgBouncer backend unreachable: PostgreSQL down and the pool draining
• PgBouncer capacity planning: runway for pools, clients, and PostgreSQL slots
• PgBouncer client connection leak: idle clients that never disconnect
• How PgBouncer actually works in production: a mental model for operators
• PgBouncer LISTEN/NOTIFY not working: why pub/sub needs session pooling
• PgBouncer max_client_conn tuning: setting the client limit against real FD headroom
• PgBouncer maxwait high: the oldest client waiter and how close it is to timing out
• PgBouncer monitoring checklist: the signals every connection pooler needs
• PgBouncer monitoring maturity model: from survival to expert
• PgBouncer no more connections allowed (max_client_conn): the front door is full