The two key properties
- Writers do not block readers. A writer creates a new version, so it never interferes with someone reading the old one
9:199:46. - Readers do not block writers. A reader holds no explicit lock; it reads at its assigned timestamp, so a writer never waits for it to finish
10:17.
Consequences:
- Every transaction gets a timestamp at BEGIN, which defines the consistent snapshot it is allowed to view
9:19. - It sees no uncommitted changes, and changes committed after its start never materialize for it
9:46. - Consistent snapshots come without acquiring explicit locks
10:17. - You get a new isolation level for free: snapshot isolation — viewing the database as it existed at a point in time
10:37.
Time-travel queries
- Big 1980s selling point: "run this query on the database as it existed three weeks ago" is trivial if you never garbage collect
10:50. - PostgreSQL originally shipped time travel as a founding design point (early/mid-1980s) and removed it in the 1990s — without garbage collection you run out of storage fast; Tom Lane was part of the team that took it out around 1999
11:1413:06. - Realistically almost nobody needs time travel — the audit/securities-exchange case is the exception
44:35.
Origin story
| Milestone | Detail | Citation |
|---|---|---|
| First description | MIT PhD dissertation, 1978, in the systems world (not databases) | 6:51 |
| First DB implementations | Rdb/VMS and InterBase, built at DEC in the early 1980s by Jim Starkey (who also claims to have invented BLOBs) | 6:51 7:21 |
| DEC's fate | DEC → Compaq → HP; Rdb bought by Oracle and rebranded Oracle Rdb (distinct from the Oracle database) | 7:21 8:34 |
| InterBase's fate | Enterprise code forked and open-sourced as Firebird; Mozilla wanted "Firebird" for its browser, couldn't use it, and landed on Firefox | 8:00 |
PostgreSQL lineage (aside)
- Berkeley academic project, originally written in Lisp, transpiled to C then compiled — eventually rewritten directly in C
11:51. - Commercialized by Stonebraker: Montage → Miro → Illustra → bought by Informix → bought by IBM
11:51. - In 1996 two grad students stripped QUEL, added SQL, and produced PostgreSQL
12:35.
Bottom line
Essentially every database system built in the last 20–30 years — and any new one built from scratch today — uses some variation of MVCC 9:08 28:24.