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:

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. This one is the odd member of the group, and the next section explains why.

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."

Where those hidden columns actually live

Calling them "hidden columns" invites a wrong mental picture: two separate filing cabinets, one where Postgres keeps its own bookkeeping and one where your data lives. It isn't like that at all, and the real layout quietly explains a lot.

There is one store, not two

A tuple is a single contiguous record. It sits inside one of the 8 KB pages we met in the previous post, and it's laid out front to back: a tuple header of roughly 23 bytes, then your own columns immediately behind it.

xmin and xmax aren't looked up in some side structure — they're the first few bytes of the very same record that holds id and balance. The header carries a little more than the two we care about (a couple of command IDs, some status flags, a bitmap marking which of your columns are NULL), but the shape is the point:

"Hidden system columns" are not a separate system. They're the front of every row.

This is also why the visibility check is so cheap. Deciding whether you're allowed to see a row needs no second lookup anywhere — the instant Postgres has the row in hand, it already has the certificates it needs to judge it. One record, one read.

ctid is an address, not a field

ctid is the exception, and the distinction matters: it isn't stored in the tuple at all.

ctid is (block number, slot number) — the tuple's location. A thing's location doesn't need to be written inside it, the same way a house's address isn't painted on its living-room wall. When Postgres hands you a ctid, it assembles the value from context: it knows which block it's reading and which slot it followed to get there.

To see where that slot number comes from, look inside a page. The first post sketched the slotted-page layout — a header, a slot array growing down, row data growing up. Here's that same page with the part that matters now:

That slot array is a line-pointer array: real, physically-stored entries of 4 bytes each, packed near the top of the page just after its 24-byte header. Each line pointer holds the byte offset and length of an actual tuple body further down.

So ctid = (0, 2) means: block 0, second entry in that block's line-pointer array — follow that entry to find the bytes. The slot number is an index into a table of pointers, not an offset into the page.

Why the extra hop is worth it

Pointing at a pointer looks like pure overhead. It buys something valuable: tuple bodies can move around inside a page without their addresses changing.

When the cleanup process later compacts a page — sliding surviving tuples together to consolidate the free space left by dead ones — it moves the bodies and rewrites the offsets inside the line pointers. Slot 2 still means slot 2. It just points somewhere new. Everything that referenced (0,2) stays correct without being touched.

Without that indirection, tidying up a single page would mean hunting down and rewriting every reference to every tuple in it.

ctid is not a row identity

One practical warning follows directly. A ctid is a physical, right-now address, and it changes whenever the tuple moves — which happens more than you'd think. An UPDATE writes the new version at a different location entirely (we're about to see exactly that), and a full table rewrite relocates everything.

So use your primary key when you need to identify a row across time. id is a logical identity that survives every version; ctid only tells you where one particular version happens to be sitting at this moment.

One last wrinkle, because it's a fair thing to trip over: the tuple header does contain a ctid-shaped field. But its job isn't to record this tuple's own address — it's a forward pointer to the newer version of the row, which is how Postgres walks old → new along a chain of versions. It's allowed inside the record precisely because it describes a different tuple. (When there's no newer version yet, it simply points back at this one — which is why the diagram above shows a live tuple pointing at its own slot.)

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.

That cache deserves more than a passing mention, though. It is where every read your database serves actually happens.

Where a read actually happens: the shared buffer cache

Notice what hasn't appeared yet in this post: a disk read. Not for the tuple, not for the visibility check, not for the SELECT. Every read PostgreSQL serves comes out of memory, and the shared buffer cache is the machinery that makes that true.

Postgres reads pages, not rows

Start with the unit of work. There is no "fetch me one row" operation anywhere in the storage layer. The smallest thing Postgres will move is an 8 KB page — the same page from the previous post. Asking for a single 60-byte tuple pulls in the entire 8 KB block that happens to contain it.

That sounds wasteful, and it isn't. Storage devices and the operating system beneath them are built to move blocks, not bytes — fetching 8 KB costs very nearly what fetching 60 bytes would. And the neighbours that came along for free are usually the next thing you want, including other versions of the row you just read.

One cache, shared by every connection

So where does that page land? In the shared buffer cache: one block of shared memory that Postgres carves out at startup and divides into slots exactly one page wide. A single slot is a buffer, and how many exist is set by shared_buffers — which ships deliberately small (128 MB) and is usually the first setting raised on a real server.

The word shared is doing literal work. Postgres runs one operating-system process per connection, and those processes do not each keep a private cache. The buffer pool lives in memory mapped into all of them at once — a hundred connections, one pool.

Beside it sits the buffer table: a hash table whose key is "which file, which block number" and whose value is "which slot is holding it." That is the index that turns "I need block 5 of accounts" into a memory address.

Two paths, and one of them is very fast

When a query needs block 5 of accounts, it hands the request to the buffer manager, and exactly one of two things happens.

Cache hit. The buffer table already has an entry for that block. The backend bumps the buffer's pin count — "I'm using this, don't take it away" — and reads the bytes where they sit. No system call, no I/O, nothing. This is the path a healthy database is on the overwhelming majority of the time.

