MVCC: Snapshots, Row Versions and Visibility

In one line

Multi-version concurrency control keeps several versions of each row so readers see a consistent snapshot without blocking writers. In PostgreSQL every tuple has xmin (creating transaction) and xmax (deleting or updating transaction); a snapshot decides which versions are visible. Old versions become dead tuples that VACUUM must reclaim.

0/5 · 0%

Think of it like this

A museum that never repaints over a painting. Each restoration hangs a new version and marks the old one "superseded on date X". Visitors who entered earlier keep seeing the version that was current when they arrived.

Key ideas

01
An UPDATE in PostgreSQL is a delete plus an insert: the old tuple gets xmax = current xid, and a new tuple with xmin = current xid is written (on the same page if space allows, as a HOT update, Phase 6.1).
02
A snapshot records xmin (oldest still-running xid), xmax (next xid to be assigned) and the list of in-progress xids. A tuple is visible if its creator committed before the snapshot and its deleter didn't.
03
Read Committed takes a new snapshot per statement; Repeatable Read and Serializable take one per transaction.
04
Costs: dead tuples accumulate (bloat) until VACUUM; a long-running transaction or an old replication slot holds back the xmin horizon so nothing newer can be vacuumed. Transaction IDs are 32-bit, which requires freezing (Phase 6.4).
05
Other engines differ: InnoDB and Oracle update in place and keep old versions in undo logs; readers reconstruct past versions from undo, and long transactions grow the undo instead of the table.

Code & diagrams

mvcc-lab.sqlsql

CREATE TABLE t (id int PRIMARY KEY, v text);
INSERT INTO t VALUES (1, 'a');
SELECT xmin, xmax, ctid, * FROM t;
--  xmin | xmax | ctid  | id | v
--   901 |    0 | (0,1) |  1 | a

UPDATE t SET v = 'b' WHERE id = 1;
SELECT xmin, xmax, ctid, * FROM t;
--  xmin | xmax | ctid  | id | v
--   902 |    0 | (0,2) |  1 | b      <- new version at a new position

-- the old version (0,1) is now dead; see it with pageinspect
CREATE EXTENSION pageinspect;
SELECT lp, t_xmin, t_xmax FROM heap_page_items(get_raw_page('t', 0));
--  lp | t_xmin | t_xmax
--   1 |    901 |    902
--   2 |    902 |      0

SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 't';

mvcc.mermaiddiagram

Rendering diagram…

Interview problem

The problem

A table that keeps growing although row count is stable

A session table has ~1M rows, updated constantly, but it has grown from 200 MB to 40 GB and queries slow down every week. Explain using MVCC and propose fixes.

When it breaks

An idle-in-transaction connection left open for days

What you see

VACUUM can't remove any dead tuple newer than its snapshot; every hot table bloats, and index scans and caches degrade cluster-wide.

Fix & prevent

Set idle_in_transaction_session_timeout (e.g. 60 s), monitor the oldest xact_start, and fix the code path that forgets to commit.

Explain it without notes

Why don't readers block writers under MVCC?

Practice

In two sessions, show that a Repeatable Read transaction keeps seeing old data after another session commits an update.

Trade-offs

↔
MVCC gives non-blocking reads and consistent snapshots in exchange for storage bloat and the need for VACUUM.

Done when you can

I can explain xmin/xmax, snapshots and visibility, and diagnose MVCC bloat.

Command Palette