PostgreSQL MVCC and VACUUM Explained

Every row version (a tuple) starts with a fixed header of 23 bytes on most machines. Three fields in it implement MVCC:

• t_xmin: the ID of the transaction that inserted this version.
• t_xmax: the ID of the transaction that deleted or replaced it, or 0 if none has.
• t_ctid: the location of this version, or of the newer version that replaced it.

You can watch them change. On PostgreSQL 18.6, inserting a row into an accounts table in transaction 755 gave a version with xmin = 755, xmax = 0 at location (0,1) (page 0, item 1). Then UPDATE accounts SET balance = 90 ran in transaction 756. Reading the raw page with the pageinspect extension showed two versions side by side:

• Item 1: xmin 755, xmax 756, ctid (0,2). This is the old version, stamped as replaced by 756 and pointing to its successor.
• Item 2: xmin 756, xmax 0, ctid (0,2). This is the new, current version.

A DELETE in transaction 757 simply set item 2's xmax to 757. Nothing was erased; both versions were still on the page. When a query reads the table, Postgres checks each version's xmin and xmax against the query's snapshot, the set of transactions that had committed when it started, and returns only the version that snapshot should see.

Because an update leaves the old version in place, a transaction that started earlier can keep reading it while the update proceeds. The writer creates version 2; the reader's snapshot says transaction 756 had not committed when it started, so it still sees version 1. Neither waits for the other.

How long a transaction keeps a snapshot depends on the isolation level:

• Read Committed (the default) takes a new snapshot for each statement, so each statement sees all changes committed before it began.
• Repeatable Read and Serializable take one snapshot at the transaction's first statement and keep it for the whole transaction.

That second point is the root of most VACUUM problems. A version can only be removed once no running transaction's snapshot could still see it. Postgres tracks the oldest such snapshot in the whole cluster, the xmin horizon. Any version replaced after that point must stay, in every table, however busy. One long transaction, even an idle one, holds the horizon back for everybody.

Writers still block writers: two transactions updating the same row take turns, because the second must wait to see whether the first commits before it can decide what to update.

A version that no snapshot can see any more is dead. Dead tuples still occupy space in the table's pages and still have index entries pointing at them, until vacuum removes them.

The experiment: a table of 100,000 rows with autovacuum disabled took 12 MB. A single UPDATE orders SET status = 'paid' on every row wrote 100,000 new versions. pg_stat_user_tables then reported 100,000 dead tuples, and the table file had doubled to 24 MB with no more live data than before.

Dead tuples cost more than disk space:

• Sequential scans read them. A scan of the 24 MB table reads twice the pages for the same rows.
• Indexes grow. Each new version that is not a HOT update (see below) needs new index entries, and the old entries remain until vacuum (or B-tree cleanup during later inserts) removes them.
• Caches hold less useful data, because pages are half full of rows nobody can see.

Bloat is the long-term version: space inside the table that is free or dead, beyond what the workload needs. Some free space is healthy, since new versions can use it without growing the file. Bloat becomes a problem when vacuum falls behind and the file keeps growing.

Standard VACUUM scans the table, removes dead versions and their index entries, and records the freed space in the free space map so future inserts and updates can reuse it. It runs alongside normal reads and writes. It also updates the visibility map, which marks pages whose rows are all visible to everyone. Index-only scans and later vacuums use that map to skip work.

What it does not do is shrink the file. The PostgreSQL documentation says standard VACUUM will not return space to the operating system, except when pages at the end of the table become entirely free and it can briefly take an exclusive lock to truncate them. The experiments showed both cases. After deleting the only row in a small table, VACUUM truncated it to zero pages. After the 100,000-row update, the second VACUUM reported tuples: 100000 removed, yet the file stayed at 24 MB. The space was reusable but not returned.

The experiment also showed the blocker. With a Repeatable Read transaction that had started before the update still open, VACUUM VERBOSE reported 100000 are dead but not yet removable. That transaction's snapshot could still see the old versions. Once it committed, the next VACUUM removed all of them.

VACUUM FULL rewrites the table into a new file without the dead space. In the experiment it brought the file back to 12 MB. The documentation warns that it is much slower and takes an ACCESS EXCLUSIVE lock on the table, which blocks all reads and writes. It also needs extra disk space for the new copy while it runs. The documentation's goal for routine maintenance is to vacuum often enough that VACUUM FULL is never needed.

A heap-only tuple (HOT) update avoids some of MVCC's cost. The PostgreSQL docs list two conditions:

• The update does not change any column used by the table's indexes (BRIN indexes excepted).
• The page holding the old version has enough free space for the new one.

When both hold, the new version goes on the same page and no new index entries are written. Older versions in the chain can be removed during normal page access, even by a SELECT, without waiting for vacuum.

The second condition is the one you control. By default, tables are filled to 100%, so a freshly loaded table has no room for new versions on the same page. The experiment loaded two identical 100,000-row tables and updated a non-indexed status column in every row:

• Default fill factor (100): 0 of 100,000 updates were HOT.
• fillfactor = 70: 41,306 of 100,000 updates were HOT, for a heap of 27 MB instead of 24 MB.

Leaving 30% of each page free made about 41% of the updates HOT on the first pass. A lower fill factor costs space up front and makes the table larger to scan. It suits tables with frequent updates to non-indexed columns, such as status flags and counters.

Autovacuum is on by default and should stay on. A launcher process starts a worker in each database every autovacuum_naptime (default 1 minute), with at most autovacuum_max_workers (default 3) running at once. A table is vacuumed when its obsolete tuples since the last vacuum exceed the vacuum threshold:

