MVCC, Dead Tuples and What VACUUM Does Not Do

Plain VACUUM does not give disk back to the operating system. It finds dead row versions and marks their space reusable inside the file the table already occupies. The file does not shrink. And if one transaction anywhere in the database is old enough, VACUUM will not even remove the dead rows — it will run, on schedule, report success, and free nothing.

Postgres never overwrites a row. An UPDATE writes a whole new version of the row and stamps the old one with the updating transaction's id in its xmax; a DELETE just does the stamping. Both versions stay in the heap. The old one is dead only in the sense that new transactions will ignore it, and it can be removed only once no snapshot that could still see it exists.

VACUUM reduces that question to a single number, computed once when it starts: the oldest transaction id that any running transaction might still need. Postgres 15 and later print it in VACUUM VERBOSE as the removable cutoff; 14 and earlier called it oldest xmin. Every dead version whose xmax is newer than that number is skipped. Not deferred, not queued — skipped, and counted in the line N are dead but not yet removable.

The panel runs ten minutes of an update-heavy workload against a 5,000-row table and shows what each autovacuum round decided and why. The slider that carries the lesson is oldest transaction held open. It starts at 600 s: one session opened a REPEATABLE READ transaction before the workload began and never committed it.

At the default settings autovacuum fires at every single round, and the decision log says the same thing at the first one and at the tenth: the cutoff is stuck at xid 1,000, every dead version has an xmax in the tens of thousands, nothing qualifies. The heap climbs from 4.9 MB to roughly eight times that while last_autovacuum keeps ticking forward.

Every control re-runs the whole ten minutes, so move one and read the result as a fresh run. Drag oldest transaction held open to 300 s: the session now commits half way through, the round at 360 s removes almost every dead version at once, and dead, not yet removable falls to 0 — while heap on disk stays at 26.0 MB against 4.9 MB of live rows. That is the whole lesson in one drag. Nothing leaked, vacuum did exactly what it promises, and the file is still five times the size of its contents.

Why running it more often changes nothing

Set the hold back to 600 s and drag autovacuum_vacuum_scale_factor from 0.20 down to 0.02. The trigger threshold drops from about 1,050 dead tuples to about 150, so every round qualifies immediately — and the final heap size does not move at all. Frequency is not the variable. The cutoff is.

The trigger is arithmetic on statistics, and it is worth knowing exactly: autovacuum takes a table when the tuples obsoleted since the last run exceed autovacuum_vacuum_threshold (default 50) plus autovacuum_vacuum_scale_factor (default 0.2) times pg_class.reltuples. On a 500-million-row table that is 100 million dead tuples before a round even qualifies, which is why the scale factor is the setting people override per-table first. PostgreSQL 18 added autovacuum_vacuum_max_threshold, default 100,000,000, as a ceiling on that whole sum. Every one of those knobs decides when a round starts. None of them decides what it is allowed to remove.

The cutoff is not per-table and not per-vacuum-run. It is a property of the whole set of transactions Postgres considers running, computed fresh each time a vacuum starts. For an ordinary table it is the oldest of:

• the xmin of every snapshot held by a session in the same database, and
• the transaction id of every session that has written anything and not yet committed, whether or not it currently holds a snapshot, and
• the xmin of every replication slot, and the feedback xmin sent by any standby running with hot_standby_feedback = on — these two hold the cutoff back regardless of database, and
• the transaction id of every entry in pg_prepared_xacts in that database — a two-phase commit nobody ever resolved, which survives restarts.

That second bullet is the one that turns a harmless-looking session into a disaster. Under READ COMMITTED, a session that is idle in transaction and has only read releases its snapshot at the end of each statement, so it stops holding the cutoff. Under REPEATABLE READ or SERIALIZABLE, the first snapshot is registered for the life of the transaction and never released. And any session that has performed a single write owns a transaction id, which pins the cutoff for as long as the transaction is open no matter what the isolation level is. "Idle in transaction is fine, it isn't doing anything" is true only for the read-only READ COMMITTED case.

The blast radius is the whole database, not the table the long transaction touched. A reporting query held open against one table stops vacuum from removing a single dead row in every other table in that database. Sessions connected to a different database in the same cluster are ignored for ordinary tables — but not for shared catalogs, and not when the holder is a replication slot or standby feedback.

