Why can a single session that has held one transaction open for hours stop a database's background version cleanup from reclaiming dead rows across every table, not just the tables that session touched?

## The rule cleanup must obey A row version can be reclaimed only when it is invisible to every snapshot that exists now or could be taken later. New snapshots always see the newest committed state, so "later" is not the problem. The binding constraint is the **oldest snapshot currently in use**. Concretely, the engine computes a horizon, the oldest transaction identifier that any live snapshot still cares about, and cleanup may reclaim only versions that became dead strictly before it. ## Why the effect is global, not per-table The horizon is a property of the *instance*, not of a table, because the engine cannot know in advance which rows a still-running transaction will look at next. A transaction that opened at 09:00 and has so far read only `orders` is legally entitled to issue `SELECT * FROM audit_log` at any moment, and it must see that table as of 09:00. The only safe rule is to preserve everything that died after 09:00 across the whole database. That is why one forgotten session in a client window can bloat tables it has never named. Engines differ in a detail worth knowing: where READ COMMITTED takes a fresh snapshot per statement, a read-only transaction sitting idle between statements may release its snapshot and stop pinning the horizon; but any transaction that has written holds an assigned transaction identifier and still constrains cleanup and freezing. Under REPEATABLE READ or SERIALIZABLE, the single transaction-scoped snapshot pins the horizon for the whole transaction, idle or not. ## Everything that can hold the horizon back - **Idle-in-transaction sessions.** An application that opened a transaction, ran one query and then went off to call an HTTP API, or a connection pool handing out connections with autocommit off, is the single most common cause. - **Genuinely long queries.** An hour-long analytical scan or a nightly export is a legitimate reader with an hour-old snapshot. - **Prepared (two-phase) transactions never resolved.** These survive disconnection and even restart, pinning the horizon indefinitely and silently. - **Replication and change-data-capture slots.** A slot that a consumer has stopped reading holds retention so the stream stays complete, which on some engines also holds the cleanup horizon. - **Standby feedback.** When a replica reports its oldest reader back to the primary to avoid having its own queries cancelled, a long report on the replica pins the primary's horizon. - **Long-lived cursors held across statements** inside a transaction, which is the same thing as a long transaction. ## What it looks like in production Disk usage climbs steadily. Cleanup runs on schedule and finishes quickly, reporting that many dead rows were found but could not be removed; that phrasing is the giveaway. Scans get slower over days. Nothing about the write rate has changed. Because cleanup *appears* to be running, teams often respond by making it more aggressive, which changes nothing: the constraint is correctness, not throughput. ## Diagnosing it Ask one question: what is the age of the oldest open transaction, and who holds it? Every engine exposes running sessions with their transaction start time, plus the state of prepared transactions and replication slots. Sort by age descending; the answer is usually at the top and usually measured in hours. Also check standby feedback if replicas exist. The healthy shape of this metric is flat and small, seconds to a couple of minutes. ## Fixing and preventing it - **Cap lifetimes mechanically.** Set a maximum statement duration, a maximum transaction duration, and an idle-in-transaction timeout that terminates offenders. Choose the numbers from your longest *legitimate* transaction, then enforce them; do not rely on developers remembering. - **Chunk batch work.** A job that deletes ten million rows should run as many short transactions with a bounded key range each, not one that stays open for an hour, because the single transaction pins the horizon for exactly as long as it runs and prevents cleanup of the very rows it is generating. - **Move long reads off the writer.** Give analytics a dedicated replica; if it uses feedback to the primary, you have merely relocated the problem, so decide explicitly whether you prefer replica query cancellations or primary bloat. - **Monitor retention holders.** Alert on oldest-transaction age and on bytes retained per replication slot, and treat an abandoned slot as an incident, because a dead consumer should not be allowed to fill the primary's disk. - **Audit for prepared transactions** if your stack uses distributed transactions; an orphaned one can pin history for weeks and survives restarts. ## The mental model Retention is a shared global resource with a single dial, and the dial is set by whoever is furthest behind. Any design that lets an arbitrary client hold that dial indefinitely has handed a client the ability to fill your disk.