PgBouncer idle in transaction: the silent pool killer in transaction mode

Your PgBouncer pool looks busy: sv_active is pinned at pool_size, cl_waiting is climbing, and applications are timing out. But PostgreSQL is barely doing anything. CPU is low, avg_query_time is a few milliseconds, and there are no slow queries to kill. The pool is full of connections that are “active” yet running nothing at all.

This is the idle-in-transaction pattern, and it is the most common silent killer in transaction-mode PgBouncer deployments. An application runs BEGIN, gets assigned a server connection, runs a query, then goes off to do non-database work (HTTP calls, serialization, computation, waiting on another service) before coming back to COMMIT. In transaction pooling mode, that server connection stays assigned to the client for the entire transaction, including every second the client spends not talking to the database. Enough clients doing this and every server connection is checked out but idle. Everyone else queues.

There is no PgBouncer alert for this condition. No error counter fires, nothing appears in the log, and cl_waiting only tells you that people are waiting, not why. You have to derive the diagnosis from a small set of signals, which this article walks through.

What this means

In transaction pooling mode, PgBouncer assigns a server connection to a client at BEGIN and takes it back at COMMIT or ROLLBACK. The design assumption is that transactions are short: a few milliseconds of query time, then the connection returns to the pool and serves the next client. The multiplexing ratio, how many clients can share one server connection, depends entirely on that assumption.

When a client holds a transaction open while doing other work, the math collapses. A pool of 20 server connections with an average transaction time of 100 ms can serve roughly 200 transactions per second. If application changes push the average transaction time to 2 seconds because most of that time is idle waiting inside the transaction, the same pool serves about 10 transactions per second. Nothing is slow. Nothing is broken in PostgreSQL. The pool is simply being used as a parking lot.

The hallmark signal is avg_xact_time much larger than avg_query_time. Query time measures actual backend execution. Transaction time measures how long the server connection was held. The gap between them is, almost exactly, the time clients spent idle inside open transactions. On the PostgreSQL side, the same backends show up in pg_stat_activity with state = 'idle in transaction'.

flowchart TD
  A[App sends BEGIN] --> B[Server connection assigned]
  B --> C[Query runs and finishes fast]
  C --> D[App does non-database work]
  D --> E[Connection held, no query running]
  E --> F[sv_active reaches pool_size]
  F --> G[New clients enter wait queue]
  G --> H[avg_wait_time and maxwait climb]
  H --> I[App timeouts, retries deepen the queue]

Common causes

Quick checks

All of these are read-only.

# 1. Compare transaction time to query time per database
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW STATS_AVERAGES;"

Look at avg_xact_time versus avg_query_time (both in microseconds). If avg_xact_time is 10x or more above avg_query_time, most of the connection hold time is idle gap, not work. A ratio near 1 means transactions are all query and this article is not your problem.

# 2. Confirm the pool is actually saturated and people are waiting
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

Check sv_active at or near pool_size, sv_idle at zero, cl_waiting above zero, and maxwait climbing. Also check SHOW DATABASES for paused or disabled before treating this as an incident, since administrative pause produces the same queue shape.

# 3. Find the specific server connections held the longest
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW SERVERS;"

Look for active connections with an old request_time. An active server connection whose last request was 30 seconds ago, in a pool where queries take 5 ms, is a held-idle connection. Note the link column.

# 4. Identify the client holding each connection
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW CLIENTS;"

Match the link from SHOW SERVERS to the client and read its addr, connect_time, and database/user. This tells you which application instance is parking connections.

-- 5. On PostgreSQL: confirm the idle-in-transaction backends
SELECT pid, usename, application_name, state,
       now() - state_change AS idle_duration,
       left(query, 100) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '5 minutes'
ORDER BY state_change;

The application_name and last_query columns usually identify the guilty code path immediately. For a broader view including everything currently idle in transaction, order by now() - xact_start descending instead.

# 6. Check which safety nets are configured
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW CONFIG;" | grep -i transaction

If idle_transaction_timeout is 0.0, it is disabled (the default) and nothing on the PgBouncer side will ever reclaim these connections.

How to diagnose it

1. Establish the ratio. Pull avg_xact_time and avg_query_time from SHOW STATS_AVERAGES per database. Compute avg_xact_time / avg_query_time. Below roughly 2x, transactions are honest work. Above 10x, idle-in-transaction time dominates. This ratio is the single cheapest detection you can build, and no native PgBouncer alert computes it for you.

2. Rule out lookalikes. Check paused/disabled in SHOW DATABASES (maintenance, not an incident). Check avg_query_time itself: if it is elevated alongside avg_xact_time, the root cause is slow PostgreSQL work, not idle gaps. If sv_login is rising and total server connections are falling, you have a backend connectivity problem, not this one. See PgBouncer backend unreachable: PostgreSQL down and the pool draining.

3. Find the holders. In SHOW SERVERS, sort by request_time among active connections. The oldest requests on a fast pool are the parked connections. Follow link into SHOW CLIENTS for the source address.

4. Confirm on PostgreSQL. Run the pg_stat_activity query above. Every PgBouncer server connection parked mid-transaction appears as idle in transaction on the backend. The count of such backends should track your pool saturation almost one to one.

5. Attribute the pattern. Use application_name, source addr, and last_query to identify the code path. The typical finding: a request-scoped transaction that spans an outbound HTTP call, a serialization step, or a wait on another service.

6. Quantify the cost. Effective pool throughput is roughly pool_size / avg_xact_time_in_seconds transactions per second. Compare that against your transaction rate from SHOW STATS_AVERAGES (avg_xact_count). If demand exceeds that number, queuing is mathematically guaranteed.

