Tracking PgBouncer Query and Transaction Latency

This guide is part of PgBouncer Metrics Monitoring. PgBouncer sits exactly where latency questions become ambiguous: an application seeing slow queries cannot tell whether the database is slow, whether the proxy queued the request, or whether its own transactions are holding server connections. SHOW STATS answers all three, because it measures each separately:

database | total_xact_count | total_query_count | total_wait_time | avg_xact_time | avg_query_time | avg_wait_time
orders   |         14882301 |          31447120 |     902144331   |         14231 |           2104 |        61022

Those three averages — 14.2 ms per transaction, 2.1 ms per query, 61 ms waiting — say that queries are fast, transactions are much longer than their queries, and clients are waiting a long time for a server connection. That is a complete diagnosis, and it takes one command. The objective is to export these series, understand what each measures, and build the alerts that distinguish the three causes automatically.

Rapid incident diagnosis

Read the three averages together. Each is in microseconds and averaged over stats_period:

SHOW STATS;
-- avg_query_time : time from a query reaching a server connection to its result
-- avg_xact_time  : time from BEGIN to COMMIT, including client think time inside the transaction
-- avg_wait_time  : time a client waited for a server connection

Interpret the relationships rather than the absolute values:

Convert to something comparable. The application measures request latency; the proxy measures microseconds per transaction. The bridge is the number of transactions per request:

db_time_per_request ≈ transactions_per_request × (avg_wait_time + avg_xact_time)

If that accounts for most of the request’s latency, the database path is the problem; if it does not, the time is elsewhere.

Watch for averaging artefacts. The avg_* columns are averaged over stats_period (60 seconds by default) and reset each period, so a short spike is diluted. The total_* counters are cumulative and safe to rate, which is what the exported metrics should be built on.

Mathematical sizing or parameter formula

Backend demand follows transaction time, not query time:

backends_needed = transactions_per_second × avg_xact_seconds ÷ target_utilisation

At 1,200 transactions per second with avg_xact_time of 14.2 ms: 1200 × 0.0142 ÷ 0.7 = 24 backends. With the idle time inside transactions removed — transactions of 4.2 ms — the same traffic needs 8.

Queue time follows utilisation. As utilisation approaches one, waiting grows sharply:

utilisation = backends_busy ÷ default_pool_size
expected_wait ≈ avg_xact_time × utilisation ÷ (1 − utilisation)

At 80 % utilisation with a 14.2 ms transaction, expected wait is about 57 ms — close to the observed 61 ms, which confirms the pool is simply too small for the transaction time it is being given.

Choosing default_pool_size from the statistics:

default_pool_size = ceil( rate(total_xact_count) × avg_xact_seconds ÷ 0.7 )

The two inputs are exactly what SHOW STATS provides, which makes this the one sizing calculation that needs no application instrumentation at all.

Alert thresholds. Wait time is the user-visible term, so thresholds belong there:

warn  : avg_wait_time > 0.25 × avg_xact_time
page  : avg_wait_time > avg_xact_time, or maxwait > 2 s

Exact remediation & configuration

Export the counters, not the averages. Rates of the cumulative counters are stable across stats_period boundaries:

# per-database, per-instance
rate(pgbouncer_stats_queries_pooled_total[5m])
rate(pgbouncer_stats_transactions_pooled_total[5m])

# mean times, derived from totals rather than the avg_* columns
rate(pgbouncer_stats_total_wait_time_seconds[5m]) / rate(pgbouncer_stats_transactions_pooled_total[5m])
rate(pgbouncer_stats_total_query_time_seconds[5m]) / rate(pgbouncer_stats_queries_pooled_total[5m])
rate(pgbouncer_stats_total_xact_time_seconds[5m]) / rate(pgbouncer_stats_transactions_pooled_total[5m])

Exporter metric names vary; the principle is to divide a time total by a count total rather than to scrape the pre-averaged column.

Record them so the alerts stay readable:

groups:
  - name: pgbouncer-latency
    interval: 30s
    rules:
      - record: pgbouncer:wait_seconds:mean
        expr: rate(pgbouncer_stats_total_wait_time_seconds[5m])
              / clamp_min(rate(pgbouncer_stats_transactions_pooled_total[5m]), 1)
      - record: pgbouncer:query_seconds:mean
        expr: rate(pgbouncer_stats_total_query_time_seconds[5m])
              / clamp_min(rate(pgbouncer_stats_queries_pooled_total[5m]), 1)
      - record: pgbouncer:xact_seconds:mean
        expr: rate(pgbouncer_stats_total_xact_time_seconds[5m])
              / clamp_min(rate(pgbouncer_stats_transactions_pooled_total[5m]), 1)
      - record: pgbouncer:xact_overhead:ratio
        expr: pgbouncer:xact_seconds:mean / clamp_min(pgbouncer:query_seconds:mean, 0.000001)

