Understanding MVCC in PostgreSQL

I work with PostgreSQL almost every day. I write queries against it, I tune it, I fail it over, I restore it. And yet, for a long time, if you had asked me “what actually happens when you run an UPDATE?”, I would have said something vague like “it changes the row” and hoped you wouldn’t ask a second question.

One evening I decided to stop hand-waving. This is what I found.

The question that started it

Imagine two transactions running at the same time:

Transaction A                         Transaction B
─────────────                         ─────────────
BEGIN;
UPDATE accounts
  SET balance = 900
  WHERE id = 1;                       BEGIN;
                                      SELECT balance FROM accounts
                                        WHERE id = 1;   -- 1000 or 900?
COMMIT;

A has changed the row but hasn’t committed yet. What should B see?

The obvious answer is locking: A locks the row, B waits until A is done. It works. But if every reader had to wait for every writer, a busy database would spend most of its time waiting. I’ve seen enough lock graphs in production to know how that ends.

PostgreSQL answers it differently, and the idea is surprisingly simple:

Don’t overwrite the old row. Write a new version of it.

That’s MVCC: Multi-Version Concurrency Control. Several versions of the same row can exist at once, and each transaction sees the version that makes sense for it. B sees 1000, because A hasn’t committed. Nobody waits.

An UPDATE is really a DELETE plus an INSERT

This was the part that changed how I think about PostgreSQL. When I run:

UPDATE accounts SET balance = 900 WHERE id = 1;

PostgreSQL does not go to the row and change 1000 to 900. It does roughly this:

before:  (v1) id=1, balance=1000

after:   (v1) id=1, balance=1000   ← marked as "replaced", still on disk
         (v2) id=1, balance=900    ← the new version

So:

UPDATE = old version becomes dead + new version is written
DELETE = existing version becomes dead

The important bit: the old version does not disappear. It stays physically on the page, taking up space, until something comes along and cleans it up. Keep that in mind, because it’s the reason the next post exists.

How PostgreSQL decides who sees what

I wanted to see this for myself, so I looked at the hidden columns every row has. Two of them do most of the work:

column	meaning
`xmin`	ID of the transaction that created this version
`xmax`	ID of the transaction that deleted or replaced it (0 if it’s still alive)

You can select them like any other column:

SELECT xmin, xmax, * FROM accounts WHERE id = 1;

 xmin | xmax | id | balance
------+------+----+---------
  741 |    0 |  1 |    1000

Now run the UPDATE in one session without committing, and the same SELECT in another session:

 xmin | xmax | id | balance
------+------+----+---------
  741 |  742 |  1 |    1000     ← old version, xmax set by transaction 742

The second session still sees 1000, but now xmax says transaction 742 is replacing it. Commit, query again, and you get the new version with xmin = 742.

The rule behind this is the snapshot. When a transaction (or a statement) starts, it takes a snapshot that roughly says “these transactions had committed when I started, and these were still running”. A row version is visible to me if:

the transaction that created it (xmin) had committed in my snapshot, and
the transaction that deleted it (xmax) hadn’t, or there isn’t one.

That’s it. Readers never need a lock, because they just pick the version their snapshot says is right.

READ COMMITTED vs REPEATABLE READ, finally explained

This also cleared up something I had memorised for years without really understanding:

In READ COMMITTED (the default), every statement takes a new snapshot. Run the same SELECT twice in one transaction and you can get two different answers, if someone committed in between.
In REPEATABLE READ, the whole transaction keeps its first snapshot. Same query, same answer, for as long as the transaction is open.

Same mechanism, just a different moment for taking the snapshot. I wish someone had told me that earlier.

Why this matters when you run PostgreSQL

Once I understood MVCC, a few things I had seen in production suddenly made sense:

Long-running transactions are dangerous. An open transaction holds an old snapshot, so PostgreSQL can’t clean up any row version that snapshot might still need. A forgotten BEGIN in someone’s SQL client, left open over lunch, is enough to make tables grow.
Every update writes a whole new row. Even if you change one small column in a wide row, PostgreSQL writes a complete new version, and (unless it’s a HOT update) a new entry in every index on the table.
Tables can get much bigger than their data. All those old versions have to go somewhere.

Pretty cool, right? So why did Uber leave? :))

At this point I was honestly a bit in love with this design. Readers don’t block writers, writers don’t block readers, and every transaction gets a consistent view of the data. Elegant.

And then, while reading around, I came across a post from Uber’s engineering team explaining why they moved from PostgreSQL to MySQL.

Wait, what? If MVCC is this nice, why would anyone leave? :))

From what I’ve read so far, a big part of their reasoning comes back to the exact things above: every update writing a new row version and touching every index (write amplification), how that interacts with replication, and the cost of cleaning up old versions on very large, very busy tables. The things that make MVCC elegant also have a price, and at Uber’s scale the price got very visible.

I haven’t fully dug into it yet, and I also want to read the replies from the PostgreSQL community, because there are two sides to that story. That will be its own post. If you’ve read it, I’d be curious what you took away from it. It’s quite an interesting read.

What I still want to dig into

What a snapshot is, and how visibility is decided
HOT (heap-only tuple) updates: when can PostgreSQL avoid touching the indexes?
Transaction ID wraparound: why 32-bit transaction IDs force PostgreSQL to “freeze” old rows
Uber vs PostgreSQL: what was right, and what has changed since