Multi-Version Concurrency Control (MVCC)

Concept. MVCC lets readers use earlier row versions while writers create new ones. MVCC takes the other route: instead of serializing access to one copy, it keeps several versions of each row, so a reader takes the version it is allowed to see and never waits. Each version is stamped with when its writer committed. Every transaction reads the version that was current as of its start. That is its own consistent view of the database without copying it, the same way git lets many engineers work on one repository without duplicating it or exposing half-done work. Readers and writers never block each other.

Intuition. Take one value, V. Mickey opens a 10-minute scan with a snapshot at 3:00. Minnie changes V and commits at 3:03, creating a second version of that one value. Mickey's snapshot is 3:00, so he keeps reading the version that was current then; a query that starts at 3:05 reads Minnie's new version. Only V has two versions. Every value nobody touched is stored once and shared. A snapshot is just this rule applied to every value at once: read the right version of each.

A familiar example: git

You already use a system that solves this exact problem: git.

A left-to-right timeline of one repository: v1 is the base, Engineer B pulls and gets v1, Engineer C commits a change that creates v2 (v1 plus the diff, in violet), and Engineer D pulls afterward and gets v2 while B still holds v1, unaffected.

Figure 1. One repository changes over time, and your checkout is frozen at the moment you pulled. Engineer C commits a change, which adds v2 (v1 plus the diff, violet). Engineer D pulls afterward and gets v2; Engineer B pulled earlier, so B still has v1 and C's change does not touch B. Same repository, but when you pulled decides what you see.

Keep two terms straight. The repository is the shared store of the project's whole history, kept once. A working copy (your checkout) is the snapshot on your laptop that you edit. That split buys two things: everyone works in parallel without waiting on anyone, and storage holds the set of changes, not one full project per engineer. (MVCC predates git: Reed described it in the late 1970s, and git reached for the same trick in 2005.)

Reading the right version

A database running thousands of concurrent transactions faces git's two problems, and MVCC answers both the way git does, by keeping multiple committed versions instead of overwriting. That gives two rules you already believe:

  1. Read only committed values. Never a teammate's half-typed edit before they commit, never a write still in flight. While Minnie is mid-update, Mickey must keep seeing the old value. (Git won't show you someone's unsaved buffer either.)

  2. Keep it compact. A private, consistent view can't mean duplicating the whole database per transaction. Store only the diff between versions, the way git diff does, so a value you never touch costs nothing.

That is the goal. One mechanism reaches it: the snapshot.

The snapshot

Your snapshot is a single timestamp, taken the moment your transaction begins. From then on you read every value as it stood at that instant: one consistent version of the whole database, frozen at your start, no copy.

One test decides every read: is this version's writer committed at or before your snapshot? An uncommitted or aborted write carries no commit stamp, so you never see it, and there are no dirty reads. A version committed after your snapshot is too new, so a long-running transaction keeps its consistent view. When several versions qualify, you read the newest.

Made concrete on one value V: Minnie's new write stays invisible until she commits, so any read that lands while it is in flight still gets the old value.

A timeline of one value V: Minnie writes XY34 from 2:45 (uncommitted, dashed violet) and commits at 3:03. Pluto (snapshot 2:50) and Mickey (snapshot 3:00) fall inside the in-flight window and read AB1; a query at 3:05 reads XY34 after the commit.

Figure 2. Minnie updates V at 2:45 but does not commit XY34 until 3:03. In that in-flight window the committed value is still AB1, so Pluto (snapshot 2:50) and Mickey (snapshot 3:00) both read AB1, never the half-written XY34. Only a query whose snapshot falls after the commit, like 3:05, reads XY34. The write becomes visible at the commit, not when it starts.

And the snapshot stays nearly free: only a value you write gets a second version, so everything you don't touch is stored once and shared by every transaction.

Vacuum: reclaiming dead versions

Every write leaves the old value in place, so one row builds a chain of versions over time. Each is cheap, just a value and a timestamp, but they still accumulate. A version goes dead once no running transaction's snapshot is old enough to read it: no query can ever return it again. Vacuum is the garbage collector that reclaims the dead versions, so the chain never grows without bound.

One row keeps four versions, each a value plus a commit timestamp. Two queries are still running: one opened at 2:50 and still reads v3, a newer one reads the current v4. No running query reads v1 or v2, so they are dead and vacuum deletes them, leaving v3 and v4.