Alert on the relationships:

      - alert: PgBouncerWaitDominatesLatency
        expr: pgbouncer:wait_seconds:mean > 0.25 * pgbouncer:xact_seconds:mean
        for: 10m
        labels: { severity: warning }
        annotations:
          summary: "{{ $labels.database }}: clients spend over a quarter of transaction time waiting"

      - alert: PgBouncerDatabaseSlower
        expr: pgbouncer:query_seconds:mean
              > 1.5 * avg_over_time(pgbouncer:query_seconds:mean[1d] offset 1d)
        for: 15m
        labels: { severity: warning }
        annotations:
          summary: "{{ $labels.database }}: mean query time 50 % above yesterday"

      - alert: PgBouncerTransactionsHoldingConnections
        expr: pgbouncer:xact_overhead:ratio > 5
        for: 15m
        labels: { severity: warning }
        annotations:
          summary: "{{ $labels.database }}: transactions are five times their query time — application holding connections"

Tune stats_period for resolution. The default of 60 seconds averages away short events; 15 seconds gives finer granularity at negligible cost:

[pgbouncer]
stats_period = 15

Align the scrape interval with it — scraping faster resamples the same averages.

Act on the diagnosis. Waiting means the pool or the transaction time; query time means the database; the ratio means the application. The last one is the most common and the least expected, and the remedy is in the application: move network calls, file writes and rendering out of the transaction.

Comparing the proxy’s view with the application’s

The proxy and the application measure different intervals, and knowing the difference prevents a long argument during an incident.

The application’s query time includes the wait. A driver’s query timer starts when the application calls execute and ends when the result arrives, so it includes the proxy’s queue time. PgBouncer’s avg_query_time excludes it. The difference between the two is the queue:

app_query_time − pgbouncer_query_time ≈ wait + network

The application’s pool wait is separate again. A client pool in front of PgBouncer has its own queue, so a request can wait twice: once for a client connection, once for a server connection. Both appear in request latency and only one appears in each system’s metrics.

Put them on one panel. Four series tell the whole story: application pool wait, application query time, PgBouncer wait time, PgBouncer query time. The gaps between them are the answer:

Check the clocks. A proxy on a host with a skewed clock produces series that do not line up with the application’s, which makes the comparison meaningless. NTP on the proxy hosts is not optional for this analysis.

Using the numbers in a capacity review

The same three series answer capacity questions as well as incident ones, and they need no application instrumentation, which makes them the easiest capacity input to collect in a mixed estate.

Backend utilisation over time. Busy backends divided by the pool size is the utilisation that drives the queueing curve:

sum by (database) (pgbouncer_pools_server_active_connections)
  / on (database) group_left pgbouncer_databases_pool_size

A peak utilisation trending upward week over week is the leading indicator; the wait time that follows is the lagging one.

Transaction time as a product signal. A gradual rise in avg_xact_time with flat query time means transactions are getting longer in the application — usually more work inside a transaction, sometimes an added external call. Catching it in a monthly review is far cheaper than catching it when the pool saturates.

Sizing the next pool change. Because both inputs come from the proxy, the recommended pool size can be computed directly and compared with the configured one:

ceil(
  rate(pgbouncer_stats_transactions_pooled_total[1h])
  * (rate(pgbouncer_stats_total_xact_time_seconds[1h])
     / clamp_min(rate(pgbouncer_stats_transactions_pooled_total[1h]), 1))
  / 0.7
)

Graphing that against pgbouncer_databases_pool_size gives a panel that says, continuously, whether the pool is the size the traffic implies.

Check the database’s side too. A pool sized from transaction time still has to fit the server’s connections and CPU; the proxy’s numbers say what the application needs, not what the database can give. Pairing this panel with the database’s free connections and CPU utilisation is what turns it into a decision.

Validation & verification

The derived means match the console. Compare a recorded mean against SHOW STATS at the same moment; they should agree closely. A large difference usually means the exporter’s units are milliseconds rather than seconds.

The ratio detects held connections. In staging, add a deliberate one-second sleep inside a transaction and confirm pgbouncer:xact_overhead:ratio rises sharply and the alert fires.

Wait time responds to pool size. Halve default_pool_size under steady load; wait time should rise roughly as the queueing formula predicts, and return when the size is restored.

Query time tracks the database. Run a deliberately slow query mix and confirm pgbouncer:query_seconds:mean rises while wait time stays flat until the pool saturates.

Clocks agree. Compare timestamps of a known event — a deploy, a reload — across the application and proxy dashboards; a systematic offset invalidates the comparison.

Frequently Asked Questions

Related

• PgBouncer Metrics Monitoring — the parent guide to the console’s views.
• Diagnosing PgBouncer cl_waiting Spikes — the short-lived version of the waiting signal.
• Alerting on PgBouncer Client and Server Limits — alerts for the limits behind these numbers.
• Configuring PgBouncer Pool Size and max_client_conn — sizing from the transaction time measured here.