MVCC Internals: Snapshots, Visibility, and Vacuum

A row changes from pending to paid while another transaction is still reading an earlier view. An in-place overwrite would force the reader either to block the writer or to accept that the value changed underneath it. Multi-version concurrency control (MVCC) takes a different path: the write creates a new version, and each reader selects the version visible to its snapshot.

Versioning explains why readers and writers can often proceed concurrently, but it also creates a storage obligation. Old tuples remain on pages, indexes may still point to them, aborted work leaves versions behind, and a transaction that stays open can make history globally unreclaimable. A background cleanup process must prove that no relevant snapshot can see a version before reusing its space.

The thesis is that MVCC is a visibility engine plus garbage collection. Snapshots are compact rules over transaction metadata, not copies of the database; vacuum is correctness-aware reclamation, not cosmetic maintenance. This article uses PostgreSQL tuple terminology for concrete examples while calling out the boundaries that differ in other engines. It assumes the isolation contract is already chosen and focuses on how versions implement it.

A Snapshot Is Not a Copy #

MVCC associates each logical row with one or more physical versions. A transaction reads a version only if the transaction that created it and any transaction that invalidated it satisfy that reader’s visibility rules. New writes install new versions rather than waiting for every older reader to finish.

Engines encode versions differently. PostgreSQL stores row versions as heap tuples and marks their creating and deleting transaction identifiers in tuple headers. InnoDB keeps current row data with transaction metadata and follows undo records for older versions. Some systems use timestamp ranges. The layout differs, but every design needs answers to three questions:

1. Which transaction created this version, and did it commit?
2. Was the version superseded or deleted, and was that action visible?
3. Does the reader’s snapshot include the relevant transaction outcomes?

A snapshot does not duplicate all rows at BEGIN. In a PostgreSQL-style representation, it captures boundaries over transaction IDs: an xmin below which transactions are generally old enough to be settled, an xmax at or above which transactions are too new, and a set of transactions active between those bounds. A simplified snapshot can be written as:

snapshot = {
  xmin: 120,
  xmax: 126,
  inProgress: [122, 124]
}

Transaction 118 is older than the lower boundary; transaction 127 is newer than the upper boundary; transaction 122 was active when the snapshot was taken. Exact rules include the reader’s own writes, subtransactions, command IDs, and transaction-status state. Treating the three fields as a complete portable algorithm would be wrong, but they reveal why a snapshot remains compact even for a large database.

The time when the engine acquires or refreshes a snapshot depends on the configured isolation behavior. That policy is important, but the internal task is the same: evaluate tuple metadata against the particular snapshot assigned to this statement or transaction.

Tuple Metadata Turns History into a Visibility Decision #

A PostgreSQL heap tuple contains, among other fields, xmin, the transaction that inserted this version, and xmax, which may identify a deleting transaction or an update that superseded it. Header hint bits can cache facts such as whether those transactions committed or aborted. A page-level visibility map can tell maintenance and scan paths that every tuple on a page is visible to all sufficiently current transactions, but it does not replace per-tuple rules for arbitrary old snapshots.

A simplified visibility decision for a candidate tuple is:

• If the inserting transaction aborted, the tuple is never visible.
• If the inserter is still in progress for this snapshot, the tuple is normally invisible, except for the reader’s own command-aware changes.
• If the inserter committed after the snapshot boundary, the tuple is too new.
• Otherwise the tuple was born in time; now inspect its xmax.
• If no effective delete/update exists, the tuple is visible.
• If the deleting transaction aborted or is invisible to the snapshot, the old tuple remains visible.
• If that transaction committed and belongs to the snapshot’s past, the tuple is dead to this reader.

The engine may consult an in-memory transaction table, commit-status storage, tuple hint bits, and snapshot state to answer those branches. A transaction ID alone does not encode committed versus aborted. Status metadata must survive restart and eventually be summarized safely as identifiers age.

Visibility is checked at several layers. A sequential heap scan evaluates tuples directly. An index lookup finds an entry for a key, then may visit the heap to determine whether the referenced tuple version is visible. An index-only scan can avoid the heap only when metadata proves the page is all-visible for the relevant conditions. Consequently, version churn can increase reads even when the index remains selective.

