Old open transaction holding locks and blocking VACUUM

A transaction has been open for minutes or hours without issuing new queries — it holds row locks, pins the transaction ID horizon, and prevents autovacuum from cleaning dead rows that other transactions can see.

Diagnose it

-- Backends with old open transactions (regardless of current state):
SELECT pid,
       usename,
       application_name,
       state,
       backend_xid,
       backend_xmin,
       now() - xact_start        AS txn_age,
       now() - state_change      AS in_current_state,
       LEFT(query, 120)          AS last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY txn_age DESC NULLS LAST;

-- Locks held by the oldest transaction:
SELECT locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE pid = <pid_of_old_txn>;

Why it happens

An open transaction pins backend_xmin — the oldest transaction ID that any
live backend might still need to read. VACUUM cannot remove dead tuple versions newer than
this horizon. On a busy database, even a transaction idle for a few minutes can accumulate
a large VACUUM debt, leading to table bloat. Logical replication also stalls if the publisher
has old backend_xmin entries.

How to fix it

-- Find the transaction pinning the oldest xmin:
SELECT pid, usename, application_name, state,
       age(backend_xmin)  AS xmin_age,
       now() - xact_start AS txn_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC NULLS LAST;

-- Terminate it (after confirming with the application team):
SELECT pg_terminate_backend(<pid>);

Prevent it next time

Set idle_in_transaction_session_timeout to automatically close sessions that
go idle inside a transaction. Monitor max(age(backend_xmin)) from
pg_stat_activity as an alert metric — anything above a few thousand transactions
in OLTP, or above the autovacuum freeze threshold in long-running analytics, warrants
investigation (guidance).