VACUUM & Autovacuum

MVCC makes concurrent reads and writes safe by never overwriting a row in place: an UPDATE writes a brand-new tuple and marks the old one dead (xmax set) rather than mutating it, and a DELETE just marks a tuple dead without removing it. Nobody reclaims that space automatically as part of the write — that's VACUUM's entire job, and if it falls behind, the table just keeps growing.

Dead tuples and why they pile up

UPDATE accounts SET balance = balance - 50 WHERE id = 1;

That single statement doesn't touch the old row at all — it inserts a new tuple with the new balance and sets xmax on the old tuple to the current transaction ID. The old tuple stays on its page, fully intact, invisible to any transaction that starts after this one commits, but still physically present and still costing space and I/O until something removes it.

What VACUUM actually does

VACUUM accounts;
VACUUM ANALYZE accounts;   -- also refreshes planner statistics
VACUUM VERBOSE accounts;   -- prints what it found and reclaimed

Plain VACUUM scans the table, identifies tuples that are dead to every current and future transaction (no open snapshot can still see them), and marks that space reusable by recording it in the table's free space map (from TOAST, FSM & Visibility Map). Crucially, it does not shrink the file on disk — freed space is only made available for future inserts and updates to reuse. A table that had a huge DELETE and then a plain VACUUM will occupy exactly the same number of bytes on disk as before, just with more of those bytes marked free internally.

VACUUM runs concurrently with normal reads and writes — it only needs a lightweight lock, never blocking queries against the table.

VACUUM FULL: the exclusive-lock alternative

VACUUM FULL accounts;

VACUUM FULL actually shrinks the file: it rewrites the entire table into a new file containing only live tuples, then swaps it in and drops the old one — the same technique as CLUSTER. That's the only way to hand disk space back to the operating system after a huge delete. The cost is an ACCESS EXCLUSIVE lock for the whole operation, blocking every read and write against the table until it finishes.

Autovacuum

In practice you rarely run VACUUM by hand — the autovacuum daemon does it automatically, per table, based on how many rows have changed since the last run:

SHOW autovacuum;                          -- on by default
SHOW autovacuum_vacuum_scale_factor;      -- default 0.2 (20% of table)
SHOW autovacuum_vacuum_threshold;         -- default 50 rows

-- trigger point, roughly:
-- dead tuples > autovacuum_vacuum_threshold
--                + autovacuum_vacuum_scale_factor * total rows

For a 10-million-row table, the default scale factor means autovacuum waits until roughly 2 million rows are dead before it bothers — fine for a slowly-changing table, potentially a real problem for a small, extremely hot one.

Transaction ID wraparound and VACUUM FREEZE

Postgres transaction IDs (xid) are a 32-bit counter, and MVCC visibility depends on comparing xids to decide what's older or newer. A 32-bit counter wraps around eventually, and if an old tuple's xmin were left as a raw comparable number forever, wraparound would make old committed rows suddenly look like they came from the future — catastrophic silent data-visibility corruption.

VACUUM prevents this by freezing old tuples: once a tuple is old enough that every current and future transaction is guaranteed to see it as committed, its xmin is replaced with a special frozen marker that's always considered "in the past," permanently, regardless of counter wraparound.

VACUUM FREEZE accounts;    -- freeze eligible tuples now, not on autovacuum's schedule

SELECT datname, age(datfrozenxid) FROM pg_database;  -- how close to wraparound

Next: even with vacuum keeping the live table lean, you still need a plan for disasters vacuum can't help with, in Backup & Restore.