Systems and protocols · Intermediate

PostgreSQL MVCC: choosing visible row versions

Follow snapshots and row-version cleanup in PostgreSQL while keeping isolation-level rules explicit.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Updates create version history
  2. Two sessions and one update
  3. Old versions and VACUUM
  4. Application invariants still need a design
  5. Compare two isolation levels with the same schedule
  6. Include the transaction’s own writes in the model
  7. Protect invariants that span several rows
  8. Ask why an older version is still needed
  9. Ask Logeia about this articleCheck your understanding

Updates create version history

PostgreSQL MVCC permits multiple versions of a logical row. A query decides which versions are visible according to its snapshot and transaction rules, rather than simply reading whichever physical value was most recently written. This provides a useful model for concurrent readers and writers.

MVCC reduces some read-write interference, but does not eliminate every lock. Concurrent updates, schema operations and explicit locking can still wait. Explain the version-visibility rule separately from the locks required by a particular operation; one mechanism does not replace all the others.

Two sessions and one update

Suppose session A starts a query and session B then updates and commits a row. The value observed by A depends on when its snapshot was established. Under Read Committed, successive statements ordinarily receive new snapshots, so two statements within one transaction can see different committed values.

Repeatable Read follows a different visibility rule. Avoid the blanket claim that MVCC always means seeing the first value read. State the isolation level, the statement boundaries and whether the operation is a plain read or an operation that may wait on concurrent updates.

Old versions and VACUUM

Updates and deletes leave old row versions that eventually need cleanup. VACUUM participates in that lifecycle, but cannot simply remove a version that remains necessary for an existing snapshot. Storage reclamation follows visibility requirements, not just elapsed time since an update.

A long-lived transaction holding an old snapshot can impede cleanup. When investigating table growth, inspect transaction lifetime and vacuum activity alongside update volume. The number of currently live rows is not enough to explain the table’s entire storage footprint or maintenance needs.

Application invariants still need a design

MVCC alone does not make every application read-modify-write pattern correct. Two sessions can derive replacement values from the same old balance. Depending on the operation and isolation level, preserving the business invariant may require an atomic UPDATE, a version predicate, locking or serializable execution with retries.

Finally, PostgreSQL’s implementation details should not be presented as universal behavior of every MVCC database. Separate the general idea of multiple versions from product-specific snapshot rules, update behavior and cleanup. A correct design names the database and isolation assumptions on which it relies.

Compare two isolation levels with the same schedule

Start with one row containing 40 and assume no other writes. A begins a transaction and performs a plain SELECT that reads 40. B then updates the value to 41 and commits. A performs another plain SELECT. With Read Committed, A’s second statement takes a new snapshot and can see 41. With Repeatable Read, if A’s first SELECT established its snapshot, the second SELECT within that transaction still sees 40. Distinguish executing BEGIN from the first statement that establishes the relevant snapshot.

This schedule is a reasoning example for plain reads, not a complete description of locking SELECTs or conflicting UPDATEs. Visibility rules also do not automatically preserve every business invariant. A long-held old snapshot can prevent VACUUM from reclaiming versions that it still needs. Record isolation level, statement order, commit order and open transaction lifetime when diagnosing behavior. Instead of saying only that a value is latest, state the statement and snapshot from which it is visible.

OperationA at Read CommittedA at Repeatable Read
A’s first plain SELECT4040
B updates to 41 and commitsLater statement can see new stateA retains established snapshot
A’s second plain SELECT4140

Include the transaction’s own writes in the model

A snapshot is not adequately described as a photograph that freezes absolutely everything at transaction start. PostgreSQL’s later commands can see changes previously made by their own transaction. Keep that rule separate from visibility of other transactions. Furthermore, a Read Committed UPDATE encountering a concurrently changed target can wait and re-evaluate its condition against the updated version. A model written for ordinary SELECT statements must not be generalized unchanged to every update or locking operation. Record the SQL operation and isolation level in a schedule. Otherwise two observers can report different yet valid results while believing they ran the same experiment.

Protect invariants that span several rows

Suppose two doctors are on call and at least one must remain available. Two transactions each see the other doctor on call in their snapshots and independently switch their own row off. They write different rows, yet both committing can leave nobody on call. PostgreSQL Repeatable Read’s stable visibility does not imply protection of every multirow business invariant. Serializable execution can detect dangerous dependencies, while an explicit common locking design can serialize the relevant decision if implemented consistently. Applications must handle aborted transactions and retry the complete decision process where appropriate. Reissuing only a final UPDATE while retaining a stale earlier decision defeats the intended protection.

Ask why an older version is still needed

For a growing table, distinguish reclaimable dead versions from versions still needed by a reader or another configured retention condition. Long-lived snapshots and replication-related settings can affect what cleanup is safe. Align transaction observations, waits, vacuum progress, and statistics timestamps before drawing a conclusion. Ordinary VACUUM making space reusable does not promise a large reduction of the relation’s file size returned to the operating system. Logical row count, file size, and reusable space measure different things. Reclaiming dead tuples and freezing transaction identifiers also serve different purposes. Use these distinctions to choose the next measurement rather than interpreting one rising number as a complete diagnosis.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 13

Choose a learning path