MVCC and the Multiversion House of Cards

Multi-Version Concurrency Control (MVCC) is the mechanism that allows readers and writers to coexist without blocking each other. Instead of locking rows during reads, MVCC maintains multiple versions of each row and lets each transaction see a consistent snapshot of the database. But managing these versions is a delicate balancing act — and the vacuum process is what keeps the system from collapsing under its own weight.

How MVCC Works

Each row in PostgreSQL has hidden system columns: xmin (the transaction that created the row) and xmax (the transaction that deleted or updated it). When you read a row, PostgreSQL checks whether the row version is visible to your transaction based on these values.

sql

-- See the hidden MVCC columns
SELECT xmin, xmax, ctid, *
FROM accounts
WHERE id = 1;
 
-- xmin: Transaction ID that inserted this row
-- xmax: Transaction ID that deleted/updated this row (0 if not deleted)
-- ctid: Physical location of the row version on disk

The visibility rules:

Row is visible if xmin is committed AND xmax is not committed
Row is visible if xmin is committed AND xmax is your own transaction
Row is invisible if xmin is not committed (unless it's your own)
Row is invisible if xmin is aborted

sql

-- Transaction A (xmin = 100)
BEGIN;
UPDATE accounts SET balance = 1500 WHERE id = 1;
-- Old row: xmin=95, xmax=100
-- New row: xmin=100, xmax=0
-- Your transaction sees both versions depending on visibility rules
 
-- Transaction B (xmin = 101, started before A committed)
BEGIN;
SELECT * FROM accounts WHERE id = 1;
-- Sees old version (xmin=95) because version 100 isn't committed yet
COMMIT;  -- After A commits, B still sees old version (snapshot at start)
 
-- Transaction C (xmin = 102, started after A committed)
BEGIN;
SELECT * FROM accounts WHERE id = 1;
-- Sees new version (xmin=100) because it's committed and C's snapshot is after
COMMIT;

Snapshot Isolation

Each transaction in PostgreSQL gets a snapshot at its first query. This snapshot determines which row versions are visible. The snapshot captures:

All committed transactions at that point
No uncommitted transactions (except the current one)

sql

-- How PostgreSQL creates a snapshot internally
typedef struct SnapshotData {
    TransactionId xmin;  // All txns with ID >= xmin are invisible
    TransactionId xmax;  // All txns with ID < xmax are visible
    TransactionId *xip;  // Array of in-progress transaction IDs
    uint32 xcnt;         // Count of in-progress txns
} SnapshotData;
 
// Visibility check for a row version with xmin=X, xmax=Y
bool is_visible(SnapshotData *snap, TransactionId xmin, TransactionId xmax){
    // If xmin is committed and < snap->xmin, row was created before snapshot
    if (TransactionIdDidCommit(xmin) && xmin < snap->xmin){
        // Row was created before our snapshot
        if (xmax == 0 || xmax >= snap->xmax){
            return true;  // Not deleted yet (from our perspective)
        }
        if (xmax < snap->xmin && TransactionIdDidCommit(xmax)){
            return false;  // Deleted before our snapshot
        }
        // Check if xmax is in our in-progress list
        for (int i = 0; i < snap->xcnt; i++){
            if (snap->xip[i] == xmax) return false;  // Deletion is concurrent
        }
    }
    return false;
}

Update Chains: Row Versioning

When you update a row, PostgreSQL doesn't modify the existing row. Instead, it:

Marks the old row's xmax with the current transaction ID
Inserts a new row version with the current transaction ID as xmin
Chains the versions via the ctid field

sql

-- Update creates a chain
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Old row: (0, 1) → xmin=95, xmax=100, ctid=(0,2)
-- New row: (0, 2) → xmin=100, xmax=0, ctid=(0,3)
-- Next update creates another link in the chain
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- Previous new row: (0, 2) → xmin=100, xmax=100, ctid=(0,3)
-- Newest row: (0, 3) → xmin=100, xmax=0
COMMIT;

Long update chains are problematic:

Sequential scans must follow the chain
Index scans may fetch the wrong version and need to follow the chain
Vacuum must process the chain to remove dead versions

sql

-- Monitor long chains
SELECT
    n_live_tup,
    n_dead_tup,
    CASE WHEN n_live_tup > 0
         THEN round(n_dead_tup::numeric / n_live_tup, 2)
         ELSE 0
    END AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

VACUUM: The Janitor

MVCC creates dead tuples (old row versions no longer visible to any transaction). VACUUM removes these dead tuples and recovers space. Without it, tables grow indefinitely and performance degrades.

sql

-- Manual vacuum
VACUUM accounts;
 
-- Vacuum with ANALYZE (updates statistics)
VACUUM ANALYZE accounts;
 
-- Full vacuum (rewrites the table, acquires exclusive lock)
VACUUM FULL accounts;  -- Dangerous in production!
 
-- Autovacuum settings
SHOW autovacuum;                          -- on/off
SHOW autovacuum_vacuum_threshold;         -- Min dead tuples to vacuum (default 50)
SHOW autovacuum_vacuum_scale_factor;      -- Fraction of table (default 0.2)
SHOW autovacuum_analyze_threshold;        -- Min changes to analyze (default 50)
SHOW autovacuum_analyze_scale_factor;     -- Fraction of table (default 0.1)

Transaction ID Wraparound

PostgreSQL's transaction IDs are 32-bit unsigned integers. After ~4 billion transactions, the counter wraps around to zero. If old transaction IDs haven't been frozen, PostgreSQL can't determine visibility and must shut down to prevent corruption.

plaintext

Transaction ID space:
[0] [1] [2] ... [2^32-2] [2^32-1]
                              ↓
                            Wraps to [0]
 
If frozen_age < current_xid - oldest_xid, we're safe
If not, emergency vacuum to freeze old XIDs

sql

-- Monitor wraparound risk
SELECT
    datname,
    age(datfrozenxid) AS xid_age,
    2^31 - age(datfrozenxid) AS remaining
FROM pg_database
ORDER BY xid_age DESC;
 
-- Emergency measure: force wraparound
VACUUM FREEZE accounts;  -- Freezes all old transaction IDs

tradeoff / Vacuum Aggressiveness vs Performance

For write-heavy tables, increase autovacuum_vacuum_scale_factor and reduce autovacuum_vacuum_delay. For read-heavy tables, the defaults are usually fine.

Conservative vacuuming uses more disk space and may cause wraparound issues. Aggressive vacuuming uses more CPU and I/O. Manual tuning requires understanding your workload's update patterns.

Visibility Map and All-Frozen

PostgreSQL maintains a visibility map that tracks which pages have all-visible rows (no dead tuples) and which are all-frozen (no transaction IDs need freezing). This accelerates VACUUM and enables index-only scans.

sql

-- Check visibility map
SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS size,
    pg_size_pretty(pg_relation_size(oid)) AS table_size,
    pg_size_pretty(pg_indexes_size(oid)) AS index_size
FROM pg_class
WHERE relkind = 'r' AND relname = 'accounts';
 
-- Visibility map is updated by VACUUM
-- Pages marked all-visible: Index-only scans can skip heap
-- Pages marked all-frozen: VACUUM can skip these pages

Synthesis

MVCC is a powerful concurrency control mechanism that trades storage space for lock-free reads. The system maintains multiple row versions, uses transaction IDs and snapshots for visibility, and relies on VACUUM to reclaim space. Understanding MVCC internals — update chains, visibility rules, and vacuum behavior — is essential for diagnosing performance issues and preventing catastrophic failures like transaction ID wraparound.

MVCC and the Multiversion House of Cards

How MVCC Works

Snapshot Isolation

Update Chains: Row Versioning

VACUUM: The Janitor

Transaction ID Wraparound

Visibility Map and All-Frozen

Synthesis

Related reading

ACID Isolation Levels Explained

Connection Pooling at the Database Level

Materialized Views and Incremental Refresh

One dispatch a week