Part 151: PostgreSQL Multi-Version Concurrency Control (MVCC) Internals

How PostgreSQL achieves lock-free concurrent reads without blocking writes.

1. What is MVCC?

In traditional locking databases, a write transaction blocks read transactions, and reads block writes. PostgreSQL solves this with Multi-Version Concurrency Control (MVCC): - Readers never block writers. - Writers never block readers. - Every row (tuple) has internal system columns tracking which transaction created it and which transaction deleted or superseded it.

2. The Secret Tuple Headers: xmin and xmax

Every row in PostgreSQL contains hidden system columns:

-- Inspecting internal MVCC tuple headers
SELECT ctid, xmin, xmax, id, name, balance 
FROM accounts;

3. How Updates Work Under the Hood

PostgreSQL never overwrites a row in place: 1. When you run UPDATE accounts SET balance = 500 WHERE id = 1; 2. The old tuple's xmax is stamped with current transaction ID (marking it dead for future transactions). 3. A brand new tuple is inserted with current transaction ID as its xmin, and its xmax is set to 0. 4. The old tuple's ctid points forward to the new tuple's physical disk address!

Key Takeaways

• Updates and deletes leave "dead tuples" behind on disk pages.
• Cleaning up these dead tuples is the responsibility of the VACUUM process.

In Part 152, we explore PostgreSQL VACUUM, Autovacuum Tuning & TXID Wraparound Protection!