Heads up: posts on this site are drafted by Claude and fact-checked by Codex. Both can still get things wrong — read with care and verify anything load-bearing before relying on it.
why → how

Why Postgres uses MVCC

Readers shouldn't have to wait for writers, and writers shouldn't have to wait for readers — so Postgres keeps multiple versions of every row.

Data intermediate Apr 29, 2026 · updated Aug 25, 2026 · 11 min read
On this page

The picture version

Six pictures for a reader who has never run two things against a database at once. The prose below fills in the seams the pictures skip.

1 · The problem

A four-minute report, and a customer who orders halfway through it.

the revenue report, sweeping the orders table 9:00 9:04 9:01 — a customer places an order Two jobs want the same rows at the same moment. What should each of them see?
Follow that one order row for the whole post. The report started before it existed, and will still be running long after it lands — so the database has to decide what each of the two is entitled to see.

2 · The two obvious answers, both bad

Make somebody wait, or let the report contradict itself.

lock the rows the customer’s order waits for a four-minute report nobody will accept that just read whatever is there the report counts some rows from before and some from after a total that was never true Both go wrong for the same reason: there is only one copy of each row.
Blocking gives a correct answer at an unacceptable cost; reading freely is fast and gives a figure that matches no moment in time. The forced choice comes from assuming a row can only hold one value, which is the assumption worth attacking.

3 · The move

Stop editing rows. Every change writes a new version and dates it.

what an update used to mean status: pending overwritten the old value is gone what it means here status: pending  ·  valid from 9:01 status: shipped  ·  valid from 9:03 both are still on disk, each stamped with when it became true Nothing is ever edited in place. History accumulates instead.
An update writes a new version of the row and records when it took effect, leaving the previous one where it was. The row stops being a value and becomes a series of them, which is what makes the next scene possible.

4 · What that buys

Hand each transaction a timestamp on the way in, and the question answers itself.

the report, stamped 9:00 the version valid at 9:00 the 9:01 order — not visible to it so its total is the one that was true at 9:00 a query started at 9:02 the version valid at 9:00 and the 9:01 order too a later stamp, so it sees more Readers never wait for writers. Writers never wait for readers. Each is just reading the versions its own stamp entitles it to.
Every transaction is handed the moment it began, and sees exactly the versions that were current then. The long report gets a consistent picture without stopping anybody, because the newer version simply isn’t addressed to it.

5 · What it costs

Old versions pile up, and something has to sweep them.

four dead versions, one live one cleanup a version can only be removed once no running transaction could still be entitled to it so one very old transaction left open pins every version behind it Which is why a forgotten open transaction can bloat a database it never wrote to.
Superseded versions stay on disk until nothing running could still need them, and a cleanup process reclaims the rest. A single long-lived transaction holds the whole queue open — the most common way this design bites in practice.

6 · Keep this card

The whole thing on one index card.

the design = a row is a series of versions, never edited + each transaction is handed a moment ∴ it sees the versions valid then, and no others Nobody waits, and nobody sees a total that was never true. paid for in disk space and in a cleanup job that a long-open transaction can stall
Picture to keep: a wall of photographs of the same room, each stamped with when it was taken. Nobody edits a photo; changing the room adds a new one. When you walk in you’re handed the stamp of the most recent photo at that moment, and every question you ask gets answered from it while other people keep taking new ones.

Why it exists

Someone in finance kicks off the monthly revenue report at 9 a.m. It’s a big query; it’ll take four minutes to sweep the orders table. At 9:01, a customer places an order. Follow that one order row for the rest of this post — whether the report sees it, what happens to the row it replaced, and who has to wait for whom.

In the simplest possible database, somebody waits. The report holds read locks and the customer’s checkout hangs; or checkout takes a write lock and the report stalls behind it. Either way the system serializes around contention, and on a busy table that contention dominates everything else.

The frustration is that the report doesn’t actually need the latest value. It needs a consistent one: a view of the orders table that doesn’t shift under its feet halfway through, so it doesn’t count a customer twice or miss them entirely. And the customer’s transaction doesn’t need exclusive access to the universe — it just needs to know whether its change collides with someone else’s.

Multi-Version Concurrency Control breaks that bottleneck by refusing the premise that there is one row. Instead of a single value everyone fights over, the database keeps several versions of the row and shows each transaction the one that was current for it. Readers don’t block writers; writers don’t block readers. The report and the checkout both proceed, and neither is lying.

Why it matters now

You are almost certainly running on this. Postgres, InnoDB inside MySQL, Oracle, SQL Server in its snapshot and read-committed-snapshot modes, CockroachDB, and YugabyteDB all use some flavor of MVCC. Three symptoms you may have already debugged without naming the cause: SELECT in Postgres not appearing to take row locks, VACUUM being something you have to think about at all, and a table whose disk usage climbs while its row count stays flat because an analytics query has been open for six hours.

That last one is the tell. MVCC is what lets Postgres offer repeatable read without collapsing under load, and it’s the foundation the serializable level is built on — though serializable needs an extra layer on top, which the seams below get into. Either way, the old row versions those levels depend on are real bytes sitting in your table, not an abstraction.

The short answer

MVCC = rows-as-versions + visibility rules per transaction

Picture to keep: a wall of photographs of the same room, each stamped with when it was taken. Nobody edits a photo; changing the room adds a new one. When you walk in, you’re handed the stamp of the most recent photo at that moment, and every question you ask gets answered from it while other people keep taking new ones.

