How does an MVCC engine decide that a particular old row version is safe to reclaim? Explain the role of the oldest still-running transaction.

## Snapshots and visibility Under MVCC each transaction acquires a **snapshot** at its start (with `REPEATABLE READ`/`SERIALIZABLE`) or at each statement (with `READ COMMITTED`). A snapshot is essentially: which transactions had committed at that instant. Each row version carries the id of the transaction that created it and, once expired, the id that expired it. Visibility is then a comparison — was the creator committed as of my snapshot, and was the expirer not. So a version stays needed exactly as long as some snapshot exists for which it is the visible version. ## The horizon The engine cannot track every snapshot's needs individually and does not need to. It computes a single conservative bound, variously called the **cleanup horizon**, `xmin` horizon, low-water mark, or oldest read view: > horizon = the oldest transaction id that any currently active snapshot could still care about. A version expired by a transaction that committed **before** the horizon is invisible to every current and future snapshot, so removing it changes nothing observable — it is garbage. A version expired by a transaction **at or after** the horizon might still be needed and is retained. The horizon is deliberately conservative: it is cheap to compute and can never delete something still required, at the cost of sometimes keeping versions nobody actually reads. ## What pins the horizon Several things hold it back, and they are the standard checklist in an incident: 1. **A long-running transaction** — a six-hour analytical query or a batch job in one transaction. 2. **An idle-in-transaction session** — the worst case, because it does no work at all. An application that opened a transaction and then waited on a slow HTTP call, or a developer's console with an uncommitted `BEGIN`, pins the horizon indefinitely while consuming no visible resources. 3. **Replica feedback**: when a primary is configured to respect standby snapshots (so long reporting queries on the replica are not cancelled), the replica's oldest snapshot enters the primary's horizon calculation. 4. **Long-lived structural artifacts**: abandoned prepared (two-phase) transactions and stale replication slots pin the horizon and survive restarts, which makes them especially insidious. 5. **Uncommitted work on any table.** The transaction need not touch the bloating table at all. ## Why 'global, not per table' is the key insight Candidates often assume cleanup on `orders` only depends on transactions reading `orders`. It does not. The horizon is computed from transaction ids across the system, so a transaction that has read nothing but `settings` still blocks reclamation on `orders`, on `events`, and on the catalog. This is what turns one careless session into database-wide bloat. A related consequence: cleanup does not fail loudly. The background worker runs on schedule, scans the table, finds every dead version still above the horizon, and exits having freed nothing. Monitoring that only checks 'did cleanup run' looks green while the table doubles. ## Undo-based engines: same rule, different pain With an undo log, the same horizon decides when undo segments can be truncated. Old snapshots must be reconstructable, so undo cannot be discarded while any read view might need it. The visible symptom shifts from table bloat to **undo/rollback-segment growth**: the tablespace or undo log balloons, and version chains for hot rows grow long, so each read of a frequently-updated row must walk more undo records — reads get progressively slower even though the table looks fine. In append-style engines the same pinned horizon shows up as dead tuples in the heap and stale entries in indexes, plus rising sequential-scan cost because pages are mostly garbage. ## Operating implications - **Bound transaction lifetime.** Set an idle-in-transaction timeout, and a statement timeout for OLTP paths, so no session can pin the horizon indefinitely. - **Keep transactions off the network.** Never hold an open transaction across an external API call or user think-time — the classic source of idle-in-transaction. - **Batch, do not marathon.** A job that deletes 50 million rows in one transaction pins the horizon for its whole duration and creates a huge amount of garbage at once; chunked transactions let cleanup interleave. - **Monitor the age of the oldest transaction**, not just table size. It is the leading indicator; bloat is the lagging one. - **Understand the replica trade-off**: enabling feedback so long reports are not cancelled deliberately couples the primary's cleanup to replica query duration. That is a choice with a cost, and it should be made explicitly. ## The one-sentence rule A version dies only when nobody can prove they might still want it, and the engine proves that with a single global oldest-snapshot bound — so the cost of any old snapshot is paid by the entire database.