Worked Example: Three Versions of One Invoice #

Suppose an invoice row begins as version v1, inserted by transaction 110:

v1: invoice=42, status='pending', amount=80, xmin=110, xmax=0

Transaction 122 starts and acquires snapshot { xmin: 120, xmax: 126, inProgress: [122, 124] }. Transaction 123 had already updated the invoice and committed before that snapshot was acquired. Its update does not overwrite v1; it marks v1 as superseded and creates v2:

v1: status='pending', xmin=110, xmax=123
v2: status='paid',    xmin=123, xmax=0

For transaction 122, the creator of v2 is committed and not listed as in progress, so v2 is visible. The xmax on v1 is also a visible committed update, making v1 invisible to this snapshot. The reader returns paid.

Now transaction 124, which appears in the snapshot’s in-progress set, changes the amount from 80 to 75. It marks v2 and creates v3:

v1: status='pending', amount=80, xmin=110, xmax=123
v2: status='paid',    amount=80, xmin=123, xmax=124
v3: status='paid',    amount=75, xmin=124, xmax=0

Even if transaction 124 commits while transaction 122 is still using this snapshot, transaction 122 sees v2. Transaction 124 was active at snapshot creation, so v3 is excluded and the deletion boundary on v2 is not effective for this reader. A newer snapshot can see v3 after 124 commits.

If transaction 124 aborts instead, v3 is permanently invisible and its attempted supersession of v2 has no committed effect. Cleanup can eventually remove the aborted tuple. This is why “take the tuple with the largest transaction ID” is not a visibility algorithm: transaction outcome and snapshot membership matter.

The chain is a conceptual history; PostgreSQL updates may place versions on different heap pages, and index entries determine how they are reached. The visibility outcome, not physical adjacency, binds them into one logical row.

Updates Multiply Heap and Index Work #

An update creates a new heap tuple even when only one column changes. Index maintenance depends on whether indexed values change and whether the engine can keep the version chain local. PostgreSQL’s heap-only tuple (HOT) optimization can avoid new index entries when no indexed column changes and the page has room for the new version. Existing index entries lead to the chain’s root, and the heap follows links to a visible member.

HOT is an optimization, not a guarantee. A full page may force the new tuple elsewhere, requiring index updates. Choosing a lower table fill factor reserves page space for future versions and can improve HOT opportunities, but it consumes more pages for the same live data. Updating an indexed column necessarily changes index keys. An application that rewrites unchanged indexed columns or updates rows at high frequency can therefore create substantial index churn.

Deletes are also versioned. Marking a tuple deleted makes it invisible to future snapshots after commit, but its bytes and index references cannot necessarily disappear immediately. Index scans may encounter entries for dead tuples and visit the heap only to reject them. Updates and deletes thus affect read latency, cache efficiency, and storage even when queries return the same number of live rows.

Page pruning can remove or redirect tuple-chain members when a backend visits a page and the versions are no longer needed there. Vacuum performs broader maintenance, updates visibility metadata, removes dead index references using engine-specific mechanisms, and makes heap space reusable. Reuse often occurs inside the existing table file; shrinking the file returned to the operating system may require a more invasive rewrite or specialized operation.

Vacuum Reclaims Only Beyond a Safe Horizon #

Vacuum determines which versions are dead to all snapshots that must still be honored. It computes a reclamation horizon from active transactions and other retention consumers. A tuple invisible to new transactions may remain necessary for one old snapshot, a replication slot, a standby query, or another feature that preserves history.

For an obsolete version, cleanup broadly proceeds through these responsibilities:

1. Establish that no required snapshot can see the tuple.
2. Prune dead heap versions and make their line-pointer space reusable where safe.
3. Remove or mark index references that no longer lead to live tuples.
4. Update free-space and visibility metadata.
5. Advance transaction-age information so old committed tuples no longer depend on ambiguous identifier comparisons.

Vacuum must interleave with ordinary readers. It cannot delete a file and invalidate their pointers indiscriminately. Page pins, locks, version metadata, and index cleanup protocols let maintenance alter structures without exposing partially repaired state.

