Reference · Cheat sheet
Lesson 0004 distilled — the 32-bit clock, why old rows must be frozen, and the outage that follows neglect. Built to print.
Each heap tuple header carries xmin (inserting XID) and xmax (expiring XID). Visibility = compare those against your snapshot: an insert XID "in the future" → not visible.
XIDs are 32-bit (~4 billion) and compared modulo-2³² — a circle. From any point: 2 billion "past", 2 billion "future".
An unmaintained old row drifts past the 2-billion horizon into the "future" half → becomes invisible. Silent data loss.
VACUUM marks old all-visible rows frozen → treated as FrozenTransactionId, always older than every XID, off the circular clock forever.
Rule: every table must be vacuumed at least once per 2 billion transactions.
1 · reclaim dead tuples (0002)
2 · maintain the visibility map (0003)
3 · freeze old rows (0004)
→ Job 3 is why even an append-only, zero-dead-tuple table still must be vacuumed.
At autovacuum_freeze_max_age (default 200M), an anti-wraparound autovacuum fires — even if autovacuum is disabled.
Warnings at 40M from the edge; under 3M it refuses writes (read-only) to prevent data loss. Recovery = database-wide VACUUM.
Watch age(relfrozenxid) vs 200M.
| Aspect | InnoDB | Postgres |
|---|---|---|
| Version identity | undo + wide IDs | 32-bit XID in tuple |
| ID space concern? | no | wraps at ~4B |
| Prevents disaster | purge | freezing |
| Neglect → | undo bloat | writes refused |