Long-Running Transaction Bloat

A transaction that holds a snapshot open far longer than it does real work pins the database's xmin horizon, so VACUUM cannot reclaim any dead tuple newer than the snapshot, anywhere in the cluster. Bloat accumulates, freezing stalls (raising transaction-ID wraparound risk), and rows the transaction modified stay locked. One forgotten transaction degrades the whole database, not just the tables it touched.

PostgreSQL transactions are meant to be short. A transaction that stays open for seconds to minutes while holding a snapshot causes several failure modes at once, and the common thread is that it holds back the xmin horizon.

The xmin horizon and bloat. Under MVCC, an UPDATE does not overwrite a row; it writes a new version and marks the old one dead, and a DELETE marks the row dead. A dead tuple can be reclaimed only once no snapshot could still need to see it. The oldest snapshot in the system defines that boundary, the xmin horizon, and VACUUM will not remove any tuple that became dead after it. A transaction that holds one snapshot for its whole life, a long-running query, or a REPEATABLE READ or SERIALIZABLE transaction, pins the horizon at the xmin it began with. From that moment VACUUM can reclaim nothing newer, regardless of which tables the long transaction actually touched, because the guarantee is database-wide. Dead tuples pile up, tables and their indexes grow, and the extra pages mean more I/O per query and worse plans. Its backend_xmin is visible in pg_stat_activity, and the dead tuples it strands show up as n_dead_tup in pg_stat_user_tables.

Transaction-ID wraparound. PostgreSQL's transaction IDs are 32-bit and must be frozen before they age past roughly two billion, or their tuples would appear to come from the future and become invisible. Freezing is VACUUM's job, and VACUUM can only freeze tuples older than the xmin horizon. A long-running transaction that pins the horizon therefore blocks freezing from advancing. If the oldest unfrozen age crosses autovacuum_freeze_max_age, autovacuum launches aggressive anti-wraparound runs; if the horizon stays pinned and the age keeps climbing toward the limit, PostgreSQL stops accepting new write transactions to protect the data ("database is not accepting commands to avoid wraparound data loss"). The transaction contributes to wraparound by blocking freezing, not by using up IDs itself.

Lock queue buildup. If the long transaction also wrote rows, it holds their row locks until it ends. Writers that need those rows queue behind it for its full duration, hold their own connections while they wait, and can exhaust the connection pool. This is the contention path, covered under lock_contention; a long transaction is one of its most common causes.

DDL blocking. An ALTER TABLE or similar DDL needs a table-level lock incompatible with ordinary traffic. A long-running transaction on the table blocks the DDL from acquiring it, and because the lock request is exclusive, every query arriving after it queues behind it. A routine migration turns into a traffic-blocking event, which is the interaction detailed under schema_migration_lock.

An idle-in-transaction session is the usual culprit behind all of these: an application or ORM opens a transaction, runs a statement, then stalls on external work or a code path that never commits, holding its snapshot and any locks it already took while doing no work at all.