PostgreSQL: Which Version Did the Reader See?

Two accounts contain 100 and 50 units. A transfer moves 20 units from the first to the second. The total remains 150.

A report arrives while that work is happening. It must read something. If it combines the first account’s old balance with the second account’s new balance, it reports 170. Nothing was deposited. The report has manufactured money by being badly timed.

This is a database problem even when every individual arithmetic operation is correct. Several users need to inspect and change the same stored information, and “whatever happens to be there when I look” is a poor specification for a financial total.

PostgreSQL’s 1990s development provides a useful route into this problem. Its new name marked continuity with an earlier research system. A later release changed how concurrent readers could see a coherent database while writers continued their work.

The important detail is the reader’s view of time.

A name with a longer history

Berkeley’s POSTGRES project was led by Michael Stonebraker; implementation began in 1986. Andrew Yu and Jolly Chen added an SQL interpreter in 1994, producing the Postgres95 branch. The PostgreSQL name was chosen in 1996, along with version numbering reset to 6.0.¹

That name change isn’t the release date. The 6.0 release notes give January 29, 1997, and list work including unique indexes and GiST in the distribution.²

The mechanism examined here belongs later still. Version 6.5, released June 9, 1999, introduced multi-version concurrency control, or MVCC. Its contemporary manual credits Vadim Mikheev’s implementation. The release also used the new behavior for consistent online database dumps.³⁴

Keeping those dates separate avoids making the 1996 name carry three years of subsequent engineering. A project can inherit ideas, acquire a new identity and deliver a major feature at different times. The calendar isn’t obliged to cooperate with a tidy anniversary paragraph.

A transfer has a boundary

Let’s give the invented accounts names, A and B, and place their transfer in a transaction. A transaction groups work into a unit that commits or rolls back. The transfer’s intended committed states are:

State	A	B	Total
Before transfer	100	50	150
After transfer	80	70	150

We can reason about intermediate calculations inside the writer without treating them as a completed public result. A writer that has changed A but hasn’t yet finished B must not casually present that partial transfer as committed information.

One way to coordinate access is to make competing operations wait behind locks. Waiting is often necessary, especially when two writers want to change the same thing. But forcing ordinary readers to wait for every writer gives reports and updates a rather close scheduling relationship.

The multi-version approach retains enough history for a reader to see an appropriate earlier state. It distinguishes the current work being performed from the committed versions visible to that reader. The 6.5 manual describes snapshots and nonconflicting ordinary reader/writer access; that doesn’t mean the database has abolished every lock.⁴

Our example will use invented time labels. They are teaching labels, not physical PostgreSQL transaction identifiers or an account of its on-disk tuple fields.

Two versions of one account

Suppose the old committed state exists at time 0. The transfer commits at time 20. A reader’s query begins at time 12, before that commit.

Its view includes A = 100 and B = 50. The writer may be busy making replacement versions, but those uncommitted values don’t belong in the reader’s committed snapshot. Even if the writer commits while that query is still running, the query shouldn’t switch halfway through to the later state.

A new query beginning at time 25 can see A = 80 and B = 70. Both complete views total 150.

For a minimal model, represent A with two version records:

A = 100    valid for snapshots before time 20
A =  80    valid for snapshots from time 20 onward

B has the matching pair, 50 and 70. Our simplified visibility function chooses the version whose interval contains the snapshot’s time. The old and new records refer to one logical account, even though more than one representation is being retained.

Original explanatory SVG, created in 2026. The invented time intervals illustrate visibility; they are not PostgreSQL transaction IDs or a reconstruction of tuple metadata.

This isn’t a complete copy of every table for every reader. The teaching ledger retains versions of changed rows. Nor is it an audit archive promising to keep every past state forever. It is enough history to support the views still needed by the concurrency mechanism.

That distinction answers a common conceptual difficulty. An update needn’t immediately make the previous value meaningless to everyone. It can make a new value eligible for some readers while older readers still have a legitimate reason to use the previous version.

The database is coordinating those reasons.

The lifetime of a snapshot matters

The 6.5 manual describes Read Committed as its default isolation level: a query sees data committed before that query began. Its separately documented Serializable mode uses the transaction’s starting view. These are the historical descriptions being compared here, not an assertion that 1999 Serializable has every guarantee of today’s implementation.⁵

For our report, that difference is visible without any complicated SQL. Imagine two separate read statements inside one transaction:

1. At time 12, read account A. The answer is 100.
2. The transfer commits at time 20.
3. At time 25, read account B.