vacuum threshold = min(autovacuum_vacuum_max_threshold, autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples)

With the PostgreSQL 18 defaults of 50, 0.2 and 100,000,000:

• 1 million rows: 50 + 0.2 × 1,000,000 = 200,050 dead tuples before autovacuum starts.
• 100 million rows: 50 + 0.2 × 100,000,000 ≈ 20 million.
• 1 billion rows: 50 + 0.2 × 1,000,000,000 ≈ 200 million, capped at 100 million by autovacuum_vacuum_max_threshold, which is new in PostgreSQL 18.

Large tables are where the scale factor hurts: waiting for 20% of a big table to be dead means vacuum runs rarely and has a lot to do when it does. The usual fix is per-table settings, for example ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01). Inserts trigger vacuums too: a separate threshold of 1,000 tuples plus 0.2 of the unfrozen part of the table, which matters for append-only tables that never update.

Autovacuum is also throttled. It sleeps 2 ms (autovacuum_vacuum_cost_delay) each time it accumulates 200 cost units (vacuum_cost_limit). The limit is shared across all running workers, so adding workers does not add throughput unless you raise the limit too. On busy systems, raising the cost limit is often the most useful single change.

Transaction IDs are 32 bits and compared with modulo-2³² arithmetic. For any XID, the two billion before it are in the past and the two billion after it are in the future. The documentation spells out the consequence: if a row version survived more than about two billion transactions, it would suddenly appear to be in the future, and therefore invisible. To prevent that, every table must be vacuumed at least once every two billion transactions.

What vacuum does about it is freezing. A frozen row version is treated as older than every normal XID, so it stays visible however far the counter moves. Each table records its oldest unfrozen XID in pg_class.relfrozenxid, and several settings act on its age:

• vacuum_freeze_min_age (50 million): rows older than this are frozen when vacuum visits their page.
• vacuum_freeze_table_age (150 million): vacuum does an aggressive scan of every page that might hold unfrozen XIDs.
• autovacuum_freeze_max_age (200 million): an anti-wraparound autovacuum is forced, even if autovacuum is disabled, and it does not yield to conflicting locks.
• vacuum_failsafe_age (1.6 billion): vacuum drops its throttling and skips non-essential work such as index vacuuming to finish faster.

If all that fails, Postgres warns once a database is within 40 million transactions of wraparound. Within 3 million, it refuses commands that assign new XIDs, which means every write. Recovery, per the docs: resolve old prepared transactions, end long-running transactions, drop stale replication slots, then run VACUUM as a superuser. Do not use VACUUM FULL, which needs an XID itself.

These queries cover the four questions that matter: which tables need vacuum, what is holding cleanup back, and how close each table is to a forced freeze. They were run on PostgreSQL 18.6:

1-- 1. Tables with the most dead tuples, and when autovacuum last ran2SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count3FROM pg_stat_user_tables4ORDER BY n_dead_tup DESC5LIMIT 10;6 7-- 2. Sessions holding back cleanup: the oldest snapshots8SELECT pid, state, age(backend_xmin) AS xmin_age, now() - xact_start AS open_for, left(query, 60) AS query9FROM pg_stat_activity10WHERE backend_xmin IS NOT NULL11ORDER BY age(backend_xmin) DESC12LIMIT 5;13 14-- 3. Other things that hold the horizon: replication slots and prepared transactions15SELECT slot_name, active, age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age FROM pg_replication_slots;16SELECT gid, prepared, age(transaction) AS xid_age FROM pg_prepared_xacts;17 18-- 4. Wraparound distance: oldest unfrozen XID per table19SELECT c.oid::regclass AS table_name, age(c.relfrozenxid) AS xid_age20FROM pg_class c21WHERE c.relkind IN ('r', 'm', 't')22ORDER BY age(c.relfrozenxid) DESC23LIMIT 5;

What to alert on:

• Dead tuples growing faster than autovacuum clears them in a hot table, or last_autovacuum far in the past for a table that changes.
• Any session whose backend_xmin age keeps growing, especially idle in transaction. This is the most common cause of bloat across a whole cluster.
• Inactive replication slots with a large xmin age. A slot for a replica that no longer exists holds the horizon forever.
• age(relfrozenxid) approaching autovacuum_freeze_max_age and staying there. A healthy table is frozen and its age drops; a table that stays near the limit has a vacuum that is not finishing.

None of these require extensions, and all are cheap enough to run every minute from a monitoring system.

Run the experiments in this guide on a disposable PostgreSQL 18 container (docker run -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:18):

1. Create a table with autovacuum_enabled = off, insert one row, update it, and read the page with heap_page_items(get_raw_page('accounts', 0)) to see both versions' t_xmin, t_xmax and t_ctid. 2. Load 100,000 rows, update every row, and compare pg_relation_size and n_dead_tup before and after. 3. In a second session, start BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM orders; and leave it open. Update every row again and run VACUUM (VERBOSE) orders; find the "dead but not yet removable" count. 4. Commit the second session, vacuum again, and confirm the dead tuples are removed but the file size is unchanged. Then run VACUUM FULL and measure again. 5. Repeat the update on two tables with fillfactor 100 and 70, and compare n_tup_hot_upd. 6. Compute the autovacuum trigger point for your largest table from the formula, and run the four monitoring queries.

Pass criterion: you can show an old snapshot blocking cleanup and point to it in pg_stat_activity, and your HOT ratio rises with the lower fill factor.