How PostgreSQL MVCC actually works
See how PostgreSQL tuple versions, transaction snapshots, visibility rules, concurrent readers, VACUUM, and freezing work together.
The whole story in 6 lines
PostgreSQL MVCC keeps row history, gives each reader a snapshot, and reclaims versions only after every reader has moved past them.
- An update adds a physical tuple version instead of erasing the bytes that older readers may still need.
- A snapshot divides transaction IDs into committed history, work still active, and work that belongs to the future.
- An update stamps the old tuple with xmax and the new tuple with xmin, then commit status decides which change survives.
- Visibility is a local test of creator and remover transactions, so a reader can choose one version without locking the chain.
- Read Committed may refresh between statements while Repeatable Read keeps one snapshot, so their answers can diverge correctly.
- VACUUM reuses versions older than every active snapshot and freezes ancient creators to keep XID comparisons safe.
Setup
PostgreSQL lets readers and writers overlap by keeping row history, so we first need four plain terms for that history.
A tuple is one physical row copy, while an XID is the transaction number stamped into that copy.
The xmin stamp names the creator, and xmax names the transaction that deleted or replaced a tuple.
A snapshot judges those stamps, while VACUUM later reclaims versions that no active snapshot can still see.
These terms form one story from row history to cleanup. Let us begin with the physical versions stored inside a heap page.
Physical Versions
Now that tuple means a physical row copy, this heap page starts with one slot holding Ada's original balance. The index entry points to that physical location.
An update reserves a fresh slot because the original bytes may still belong to an older snapshot. The logical account stays the same while its physical history grows.
The old slot remains in place while the replacement arrives. What connects these separate copies into one logical row history?
A CTID link now joins each older slot to its successor. The index can keep one entry while PostgreSQL follows the chain to newer physical versions.
Toggle the Older reader control. Compare the protected eye path with the cleanup mark and decide whether the oldest tuple must remain available.
Physical history exists because readers may stand at different moments. That chain preserves the choice instead of forcing one global present. Next, we need the snapshot boundary.
Snapshot Cut
We just built a chain of candidate tuples. A reader captures one snapshot before deciding which physical copy belongs in its consistent view.
The snapshot records xmin as its oldest active transaction. Work older than that boundary is safely in the reader's committed past.
It also records the next unused XID as xmax. How should the reader treat transactions still running between those two boundaries?
Active XIDs remain as holes inside the numbered range. The snapshot therefore separates committed history, unfinished work, and future transactions without copying the table.
Switch the Moment control through Early, Mid update, and Late. Compare the moving boundaries and admitted balance.
A snapshot is a rule for judging XIDs, not a duplicate database. Its in progress list keeps concurrent work from masquerading as committed history. That compact record travels with the reader's view. It remains stable throughout that statement. Next, one writer will stamp an update.
Update Stamps
The snapshot showed transaction 105 as active. We now follow that writer while it changes Ada's balance from 55 to 70.
PostgreSQL reserves a fresh heap slot before completing the change. The old tuple stays intact while the replacement receives its data and header.
Both physical copies now exist together. Where does transaction 105 appear to mark the handoff between the old value and its replacement?
Twin 105 stamps land in the old xmax and new xmin together. Commit status then decides whether this prepared boundary becomes durable history.
Switch the Outcome control between Commit and Abort. Compare whether the replacement survives or the old tuple remains the valid endpoint.
An update prepares two tuple headers but commit status gives those stamps meaning. An abort leaves tentative stamps unable to replace committed data. That distinction keeps failed writes out of future snapshots. Next, readers will judge those surviving headers against snapshots.
Visibility Check
We now have tuple stamps and a snapshot. The reader can evaluate each physical version locally without waiting for a writer to release a row lock.
The creator rule rejects a tuple when xmin is active or future. That version did not yet belong to the reader's captured history.
A committed creator makes the tuple a candidate. Can it remain visible when its xmax remover committed before this snapshot began?
The aperture keeps the newest tuple with a visible creator and no earlier committed remover. Its balance drops into the result well as the query answer.
Switch the Snapshot control through Early, Middle, and Late. Compare which tuple passes both stamp tests and which balance reaches the result.
Visibility combines creator status with remover status for one snapshot. The same stored bytes can therefore support several correct answers over time. That choice needs no row lock. Next, two readers will apply these rules.
Concurrent Readers
★ If you remember one thing · Two correct readers can select different versions from the same heap page.
The visibility rule now becomes a concurrency story. Two statements can inspect the same heap version chain at different moments without corrupting either answer.
Statement one uses the early snapshot while transaction 103 is active. Balance 40 therefore remains the newest version allowed by that view.
Transaction 105 commits before statement two begins. Must the second statement reuse the first snapshot or may it capture a newer one?
Read Committed refreshes and reaches balance 70 while the earlier snapshot still points to 40. Both answers remain correct because each follows its own captured boundary.
Switch the Isolation control between Read committed and Repeatable read. Compare whether statement two advances to 70 or remains at 40.
Isolation determines when a statement receives a new snapshot, not whether tuple history exists. Both modes remain consistent because neither reader invents a version. The distinction appears between statements. Next, VACUUM will ask when history is safe to remove.
VACUUM and Freeze
The two readers showed why old versions cannot disappear immediately. VACUUM begins with the oldest snapshot that remains active anywhere in the cluster.
A tuple removed before that horizon is dead to every current reader. Its heap slot becomes a cleanup candidate instead of permanent history.
Dead candidates are now identifiable. Which side of the oldest snapshot horizon can VACUUM reclaim without harming a slow reader?
The horizon sweeps only unreachable versions into reusable slots. Every tuple at or beyond the boundary remains available for the oldest possible reader.
Adjust Oldest snapshot and toggle Freeze old xmin. Compare reusable slots with protected creator stamps.
VACUUM follows reader safety while freezing protects long term XID comparisons. Cleanup and visibility therefore meet safely at the same oldest reader boundary. Freezing protects the survivors after reclamation stops. The final safety rule is conservative by design. Now let us connect the complete MVCC story.
Recap
We started with page slots because every logical update becomes another physical tuple version that an older reader may still need.
Then we placed a snapshot across XID space, separating committed history from active transactions and work that had not started.
We stamped old xmax and new xmin with one updater XID, joining both versions at a single transaction boundary.
Next, the reader tested creator and remover status locally, selecting one visible tuple without locking the whole version chain.
Two statement snapshots then selected different balances from the same real fixture, showing why concurrency can preserve consistency without one shared present.
Finally, VACUUM followed the oldest snapshot horizon and freezing marked ancient committed creators as safely in the past.
Together these mechanisms keep history while readers need it, choose one view per snapshot, and recycle storage only after every view moves forward.
PostgreSQL MVCC works because row history, snapshot rules, and delayed cleanup share one boundary: the oldest view that must remain correct.
The whole story in 6 lines
PostgreSQL MVCC keeps row history, gives each reader a snapshot, and reclaims versions only after every reader has moved past them.
- An update adds a physical tuple version instead of erasing the bytes that older readers may still need.
- A snapshot divides transaction IDs into committed history, work still active, and work that belongs to the future.
- An update stamps the old tuple with xmax and the new tuple with xmin, then commit status decides which change survives.
- Visibility is a local test of creator and remover transactions, so a reader can choose one version without locking the chain.
- Read Committed may refresh between statements while Repeatable Read keeps one snapshot, so their answers can diverge correctly.
- VACUUM reuses versions older than every active snapshot and freezes ancient creators to keep XID comparisons safe.