How Does a Database Let Everyone Read and Write at Once?

In the previous post we dug into how a database physically stores your rows — pages, slotted pages, heap files. Near the end, one small detail slipped by: when you delete a row, it doesn't actually disappear from disk right away. It sits there, marked in a way that some transactions can still see it and others can't.

That wasn't a quirk. It's the visible edge of one of the most important ideas in modern databases: MVCC — Multi-Version Concurrency Control. It's the machinery that lets hundreds of transactions read and write the same table at the same time without stepping on each other, and it's the reason SELECT on a busy table doesn't grind to a halt just because someone else is mid-UPDATE.

In this post we'll build that picture from the ground up: the problem MVCC solves, the one rule that makes it work (UPDATE is secretly a delete plus an insert), the tiny "snapshot" each transaction carries, and the arithmetic that decides which version of a row you're allowed to see.

The problem: what happens when everyone touches the same row?

Imagine a balance row that a hundred transactions want to read while one transaction is busy changing it. How do you keep everyone's view consistent without chaos?

The obvious answer is locking: whoever touches a row locks the door, and everyone else waits their turn. It's safe — nobody ever sees a half-finished change — but it's slow. Readers wait for writers, writers wait for readers, and the database spends its life stuck in traffic jams.

MVCC makes a completely different bet: never let two people fight over the same copy. Instead of one contended row that everyone queues for, the database keeps multiple versions of the row alive at once, and hands each transaction the version that is correct for its point in time.

The headline result is worth memorizing:

Readers never block writers, and writers never block readers.

The one thing that isn't free: two writers trying to change the same row still have to take turns. But that's a much narrower bottleneck than "everybody waits for everybody."

The core idea: an append-only ledger

The mental model to carry through the rest of this post is a ledger book with one iron rule: we never erase anything immediately.

• To change a record, we don't overwrite it. We cross out the old line ("invalid as of transaction #200") and write a fresh copy below it ("valid as of transaction #200").
• Every visitor who walks in gets a numbered ticket and jots down a quick note: which earlier visitors had already finished and left at the moment they arrived.
• "But won't the ledger fill with crossed-out lines forever?" Eventually a cleanup step removes the lines nobody can possibly still need — but that's a story worth its own post.

Here's how the analogy maps onto real PostgreSQL terms — keep this table handy:

Analogy	Real PostgreSQL term
Ticket / badge number	Transaction ID (XID)
"valid from #N"	hidden column xmin
"invalid as of #M"	hidden column xmax
The note you jot on entry	visibility snapshot
The janitor	VACUUM

Every transaction, on its first write, grabs the next number from an ever-increasing counter: 100, 101, 102, and so on. That number is its XID.

What a row really is: tuples and hidden columns

A single version of a row is called a tuple. And every tuple secretly carries a few hidden system columns that you never created and never see in SELECT * — but they're physically stored on disk, and you can ask for them by name:

SELECT xmin, xmax, ctid, id, balance FROM accounts WHERE id = 1;

Three of them do the heavy lifting:

• xmin — the XID of the transaction that created this version. Think of it as the row's birth certificate: "born from transaction N."
• xmax — the XID of the transaction that killed this version, via an update or a delete. 0 means it's still alive. This is the death certificate.
• ctid — the physical location of the tuple as (page, slot) — literally where on disk it sits. (ctid also chains an old version forward to its replacement after an update, so Postgres can walk old → new to find the latest one.)

So xmin and xmax are the birth and death certificates of a row version. Almost everything else in this post is just the rule that decides which certificates "count yet."

The golden rule: UPDATE = DELETE + INSERT

Here's the single most important implementation fact, and the one that surprises people most:

Postgres never updates a row in place. An UPDATE is internally an invalidation of the old version plus an insert of a new version.

When you run UPDATE accounts SET balance = 90 WHERE id = 1, Postgres does not find the 100 on disk and overwrite it with 90. Instead it:

