MVCC and reading the past

Not stopping the world

Multi-version concurrency control starts from a practical goal: readers should not have to stop the world to get a stable answer. Instead of overwriting rows in place and forcing readers to wait behind writers, the database keeps multiple tuple versions. A reader uses a snapshot to decide which versions belong to its view of time. Writers can create newer versions while older readers continue to see the past.

A snapshot describes transaction visibility. It records enough information to distinguish transactions that committed before the snapshot, transactions still in progress, and transactions that are too new to be visible. When a scan finds a tuple version, it checks the creating transaction, the deleting or superseding transaction, and the snapshot. The result answers one question with a lot of machinery behind it: should this version exist for this reader?

Time lives in tuple headers

Tuple headers make MVCC physical. A version has an inserting transaction identifier and may have a deleting transaction identifier. An update is commonly represented as a delete of the old version plus an insert of a new version, linked by metadata. The old version remains for snapshots that should still see it. The new version is visible only to snapshots where its creating transaction qualifies and the old version no longer does.

The locking alternative

Strict read-write locking takes a more literal route. In a lock-heavy design, a writer updating a row may block readers until it commits, and readers may block writers to preserve their view. This is easier to conceptualize, but it damages concurrency for read-heavy workloads. MVCC lets readers and writers pass each other more often because they are not always competing for the same single version.

MVCC is not free. Each update creates another physical version. Indexes may point to old and new entries. Pages accumulate dead tuples. Visibility checks add per-row work. Vacuum becomes necessary to reclaim space. The system trades storage and cleanup complexity for better concurrency and stable snapshots. That trade is one of the defining features of Postgre-like architecture.

How snapshots shape what you see

Different isolation levels use snapshots differently. Under read committed, each statement can get a fresh snapshot, so a transaction may see changes committed by others between statements. Under repeatable read, a transaction keeps a stable snapshot, so repeated queries see the same committed world even as others continue writing. Serializable isolation adds stronger rules to prevent dangerous interleavings that would not match any serial order. MVCC supplies the versions, and isolation policy decides how snapshots are chosen and validated.

Writers still conflict with writers. If two transactions try to update the same row, the database must coordinate. One may wait for the other. If the first commits, the second may need to recheck whether its update still makes sense under its isolation level. MVCC reduces reader-writer blocking, but it does not make all conflicts disappear. Physically there is still one current update chain for a row.

Visibility also affects indexes. An index entry may lead to a heap tuple that is not visible to the current snapshot. That is why many index scans must recheck the heap. The index can quickly find candidate locations, but MVCC truth lives in the heap tuple header and transaction status. Index-only scans need extra visibility information to avoid heap visits safely.

Long-running transactions are expensive. If a snapshot remains open for a long time, vacuum may be unable to remove tuple versions that snapshot could still see. Space grows, indexes carry obsolete entries, and transaction ID cleanup is delayed. The database supports old readers, but the whole system pays the cost. This is why operational guidance often warns against idle transactions left open.