Skip to content

Phase 03: The Engine: MVCC, Vacuum & Locks

Chapter 11: MVCC Internals (Multi-Version Concurrency Control)

11.1 The Anatomy of a Heap Tuple

In PostgreSQL, table rows are stored as tuples on 8KB disk pages. Every tuple contains a 23-byte header (HeapTupleHeaderData) preceding the actual user data.

+-------------------------------------------------------------------------------+
|                             HeapTupleHeaderData                               |
+-------------------+-------------------+-------------------+-------------------+
| t_xmin (4 bytes)  | t_xmax (4 bytes)  | t_cid (4 bytes)   | t_ctid (6 bytes)  |
| Insert Transaction| Delete Transaction| Command ID in TX  | Physical Row ID   |
+-------------------+-------------------+-------------------+-------------------+
| t_infomask (2 B)  | t_infomask2 (2 B) | t_hoff (1 byte)   | NULL Bitmap (var) |
| Tuple Status Flags| Num Attributes/HOT| Header Offset     | 1 bit per column  |
+-------------------+-------------------+-------------------+-------------------+
|                               USER DATA PAYLOAD                               |
+-------------------------------------------------------------------------------+

Key Header Fields Explained:

  • t_xmin: 32-bit transaction ID (XID) that inserted this tuple.
  • t_xmax: 32-bit transaction ID (XID) that deleted or updated this tuple. If 0, the tuple has not been deleted or updated.
  • t_cid: Command ID within the transaction (tracks ordering if multiple SQL commands run in one transaction block).
  • t_ctid: Points to the physical location of the tuple (page_number, item_offset). If the row was updated, t_ctid points to the newer version of the row!
  • t_infomask: Bit flags defining tuple status (e.g., HEAP_XMIN_COMMITTED, HEAP_XMIN_INVALID, HEAP_XMAX_COMMITTED). Once set, backends can check visibility without consulting the commit log (pg_xact).

11.2 The Visibility Algorithm

When transaction \(T\) executes SELECT, Postgres takes a Snapshot: Snapshot = (xmin, xmax, xip_list) * xmin: The earliest active transaction ID still running at snapshot creation. All transactions \(< xmin\) have already committed and are visible. * xmax: The first unassigned transaction ID. All transactions \(\ge xmax\) started after this snapshot and are invisible. * xip_list: Active transactions running between xmin and xmax. Invisible.

A tuple is visible to transaction \(T\) if and only if: 1. Tuple's xmin is committed AND visible in \(T\)'s snapshot. 2. Tuple's xmax is either: * 0 (never deleted), OR * Belongs to an aborted transaction, OR * Belongs to a transaction that committed after \(T\)'s snapshot was created.

Chapter 12: The Vacuum Process & Transaction ID Wraparound

12.1 Dead Tuples and Free Space Management

Because updates create new tuples and leave old ones behind, tables accumulate dead tuples (tuples where xmax is committed and older than any currently active snapshot).

  • Standard VACUUM:
  • Scans heap pages, identifies dead tuples.
  • Cleans index pointers referencing those dead tuples.
  • Marks dead space as free in the Free Space Map (FSM).
  • Critical Production Reality: Standard VACUUM never shrinks the OS file size on disk (except for rare truncation of empty pages at the very end of the file). It simply recycles internal space for future INSERTs.
  • VACUUM FULL:
  • Rewrites the entire table and its indexes into brand-new disk files.
  • Shrinks OS file size to minimum.
  • The Fatal Trap: Takes an AccessExclusiveLock on the table, blocking all reads and writes for minutes or hours. Never run VACUUM FULL in production.

12.2 Autovacuum Tuning Formula

