MVCC and VACUUM in PostgreSQL: Dead Tuples, Autovacuum Tuning, and What PG18 Changed

Every UPDATE in PostgreSQL is secretly an INSERT-plus-a-tombstone. The old row version stays on disk, fully readable to any transaction that might still need it, until a background process decides nobody needs it anymore. That process is VACUUM, and how well you understand it determines whether your tables stay fast for years or quietly bloat until queries crawl.

Most developers treat VACUUM as a mysterious janitor that occasionally runs and sometimes fails. But the mechanics are learnable, and once you understand how multiversion concurrency control (MVCC) actually stores rows, tuning VACUUM stops being folklore and becomes arithmetic. This post walks through the full lifecycle: how dead tuples are born, how VACUUM reclaims them, why the disk doesn’t shrink, and which knobs actually matter.

MVCC: Why Deleting a Row Doesn’t Delete Anything

PostgreSQL implements concurrency by keeping multiple versions of each row. Every tuple carries two hidden system columns: xmin, the transaction ID that created it, and xmax, the transaction ID that deleted or replaced it. A transaction reading the table compares those IDs against its own snapshot to decide which version of a row is visible to it.

When you UPDATE a row, PostgreSQL writes a brand-new tuple with the new values and sets xmax on the old one. When you DELETE, it just sets xmax. Nothing is overwritten or removed in place. The old versions — dead tuples — remain physically present until every transaction that could possibly see them has finished, and then a bit longer, until VACUUM gets to them.

You can see this directly. Open two sessions, and in the first one start a transaction that updates a row but doesn’t commit. In the second session, check the tuple’s state:

SELECT xmin, xmax, id, name FROM accounts WHERE id = 1;
-- The uncommitted updater still holds xmax on this tuple.
-- Any running snapshot may still need this "dead" version.

This design buys PostgreSQL its most valuable property: readers never block writers and writers never block readers. The price is that the table accumulates garbage as a normal side effect of ordinary work. A table churning through 100,000 updates per hour generates 100,000 dead tuples per hour that someone has to clean up.

What VACUUM Actually Does

VACUUM scans the table, identifies tuples that are no longer visible to any running transaction, and reclaims their space. Concretely, one pass does several things: it removes dead tuples from the heap, marks their slot space as reusable within existing pages, vacuums the indexes (removing index entries pointing at dead tuples), updates the visibility map so future index-only scans can skip pages, and advances the freeze bookkeeping that protects against transaction ID wraparound.

The critical subtlety: plain VACUUM never returns space to the operating system. It compacts space within existing pages, so subsequent inserts reuse them instead of allocating new pages — but the table’s file on disk stays exactly the same size. If you watch disk usage after VACUUM, you will see nothing change, and that is by design. Reclaiming pages would require exclusive access and rewrite the table.

That heavier operation exists as VACUUM FULL: it takes an ACCESS EXCLUSIVE lock, rewrites the entire table into a fresh, compact file, and returns the space to the OS. It is a maintenance window operation, not a cleanup tool. If you find yourself running VACUUM FULL regularly, the real fix is upstream — tune autovacuum so bloat never accumulates to the point where a rewrite seems necessary. The middle-ground option is pg_repack, an extension that achieves a similar rewrite online by building a shadow copy of the table and syncing changes via triggers.

Autovacuum: The Threshold Math

You almost never run VACUUM by hand. The autovacuum launcher wakes up periodically (every autovacuum_naptime, default one minute), consults the statistics collector, and starts worker processes on tables that have accumulated enough garbage.

“Enough” is a formula worth memorizing:

dead tuples > autovacuum_vacuum_threshold
           + autovacuum_vacuum_scale_factor * reltuples

Defaults are a threshold of 50 and a scale factor of 0.2. On a 1,000-row table, autovacuum triggers after 250 dead tuples — fine. On a 500-million-row table, the scale factor alone means waiting for 100 million dead tuples before a single vacuum runs. That is the single most common autovacuum misconfiguration in the wild: the defaults are calibrated for small tables and silently starve large ones.

The fix is per-table storage parameters. Lower the scale factor on your big, hot tables and leave the defaults for everything else:

ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 1000
);

-- Tables with heavy update churn benefit from aggressive tuning:
ALTER TABLE sessions SET (
    autovacuum_vacuum_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 0
);

Updates have their own, deliberately different trigger. Because HOT (heap-only tuple) updates reuse space in the same page, PostgreSQL vacuums a table after fewer insertions than dead-tuple threshold would suggest — the insert threshold uses autovacuum_vacuum_insert_scale_factor, defaulting to 0.2, primarily to maintain the visibility map.

