MVCC snapshot visibility
Simple terms
Postgres never overwrites a row in place. Every version carries two marks: which transaction created it, and which one removed it. When your query starts it takes a snapshot, a record of which transactions had already finished at that instant, and from then on it will only look at versions created before that line and not yet removed. That is how somebody else can update a row and commit while you keep reading the old value. You are not seeing stale data. You are seeing your version of it.
You might be asked
How does PostgreSQL's MVCC decide whether one transaction sees another's committed change? Walk through xmin/xmax, the snapshot (xmin:xmax:xip), and why a REPEATABLE READ reader can keep seeing the old value after a concurrent commit.
How you’d answer it
Every row version carries xmin (inserting xid) and xmax (deleting/superseding xid). A reader holds a snapshot = {xmin, xmax, xip[]}. A version is visible if its xmin is committed-and-before the snapshot (and not in xip[]) and its xmax is not. The fast rule: xid < snapshot.xmin => committed/visible-eligible; xid >= snapshot.xmax => in-progress/invisible. An UPDATE stamps xmax on the old version and inserts a new one, so a REPEATABLE READ transaction (whose snapshot was frozen first) keeps reading the old version even after the writer commits.
What the docs say
PostgreSQL never overwrites a row in place. Instead every row version carries two transaction stamps: xmin, the transaction that created it, and xmax, the transaction that deleted it or replaced it with a newer version. Visibility is decided from those two stamps and the snapshot the reader is holding.
The manual describes a snapshot as a fixed picture of the database "as it was some time ago, regardless of the current state of the underlying data", which is what protects a transaction from seeing half-applied concurrent changes. Concretely, a snapshot captures which transactions had already committed at the moment it was taken. A row version is visible to you only if its xmin is a transaction that, by your snapshot, is already committed and in the past, and its xmax is not.
That is what makes REPEATABLE READ behave the way it does. Such a transaction, the manual says, "sees only data committed before the transaction began; it never sees ... changes committed during transaction execution by concurrent transactions", and importantly, the snapshot is taken at the first real statement, not at BEGIN.
Now watch an UPDATE run underneath a repeatable-read reader. The writer doesn't modify the existing row; it stamps xmax onto the old version and inserts a brand-new version with its own xmin. The old version is still sitting there, and because the reader's snapshot predates the writer's commit, the reader keeps seeing that old version even after the writer commits. Two versions of the row coexist, and each transaction is simply directed to the one its snapshot allows.
References
- PostgreSQL 17 manual, Concurrency Control / Transaction Isolation
- PostgreSQL 17 manual, MVCC Introduction
- Source @REL_17_10, src/backend/storage/ipc/procarray.c (GetSnapshotData)
- Source @REL_17_10, src/backend/utils/time/snapmgr.c (XidInMVCCSnapshot)
- Source @REL_17_10, src/backend/access/heap/heapam_visibility.c (HeapTupleSatisfiesMVCC)
- pageinspect extension docs
What the code does
Navigated at tag REL_17_10. Snapshot construction: src/backend/storage/ipc/procarray.c, function GetSnapshotData() (line 2177). It scans the proc array and fills the Snapshot struct: snapshot->xmin (line 2503) = oldest still-running xid, snapshot->xmax (line 2504) = latest committed xid + 1 (the first xid NOT yet assigned/seen), and snapshot->xip[] (filled at line 2326) = the array of xids in progress at snapshot time. These three fields ARE the picture A froze: in the lab they printed as `801:801:` (xmin=801, xmax=801, empty xip). The visibility test: src/backend/utils/time/snapmgr.c, function XidInMVCCSnapshot() (line 1856). Its fast-path range check is exactly the rule the lab demonstrates: `if (TransactionIdPrecedes(xid, snapshot->xmin)) return false;` -> xid older than xmin: surely committed, NOT in progress. `if (TransactionIdFollowsOrEquals(xid, snapshot->xmax)) return true;` -> xid >= xmax: in progress / invisible. B's xid 801 >= A.xmax 801 hits THIS line, which is why A never sees 200. otherwise it binary-searches xip[]/subxip[] to decide. Where it's applied: src/backend/access/heap/heapam_visibility.c, function HeapTupleSatisfiesMVCC() (line 960). Line 1054: `else if (XidInMVCCSnapshot(HeapTupleHeaderGetRawXmin(tuple), snapshot)) return false;`, if the inserting xid is in-progress for this snapshot, the new version is invisible; the matching xmax checks (lines 1102-1138) decide whether the deleting xid hides the old version. mechanism (docs/source): how the snapshot is built and how xmin/xmax bound visibility. consequence (measured): the literal `801:801:` snapshot, the two `1|100` reads across B's commit, and the two coexisting tuple versions (t_xmin/t_xmax 800/801 and 801/0) in the proof above.
Proof from a real run
# Lab: pgi17 / PostgreSQL 17.10, two concurrent sessions, LITERAL output (the moat)
Session A runs at REPEATABLE READ; its snapshot is frozen on first statement.
Session B updates the same row and COMMITS while A is still open.
## (1) Before B's update: one live tuple, t_xmax = 0
=== heap BEFORE update: one live tuple, t_xmax=0 ===
lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 | 800 | 0 | (0,1)
(1 row)
## (2) Session A's snapshot is "801:801:" (xmin:xmax:xip-list)
From A's transcript below, `pg_current_snapshot()` returned `801:801:`, xmin=801, xmax=801,
no in-progress xids. Any xid >= xmax (801) is treated as in-progress / not-yet-visible to A.
## (3) B commits with transaction id 801, and A STILL reads 100
B's new row version is stamped t_xmin = 801. Because 801 >= A.snapshot.xmax (801),
XidInMVCCSnapshot() reports it as in-progress for A, so A keeps reading bal = 100
(see A's two `1|100` reads straddling B's commit), while B reads its own `1|200`.
## (4) pageinspect proves BOTH physical versions coexist on the page
=== pageinspect: BOTH versions exist. old.t_xmax = 801 (B), new.t_xmin = 801 (B) ===
lp | t_xmin | t_xmax | t_ctid | infomask_hex
----+--------+--------+--------+--------------
1 | 800 | 801 | (0,2) | 500
2 | 801 | 0 | (0,2) | 2900
(2 rows)
Decoding (htup_details.h):
lp 1 (old) t_xmin=800 t_xmax=801 t_ctid=(0,2) infomask=0x500
= 0x0100 HEAP_XMIN_COMMITTED + 0x0400 HEAP_XMAX_COMMITTED
-> inserted by 800 (committed), deleted/superseded by 801 (committed); t_ctid points
FORWARD to the new version (0,2). It is "recently dead": still needed by A's snapshot.
lp 2 (new) t_xmin=801 t_xmax=0 t_ctid=(0,2) infomask=0x2900
= 0x0100 HEAP_XMIN_COMMITTED + 0x0800 HEAP_XMAX_INVALID + 0x2000 HEAP_UPDATED
-> the live version produced by B's UPDATE; invisible to A only because of the snapshot test,
NOT because of any bit on the tuple.
Key point: visibility is decided per-snapshot at READ time (xmin/xmax vs the tuple's xids),
not by mutating the row. The old version cannot be pruned while A's snapshot still needs it.
## (5) A commits, takes a fresh snapshot, and now sees 200
A's last read in its transcript returns `1|200`.
======================== Session A full transcript (literal) ========================
= Session A full transcript ========================
BEGIN
__DONE_1781828883088910920__
801:801:
__DONE_1781828883650843537__
1|100
__DONE_1781828883662473581__
1|100
__DONE_1781828884849754861__
801:801:
__DONE_1781828884862513465__
COMMIT
__DONE_1781828885303233809__
1|200
__DONE_1781828885339239415__
=======================
======================== Session B full transcript (literal) ========================
= Session B full transcript ========================
UPDATE 1
__DONE_1781828883677470083__
1|200
__DONE_1781828884361482347__Using it under pressure
A situation where it matters
A reporting transaction runs for minutes at REPEATABLE READ while OLTP traffic updates the same rows. The report stays internally consistent because its snapshot is frozen at the first statement, it never sees mid-flight commits. The cost: every old row version it might still need cannot be vacuumed, so a long reader pins the xmin horizon and causes bloat. This is the classic 'idle-in-transaction / long-running query blocks VACUUM' incident.
The call you would make
Choose isolation deliberately: READ COMMITTED takes a fresh snapshot per statement (sees the latest committed data), REPEATABLE READ/SERIALIZABLE freeze one snapshot for the whole transaction (stable reads, but pin the visibility horizon). Keep read transactions short, and never leave a transaction idle-in-transaction, because its snapshot holds back the global xmin and blocks dead-tuple cleanup for the whole cluster.
When it goes wrong
Bloat or VACUUM 'not removing dead rows'? Find the oldest snapshot holder: SELECT pid, state, xact_start, backend_xmin, query FROM pg_stat_activity ORDER BY backend_xmin ASC NULLS LAST;, the smallest backend_xmin is pinning the horizon. Check SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction'. Use pageinspect heap_page_items() to confirm dead versions (t_xmax set, committed) are 'recently dead' rather than removable. pg_current_snapshot() and txid_current() help you reason about which xids a session can/can't see.
What people get wrong
Myth: 'an UPDATE overwrites the row, so other sessions immediately see the new value.' The lab disproves it: after B committed bal=200, pageinspect showed TWO physical versions (xmin 800/xmax 801, and xmin 801/xmax 0), and session A kept reading 100. Visibility is computed at read time from the reader's snapshot vs the tuple's xmin/xmax, the writer's commit does NOT retroactively change what an already-open REPEATABLE READ snapshot sees.
Second gotcha: a tuple invisible to A has NO special 'hidden' bit; in the lab B's new tuple was even marked HEAP_XMIN_COMMITTED, it's invisible purely because 801 >= A.snapshot.xmax.
Follow-ups they'll push on
Q. At READ COMMITTED, would session A have seen 200?
Yes. READ COMMITTED takes a NEW snapshot at the start of each statement, so A's second SELECT (after B committed) would compute a snapshot with a higher xmax and B's xid 801 would be < xmax, hence visible. The frozen-snapshot behavior is specific to REPEATABLE READ+.
Q. What exactly is in xip[] and why isn't B in A's xip[]?
xip[] lists xids that were IN PROGRESS at snapshot time. A froze its snapshot before B even started (snapshot 801:801:, empty xip), so B's xid 801 isn't in xip[]; it's excluded by the simpler xid >= xmax range check instead.
Q. Why can't the old version (t_xmax=801) be vacuumed yet?
A's snapshot still needs it. VACUUM can only remove a version once it's invisible to ALL snapshots (xmax committed AND below the global xmin horizon). A's open snapshot holds the horizon back, so the old version is 'recently dead', not removable.
Q. How does an index-only or seq scan actually invoke this test?
For each candidate tuple the executor calls HeapTupleSatisfiesVisibility, which for an MVCC snapshot dispatches to HeapTupleSatisfiesMVCC (heapam_visibility.c), the function decoded above, passing the tuple's header and the active snapshot.
Version notes
The model (xmin/xmax per version, snapshot = xmin/xmax/xip, XidInMVCCSnapshot range check) is identical on PG 14-18; captured on 17.10. pg_current_snapshot() (the 'pg_snapshot' type, xmin:xmax:xip text form) replaced the older txid_current_snapshot() naming in PG13+, so it is present on all of 14-18. GetSnapshotData in procarray.c has had performance rewrites (notably the PG14 caching/GlobalVis work) but the resulting xmin/xmax/xip semantics this lab relies on are unchanged. Source line numbers were read only for REL_17_10; older branches are cited by function name.
How this was verified
This concept is free end to end. The manual section is grounded in the official PostgreSQL documentation, the source walk was navigated in postgres/postgres at a version-pinned tag, and the evidence is labeled as raw output or a captured run summary. Where the text distinguishes mechanism (from docs/source) from consequence (measured), that boundary is kept explicit.
Connected
Where this concept connects
How this concept links across the library, the interview questions that test it, its plain-English glossary definition, and the guided pathways it belongs to. Open the full map to explore further.
Part of these pathways
Related concepts