Autovacuum launches a worker when dead tuples exceed a threshold: $\(\text{Threshold} = \text{autovacuum\_vacuum\_threshold} + (\text{autovacuum\_vacuum\_scale\_factor} \times \text{reltuples})\)$ * Defaults: threshold = 50, scale_factor = 0.2 (20%). * The High-Scale Choke Point: * On a 100-million row table, scale_factor = 0.2 means Autovacuum will not run until 20 million dead rows accumulate! * Table bloat explodes before vacuum even begins. * Production Tuning for Busy Tables: 

ALTER TABLE high_velocity_table SET (
    autovacuum_vacuum_scale_factor = 0.02, -- Run at 2% dead rows
    autovacuum_vacuum_cost_limit = 2000,    -- Give worker more I/O quota
    autovacuum_vacuum_cost_delay = 2        -- Reduce throttling delay
);

12.3 The Catastrophe: 32-bit Transaction ID (XID) Wraparound

PostgreSQL represents transaction IDs with a 32-bit unsigned integer (\(2^{32} \approx 4.29\) billion values). Postgres uses modular arithmetic: for any current transaction \(T\), the previous 2 billion IDs are in the "past", and the next 2 billion IDs are in the "future".

                          [Current TXID: 2.1 Billion]
                                       |
                   Past 2 Billion <----+----> Future 2 Billion

What Happens When Transactions Exceed 2.14 Billion?

If transaction IDs wrap around without intervention, past transactions suddenly appear to be in the future, rendering historic data completely invisible (catastrophic silent data loss).

The Solution: Tuple Freezing

To prevent wraparound, Autovacuum freezes old tuples: * It sets the HEAP_XMIN_FROZEN bit in t_infomask. * A frozen tuple is defined to be older than every possible transaction ID forever.

Emergency Failsafe Thresholds:

  1. autovacuum_freeze_max_age (Default: 200 million): Autovacuum forces an aggressive, un-throttled vacuum on the table to freeze tuples.
  2. At 2 billion transactions remaining age: Postgres emits warnings to logs.
  3. At 1 million transactions remaining: Postgres performs a safety shutdown, refusing to accept any new write transactions: PANIC: could not locate a valid checkpoint record HINT: database is shutting down to prevent transaction ID wraparound.

The Fix for Emergency Wraparound Outages:

Start Postgres in single-user mode and run an un-throttled freeze vacuum: 

postgres --single -D /path/to/data postgres
backend> VACUUM FREEZE;

Chapter 13: Locking Mechanisms & Queueing Cascades

13.1 Table-Level Lock Hierarchy (8 Levels)

PostgreSQL table locks form a matrix where higher lock levels conflict with lower ones:

  1. AccessShareLock: Acquired by SELECT. Conflicts only with level 8.
  2. RowShareLock: Acquired by SELECT ... FOR UPDATE / FOR SHARE.
  3. RowExclusiveLock: Acquired by INSERT, UPDATE, DELETE.
  4. ShareUpdateExclusiveLock: Acquired by VACUUM (non-full), ANALYZE, CREATE INDEX CONCURRENTLY.
  5. ShareLock: Acquired by CREATE INDEX (standard).
  6. ShareRowExclusiveLock: Acquired by CREATE TRIGGER.
  7. ExclusiveLock: Blocks concurrent writes and index creation.
  8. AccessExclusiveLock: Acquired by ALTER TABLE, DROP TABLE, TRUNCATE, VACUUM FULL. Conflicts with ALL lock levels (even a simple SELECT).

13.2 The Lock Queue Domino Effect (The 3 AM Production Outage)

This is one of the most common causes of total production outages:

Step 1: Long-running analytical query starts:
        SELECT * FROM orders WHERE ... (Holds AccessShareLock, takes 10 minutes)

Step 2: Migration script executes:
        ALTER TABLE orders ADD COLUMN referral_code TEXT; 
        (Needs AccessExclusiveLock. Blocked by Step 1 query!)
        Postgres puts ALTER TABLE in the lock waiting queue.

Step 3: Web application serves 1,000 normal fast reads/sec:
        SELECT * FROM orders WHERE id = 1; (Needs AccessShareLock)

