MVCC: tuple versions, snapshots and HOT

conceptThe concept

Every tuple carries xmin (the transaction that created it) and xmax (the transaction that deleted or replaced it, or 0). An UPDATE sets xmax on the old version and inserts a new version with a new ctid (page, slot) location. A snapshot records which transactions were committed at a point in time; a tuple is visible if its xmin committed before the snapshot and its xmax did not.

Old versions remain until no snapshot can need them, then they are dead, and VACUUM reclaims the space. This is the price of MVCC: updates create garbage, and a long-running transaction keeps garbage alive for the whole database.

A HOT (heap-only tuple) update happens when no indexed column changes and the new version fits on the same page. The indexes are not touched; the old version points to the new one. HOT is much cheaper, and a lower fillfactor (for example 80) leaves room on each page to make it likely.

Open the interactive lesson to use this diagram.

whyWhy it matters

MVCC explains table bloat, why VACUUM exists, why a forgotten "idle in transaction" session can degrade a whole server, and why updating an indexed column costs more than updating a non-indexed one.

Use caseReal-world scenario

A session counter table updated 1,000 times per second grows without bound. Removing an index on the updated column and setting fillfactor to 70 turns every update into a HOT update and keeps the table small.

Try itExamples

Run and edit these in the interactive lesson.

SELECT xmin, xmax, ctid, id, name FROM employees ORDER BY id LIMIT 4;
SELECT pg_current_xact_id() AS my_xid, pg_current_snapshot() AS snapshot;

DROP TABLE IF EXISTS mvcc_lab;
CREATE TABLE mvcc_lab (id int PRIMARY KEY, val int) WITH (autovacuum_enabled = off);
INSERT INTO mvcc_lab VALUES (1, 10), (2, 20);
UPDATE mvcc_lab SET val = val + 1 WHERE id = 1;
UPDATE mvcc_lab SET val = val + 1 WHERE id = 1;
DELETE FROM mvcc_lab WHERE id = 2;
SELECT lp, t_xmin, t_xmax, t_ctid,
       (t_infomask2 & 16384) > 0 AS hot_updated,
       (t_infomask2 & 32768) > 0 AS heap_only
FROM heap_page_items(get_raw_page('mvcc_lab', 0));
SELECT ctid, * FROM mvcc_lab;
DROP TABLE mvcc_lab;

DROP TABLE IF EXISTS hot_lab;
CREATE TABLE hot_lab (id int PRIMARY KEY, indexed_col int, plain_col int) WITH (fillfactor = 50, autovacuum_enabled = off);
CREATE INDEX ON hot_lab (indexed_col);
INSERT INTO hot_lab SELECT g, g, g FROM generate_series(1, 1000) g;
UPDATE hot_lab SET plain_col = plain_col + 1;
SELECT pg_stat_force_next_flush();
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'hot_lab';
UPDATE hot_lab SET indexed_col = indexed_col + 1;
SELECT pg_stat_force_next_flush();
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'hot_lab';
DROP TABLE hot_lab;

BEGIN;
UPDATE products SET stock = 0 WHERE id = 1;
SELECT xmin, xmax, stock, pg_current_xact_id() FROM products WHERE id = 1;
ROLLBACK;
SELECT xmin, stock FROM products WHERE id = 1;

internalsUnder the hood

Transaction IDs are 32-bit counters. Commit status lives in pg_xact (the CLOG), with hint bits cached on tuples after the first check. pg_current_snapshot() shows xmin, xmax and the in-progress list. n_tup_hot_upd in pg_stat_user_tables counts HOT updates.

Avoid / preferDo this, not that

UPDATE products SET active = true;  -- most rows already active

UPDATE products SET active = true WHERE active IS DISTINCT FROM true;

-- application opens a transaction, then waits for user input

SET idle_in_transaction_session_timeout = '5min';
-- and keep transactions short

PitfallsCommon mistakes

• Leaving sessions "idle in transaction". Their snapshot pins every dead tuple created since, across all tables, so VACUUM cannot clean anything.
• Indexing frequently updated columns that are rarely queried. It disables HOT for those updates and multiplies write I/O.
• Expecting DELETE to free disk space. It creates dead tuples; VACUUM makes the space reusable, and only VACUUM FULL or pg_repack returns it to the OS.

LimitsWhen not to use it

MVCC is always on; the choice is your update pattern. Very frequent updates of wide rows (counters, last_seen timestamps) are better in a narrow separate table or an append-only design.

SecuritySecurity notes

Deleted and updated rows stay in data files until VACUUM reclaims and later writes overwrite them, and they remain in WAL archives and backups. Erasure requirements must account for backups and retention, not just DELETE.

ChallengeTry it yourself

BEGIN;
SELECT ctid FROM products WHERE id = 1;
UPDATE products SET stock = stock + 1 WHERE id = 1;
SELECT ctid, xmin::text::bigint = txid_current() AS written_by_me FROM products WHERE id = 1;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Concurrency control
• pageinspect