PostgreSQL long idle transaction: find the session holding back cleanup

The symptoms

• •Autovacuum workers are running but the targeted relation's n_dead_tup keeps climbing because xmin horizons cannot advance.
• •pg_stat_activity shows backend_state = 'idle' combined with backend_xmin older than other transactions, indicating a snapshot is held without work being done.
• •pg_locks reports granted virtualxid and transactionid locks held by a session with no corresponding active query, blocking other writers waiting on the same transactionid.
• •Replication or logical decoding lag grows even though the publisher is idle, because a slot is anchored to an idle transaction's snapshot.
• •Application-side errors surface as 'cannot serialize access due to concurrent update' or lock-wait timeouts on rows that no active query should be touching.

Likely causes

• •An application connection pool returned a session to the pool while a BEGIN block had not been committed, leaving an idle-in-transaction backend holding xmin.
• •A developer opened psql or a GUI client, ran a SELECT, then left the window open inside an implicit transaction, pinning tuples and replication slots.
• •An ORM connection leaked between requests because autocommit was disabled at the driver level and the request handler exited before COMMIT.
• •A long-running report, EXPLAIN, or pg_dump session is in a transaction that has finished its statement but not closed, so backend_xmin still anchors cleanup.
• •Application code swallows a deadlock or timeout exception inside a transaction, retries without releasing the original connection, and accumulates idle holders.

First ten minutes

1. 01Snapshot the current state of pg_stat_activity so later comparisons are anchored, and record the timestamp so xmin age is interpretable.
2. 02Filter pg_stat_activity to state IN ('idle in transaction', 'idle in transaction (aborted)') and rank candidates by xact_start or backend_xmin age.
3. 03Read the application_name, client_addr, and query text of the oldest idle-in-transaction session to identify the owning component before any termination.
4. 04Cross-reference the candidate pid in pg_locks to confirm whether granted transactionid, virtualxid, or relation locks are blocking other backends.
5. 05Check for replication slots anchored to the same xmin horizon via pg_replication_slots, because terminating a slot holder can stall a replica.
6. 06Decide between letting idle_in_transaction_session_timeout close the session and an explicit pg_cancel_backend or pg_terminate_backend, based on replication impact.
7. 07Before any termination, capture the pid, usename, application_name, client_addr, query, and xact_start so post-incident analysis is possible.

Evidence to collect

• •Snapshot of pg_stat_activity filtered to idle-in-transaction states, ordered by xact_start ascending, captured before and after mitigation.
• •The candidate session's backend_xmin value and the current transaction xmin from pg_stat_activity, demonstrating that the idle session is the horizon holder.
• •Granted locks held by the candidate pid from pg_locks, including locktype, relation, page, tuple, and transactionid rows.
• •Replication slot state from pg_replication_slots, including active, restart_lsn, and confirmed_flush_lsn for any slot that might be anchored to the candidate xmin.
• •Per-relation autovacuum count from pg_stat_user_tables for the relation most likely affected, to show cleanup resumes after termination.

Where to look

• •The pg_stat_activity system catalog view, which exposes backend_xmin, xact_start, state, and query for every backend.
• •The pg_locks view, which pairs granted and awaited locks so you can see whether the idle session is the blocker of a real waiter.
• •The pg_replication_slots view, because logical and physical slots freeze at the slot's confirmed restart point and can be pinned by an idle xmin.
• •The autovacuum worker entries in pg_stat_activity, which will report relation targets that match the relations pinned by the idle xact.
• •The application's connection pool metrics and the database-side pg_stat_database.xact_commit versus xact_rollback counters, to distinguish leaks from real workload.

Diagnostic steps

1. 01Run a read-only query against pg_stat_activity filtering state IN ('idle in transaction', 'idle in transaction (aborted)') and order by xact_start to surface the oldest holder first.
2. 02Capture the candidate's backend_xmin and compare it to the cluster-wide transaction xmin; if the candidate is strictly older, it is the horizon holder.
3. 03Look up the candidate's pid in pg_locks; if granted transactionid or relation locks are present with no waiter, the session is pinning cleanup without contention yet.
4. 04Cross-check pg_replication_slots; if active = false and restart_lsn is older than recent WAL, the slot itself may be holding xmin even after the session is closed.
5. 05Inspect pg_stat_user_tables for the relation under cleanup suspicion and verify autovacuum_count and n_dead_tup trajectory before and after candidate removal.
6. 06Confirm application_name and client_addr from the candidate row to map the idle session to a pool, host, or tool before issuing any cancel or terminate.
7. 07Decide evidence-based action: if no replication slot is anchored, prefer pg_cancel_backend(pid) and observe state transition; escalate to pg_terminate_backend(pid) only if cancel does not release.