Figure 3. A version is one value plus a timestamp, not a copy of the row. MVCC must keep an old version while any running query still reads it: here the query that opened at 2:50 still reads v3, and newer queries read v4, but nothing reads v1 or v2. Vacuum deletes the versions no running query needs, so the chain does not grow forever.

MVCC is the default mechanism; its default level is not serializable. Every modern database runs MVCC, but its everyday isolation levels (read committed, snapshot isolation) trade full serializability for speed. The gap they allow is write skew: two transactions each read from their own snapshot, each commit a change that is safe alone, yet together leave a state no serial order could produce. Picture a playlist that must keep at least one song: two editors each confirm others remain, each delete a different one, and it ends empty. That is the one place MVCC breaks the conflict-serializability standard the rest of this module holds to. An app that cannot tolerate it sets ISOLATION LEVEL SERIALIZABLE, turning on serializable snapshot isolation (SSI), commit-time checks that abort a transaction about to break serializability. Most workloads accept the gap and stay on the faster default.

Tech details

  • Do two readers of the same row block? No. Any number of transactions read the same row at once, each from its own snapshot.

  • Do a reader and a writer block each other? No. The writer adds a new version; readers keep seeing the old one until they take a newer snapshot.

  • Do two writers block? Yes, the one lock MVCC keeps. Two writes to the same row serialize: the second waits on a row lock until the first commits or aborts, and it touches just that one row.

  • Timestamp or transaction id? Either. The rule needs one number that always increases, larger meaning later. Postgres uses a transaction id, Spanner a real timestamp, Oracle a system change number. Same mechanism, different name.

  • What does an UPDATE do under the hood? UPDATE Listens SET rating = 3.5 WHERE listen_id = 5 writes a new version stamped with your transaction and leaves the old one in place. Nobody sees it until you commit; if you abort, it never becomes visible.

  • What does all this versioning cost? Every UPDATE leaves a dead version behind (Postgres stamps each with hidden xmin/xmax columns: the transaction that created it and the one that replaced it). On a write-heavy table, dead versions can outnumber live rows within hours. VACUUM reclaims versions no active transaction can still see, the way git gc reclaims commits nothing points to.

  • What bites in production? VACUUM can only reclaim versions older than the xmin horizon, the oldest snapshot any running transaction still holds. One forgotten BEGIN left open for an hour pins that horizon; for that hour VACUUM frees nothing newer, even versions dead for 59 minutes. Tables bloat, indexes get sparser, every write degrades. The open transaction blocks cleanup, not the dead data.

The payoff: why OLTP and OLAP get separated at scale

So why not run the year-end analytics on the production Postgres?

It isn't lock contention. MVCC fixed that. A 10-minute analytical scan instead strains shared resources:

  1. CPU and IO saturate the bandwidth that user-facing 100ms queries need.

  2. Buffer cache pollution. The scan evicts hot OLTP pages from shared_buffers in favor of cold analytical pages.

  3. The xmin horizon. The 10-minute snapshot pins VACUUM for 10 minutes; tables bloat; writes degrade.

None of these is a lock problem. The fix is physical separation: replicate the data via change-data-capture (CDC) to a separate columnar warehouse (BigQuery, Snowflake, Redshift). The OLTP Postgres stays user-facing-only.

MVCC ends the reader-writer lock fight, but a 10-minute analytical scan still saturates the OLTP database's CPU/IO, pollutes its cache, and pins VACUUM; the fix is to replicate via CDC to a separate columnar OLAP warehouse and keep the OLTP database user-facing only.

Figure 4. MVCC removes the reader-writer lock contention, but a long analytical scan still strains shared resources on the production database: it saturates CPU and IO, pollutes the buffer cache, and pins VACUUM so tables bloat. None is a lock problem, so the fix is physical: replicate the data to a separate columnar warehouse for analytics and keep the OLTP database user-facing only.

Key Takeaways

  • MVCC is git for your rows. Each transaction reads from its own snapshot, your writes are invisible until you commit, and only changed rows get a new version.

  • A snapshot is a time plus one rule: for each value, read the newest version committed at or before it. Apply that rule to every value at once and you get one consistent version of the whole database, no copy required. The stamp is one monotonic number (a transaction id in Postgres, a timestamp elsewhere).

  • Visibility needs a committed writer, so in-flight writes are never read (no dirty reads) and aborts are a status flip, not a rewrite. Two writers to one row still serialize on a lock.

  • At scale, the real reason to separate OLTP from OLAP isn't lock contention. MVCC solved that. It's resource contention and the xmin horizon pinning VACUUM.