Find long-running and 'idle in transaction' transactions

Command

SELECT pid,
       usename,
       datname,
       application_name,
       client_addr,
       state,
       now() - xact_start AS xact_age,
       now() - state_change AS in_state_for,
       backend_xid,
       age(backend_xmin) AS xmin_age,
       left(regexp_replace(query, '\s+', ' ', 'g'), 100) AS last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid()
  AND (now() - xact_start > interval '5 minutes' OR state LIKE 'idle in transaction%')
ORDER BY xact_start;

What it does

Shows sessions whose open transaction is older than 5 minutes or that sit idle inside a transaction; with transaction age, time in the current state, assigned XID and the age of the xmin they hold.

When to use it

When bloat grows, VACUUM reports 'dead row versions cannot be removed yet', during lock waits, or when a connection pool leak is suspected.

Caution

Long transactions (especially REPEATABLE READ/SERIALIZABLE or those holding open cursors) hold back VACUUM's cleanup horizon (xmin horizon) via backend_xmin, causing bloat and XID aging across the database. 'idle in transaction (aborted)' is a session that hit an error and is waiting for ROLLBACK. Permanent fix: per-role idle_in_transaction_session_timeout and correct transaction boundaries in the application.