Step 4: CATASTROPHE:
        Postgres lock manager prioritizes fairness to prevent lock starvation.
        All new SELECT queries queue BEHIND the ALTER TABLE!
        Within 10 seconds: Connection pool maxes out (500 connections waiting).
        The database is completely unresponsive to all application traffic.

The Bulletproof Migration Pattern:

Always set a tight statement lock timeout before DDL: 

BEGIN;
SET LOCAL lock_timeout = '2s'; -- Fail fast if we cannot acquire lock in 2s
ALTER TABLE orders ADD COLUMN referral_code TEXT;
COMMIT;

13.3 Row-Level Locks

  • FOR UPDATE: Locks rows for write. Blocks concurrent FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, and UPDATE/DELETE.
  • FOR NO KEY UPDATE: Locks rows for update, but allows concurrent FOR SHARE (read) if the update does not modify unique primary key columns.
  • FOR SHARE: Locks rows for reading, prevents concurrent deletes/updates.
  • FOR KEY SHARE: Allows concurrent updates as long as they don't touch the key.

13.4 Advisory Locks

PostgreSQL provides application-level distributed locks that do not tie to any database row or table: 

-- Acquire an application lock (e.g., cron job deduping):
SELECT pg_try_advisory_lock(12345); -- Returns true if acquired, false if busy

-- Release lock:
SELECT pg_advisory_unlock(12345);

Chapter 14: Write-Ahead Logging (WAL) Internals

14.1 The Write Pipeline (ACID Durability)

  1. Backend process modifies a row in shared_buffers (dirty page).
  2. Before the dirty page is ever written to disk, the change is recorded as a sequential record in wal_buffers.
  3. On COMMIT, WAL buffer is written to $PGDATA/pg_wal and flushed (fsync).
  4. Once WAL is persistent, the transaction reports success to the client. The actual data page in shared_buffers can be written to disk minutes later by the Checkpointer or Background Writer.

14.2 Log Sequence Numbers (LSN)

Every WAL record has a 64-bit byte offset known as an LSN (e.g., 0/16B3748). * Pages in memory and disk store the LSN of the last WAL record that modified them (pd_lsn in the page header). * Crash Recovery: On reboot, Postgres finds the last Checkpoint LSN, scans WAL forward, and reapplies any changes where record_lsn > page.pd_lsn (REDO phase).

14.3 Full Page Writes (full_page_writes = on)

  • Filesystem blocks are typically 4KB; Postgres pages are 8KB.
  • If the server crashes mid-write, a page on disk can be left half-written (torn page).
  • To prevent unrecoverable corruption, after every Checkpoint, Postgres writes the entire 8KB page image to WAL on its first modification.

Chapter 15: Bloat Management & Zero-Downtime Repack

15.1 Heap-Only Tuples (HOT) Optimization

To avoid updating B-Tree indexes when an update occurs: 1. The new tuple must fit inside the exact same 8KB data page as the old tuple. 2. None of the columns indexed by any index on the table can be modified. If both conditions are met: * The index continues pointing to the old tuple. * The old tuple sets the HEAP_HOT_UPDATED flag and points its t_ctid to the new tuple. * Index reads transparently follow the pointer. Index bloat = 0.

15.2 Measuring Bloat (pgstattuple)

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT table_len, tuple_percent, dead_tuple_percent, free_percent 
FROM pgstattuple('orders');

15.3 Zero-Downtime Defragmentation (pg_repack)

Because VACUUM FULL takes an exclusive lock, use pg_repack: 1. Creates a shadow table with the exact schema. 2. Creates an incremental log trigger on the original table. 3. Copies all live tuples from the original table to the shadow table. 4. Builds indexes concurrently on the shadow table. 5. Replays incremental changes from the log. 6. Swaps catalog tables in a brief millisecond lock window and drops the old bloated table.