How PostgreSQL MVCC and VACUUM actually work

PostgreSQL lets readers and writers work at the same time without blocking each other. The mechanism behind that is Multi-Version Concurrency Control (MVCC) — and the price you pay for it is VACUUM.

Every row has a birth and death certificate

Each row version (a tuple) in a PostgreSQL table carries hidden system columns:

• xmin — the ID of the transaction that created this version
• xmax — the ID of the transaction that deleted or replaced it (0 if still live)

You can see them yourself:

CREATE TABLE accounts (id int PRIMARY KEY, balance numeric);
INSERT INTO accounts VALUES (1, 100);

SELECT xmin, xmax, * FROM accounts;

When a transaction reads a table, it uses its snapshot to decide which tuples are visible: a tuple is visible if its xmin committed before the snapshot and its xmax did not.

UPDATE is really INSERT + DELETE

PostgreSQL never overwrites a row in place. An UPDATE:

1. writes a new tuple with the new values (new xmin), and
2. sets xmax on the old tuple.

UPDATE accounts SET balance = 150 WHERE id = 1;

The old version stays on disk so that transactions with older snapshots can still see it. Once no running transaction can possibly need it, it becomes a dead tuple — wasted space.

What VACUUM does

VACUUM scans tables and:

• marks space held by dead tuples as reusable for future inserts and updates,
• updates the visibility map, which enables index-only scans,
• freezes old tuples to protect against transaction ID wraparound.

Plain VACUUM does not usually shrink the file on disk — it makes space reusable inside the table. VACUUM FULL rewrites the whole table compactly but takes an ACCESS EXCLUSIVE lock, blocking all reads and writes. Treat it as a last resort.

Autovacuum and its thresholds

The autovacuum launcher vacuums a table when its dead tuples exceed:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

With the defaults (50 and 0.2), a 100-million-row table accumulates about 20 million dead tuples before autovacuum starts. For large, busy tables, lower the scale factor per table:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.02
);

Watching for bloat

pg_stat_user_tables tells you whether autovacuum is keeping up:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

The things that block VACUUM

VACUUM can only remove tuples that are dead to every transaction. A few things hold back that horizon:

• Long-running transactions — including sessions left idle in transaction. Set idle_in_transaction_session_timeout.
• Abandoned replication slots — check pg_replication_slots.
• Old prepared transactions — check pg_prepared_xacts.

SELECT pid, state, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

Transaction ID wraparound

Transaction IDs are 32-bit and compared using modular arithmetic, so PostgreSQL must freeze old tuples before their IDs get too old. If freezing falls too far behind, PostgreSQL will eventually refuse new write transactions to protect your data. Monitor age(datfrozenxid):

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

HOT updates: the cheap kind

If an update doesn't modify any indexed column and there's free space on the same page, PostgreSQL performs a Heap-Only Tuple (HOT) update: no new index entries are needed. Leaving free space with a lower fillfactor on update-heavy tables increases the HOT ratio:

ALTER TABLE sessions SET (fillfactor = 80);

Key takeaways

1. Updates and deletes create dead tuples; VACUUM makes their space reusable.
2. Tune autovacuum per table for large, busy tables.
3. Kill long transactions and stale replication slots — they stall cleanup.
4. Monitor dead tuples and datfrozenxid age before they become an incident.