Idle-in-Transaction Backends, kpi

​

At a glance

The number of backend connections sitting in the idle in transaction state: a client opened a transaction with BEGIN, did some work, and then stopped, holding the transaction open without committing or rolling back. This is one of PostgreSQL’s most distinctive and dangerous failure modes. An idle-in-transaction backend keeps locks held, pins the transaction horizon so autovacuum cannot reclaim dead tuples, and occupies a connection slot. A handful is normal churn; a sustained pile of them is a leaking application connection pool slowly strangling the database.

​

Calculation

SELECT count(*)
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)');

• now() - xact_start is the age of the open transaction. A backend idle in transaction for 200 milliseconds is fine; one idle for 45 minutes is holding the vacuum horizon back by 45 minutes.
• now() - state_change is how long it has been idle in this transaction specifically (since the last statement finished).
• application_name and client_addr attribute the leak to a service or host, which is how you find the offending code path.
• pid is what you feed to pg_terminate_backend(pid) if you need to kill it.

​

Worked example

The anti-pattern the deploy introduced (simplified):

  BEGIN;
  UPDATE orders SET status = 'paying' WHERE id = $1;
  -- transaction left OPEN while the app awaits the payment gateway
  result = call_payment_gateway(...)   <-- 2 to 30 seconds, sometimes hangs
  UPDATE orders SET status = 'paid' WHERE id = $1;
  COMMIT;

Why it piles up:
  - The gateway call is slow and occasionally stalls.
  - Every in-flight payment holds a transaction open for the call's duration.
  - At peak checkout volume, dozens of payments are in flight simultaneously,
    so dozens of transactions sit idle-in-transaction waiting on the network.
  - The slow payments pin the vacuum horizon and exhaust the pool.

1. Stop the bleeding now. Set a guard so these cannot accumulate forever: ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s'; SELECT pg_reload_conf();. Any transaction idle longer than 30 seconds is terminated by PostgreSQL automatically. This protects the database while the code is fixed.
2. Fix the code. Move the external gateway call outside the transaction. Open a transaction only to write the result: read the order, call the gateway with no open transaction, then BEGIN; UPDATE ...; COMMIT; to record the outcome. Network calls must never sit inside an open database transaction.

1. Idle-in-transaction is almost always an application bug, not a database problem. The fix lives in the code: a transaction left open across a slow operation (a network call, a user think-time, a forgotten commit). The database is the victim, not the cause.
2. The oldest transaction matters more than the count. Twenty backends idle for two seconds each are a minor pool nuisance. One backend idle for 40 minutes blocks vacuum cluster-wide and is the real emergency. Read the oldest xact_start, not just the headline number.
3. idle_in_transaction_session_timeout is your seatbelt. Setting a sane timeout (often 30 to 60 seconds for OLTP) means a leaking code path cannot pin the vacuum horizon indefinitely. It is a safety net, not a substitute for fixing the code, but every production primary should have one.

​

Sibling cards

​

Reconciling against the source

The live count: SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction'; The offenders, oldest first: SELECT pid, application_name, client_addr, state, now() - xact_start AS tx_age, now() - state_change AS idle_for, wait_event FROM pg_stat_activity WHERE state IN ('idle in transaction', 'idle in transaction (aborted)') ORDER BY xact_start; The vacuum horizon they pin: compare the oldest xact_start against SELECT datname, age(datfrozenxid) FROM pg_database; to see the wraparound pressure they are causing. Kill one safely: SELECT pg_terminate_backend(<pid>); rolls back the open transaction and frees the slot. Managed services: RDS / Aurora and Cloud SQL all expose pg_stat_activity directly via a normal client connection; there is no provider abstraction for this metric.

​

Known limitations / FAQs

​

Tracked live in Vortex IQ Nerve Centre