A reporting query has been running for six hours, and another session has sat idle inside an open transaction all morning. What does that do to version cleanup, undo or rollback-segment growth, and table size, and how would you diagnose and prevent it?

## The mechanism in one line Both sessions have a snapshot, so the engine must keep every row version that any snapshot that old could need. Cleanup cannot advance past them, and every table in the database pays. ## What you observe, by engine family **Undo-based (InnoDB, Oracle).** The current row lives in place; older versions are reconstructed from undo. While an old read view exists, undo cannot be purged, so: - the undo tablespace or rollback segments grow steadily; - **version chains lengthen**: a row updated 200,000 times since the old snapshot started requires walking many undo records to build any older view, and even ordinary reads pay more; - delete-marked index records are not purged, so indexes are scanned with dead entries in them; - in the extreme, undo space is exhausted and DML starts failing, which is a full write outage. A notorious pattern is a single hot counter row updated thousands of times a second while a long report runs: the chain for that one row grows so long that reads on it collapse. **Append-style (PostgreSQL-style heaps).** Dead tuples accumulate in the table's pages and in every index: - table and index files grow continuously even at constant row count; - sequential scans read mostly dead tuples, so I/O per useful row rises; - the buffer pool fills with pages of garbage, evicting useful data; - autovacuum wakes, scans, finds everything above the horizon, and exits with nothing freed — repeatedly, burning I/O for no benefit; - in the extreme, transaction-id wraparound protection kicks in and the engine takes aggressive measures to prevent data loss, up to refusing new write transactions. ## Diagnosis, in order 1. **Find the oldest transaction and its state.** Sort sessions by transaction start time. Record: age, state (`active` vs `idle in transaction`), the application name, the client host and the current or last statement. This one query usually names the culprit. 2. **Check the non-obvious horizon holders**, because they do not appear as ordinary sessions: abandoned **prepared/two-phase transactions** (they survive restarts and are invisible in most dashboards) and **stale replication slots** or a replica with feedback enabled running a long report. 3. **Correlate timelines.** Growth that began exactly when the oldest transaction started is conclusive; growth from a genuinely higher write rate is not the same problem. 4. **Confirm the cleanup side.** Look at dead-version estimates and last-cleanup timestamps: cleanup *running* but dead counts *not falling* is the signature of a pinned horizon, as opposed to cleanup being starved of I/O or throttled too aggressively. 5. **Distinguish the two failure modes.** Pinned horizon means cleanup cannot free anything. Insufficient cleanup capacity means it could, but is not keeping up with churn. The fixes are opposite — kill a session versus tune concurrency and throttling — so getting this wrong wastes an outage. ## Remediation Short term: end the offending transaction. Cancel the query or terminate the session — after checking what it is, because killing a legitimate six-hour month-end report has its own cost. Once the horizon advances, cleanup can reclaim; note that reclaimed space is returned for reuse inside the object, so files will not shrink and the table will simply stop growing. Medium term, the controls that actually prevent recurrence: - **`idle_in_transaction_session_timeout`** (or its equivalent) — the single highest-value setting, since idle transactions have no legitimate reason to persist. - **Statement timeout** on OLTP roles, with a separate, more permissive role for reporting so one policy does not have to serve both. - **Transaction hygiene in application code**: begin late, commit early, and never hold a transaction across an HTTP call, a queue publish, or user think time. Autocommit-by-default plus explicit short transactions beats a framework that silently opens one per request. - **Chunked batch jobs**: delete or backfill in bounded batches, committing between them. One 50-million-row transaction pins the horizon for hours *and* produces a mountain of garbage at once; 5,000 batches of 10,000 rows let cleanup interleave and keep the horizon young. - **Move long reads off the primary**, accepting that if you enable replica feedback to stop query cancellation you have re-coupled the primary's cleanup to the replica's longest query — a deliberate trade, not a free win. - **Monitor oldest-transaction age** with an alert well before the pain (say 15 minutes for OLTP), plus undo size or dead-tuple ratio. Age is the leading indicator; disk usage is lagging. ## The framing that impresses Say explicitly that a long transaction is not a local cost to its own session — it is a **global tax on reclamation for the whole database**, and that the durable fix is bounding transaction lifetime by policy rather than hunting offenders one at a time.