With a fresh committed snapshot for each statement, the second answer is 70. Add the answers and the application produces 170.

Neither statement read an uncommitted value. Each statement saw a coherent state at its own starting time. The mistake is in treating two different starting times as one report.

With a transaction-start snapshot from time 10, the second read instead returns B = 50. The report’s two answers come from the earlier view and total 150. A new transaction after the transfer can see 80 and 70, also totaling 150.

Original explanatory SVG, created in 2026. Two separate statement snapshots can be individually consistent while their combined application result mixes times.

A single read query that examines both accounts under one snapshot can likewise calculate the consistent total. The report’s structure and its isolation choice need to agree about what “the accounts” means.

This is why a database capability doesn’t automatically settle an application’s policy. The server can provide a well-defined view. The program still has to request a view broad enough for the question it is asking.

Reading doesn’t reserve the answer

Now let a clerk read A = 100 and decide to deduct 20. Another writer changes A before the clerk sends the update.

The earlier read was legitimate. It wasn’t a reservation of the account’s future value. If the application later submits an unconditional instruction to set the balance to 80, it may be acting on information that has become stale.

Our teaching model can expose this without pretending to implement the whole server. Start at 100. Two separate callers read 100 and each calculate 100 minus 20. If both later replace the balance with their calculated 80, the final result is 80. Two intended deductions should have produced 60.

The arithmetic passed. The relationship between reading and writing failed.

A real application needs an appropriate update protocol: perhaps a database-side operation, a condition that detects a changed value, or explicit protection while making its decision. Which is suitable depends on the transaction’s requirements. The example isn’t a recommendation to implement account management with a few lines of unchecked client arithmetic.

The 6.5 migration notes explicitly warn that ordinary reads don’t protect returned rows from later changes. They point to SELECT FOR UPDATE or appropriate table locks when protection is needed, and describe built-in referential integrity as future work.³

The same period manual makes the boundary concrete: row locks coordinate competing writers, while some stronger table locks can block ordinary reads.⁶ “Readers and writers can coexist” is useful. “Nothing ever waits” would be a different, unsupported claim.

Check the model, keep the boundary

The accompanying checker tests the original interval model. At snapshot time 12, A and B are 100 and 50 even after the model records a commit at 20. At time 25, they are 80 and 70. Both totals are 150.

It then combines A from the first statement with B from the second and verifies the misleading 170. Keeping both reads on the transaction’s earlier snapshot restores 150. Finally, the separate stale-write demonstration produces 80 rather than the intended 60.

These checks were run in modern Python. They verify the example’s arithmetic and defined visibility rule. They don’t exercise PostgreSQL 6.5, its lock manager, physical version storage, crash recovery or conflict handling. Keeping that limit explicit is more useful than dressing a short simulation as a historical benchmark.

The original release documentation establishes the feature and isolation boundaries. The invented ledger gives the reader something small enough to inspect.

A database keeps several promises

PostgreSQL’s decade is a sequence of changes, rather than one clean beginning: Berkeley inheritance, SQL work, community naming, release improvements and a new concurrency model.

I find the snapshot distinction especially revealing. A value can be committed, yet too recent for an already running reader. An answer can be consistent, yet insufficiently protected for a later write. Two correct answers can produce a wrong report if they describe different moments.

The transfer still contains only two balances and one subtraction. Making that work amid other people’s activity requires a more careful definition of what the program is allowed to see.

The useful promise is a coherent view with known boundaries. A report cannot avoid choosing a moment merely by omitting the timestamp from its heading.

Footnotes

1. PostgreSQL project, “A Brief History of PostgreSQL”, read for Berkeley leadership, Yu and Chen’s SQL work and the 1996 naming/version decision; modern feature descriptions are not used as period evidence. ↩

2. PostgreSQL, 6.0 release notes, January 29, 1997, including unique-index and GiST changes. ↩

3. PostgreSQL, 6.5 release notes, June 9, 1999; MVCC, online dump behavior and migration caveats concerning reads, protection and referential integrity. ↩ ↩²

4. PostgreSQL 6.5 manual, “Multi-Version Concurrency Control”, including implementation credit to Vadim Mikheev. ↩ ↩²

5. PostgreSQL 6.5 manual, “Read Committed Isolation Level” and “Serializable Isolation Level”. These supply the query-start versus transaction-start distinction for ordinary reads; the article doesn’t backdate modern Serializable behavior. ↩

6. PostgreSQL 6.5 manual, “Locking and Tables”, distinguishing row-level writer coordination and stronger table-lock conflicts. ↩