Common mistakes

• •Terminating a session that backs an active logical replication subscription, which can break replication and require slot recreation.
• •Confusing 'idle' with 'idle in transaction'; only the latter holds backend_xmin and blocks cleanup, so the wrong filter gives a false positive.
• •Killing the autovacuum worker instead of the idle holder, which only worsens cleanup without releasing the xmin horizon.
• •Reading backend_xid (the session's own current xid) instead of backend_xmin (the oldest xid the session can still see), which leads to misidentification.
• •Assuming 'no query' means no impact: an idle-in-transaction session with no current query still pins tuples via its snapshot until COMMIT or ROLLBACK.
• •Closing the application without releasing the connection, so the pool returns the same broken backend to a different request and the leak multiplies.

Safe fixes

• •If evidence shows the candidate is the xmin horizon holder and no replication slot is anchored to it, issue pg_cancel_backend(<pid>) and observe the state transition in pg_stat_activity within seconds.
• •If pg_cancel_backend does not release the session, escalate to pg_terminate_backend(<pid>) only after confirming replication slots are not anchored to that pid's xmin.
• •For a sustained prevention path, set idle_in_transaction_session_timeout to a bounded value such as several minutes at the server or role level so the server self-heals future leaks.
• •Fix the application owner by ensuring autocommit is enabled or by wrapping every request in BEGIN/COMMIT with a finally block that always ends the transaction, including on exception.
• •Configure the connection pool to validate and reset borrowed sessions on return, discarding any session whose previous transaction state was not idle.
• •If the culprit is a logical replication slot that itself has been idle too long, advance or drop the slot only after documenting confirmed_flush_lsn and confirming downstream consumers.

Prove the fix

1. 01After the candidate is released, pg_stat_activity no longer shows any session with state 'idle in transaction' whose backend_xmin equals the prior horizon value.
2. 02autovacuum runs against the previously pinned relation complete within a normal maintenance window, and n_dead_tup trends downward rather than climbing.
3. 03pg_stat_user_tables for the affected relation shows an increment in autovacuum_count and idx_scan or seq_scan resumes without lock-wait errors in logs.
4. 04If a replication slot was the secondary anchor, confirmed_flush_lsn advances on its consumer and no new 'replication slot cannot advance' errors appear in logs.
5. 05A monitoring query for idle_in_transaction_age_seconds stays below the configured idle_in_transaction_session_timeout for at least one full business cycle.
6. 06Application-side errors about concurrent update or lock-wait timeouts no longer reproduce for the rows in the previously pinned relation.

Prevention and next steps

• •Enable and tune idle_in_transaction_session_timeout at the server or role level so leaked transactions self-close within a bounded window.
• •Require autocommit at the driver layer, or enforce a code-review check that every BEGIN has a paired COMMIT or ROLLBACK reachable from every exit path.
• •Configure connection pools to run a lightweight validation query on session return and to discard any session whose backend state is not idle.
• •Alert on idle_in_transaction_age_seconds exceeding a fixed threshold and route the alert to the owning service rather than just the database team.
• •Monitor pg_replication_slots for active=false with stale restart_lsn, and reconcile slots whose downstream consumer is gone so they do not pin xmin indefinitely.

Safe commands and checks

psql -h <host> -p <port> -U <user> -d <dbname> -c "SELECT pid, usename, application_name, client_addr, state, xact_start, backend_xmin, query FROM pg_stat_activity WHERE state IN ('idle in transaction','idle in transaction (aborted)') ORDER BY xact_start ASC LIMIT 20;"
psql -h <host> -p <port> -U <user> -d <dbname> -c "SELECT pg_blocking_pids(pid) AS blockers, pid, usename, application_name, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event_type = 'Lock' LIMIT 20;"
psql -h <host> -p <port> -U <user> -d <dbname> -c "SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks WHERE pid = <pid> ORDER BY locktype, relation;"
psql -h <host> -p <port> -U <user> -d <dbname> -c "SELECT slot_name, plugin, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots;"
psql -h <host> -p <port> -U <user> -d <dbname> -c "SELECT schemaname, relname, n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > 0 ORDER BY n_dead_tup DESC LIMIT 10;"
psql -h <host> -p <port> -U <user> -d <dbname> -c "SELECT pid FROM pg_stat_activity WHERE pid = <pid> AND state IN ('idle in transaction','idle in transaction (aborted)');"