Autovacuum-style scheduling uses thresholds based on dead tuples, table size, insert activity, and transaction age. A large table may need scale-factor tuning because a small percentage still represents many dead rows. A tiny, intensely updated table may need a low absolute threshold. Too little vacuum permits bloat and transaction-age risk; too aggressive a schedule can compete for I/O and CPU without finding enough reclaimable work.

Long Snapshots Turn Retention into Bloat #

One forgotten transaction can hold the global visibility horizon behind a large stream of updates. Vacuum may scan tables but be unable to remove versions newer readers no longer need. Table and index pages grow, cache density falls, scans inspect more dead tuples, and future maintenance has more work. The blocker can be idle in transaction and consume little CPU while causing large downstream cost.

Logical replication slots and lagging replicas can impose related retention constraints. They may require old log or row information even when no local application query does. Backup tools and exported snapshots can also extend horizons. Monitoring only dead_tuple_count without identifying the oldest retained transaction or slot leaves the cause hidden.

PostgreSQL transaction IDs are finite-width values compared with wraparound-aware rules. Very old unfrozen tuple IDs could eventually be mistaken for future transactions as identifiers cycle. Vacuum freezes sufficiently old committed tuple metadata, recording that their creation is universally in the past. Anti-wraparound maintenance is a correctness requirement and can become urgent if routine vacuum is blocked or disabled.

Operationally, track transaction age, oldest snapshot age, idle-in-transaction sessions, dead and live tuple estimates, table and index growth, vacuum progress and duration, pages scanned versus skipped, tuples removable versus retained, replication-slot lag, and autovacuum cancellations. Alert before transaction age reaches emergency territory and before disk exhaustion turns maintenance pressure into an outage.

The remedy is not always “run vacuum harder.” First remove or bound the retention source: terminate abandoned transactions according to policy, shorten batch work, paginate long reads without holding one unnecessary snapshot, repair stalled consumers, and give maintenance enough resources. Then vacuum can reclaim what was previously protected.

Verify Visibility, Cleanup, and Operational Safety #

Visibility tests need controlled transaction interleavings. Open real database sessions, capture a snapshot, update and commit from another session, and assert which version the first session sees according to its configured snapshot lifecycle. Test an aborted insert, an aborted delete, the reader’s own writes before and after command boundaries, subtransactions, and concurrent updates. Do not substitute a mock repository; tuple and snapshot behavior belongs to the database engine.

For the worked example, hold transaction 122 open while 124 commits. Confirm the older snapshot still sees amount 80 and a newly acquired snapshot sees 75. Run maintenance while 122 remains active and verify its required version survives. End 122, run maintenance under test conditions, and inspect engine statistics or page-level diagnostic tools to confirm the obsolete version becomes reclaimable without changing query results.

Exercise HOT and non-HOT paths by updating a non-indexed column with page space available, then an indexed column or a full page. Assert logical results and inspect index growth rather than assuming the optimizer fired. Generate repeated updates under a held snapshot to reproduce bloat in a bounded fixture, release the snapshot, and verify maintenance debt drains.

Recovery and upgrade tests should include transaction-status metadata because tuple headers refer to it. Corrupt or missing status information must not silently make an uncommitted version visible. For operations, rehearse cancellation of a blocking long transaction, autovacuum worker saturation, a lagging replication slot, low disk headroom, and an anti-wraparound alert. Each runbook needs a query that identifies the retention owner before taking destructive action.

Takeaways #

• MVCC stores row histories and evaluates them against compact snapshots; it does not copy the database for each reader.
• Tuple creator/deleter IDs require transaction-status and snapshot context before they imply visibility.
• Updates and deletes create physical versions, heap work, and often index work even when the logical row count is unchanged.
• Vacuum reclaims a version only after every protected snapshot and retention consumer has moved beyond it.
• Long transactions, old snapshots, and stalled replication can convert ordinary versioning into table and index bloat.
• Freezing old transaction metadata prevents identifier wraparound from corrupting visibility decisions.
• Correct tests orchestrate real sessions and maintenance boundaries, while operations monitor horizons and age rather than only table size.

MVCC makes concurrency practical by retaining enough past to answer each reader consistently. Its operational discipline is the inverse: identify precisely when that past has no remaining observer, and reclaim it before retained history overwhelms the present.