MVCC — Must Understand

Key features

• Each SQL statement sees a snapshot of the database as it existed at some point, regardless of what has changed since
• Locks acquired for reading do not conflict with locks acquired for writing — reading never blocks writing, and writing never blocks reading
• This works because a writer creates a new row version rather than overwriting the old one in place — a concurrent reader can keep using the old version undisturbed
• A transaction snapshot determines which row versions are visible to that transaction for its whole duration (Repeatable Read/Serializable) or per-statement (Read Committed)
• PostgreSQL achieves even its strictest isolation level (Serializable) through MVCC-based mechanisms (Serializable Snapshot Isolation) rather than falling back to heavy locking

Command

-- MVCC in one sentence, demonstrated:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- snapshot taken here
-- (a concurrent transaction commits an UPDATE to this row)
SELECT balance FROM accounts WHERE id = 1;  -- still sees the OLD version -- no lock, no block
COMMIT;

Key features

• UPDATE creates a new row version (new xmin = the updating transaction) and sets xmax on the old version to the same transaction id
• DELETE sets xmax on the existing row version — it does not erase the row's bytes immediately
• A row version whose xmax is set and committed is only invisible to snapshots taken after that commit; older snapshots can still legitimately see it
• Both the old and new versions occupy real disk space until VACUUM reclaims the old, no-longer-visible-to-anyone version
• This is why an UPDATE — even one that changes nothing about a row's size — can still require rewriting the entire row and can increase a table's total size

Command

-- xmin/xmax are hidden system columns, visible if selected explicitly
SELECT xmin, xmax, * FROM accounts WHERE id = 1;

UPDATE accounts SET balance = 900 WHERE id = 1;
-- old row version: xmax is now set to this transaction's id
-- new row version: xmin is set to this transaction's id, xmax unset

Key features

• A snapshot records, in effect, which transaction ids were already committed at the moment it was taken
• A row version is visible to a snapshot if its xmin was committed by then, and its xmax (if set) was not
• Read Committed takes a new snapshot for every individual statement
• Repeatable Read and Serializable take one snapshot at the start of the transaction and reuse it for every statement inside it
• Because visibility is computed by comparing static xmin/xmax values against a snapshot, no lock is needed just to determine whether a row is visible

Command

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts;  -- snapshot taken HERE, reused for the rest of this transaction
-- every later statement in this transaction uses the SAME snapshot
COMMIT;

Key features

• A SELECT reads whichever row version is visible to its own snapshot — a concurrent UPDATE creating a new version does not touch that version at all
• This is why a long-running report query does not block ordinary application writes, and vice versa, under normal operation
• The "generally" qualifier matters: DDL statements (ALTER TABLE, DROP TABLE, etc.) do take locks that can block readers — MVCC's guarantee applies to ordinary DML, not schema changes
• Two genuine writers to the SAME row still conflict and one must wait — MVCC solves the reader-vs-writer case, not the writer-vs-writer case
• This property is why PostgreSQL does not need a "read lock" concept the way some other systems do for basic SELECT statements

Command

-- Session A: a long-running report query
SELECT sum(balance) FROM accounts;  -- takes 30 seconds on a huge table

-- Session B, meanwhile, is completely unblocked:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- succeeds immediately

Key features

• A dead tuple is a row version with xmax set, where the superseding transaction has committed AND no open snapshot could still need the old version
• UPDATE and DELETE both produce dead tuples — UPDATE indirectly (the old version becomes dead once superseded and no longer needed), DELETE directly
• A dead tuple still occupies real disk space — INSERT-heavy tables grow from new rows, but UPDATE/DELETE-heavy tables can grow just as much from accumulating dead tuples
• Dead tuples are exactly what VACUUM exists to find and reclaim, making their space available for reuse by future INSERTs/UPDATEs
• A long-running or idle-in-transaction session holds back the point at which a superseded row version can be considered dead, letting dead tuples pile up even on unrelated tables

Command

-- see the dead tuple count for a table
SELECT relname, n_dead_tup, n_live_tup
  FROM pg_stat_user_tables
 WHERE relname = 'accounts';

VACUUM accounts;  -- reclaims space from dead tuples

Key features

• Standard VACUUM removes dead tuples and marks their space reusable within the table file — it does not shrink the file
• The one exception: if entirely-empty pages happen to sit at the very end of the file and an exclusive lock is easy to get, VACUUM can truncate and return that space to the OS
• VACUUM FULL rewrites the entire table into a new, compact file with no dead space, then swaps it in — this DOES return space to the OS, but requires an ACCESS EXCLUSIVE lock (blocking all other access) and extra disk space during the operation
• The routine goal is running standard VACUUM often enough that VACUUM FULL is rarely, if ever, needed
• VACUUM also updates the visibility map and free space map, both of which speed up future scans and future writes respectively

Command

VACUUM accounts;        -- routine: reclaims space for reuse, no exclusive lock
VACUUM FULL accounts;    -- heavy: rewrites the table, returns space to the OS, exclusive lock