Metrics and signals to monitor

The two nastiest states are the quiet ones: sv_idle = 0 with cl_waiting = 0 (zero headroom, nobody waiting yet), and avg_wait_time = 0 with avg_xact_time trending up (headroom being consumed before any symptom). By the time cl_waiting moves, you are already at the cliff edge. For the wait-time side of the picture, see PgBouncer avg_wait_time high: the latency the pool itself is injecting and PgBouncer maxwait high: the oldest client waiter and how close it is to timing out.

Fixes

Fix the application pattern (the real fix)

Move non-database work out of the transaction. The rule is simple: BEGIN as late as possible, COMMIT as early as possible, and never hold an open transaction across a network call, a sleep, a queue wait, or user think time. Concretely, this usually means restructuring request handlers that wrap the entire request in a transaction, replacing “begin on first query” patterns with explicit transaction scope, and splitting batch jobs into per-item or per-chunk transactions.

Tradeoff: this is a code change with a deploy cycle, so it is never the fast mitigation. It is also the only fix that removes the problem rather than bounding it.

PostgreSQL safety net: idle_in_transaction_session_timeout

PostgreSQL’s idle_in_transaction_session_timeout (available since 9.6) terminates any session idle inside an open transaction longer than the configured time. Set it in postgresql.conf or via ALTER SYSTEM, for example 60 seconds for OLTP workloads.

This is a hard kill at the database level. When it fires, the backend is terminated, the client’s transaction is aborted, and PgBouncer detects the broken server connection and replaces it. The client sees an error on its next statement. Some teams deliberately run with PgBouncer’s own timeout disabled and rely on this GUC as the single enforcement point.

Tradeoff: any application that legitimately idles in transaction past the limit will start failing. It protects the pool and the database, but the errors land on the client, which must handle aborted transactions and retry sanely.

PgBouncer idle_transaction_timeout

idle_transaction_timeout (default 0.0, disabled) closes the backend server connection after a client has been idle in transaction longer than the configured seconds. The client sees the resulting disconnection and the backend slot is released.

Two important caveats:

• It only counts idle time. A transaction continuously running slow queries never trips it, no matter how long it lives. That gap is what transaction_timeout fills in newer versions.
• Before PgBouncer 1.18.0, the timer used the client’s last request rather than the server’s last request, so back-to-back slow queries could trigger premature disconnects. This was fixed in 1.18.0; do not rely on the old behavior.

PgBouncer transaction_timeout (1.25.0+)

PgBouncer 1.25.0 added transaction_timeout, configurable globally and per user. Its timer starts with the transaction and counts idle and active query time; when it expires, PgBouncer closes the backend and logs transaction timeout.

Also in 1.25.0, the state column in SHOW CLIENTS can report idle, which makes PgBouncer-side identification of idle clients more direct than inferring from request_time.

Which timeout should fire first

If you set both the PgBouncer-side and PostgreSQL-side timeouts, stagger them deliberately so the cheaper kill wins and behavior is predictable. There is no coordination between the two layers. Within PgBouncer, the janitor checks query, idle-transaction, and transaction timeouts in that order; PostgreSQL produces its own timeout error, while PgBouncer closes the backend and reports a pooler-side disconnection.

Whatever you choose, do not leave both disabled, which is the default state and the reason this failure mode earns the word “silent.”

Prevention

• Alert on the derived ratio. No built-in PgBouncer signal covers this. Compute avg_xact_time / avg_query_time and alert on sustained values above your baseline, alongside cl_waiting and maxwait.
• Set the timeouts in staging first. A low idle_in_transaction_session_timeout in pre-production surfaces the offending code paths before they reach production.
• Audit framework defaults. ORMs and request frameworks that disable autocommit or wrap handlers in transactions are the usual source. Verify what your stack actually does under transaction pooling.
• Watch headroom, not just symptoms. Trend sv_idle and the active ratio so you see idle-in-transaction growth before the queue forms. Capacity runway for this lives in PgBouncer capacity planning: runway for pools, clients, and PostgreSQL slots.
• Separate batch workloads. Route batch jobs to their own pool or user with their own limits so a batch transaction pattern cannot starve OLTP traffic.

How Netdata helps

• Netdata collects PgBouncer SHOW POOLS and SHOW STATS output continuously, so avg_xact_time, avg_query_time, cl_waiting, maxwait, and per-pool sv_active/sv_idle are available as time series rather than point-in-time snapshots you took during the incident.
• Plotting avg_xact_time against avg_query_time on the same dashboard makes the idle-in-transaction gap visible at a glance, including the slow creep that precedes any queuing.
• Per-pool breakdowns show whether one (database, user) pool is being parked on while others are healthy, which aggregate views hide.
• Correlating PgBouncer saturation signals with PostgreSQL-side activity (idle-in-transaction session counts, backend states) on one dashboard confirms the diagnosis in minutes instead of requiring manual cross-checking during an incident.
• Because PgBouncer exposes no native idle-in-transaction alert, Netdata’s alerting on derived combinations (transaction-to-query time ratio rising, sv_idle at zero, maxwait sustained) fills exactly the gap this failure mode exploits.

Related guides

• How PgBouncer actually works in production: a mental model for operators
• PgBouncer avg_wait_time high: the latency the pool itself is injecting
• PgBouncer maxwait high: the oldest client waiter and how close it is to timing out
• PgBouncer client connection leak: idle clients that never disconnect
• PgBouncer advisory locks in transaction mode: orphaned locks and mysterious contention
• PgBouncer capacity planning: runway for pools, clients, and PostgreSQL slots
• PgBouncer monitoring checklist: the signals every connection pooler needs