Row versions: xmin, xmax, ctid
Chapters 2 and 3 kept saying "snapshot" and "row version" — this chapter opens the hood. The core trick of MVCC is that PostgreSQL never modifies a row in place: every UPDATE writes a complete new copy, every DELETE only stamps the old one. That's why reading never blocks writing and writing never blocks reading — readers and writers are literally looking at different physical tuples.
Three hidden system columns tell each version's story:
| column | meaning |
|---|---|
xmin | the transaction id (xid) that created this version |
xmax | the xid that deleted or replaced it — 0 while nobody has |
ctid | its physical address: (page, slot) within the table file |
Watching an UPDATE make a copy
Every row carries hidden system columns: xmin = the transaction that created this version, xmax = the one that deleted or replaced it (0 = nobody yet), ctid = its physical address (page, slot).
A> SELECT xmin, xmax, ctid, balance FROM accounts WHERE id = 1;
xmin | xmax | ctid | balance
------+------+-------+---------
1001 | 0 | (0,1) | 100
(1 row)
B> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
B> SELECT xmin, xmax, ctid, balance FROM accounts WHERE id = 1;
xmin | xmax | ctid | balance
------+------+-------+---------
1001 | 0 | (0,1) | 100
(1 row)A's UPDATE doesn't touch that version — it writes a brand-new one at a new ctid.
A> UPDATE accounts SET balance = 200 WHERE id = 1
RETURNING xmin, xmax, ctid, balance;
xmin | xmax | ctid | balance
------+------+-------+---------
1002 | 0 | (0,2) | 200
(1 row)B still reads the old version — but its xmax is no longer 0: A's xid is stamped on it.
B> SELECT xmin, xmax, ctid, balance FROM accounts WHERE id = 1;
xmin | xmax | ctid | balance
------+------+-------+---------
1001 | 1002 | (0,1) | 100
(1 row)
B> COMMIT;
COMMIT
B> SELECT xmin, xmax, ctid, balance FROM accounts WHERE id = 1;
xmin | xmax | ctid | balance
------+------+-------+---------
1002 | 0 | (0,2) | 200
(1 row)pageinspect shows both versions physically on page 0 — the old one points at its successor.
A> SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('accounts', 0)) ORDER BY lp;
lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 | 1001 | 1002 | (0,2)
2 | 1002 | 0 | (0,2)
(2 rows)DELETE doesn't erase anything either — it only stamps xmax on the current version.
A> DELETE FROM accounts WHERE id = 1 RETURNING xmin, xmax, ctid;
xmin | xmax | ctid
------+------+-------
1002 | 1003 | (0,2)
(1 row)
A> SELECT count(*)::int AS live_rows FROM accounts;
live_rows
-----------
0
(1 row)Zero rows for SELECT — yet both versions are still on disk, awaiting VACUUM.
A> SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('accounts', 0)) ORDER BY lp;
lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 | 1001 | 1002 | (0,2)
2 | 1002 | 1003 | (0,2)
(2 rows)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
The subtle beat in the middle: after A's update, B's row suddenly shows a non-zero xmax while still reading the old balance. B is looking at the old version, and the old version now carries the xid of the transaction that replaced it. The manual notes that a visible row version can have non-zero xmax — "that usually indicates that the deleting transaction hasn't committed yet, or that an attempted deletion was rolled back" — and B's case is the third flavor: the deletion committed, but B's snapshot predates it.
Then heap_page_items (from the pageinspect extension) drops all pretense: both versions sit on page 0, and the old tuple's t_ctid points at its successor (0,2) — the version chain PostgreSQL walks to find the current row.
DELETE is a stamp, not an eraser
SELECT finds nothing; the disk still holds every byte. In PostgreSQL, "an UPDATE or DELETE of a row does not immediately remove the old version of the row" — removal is VACUUM's job, and the bloat lesson shows what piles up in the meantime.
So the two write verbs reduce to the same primitive: an UPDATE writes a fresh version and stamps the old one's xmax, a DELETE stamps xmax and stops there. Nothing is ever changed in place, and nothing is removed on the spot. Those xmin/xmax stamps are how PostgreSQL knows which transaction created and killed each version, which is the raw material the snapshot lesson turns into a visibility rule. The ctid is more slippery: it changes on every update and again on VACUUM FULL, so never store it as a row identifier — that's what a primary key is for. And this is the same mechanism behind two things you already met: SELECT FOR UPDATE writes to disk because row locks live in the row header, and an update-heavy table needs VACUUM to stay small.