Under the Hood: Multi-Version Concurrency Control (MVCC)
In older database engines, readers blocked writers and writers blocked readers using shared and exclusive page locks. Multi-Version Concurrency Control (MVCC) eliminates read-write lock contention by ensuring that readers never block writers, and writers never block readers. (Writers updating the same row still block each other.)
PostgreSQL MVCC Tuple Header Anatomy
How Mutations Work in MVCC
1. Updating a Row (No In-Place Overwrites in PostgreSQL)
This append-in-heap design is PostgreSQL's. InnoDB and Oracle instead update rows in place and keep old versions in undo logs (see the table below).
When you execute UPDATE users SET email = '[email protected]' WHERE id = 1;, PostgreSQL does not overwrite the existing bytes on disk:
- It records the current transaction ID into the old tuple's
xmaxfield, marking it dead to future transactions. - It writes an entirely new tuple onto a disk page with
xmin = current_tx_idandxmax = 0. - The old tuple remains on disk so active concurrent read transactions can continue reading the old version consistent with their snapshot.
2. The Vacuum Requirement (Dead Tuple Bloat)
Because updates and deletes create dead tuples on disk, disk space bloats over time. The VACUUM daemon scans table pages, checks if any active transaction can still observe old versions, and marks the space of dead tuples as reusable within each page. Plain VACUUM returns space to the operating system only by truncating empty pages at the end of the table; shrinking a bloated table needs VACUUM FULL or pg_repack.
Worked example: two snapshots, one row
Row users id=1 is inserted by transaction 100, so it has xmin=100 and xmax=0.
| Step | Action | Heap tuples for id=1 |
|---|---|---|
| 1 | T200 begins (Repeatable Read) and takes snapshot: sees commits up to 199 | v1 (xmin 100, xmax 0) |
| 2 | T201 runs UPDATE users SET email = 'b' WHERE id = 1 | v1 (xmin 100, xmax 201), v2 (xmin 201, xmax 0) |
| 3 | T201 commits | unchanged; commit status recorded in the commit log (pg_xact) |
| 4 | T200 reads id=1 | v1 is visible (201 is not in its snapshot) and v2 is invisible, so it sees the old email |
| 5 | New T202 reads id=1 | v1 is dead (xmax 201 committed) and v2 is visible, so it sees 'b' |
| 6 | T200 ends, then VACUUM runs | v1 is no longer visible to anyone and its space is reclaimed |
Where the old versions live
| Engine | Old versions stored in | Consequence |
|---|---|---|
| PostgreSQL | The table heap itself (new tuple per update) | Table and index bloat; needs VACUUM; HOT updates avoid index writes when no indexed column changes |
| MySQL InnoDB | Undo log (row updated in place, previous image in undo) | Readers rebuild old versions by walking undo; a long transaction makes the history list grow |
| Oracle | Undo tablespace | "Snapshot too old" error if undo is overwritten before a long query finishes |
| CockroachDB | Multiple timestamped keys in the LSM | Garbage collected after gc.ttlseconds (default 4 hours in current versions) |
Numbers that matter
- An UPDATE-heavy table doing 1,000 updates/s produces 86M dead tuples per day. Default autovacuum triggers at 20% dead rows, which is too lazy for large hot tables. Set per-table scale factors near 0.01-0.05.
- XID wraparound: about 2 billion transactions of headroom. At 20k transactions/s that is roughly 28 hours if freezing stalls completely, so monitor
age(datfrozenxid).
Failure modes
- Idle in transaction: set
idle_in_transaction_session_timeout. - Replica feedback:
hot_standby_feedbackon replicas also holds back vacuum on the primary. - Write-write conflicts: at Repeatable Read, updating a row changed after your snapshot raises "could not serialize access due to concurrent update", so retry.