Row versions: xmin, xmax, ctid
PostgreSQL 18's system-column contract distinguishes a logical row from its physical tuple versions. UPDATE creates a new version; the old version can remain while snapshots need it. DELETE does not immediately remove the old version. These MVCC rules do not eliminate table-lock or locking-read waits.
| column | meaning |
|---|---|
xmin | xid of the transaction that inserted this tuple version |
xmax | deleting transaction's xid, or zero for an undeleted version; a nonzero value can belong to a visible version. Row locking and MultiXacts also use the tuple header, so do not use a nonzero value alone as proof of deletion. |
ctid | physical tuple address (page, slot); changes with UPDATE and may change with VACUUM FULL |
Watching an UPDATE make a copy
Inspect this tuple version: xmin identifies its inserting transaction, ctid its physical location. xmax can record deletion or row locking; this schedule follows the update/delete case.
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 creates a new tuple version and changes the old version's xmax and successor pointer.
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 marks the current version deleted; the following page inspection checks that its tuple body remains here.
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.6 · Run it yourself · Scenario source
A's update returns balance 200 at (0,2). B's older Repeatable Read snapshot still returns balance 100 at (0,1) with the updater's xid in xmax. After B commits, its new read returns the new version. The asserted pageinspect fields show both tuples and the old tuple's successor pointer. This small page layout is demonstrated, not a guarantee of consecutive slots for every update.
DELETE is a stamp, not an eraser
The DELETE's returned xid and the inspected page fields are asserted alongside zero live rows. Both tuple bodies still appear in this observation. Later pruning or VACUUM may reclaim them. In-place header changes do occur: visibility hints and row locks are examples, so “nothing ever changes in place” would be too broad. HOT can reuse space during ordinary access when its conditions permit.
Use a primary key as the stable logical identifier, not ctid. The snapshot lesson relates versions to visibility, and VACUUM shows reclamation. Row-lock encoding and MultiXact members are documented by the transaction-information functions; this schedule does not execute every encoding.