Cache miss. No entry for that block, so it has to be read in — and it needs a slot to be read into. Here the fixed part of shared_buffers starts to matter: the number of slots is decided at startup and never changes. Postgres does not go ask the operating system for more memory when it wants to cache another page.

So "isn't there free memory somewhere?" has two answers, depending on how long the server has been up:

• Just after startup, yes. Slots nobody has used yet sit on a free list, and the buffer manager simply takes one. That state lasts about as long as it takes your queries to touch shared_buffers worth of data — often a few minutes.
• From then on, no. Every slot holds some page. "Find a slot" now means "take one away from the page currently sitting in it." That is eviction, and the page picked to be thrown out is called the victim.

Choosing the victim is the clock sweep. The buffer manager walks the pool in a circle, and each buffer it passes either has been used since the last time around — in which case its usage counter is decremented and it survives this pass — or has a counter already at zero, in which case it's the victim. It's a cheap approximation of "least recently useful" that costs nothing on the hit path.

One step remains before the slot can be reused. If the victim is clean, disk already holds an identical copy, so it can simply be overwritten and forgotten. If it's dirty — carrying changes not yet written out — that page must be written to disk first, and the incoming read waits on it. Only then does the 8 KB read happen, the buffer table gets its new entry, and the page is finally in memory.

That pin from the hit path is worth separating from the locks this post has been discussing, because the two aren't the same kind of thing at all. They guard different things and answer different questions:

A lock is concurrency control — it's about your data, and MVCC's whole achievement is that readers need almost none of them. A pin is memory management, and it excludes nobody: it's a counter, so ten backends can pin the same buffer at once, and the pin itself stops none of them from reading the tuples inside. All it tells the clock sweep is this slot is in use, don't hand it to another page right now. Pins are held for as long as a scan is looking at a page and dropped immediately after.

Think of a library. A lock decides who may write in the book. A pin only means the book is open on someone's desk, so the librarian mustn't reshelve it yet. (Stopping two people scribbling on the same page at the same moment is a third mechanism again — a very short-lived internal lock on the page's contents.)

Three consequences worth knowing:

• If every slot is pinned, the sweep gives up after a full lap and the query fails outright with no unpinned buffers available — it never waits for one. That takes a pathologically small pool: pins last moments, so a real server always has unpinned slots to spare.
• A giant sequential scan does not get to flush your cache. Postgres confines large scans to a small ring buffer — a few hundred KB that it recycles — so one nightly report can't evict the pages your live traffic depends on.
• Underneath the buffer cache sits the operating system's own page cache. Even a "miss" is often served from RAM rather than from an actual device.

Writes go through the same door

One loop worth closing, because it's tempting to picture an UPDATE as "a write to disk." It isn't — not at the moment you run it.

An UPDATE reaches its page through the buffer cache exactly like a read does. It stamps the old tuple's xmax, writes the new tuple into free space on that same page if it fits, and marks the buffer dirty. The page itself is written out later, on a background process's schedule. What makes your commit durable in the meantime isn't that page write at all — it's the WAL (write-ahead log), a compact sequential record of the change that is flushed before the transaction is told it succeeded. The heap page catches up in its own time.

So a hot row can be read and updated thousands of times while its page never leaves memory — each commit sends a compact WAL record to disk, not eight kilobytes of page.

One copy, many answers

Now the part that earns this section its place in an MVCC post.

Ten transactions reading the same row hold ten pins on the same buffer. Not ten copies — ten pointers into one set of bytes, and those bytes contain every version of that row that hasn't been cleaned up yet. Each of those transactions then applies its own snapshot to the identical bytes, and they can legitimately reach different conclusions about which version they're allowed to see.

The bytes are shared. The interpretation is per-transaction.

That is the whole trick, and it's why "readers never block writers" costs so little memory. There is no private copy to make and no per-transaction view to materialize — just one page in RAM holding all the versions, and a handful of numbers per transaction deciding which of them counts.

Snapshots: what a transaction actually carries

So the table 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":

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.

That last detail has a surprising consequence, now that we know where a tuple sits while it's being read: the hint bit is written onto the page in the shared buffer cache, which marks that page dirty and eventually sends it to disk. So a plain SELECT really can cause writes. It still creates no new version — the same tuple just got a note stapled to it.

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 {60, 70, 80, 90} have committed, and {100, 130, 150} 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 — crowding live rows out of the buffer cache on the way. 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)).
• Physically, a tuple is one contiguous record — a ~23-byte header holding xmin/xmax followed straight by your own columns, so the visibility check needs no second lookup. ctid is the exception: an address, not a stored field. Its slot number indexes the page's line-pointer array, and that indirection is what lets tuples shift within a page without breaking anything that points at them.
• 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.
• Nothing is read from disk row by row. Whole 8 KB pages are pulled into the shared buffer cache — one pool of memory that every connection shares — and all readers of a row work from that single cached copy. The bytes are shared; each transaction's snapshot decides what they mean.
• 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.