1. Stamps the old tuple's xmax with the current XID — "invalid as of #200."
2. Writes a brand-new tuple with the new balance, xmin = the current XID ("born at #200") and xmax = 0 ("still alive").

The old version physically stays on disk — it's just marked ended. Any reader who was mid-transaction never notices; their point-in-time view still points at the old version. And notice the two tuples have different ctids: they are two distinct physical rows, not one row edited twice.

DELETE is the same move, minus the insert. It only stamps xmax on the existing tuple and stops — no new row is written. The row is marked dead now, physically removed later. This is exactly the behavior we saw in the last post: a deleted row isn't erased right away, it's handed a death certificate — and the cleanup that eventually reclaims it is a topic for a later post.

Put the two side by side and the pattern is clear: an UPDATE stamps the old tuple and appends a new one; a DELETE just stamps and stops. Neither one ever touches the original bytes in place — which is exactly why a reader mid-transaction keeps seeing the row as it was.

One consequence worth internalizing: updating a single field copies the whole row. UPDATE users SET name = 'Bob' WHERE id = 1 copies id, email, bio, created_at — everything — into a fresh tuple, even though only name changed. That's why updating a row with a big text column is expensive, and why a one-byte change still leaves behind a full-size dead tuple. (There are mitigations — very large values live out-of-line in TOAST and can be shared; the HOT optimization skips extra index work when no indexed column changed — but the mental model holds: an update writes a whole new row.)

Reads create nothing

If writes pile up versions, what about reads? This part is critical:

A SELECT creates nothing. No copy, no version, no new row. Ever.

Versions are born only from INSERT, UPDATE, and DELETE — only when data actually changes. Ten transactions reading the same row produce zero new copies. All ten look at the same single physical tuple. It's a pointer situation — "everyone reads the same page of the same book" — not "everyone gets a photocopy."

There's one nuance. That tuple lives on disk in an 8 KB page. To read it, Postgres loads that page into RAM (the shared buffer cache) once, and every reader shares that one cached copy. So:

• Copies created by reading: zero.
• Times the page is loaded from disk into RAM: once, then shared by everyone.

The takeaway: the memory and storage cost of MVCC comes entirely from the write side (dead versions), never from the read side.

Snapshots: what a transaction actually carries

So the table on disk is a jumble of versions — some alive, some dead, some created by transactions that haven't even committed yet. When your query reads a row and finds several versions of it, how does it pick the right one?

It uses a snapshot: the note you jotted down the instant your transaction began. And here's the beautiful part — a snapshot copies nothing. It's essentially three numbers capturing "who had finished at the moment I started":

Piece	Meaning
xmin (of snapshot)	Oldest transaction still running when I started. Anything older is definitely finished.
xmax (of snapshot)	Next XID not yet handed out. Anything ≥ this hadn't started → can't be visible to me.
in-progress list	The exact XIDs that were running at my start moment.

A snapshot for a 5-row table and a 5-billion-row table is the same size — it doesn't scale with your data, because it's just three values. A billion transactions could fly by; your snapshot is still those three numbers, applied lazily to each row your query actually touches, at the moment it touches it.

To do the check, Postgres also consults the commit log (pg_xact) — a record of, for every XID, whether it committed, aborted, or is still in progress. And to avoid re-checking, it caches the answer right on the tuple as hint bits the first time someone looks.

The visibility rule

Now the payoff. A tuple version is visible to your snapshot when both of these hold:

1. Its xmin (creator) committed before your snapshot — or the creator is your own current transaction. AND
2. Its xmax (deleter) is 0, or belongs to a transaction that had not committed as of your snapshot (or aborted). In other words: it hasn't been killed by anyone who already finished.

Said plainly: a version is visible if "its creator has committed (or it's me)" and "it hasn't been deleted by anyone who's already committed" — all relative to your frozen snapshot.

Let's run it. Take a row id = 1 with three versions on disk. Your snapshot was taken at a moment when transaction 90 has committed and transaction 150 has started but not yet committed:

• v0 (born 80, died 90) — NOT visible. Its creator 80 committed, fine. But it was killed by 90, and 90 committed before your snapshot. Rule 2 fails → it's gone for you.
• v1 (born 90, died 150) — VISIBLE. ✅ Creator 90 committed (rule 1 ✓). Its killer is 150, which is still in-progress — so from your snapshot's point of view, that death hasn't happened yet (rule 2 ✓). Still alive → visible.
• v2 (born 150) — NOT visible. Created by 150, which hadn't committed when your snapshot was taken. Rule 1 fails → this version doesn't exist yet from your point of view.

Three physical versions, and exactly one passes the lens. That's not luck. A row's versions form a chain where each version's xmax equals the next version's xmin — the death of the old is the birth of the new, a single atomic event by a single transaction. Committed transactions cleanly slice up the timeline, your snapshot draws one line across it, and exactly one version straddles that line.

So the right way to think about it isn't "which version will my query grab?" It's: the snapshot is a fixed lens — three numbers — carried across the whole query, and for each row independently, exactly one version (or none) passes through it. The same three numbers apply to every row you touch, whether you scan one row or a million.

The same lens, applied to a whole table

That single-row walk-through can make it look like the snapshot works one row at a time. It doesn't — it's one set of three numbers applied to every version your query touches. So let's scale it up. Here's a whole heap on disk: five different ids, seven physical tuples all jumbled together — some alive, some dead, some not yet born. We run a plain SELECT id, value FROM accounts with no WHERE at all.

Same style of snapshot as before: transactions have committed, and are still running (nothing numbered 151 or higher has started yet).

Apply those exact same three numbers to every version, one row at a time:

• id = 1 has three versions. (0,1) was killed by 90, which committed → dead. (0,2) was born by 90 (committed) and killed by 150 (still running, so that death doesn't count yet) → visible: "A-mid." (0,3) was born by 150, still running → doesn't exist yet.
• id = 2 — (0,4) born by 70 (committed), never deleted → visible: "B."
• id = 3 — (0,5) born by 100, still running → invisible, so this row doesn't appear at all.
• id = 4 — (0,6) born by 60 (committed), killed by 130 (still running — that death doesn't count) → visible: "D."
• id = 5 — (0,7) born by 150, still running → invisible, so this row vanishes too.

Seven physical tuples collapse into a clean, consistent three-row answer. Two of the five ids disappear completely — simply because their only version isn't visible to this snapshot yet. That's the whole payoff: one tiny snapshot, applied uniformly, hands every transaction a coherent point-in-time view of the entire table — no locks, no coordination, no matter how much churn is happening around it.

The one catch

MVCC's gift — no locks between readers and writers — isn't free. Every update leaves a dead version behind, and on a heavily-updated table those pile up and quietly grow the table's file on disk. Reclaiming that space so it can be reused is a whole topic on its own, which we'll pick up in a later post.

Bringing it all together

Here's the whole picture, step by step:

• The naive way to stay consistent under concurrency is locking, which makes readers and writers wait on each other. MVCC avoids that by keeping multiple versions of every row.
• A single version is a tuple, carrying hidden columns: xmin (born from which transaction), xmax (killed by which transaction, 0 if alive), and ctid (its physical (page, slot)).
• Postgres never updates in place: an UPDATE stamps the old tuple's xmax and appends a brand-new tuple. A DELETE is the same, minus the insert. Reads create nothing at all.
• Each transaction carries a tiny snapshot — three numbers describing who had finished when it started — and applies the visibility rule to decide which single version of each row it's allowed to see.
• All those extra versions come at a cost — dead versions accumulate — and reclaiming that space is a story for a later post.

The result is the promise we started with: on a busy table, your SELECT and someone else's UPDATE sail past each other, each looking at exactly the version that's true for its own moment in time — no locks, no waiting, no traffic jam.