When you write a row in Postgres the old version isn’t overwritten. A new version is written, tagged with the transaction that created it. A reading transaction holds a snapshot — in effect, a record of which transactions had committed at a given instant — and Postgres walks the versions of each row and shows the one that snapshot should see.

So readers and writers don’t fight: they’re looking at different copies. The limit is worth stating right next to the picture, because it’s the thing people get wrong: two transactions writing the same row still collide. MVCC removes read-write contention, not write-write contention.

How it works

Build it from the failure. Follow the order row.

Naive attempt: overwrite the row in place, and let the report take a lock so nothing moves under it. Correct, and it’s what the report needs. Why it breaks: checkout is now blocked for four minutes. Correctness bought with availability, which is the trade the whole design exists to refuse.

Fix: never overwrite. Write a new version and leave the old one. Now the 9:01 write doesn’t destroy what the report is reading, so nobody has to hold a lock to keep their view stable. Why it breaks: if both versions are sitting in the table, every reader now sees the row twice. You’ve replaced blocking with ambiguity.

Fix: stamp every version with who made it, and give every transaction a rule for which stamps count. Each row in Postgres carries two hidden columns:

An UPDATE is mechanically an INSERT of a new version plus a stamp on the old one’s xmax. Both versions sit in the table at once. When a transaction reads, Postgres asks of each candidate version, roughly: was xmin’s transaction already committed as of my snapshot, and is there no committed xmax as of my snapshot? If yes, this is the version you see. That’s the visibility check, and it’s how the report gets a stable view without taking a single row lock.

A wrinkle worth knowing, because it surprises people: how often that snapshot is taken depends on the isolation level. At Postgres’s default READ COMMITTED, each statement gets a fresh snapshot — so a multi-statement transaction can see the 9:01 order in its second query having missed it in the first. At REPEATABLE READ and SERIALIZABLE, the snapshot is taken once for the whole transaction. The four-minute report only gets its consistent view for free if it’s a single statement, or if it asked for the stronger level.

Why it breaks: superseded versions pile up forever. The row the 9:01 order replaced becomes unreachable once no live snapshot could still need it, but nothing has removed it. Fix: collect the garbage in the background. VACUUM, run automatically by autovacuum, scans tables and marks dead versions’ space reusable. Note reusable, not returned to the operating system — plain VACUUM generally doesn’t shrink the file; that’s VACUUM FULL, which rewrites the table and takes an exclusive lock.

Why it still breaks: vacuum can only remove a version older than the oldest thing that might still need it. Postgres tracks this as a horizon, and anything that holds the horizon back — a six-hour analytics query, an idle open transaction, a prepared transaction, a lagging replication slot — keeps everything superseded since then alive. The table grows while its logical row count doesn’t. That’s table bloat, and in my experience long-lived transactions are the usual culprit, though they’re not the only way to hold a horizon.

Then there’s transaction ID wraparound. Transaction IDs are 32-bit, so the comparison “was this committed before me?” stops meaning anything once the counter laps. Old row versions have to be frozen — marked as visible to everyone, no comparison needed — before that happens. This is the failure that arrives at 3 a.m. on the table nobody vacuumed for months.

Show the seams

A few things the textbook version of MVCC tends to gloss over:

There is no crisp public date for when MVCC first entered Postgres specifically — the design traces back to its Berkeley POSTGRES roots in the 1980s, but which release the modern semantics solidified in isn’t cleanly recorded, so this post doesn’t pin a year.

You started with MVCC = rows-as-versions + visibility rules per transaction. What did this post add? — + someone has to delete the old versions. Versions and visibility rules explain how the 9 a.m. report and the 9:01 checkout coexist. They don’t explain VACUUM, bloat, freezing, or wraparound — and those are what you’ll actually spend time on. Keeping the old row is what makes MVCC work; being allowed to forget it is what makes MVCC operable, and that permission only arrives when the last snapshot that could need it goes away.

Check yourself

Before you go — an engineer opens a psql session, runs one SELECT, gets distracted, and leaves the transaction open (not committed) for two days. Nobody else is blocked. Is anything wrong?

Answer

Yes, and the absence of blocking is what makes it dangerous. That idle transaction holds a snapshot, and vacuum cannot reclaim any row version that snapshot might still need. So every update and delete across the whole database for two days accumulates as dead rows nobody is allowed to remove. Tables grow, indexes grow, sequential scans get slower, and — if it ran long enough on a busy system — freezing gets held back too, which is the road to a wraparound emergency. The lesson to carry: in MVCC, the expensive thing is not a transaction that holds locks, which is loud and obvious. It’s one that holds a snapshot, which is silent.

And one more — would switching the four-minute revenue report from READ COMMITTED to REPEATABLE READ make bloat better or worse?

Answer

Worse, and the reason is worth being able to derive. At READ COMMITTED each statement takes a fresh snapshot, so between statements there’s no snapshot pinning old versions. At REPEATABLE READ one snapshot is held for the whole four minutes, so nothing superseded during that window can be vacuumed until it finishes. You’d be buying a genuinely better report — no rows counted twice across statements — and paying for it in retained dead tuples. That’s the actual trade, and it’s a good one for a four-minute report and a terrible one for a six-hour job.

Going deeper