Where the space actually goes

Set the hold to 300 s. The rounds at 60, 120, 180, 240 and 300 s remove nothing, and by the time the fifth one finishes the heap is 22.5 MB. At 360 s the transaction is gone, and that single round removes almost every dead version at once — the unremovable readout drops to zero. The heap on disk is 26.0 MB when the run ends, and stays there, with 21 MB of free slots inside it.

That is not a failure. It is the documented contract. Plain VACUUM "reclaims space and makes it available for re-use", and the extra space "is not returned to the operating system (in most cases); it's just kept available for re-use within the same table". The freed slots go into the free space map, and the next INSERT or new row version lands in them instead of extending the file. Look at the page map with the hold at 0: pages are a mix of live rows and free slots, and the file has stopped growing at 8.4 MB against 4.9 MB of live rows. That steady state — minimum size, plus whatever churn happens between two vacuum runs — is what routine vacuuming is for.

What sets the steady state is the longest gap before a round qualifies, not the average. With the hold at 0, push autovacuum_vacuum_threshold to 5,000 and autovacuum_vacuum_scale_factor to 0.40 and the first qualifying round arrives late: the heap reaches 11.9 MB before anything is reclaimed, and then sits at 11.9 MB for the rest of the run while reusing its own free space perfectly well. A file that has grown once does not un-grow. This is why a single unmonitored batch job can leave a table permanently larger, and why the bloat you are looking at today is usually a record of the worst hour this table ever had.

The "in most cases" is the one exception, and the simulation implements it: at the end of a run, VACUUM truncates pages off the end of the file if — and only if — a contiguous tail of pages is entirely empty and it can grab a brief ACCESS EXCLUSIVE lock. With rows dying at random positions, that tail almost never forms. Watch the last line of each run in the decision log: "no trailing page was entirely empty". Bulk-deleting the newest rows can produce a real truncation; an update-heavy workload essentially never does.

VACUUM FULL is the thing that actually returns space, and the log spells out its price: it writes a complete new copy of the heap and every index, so it needs as much free disk again as the live data occupies, and it holds ACCESS EXCLUSIVE for the duration — no reads, no writes, not even an EXPLAIN. CLUSTER and the table-rewriting forms of ALTER TABLE have the same cost and the same lock. Autovacuum will never issue VACUUM FULL on your behalf. If you need the space back without the outage, that is what pg_repack exists for.

The parts that stay broken after you fix the transaction

With the hold at 0 and everything else at its default, toggle HOT off and on and watch the decision log rather than the readouts. With HOT updates allowed, page pruning removes 24,212 superseded versions during ordinary UPDATE statements — no vacuum, no index work — autovacuum qualifies at seven rounds instead of ten, and index vacuuming deletes 4,729 entries instead of 36,000. HOT is doing two thirds of the cleanup before vacuum is even asked.

And the heap ends at 8.4 MB either way. HOT needs free space on the page the old version already occupies, and a freshly packed table at the default fillfactor of 100 has none, so the first update to land on any given page cannot be HOT and extends the file instead. The whole 4.9 MB → 8.4 MB climb happens inside the first minute, before a single vacuum round has run. Real pruning blunts that a little — it can free a non-HOT dead tuple's space too — but the shape holds: the high-water mark is set early, largely before HOT can apply. That is the entire argument for lowering fillfactor on an update-heavy table: you are not saving space, you are buying the room that keeps updates HOT.

Now do it with the hold at 600 s. HOT saves you nothing. Pruning obeys exactly the same cutoff as vacuum, so it frees nothing; and a HOT update needs free space on the page, which is precisely what the cutoff is preventing anyone from creating. The decision log says so directly. This is the general shape of the failure: every space-recovery mechanism in Postgres is downstream of the same visibility test, so one stuck cutoff disables all of them at once.