The Throttle: Why Autovacuum Seems Slow

Autovacuum is deliberately throttled so it never competes with your workload. It operates on a cost budget: each page it touches (heap page read, page dirtied, index page scanned) accrues cost, and when the accumulated cost exceeds autovacuum_vacuum_cost_limit, the worker sleeps for autovacuum_vacuum_cost_delay (2ms by default) before continuing. The full list of these settings lives in the autovacuum documentation.

On a busy server this throttling can make workers so slow that garbage accumulates faster than it is cleaned — the classic “autovacuum can’t keep up” failure. Two levers fix it. Increase the global budget by raising autovacuum_max_workers (default 3; note the cost limit is divided among workers, so more workers means each gets a smaller share unless you raise the limit too), or lower the per-worker delay. Setting autovacuum_vacuum_cost_delay = 0 on specific tables removes throttling for those tables entirely — reasonable for a write-hot table with SSD storage, reckless for everything else.

Two more operational realities. First, VACUUM cannot remove a dead tuple if any open transaction might still see it — one forgotten idle-in-transaction session pins every dead tuple in the entire database. Monitor for them:

SELECT pid, now() - xact_start AS xact_age, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_age DESC;

Second, every write-heavy table needs enough memory to do its job efficiently. autovacuum_work_mem (defaulting to maintenance_work_mem) bounds how many dead tuple identifiers a worker can hold at once; when the array fills, the worker makes extra passes over the indexes. Raising it on vacuum-heavy servers reduces that churn. And vacuum_buffer_usage_limit (128kB to 16GB) controls how large a slice of the buffer pool VACUUM is allowed to use — by default it confines itself to a tiny ring so it never evicts your cache, at the cost of reading pages from disk repeatedly on large tables.

Postgres 18: AIO and Faster Vacuums

PostgreSQL 18 shipped the largest I/O change in the project’s recent history: an asynchronous I/O subsystem. Historically, Postgres read disk synchronously — one block at a time, waiting for each read to complete. The new AIO layer lets a backend submit many reads at once and continue working while they are in flight. The operations that benefit most are exactly the large sequential reads that VACUUM performs, alongside sequential scans and bitmap heap scans.

It is controlled by io_method, set at server start:

-- worker mode (default): a pool of io_workers processes handles reads
io_method = 'worker'
io_workers = 3   -- default; raise for fast NVMe storage

-- io_uring mode: lower overhead, requires Linux and a --with-liburing build
io_method = 'io_uring'

For vacuum workloads on NVMe storage, this means the scan phase can finally saturate the disk instead of the throttle. You can observe in-flight I/O through the new pg_aios monitoring view. If you run PG18 or later on Linux, verifying that AIO is actually enabled (and that io_workers matches your storage’s capability) is one of the highest-leverage checks you can do.

Monitoring: Know Your Bloat Before It Bites

The statistics collector tells you everything you need. The query below is the one to put in your dashboards — dead tuples trending up while last_autovacuum stays stuck is the early-warning signature of a table about to bloat:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup * 100.0 / greatest(n_live_tup, 1), 2) AS dead_pct,
       last_autovacuum,
       autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

As a rule of thumb, sustained dead-tuple ratios above 10-20% on a hot table mean autovacuum is losing the race. Also watch n_mod_since_analyze — bloat gets the attention, but stale statistics after heavy churn silently degrade query plans too.

A Practical Tuning Checklist

• Set per-table autovacuum_vacuum_scale_factor (0.01 or lower) on tables larger than a few million rows. Leave defaults for small tables.
• Ensure no long-running or idle-in-transaction sessions pin dead tuples. Set idle_in_transaction_session_timeout if your application allows it.
• Raise autovacuum_max_workers and autovacuum_vacuum_cost_limit together on servers with many hot tables; consider cost_delay = 0 per-table on SSD-backed write-hot tables.
• Increase autovacuum_work_mem (or maintenance_work_mem) where workers make repeated index passes.
• On PostgreSQL 18+, confirm io_method is enabled and sized to your storage.
• Treat VACUUM FULL as an emergency measure. If it appears in your runbook, tune autovacuum until it doesn’t need to be.

Wrapping Up

VACUUM is not housekeeping bolted onto PostgreSQL — it is one half of the MVCC bargain that gives you non-blocking reads and writes. Once you internalize that every update leaves a corpse behind and the formula that decides when the collector arrives, the tuning knobs stop being magic. Per-table scale factors on your big tables, sane memory settings, no pinned transactions, and AIO enabled on modern versions will carry most workloads a very long way. Check your pg_stat_user_tables today — the bloat you find early is the maintenance window you never have to schedule.