Runbook: idle in transaction

Why it matters: an open transaction — even idle — pins the xmin horizon: vacuum can't clean dead tuples (bloat grows), and it may hold locks that block DDL/queries.

Find them

SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age,
       now() - state_change AS idle_for,
       left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Immediate action

Terminate offenders older than your tolerance (coordinate first if it's a known batch job):

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '15 minutes';

Prevent

• idle_in_transaction_session_timeout = '10min' (server or per-role) — the single best guardrail.
• Root causes are almost always application-side: ORMs opening transactions eagerly, missing commit on error paths, humans in psql. Fix the client; the timeout is the seatbelt.
• Alert on max(now() - xact_start) > threshold, not just session counts.