Index files have their own version of the problem. Index vacuuming removes the entries that point at removed heap tuples and marks those index pages reusable, but a B-tree never gives pages back to the operating system, never merges partly-full pages, and deletes a page only once it has become entirely empty. Even then the page is not reusable immediately: it is stamped with an XID and recycled only when that XID is old enough to be visible to everyone, which is the same horizon test again. The log reports the index's high-water mark separately from its live size for that reason. Run the 300 s hold and read that line: the index peaked at 856 kB and now holds 160 kB of live entries. After a bloat episode the heap re-densifies on its own; the index does not. REINDEX CONCURRENTLY is the fix, and it is a separate decision from anything vacuum does — see index bloat.

Two more boundaries the simulation does not model. First, a vacuum that is slowed to a crawl by autovacuum_vacuum_cost_limit can fall permanently behind a hot table even with a perfectly healthy cutoff; that is a throughput problem, not a visibility problem, and it is fixed with per-table autovacuum tuning rather than by hunting for old transactions. Second, if the cutoff stays pinned long enough, the dead tuples stop being the emergency: unfrozen transaction ids age toward transaction ID wraparound, Postgres starts launching anti-wraparound vacuums that also cannot advance, and eventually it refuses new write transactions entirely. The bloat is the early symptom of that, not a separate problem.

Checking it on a real system

The diagnostic is three queries, in this order. First, confirm that vacuum is running and still not winning — high n_dead_tup together with a recent last_autovacuum is the signature this lesson is about, and it is the case a dashboard showing "autovacuum: OK" will hide from you:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Note that after a vacuum that could not remove anything, Postgres reports the still-unremovable tuples back into n_dead_tup rather than zeroing it — so the counter genuinely stays high across successful runs rather than resetting and re-climbing.

Second, find who holds the cutoff. This is the query that ends the incident:

SELECT pid, datname, state, backend_xid, backend_xmin,
       age(coalesce(backend_xmin, backend_xid)) AS held_age,
       now() - xact_start AS open_for,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL
ORDER BY age(coalesce(backend_xmin, backend_xid)) DESC;

SELECT slot_name, active, xmin, catalog_xmin, age(xmin) FROM pg_replication_slots;
SELECT gid, prepared, age(transaction) AS xid_age FROM pg_prepared_xacts;

Both columns matter, and filtering on backend_xmin alone is the common mistake. A session that has written and then gone idle in transaction under READ COMMITTED has released its snapshot, so its backend_xmin is null — but its backend_xid is still assigned and still holds the horizon. That is exactly the session the previous section warned about, and a query that only looks at backend_xmin will report that nobody is blocking you.

A backend whose held_age is in the millions is your answer; pg_terminate_backend(pid) ends it. An inactive replication slot with an old xmin is the version of this that survives restarts and outlives the engineer who created it. The documentation for that column is explicit: a slot's xmin is "the oldest transaction that this slot needs the database to retain. VACUUM cannot remove tuples deleted by any later transaction."

Third, get the arithmetic from vacuum itself rather than inferring it. VACUUM (VERBOSE) mytable; prints, on Postgres 15 and later:

tuples: 0 removed, 5000 remain, 36000 are dead but not yet removable
removable cutoff: 1000, which was 36000 XIDs old when operation ended

Those two lines together are conclusive. A large third number with a cutoff that is tens of thousands of XIDs old is not a tuning problem and no amount of extra vacuuming will touch it. On Postgres 14 and earlier the same information arrives as one line ending in oldest xmin: 1000. A large third number with a cutoff only a few hundred XIDs old is the opposite diagnosis: vacuum is fine and simply has not caught up yet.

One preventative setting is worth more than all the monitoring: idle_in_transaction_session_timeout, set to something like 5min globally and raised only for the roles that genuinely need it. It ends the idle-in-transaction case and nothing else, which is the point — but note what it does not cover: a session actually running a six-hour analytics query is not idle and survives it. That one needs statement_timeout, or transaction_timeout, which PostgreSQL 17 added to bound an entire transaction whether it is working or waiting. Pair whichever you set with an alert on max(age(coalesce(backend_xmin, backend_xid))) rather than on dead tuple counts, because the tuple count is the symptom and the xmin age is the cause.

Next, the two mechanisms this one sits between: the map that lets vacuum skip pages and lets the planner skip the heap, the visibility map, and the deadline that turns unvacuumable bloat into an outage, transaction ID wraparound.