PostgreSQL MVCC & VACUUM: Taming Table Bloat and Transaction Wraparound
Analyzing heap tuple headers, xmin/xmax visibility rules, HOT updates, and autovacuum tuning.
Table of Contents (30 Concepts)
1. Multi-Version Concurrency Control (MVCC) Heap Tuples
When you UPDATE a row in PostgreSQL, it does not overwrite the data in place. Instead, it inserts a brand new tuple with a new transaction ID (xmin) and marks the old tuple with xmax, leaving dead tuples in the table page.
2. Transaction ID (xmin/xmax) Visibility Rules
PostgreSQL determines which version of a tuple a query can read by comparing the tuple’s xmin/xmax transaction IDs against the transaction snapshot active at the query start.
3. Table Bloat: Why Dead Tuples Accumulate on Disk
If dead tuples are not cleaned up in time, table files continuously expand on disk, inflating memory cache consumption and index scan times.
4. Heap-Only Tuples (HOT) Optimization Mechanics
HOT optimization prevents index updates when modifying columns that are not part of any index, drastically reducing page write amplification.
5. Autovacuum Workers & Cost-Based Throttling
Background autovacuum worker processes scan tables and reclaim dead space, throttled by autovacuum_vacuum_cost_limit.
6. Free Space Map (FSM) & Visibility Map (VM)
FSM tracks available space in existing 8KB pages so new inserts can reuse reclaimed dead tuple space without expanding files.
7. Transaction ID Wraparound (2 Billion Tx Ceiling)
Transaction IDs are 32-bit integers. Without periodic freezing, reaching 2^31 transactions causes catastrophic database shutdown to protect data integrity.
Subscribe to continue reading the full masterclass
You’ve finished the first 7 concepts. Join 120,000+ senior engineers to unlock the remaining 23 concepts, deep-dive trade-off diagrams, and our 120+ edition archive.