MVCC and Snapshot

Multiversion concurrency control, or MVCC, lets a database retain versions so readers can use a defined view while writers make changes. A snapshot here supplies visibility rules; it is not a full physical copy of the database or simply every transaction ID below a number. PostgreSQL considers transaction status and the reader’s own changes when deciding which row version is visible.

Why versions instead of locks

The point of keeping versions is stated in PostgreSQL’s own documentation: with MVCC, “locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading.” A reporting query that scans a table for a minute does not stop orders from being updated during that minute, and the orders being updated do not change what the query sees. The documentation puts the reader’s side as seeing “a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data.”

The mechanism that makes this possible is that an update does not overwrite. A new version of the row is written and the old one is left in place with the transaction information that says when it stopped being current, so a reader can be shown whichever version was current as of its own snapshot. That is also why the answer to “which version do I see” depends on transaction status rather than on time: a version written by a transaction that has not committed is visible to nobody but its own writer, however recently it was written.

Suppose an order changes from paid to refunded. A snapshot taken before the refund commits can still read paid; a later snapshot can read refunded. The isolation level determines how long the snapshot lasts. Sharing a view can prevent an extract from mixing different commit points, but it cannot fix invalid source relationships or application updates committed in separate transactions.

The cost of not overwriting is that old versions accumulate. PostgreSQL states the rule and its reason together: an UPDATE or DELETE “does not immediately remove the old version of the row” because “the row version must not be deleted while it is still potentially visible to other transactions” — and eventually such a version “is no longer of interest to any transaction,” at which point its space “must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM.” Two consequences matter in operation. Ordinary VACUUM “removes dead row versions in tables and indexes and marks the space available for future reuse” but “will not return the space to the operating system” except in a narrow end-of-table case, so a table that grew during an incident stays large. And because a version is retained while any transaction might still need it, a session holding an old snapshot open — a forgotten analytical query, an idle transaction — keeps dead rows uncollectable for as long as it lives, which is how a read-only workload ends up causing a write-side space problem.

SQLite WAL provides stable page views and allows one writer at a time, with different cleanup rules. The same versioning is what lets a read replica answer a long query while replay continues, and what makes that replay wait. Versioning reduces some reader/writer conflicts; it does not eliminate locks or every wait.

Versioned reads also appear in an open table format: a writer publishes a new table state while a query reads the version it selected. The cleanup contract differs from PostgreSQL’s active-snapshot tracking. Iceberg retains data files referenced by retained snapshots; expiring a snapshot can remove files no longer referenced by another retained snapshot. An executing query does not, by itself, establish a universal file-retention lock. Delta Lake likewise requires its VACUUM retention interval to exceed the longest concurrent transaction and relevant stream lag; deleting files still in use can break readers or interfere with pending writes. Long retention consumes storage, but shortening it safely requires accounting for active work, lagging consumers, and any explicit snapshot-reference or pinning mechanism the engine provides. PostgreSQL row-version retention and object-store file retention therefore need separate operating rules.

Worked examples: Relational Database Internals.

References

• PostgreSQL — Concurrency Control
• PostgreSQL — Transaction Isolation
• PostgreSQL — Routine Vacuuming
• SQLite — Write-Ahead Logging
• Delta Lake, Table utility commands: VACUUM retention and concurrent operations
• Apache Iceberg, Maintenance: snapshot expiration and orphan-file deletion