Versions, not overwrites
PostgreSQL never changes a row in place. Each row version, a tuple, carries two stamps: xmin, the transaction that made it, and xmax, the one that replaced it (0: nobody yet). Here v1 holds qty 5, made by transaction 100.
T2 gets transaction id 101 and runs UPDATE. PostgreSQL leaves qty 5 where it is, writes a new version v2 with qty 4 and xmin 101, and stamps 101 into v1’s xmax. The commit log marks 101 in progress.
Snapshots decide
T1 starts a Repeatable Read transaction. Its first SELECT takes a snapshot, a note of which transactions count as finished, written 101:101: here. Every id below 101 is finished; 101 and later are not, so their work stays hidden.
T1 checks each version against its snapshot and the commit log. v1 was made by 100, committed: yes. Its xmax 101 is not finished for T1, so v1 still counts. v2 was made by 101: not yet. T1 reads 5, without waiting.
T2 commits. PostgreSQL does not touch the row versions: it flips 101 to committed in the commit log. Had T2 rolled back, the log would say aborted, and every reader would simply ignore v2.
T1 reads again. Its snapshot has not changed: to T1, transaction 101 is still unfinished, even though it has committed. So T1 still sees v1 and reads 5. Repeatable Read keeps one snapshot for the whole transaction.
T1 commits and runs SELECT again, so it takes a new snapshot (102:102:). Now 101 counts as committed. v1 was replaced by a finished transaction, so it is hidden; v2 is visible. T1 reads 4.
Cleaning up
No snapshot still running can see v1, and every new one treats 101 as committed. v1 is a dead tuple: nobody will ever read it again, yet it still fills 32 bytes of the page. Many updates leave many dead tuples.
VACUUM removes versions that no snapshot can see. v1’s 32 bytes go back to the page’s free space, ready for the next insert or update. The file stays 8,192 bytes: plain VACUUM reuses space but does not shrink the table.
What happens
;; step 1/9 · versions
SELECT xmin, xmax, ctid, * FROM stock;
xmin xmax ctid id qty
100 0 (0,1) 1 5
every row version (tuple) has a header:
xmin the transaction that inserted it
xmax the transaction that deleted or replaced it
(0 = nobody: this is the current version)
commit log: 100 committed
Real transaction ids are much larger than 100 and 101; the figure keeps only their order and gaps. A transaction that only reads does not even get an id. The visibility rule in the figure is simplified: the real check also handles a transaction seeing its own changes, subtransactions and locked rows.