transactions • Expert

Multi-Version Concurrency Control (MVCC) & Vacuuming

MVCC allows readers not to block writers and writers not to block readers by storing multiple immutable versions of row tuples with creation (xmin) and deletion (xmax) transaction IDs.

Intuitive Mental Model

The Document Version History: Instead of erasing text on paper with an eraser while someone is reading it, you print a new edition (version 2) with a timestamp stamp, leaving version 1 untouched for current readers.

SQL DDL / DML QueryPostgreSQL / MySQL

-- PostgreSQL Row Header (Tuple Header):
-- t_xmin: Transaction ID that inserted this row version
-- t_xmax: Transaction ID that deleted/updated this row version

-- UPDATE user SET name = 'Bob' WHERE id = 1:
-- 1. Sets xmax = current_tx_id on old tuple (marking dead)
-- 2. Inserts new tuple with xmin = current_tx_id, xmax = 0

-- Table Bloat cleanup:
VACUUM (VERBOSE, ANALYZE) users;

Key Architectural Takeaways

•Readers never wait for writers, and writers never wait for readers.
•Dead Tuples: Updated or deleted rows remain on disk until cleaned up by the background AutoVacuum daemon.
•Transaction ID Wraparound: PostgreSQL must vacuum older tables to freeze old 32-bit transaction IDs before reaching 2 billion transactions.

Common Production Mistake

Holding open long-running idle transactions (e.g. idle in transaction for hours), preventing AutoVacuum from reclaiming dead tuples and causing massive table bloat.