What does a reader see while a value changes?

Our shop is preparing a price report. While the report runs, a seller changes the canvas tote’s price from 20 to 25 credits.

Should the report see 20 or 25? If it reads the product twice, could it get a different answer the second time?

The previous report example combined two committed values from different moments and produced the wrong total. We need a way for a reader to keep one consistent view while a writer continues. Here we follow the mechanism using just one price. If commit and rollback are unfamiliar, start with the transaction introduction.

Give the report a fixed view

For this lesson, use a deliberately simple model:

Every committed change gets an ordered commit timestamp.
The report receives a snapshot timestamp of 15, which stays fixed for its transaction.
For each key, it reads the newest committed version at or before 15.
The report only reads; it makes no changes of its own.

The timestamps are ordering labels, not seconds on a wall clock. This is a model for understanding visibility, not a claim about how PostgreSQL stores transaction metadata.

A snapshot describes which committed changes a reader is allowed to see. A fixed snapshot lets the report keep the same view while later changes happen elsewhere.

Watch the price change

The price of 20 was committed at timestamp 10. The seller’s new price of 25 commits at timestamp 20, after the report’s snapshot.

10 · Original price commits
The committed price is 20 credits.
15 · Report takes its snapshot
The report's view includes commits through 15. It reads 20 credits.
20 · Seller's update commits
The new committed price is 25 credits. Keep the older version for readers that still need it.
25 · A new reader takes a snapshot
The new reader sees 25 credits. The report at snapshot 15 still sees 20.

Time runs downward. Both readers use committed data, but their snapshots include different sets of changes.

The report can read the product again after the seller commits and still get 20. Its snapshot has not advanced. A new reader at snapshot 25 can see the change committed at 20.

Neither answer is an unfinished write. Both prices were committed; the readers are looking at different points in the database’s history.

Keep versions instead of overwriting the only copy

If the database destroyed the old price when the seller changed it, the report could no longer read its original view. Keeping multiple versions solves that problem.

This is the central idea of multiversion concurrency control, or MVCC. A writer creates a new version, and a reader uses visibility rules to choose the version belonging to its snapshot.

Older version
Price: 20 credits
Committed at 10
Report at 15 chooses this.

Newer version
Price: 25 credits
Committed at 20
Reader at 25 chooses this.

The version chosen depends on the snapshot. “Newest version in memory” is not the same as “version this reader may see.”

In our model, choosing a version has three steps:

Start with the newest version of the key.
Ignore it if it has not committed, or if its commit timestamp is later than the snapshot.
Return the first version that passes both checks.

At snapshot 15, the price committed at 20 is too new. The next version, committed at 10, qualifies. At snapshot 25, the version committed at 20 already qualifies, so the search stops there.

This is why an ordinary reader need not wait for every writer to finish: an appropriate older committed version can answer the read. Writers still need rules for conflicting changes, and some operations can still wait on locks.

Change one condition: the seller has not committed

Suppose the seller has prepared the price of 25, but the update remains uncommitted. A new reader starts after that preparation.

The reader still cannot treat 25 as committed data. The seller might roll back. In our model, the reader ignores the tentative version and uses the latest eligible committed one, which is 20.

This is also why a transaction number alone is insufficient. A transaction can start early and finish late. A real engine must know whether a version’s creating transaction committed and whether the reader’s snapshot includes it. Our commit timestamps make those two ideas easier to separate.

Snapshot lifetime changes the answer

So far, the report kept one snapshot for its whole transaction. That choice matters.

PostgreSQL 18 uses a fresh snapshot for each ordinary statement at Read Committed. Two SELECT statements in the same transaction can therefore see different prices if another transaction commits between them. At Repeatable Read, ordinary reads use a snapshot established by the transaction’s first non-transaction-control statement. PostgreSQL isolation levels

Keep the two ideas separate: MVCC provides versions and visibility machinery; the isolation level determines the promised behavior. Merely hearing that a database “uses MVCC” does not tell you how long a snapshot lasts.

A stable view also does not make every decision safe. Two writers can read the same old state and make changes that conflict with a shared business rule. The deeper reading on write skew follows that separate problem step by step.

Why old readers cost space

After the seller commits 25, can the database delete the version containing 20?

Not while our report at snapshot 15 might still need it. Once that report ends, the old version may become eligible for cleanup, provided no other reader or retention requirement still needs it and no active access makes reclaiming it unsafe.

Keeping a report open for a long time can therefore keep old data around. This is one cost of allowing writers to continue while readers use historical views. PostgreSQL’s vacuum process reclaims space from row versions that are no longer needed. PostgreSQL routine vacuuming

Predict what a deletion means

Extend our model: at timestamp 30, the seller deletes the product. That committed deletion is represented by a marker meaning “this key is absent.”

What should readers at snapshot 25 and snapshot 35 see?

Show the reasoning

The reader at 25 still sees the price of 25 credits, committed at 20. The deletion at 30 is outside its snapshot.

The reader at 35 sees that the product is absent. The deletion marker is the newest committed version visible to it. It must stop there. Skipping the deletion marker and returning an older price would incorrectly bring the product back.

An old snapshot can therefore need a value that a new snapshot correctly treats as deleted. Cleanup must respect both readers.

Return to the report

Our report at snapshot 15 keeps seeing 20 credits, even after the seller commits 25. The database can support that view by retaining the old version and choosing it using the report’s snapshot.

The tradeoff is now visible: readers can use older committed data while writes continue, but someone must track visibility, resolve conflicting writes, and eventually clean up old versions.

The next lesson compares other ways to coordinate transactions. Use this picture as the comparison point: which work waits, which work retries, and what state must be retained?