A Straightforward Guide for MVCC in Postgres

Overview

In this article, I'll introduce the concept of Multi-Version Concurrency Control (MVCC) and explain how Postgres implements this protocol across different isolation levels. I'm assuming you already have a basic understanding of isolation levels, database locks, and concurrency in general. I won't cover those concepts here, so if you're not familiar with them, I highly recommend checking out A Straightforward Guide for Isolation Levels first before continuing.

The goal of this article is to help you understand:

• What Multi-Version Concurrency Control is
• How Postgres implements MVCC across different isolation levels

 

Multi-Version Concurrency Control

 

High-Level Concept

The idea behind MVCC is simple: it's a protocol designed to accomplish one goal — when two or more transactions run concurrently on the same data, the end result should look as if those transactions ran one after another, in sequence.

Take a look at the diagram above. Two transactions are running concurrently, and we want the end result to look as if either the first transaction ran and committed before the second one started, or vice versa. MVCC guarantees there are only two possible outcomes — never a third. But in reality, these transactions are running at the same time, so this is exactly the core idea of MVCC: it's a protocol that gives us this guarantee even though the transactions genuinely overlap in time.
Note that other protocols aim for the same goal, like Two-Phase Locking and Optimistic Concurrency Control. They all take different approaches, but they're all working toward the same thing.

The core idea of MVCC is that whenever a transaction updates a row, it doesn't mutate the value in place. Instead, it creates a new record as the latest version and links it back to the old version. After the update, the row has a new version, and the old version is never changed. This version chain exists per record — every update to a row creates a new version, and since each version is linked to the previous one, we call this a version chain.

When a transaction wants to read data, it walks through this version chain and reads whichever version is visible to it. We'll get into exactly how it determines visibility a bit later.

That's really all there is to it: the core idea of MVCC is that each record can have multiple versions, every update appends a new version to form a version chain, and every read walks that chain and picks the version that's visible to it.

 

Read Logic

In MVCC, there are two primary objects:

1. A transaction
2. A row version

Throughout this article, pretty much every example comes down to how these two objects interact with each other.

Before explaining the read and update logic, let's look at the metadata attached to each of these objects.

Every transaction carries two pieces of information:

• TrxID (transaction ID): an incrementing ID assigned to each transaction when it starts.
• MID_FLIGHT_TRANSACTION_LIST: a list of transaction IDs representing "which transactions were still running when this list was built." This list is built at the transaction's first statement (the first SELECT/INSERT/UPDATE/DELETE).

Similarly, every row version carries a TrxID. When a transaction creates a version, it stamps that version with its own transaction ID, so we always know which transaction created it.

All of these values are frozen at creation time — none of them get mutated during the transaction's lifetime.

Now let's look at how a transaction decides which version is visible to it when it reads a row. For each version, the transaction asks two questions:

1. Is this the version I created? If yes, return it immediately — a transaction can always read a version it created itself, even if that version hasn't been committed yet. We know this from the TrxID.
2. Is this version visible to me based on the transaction list? To answer this, we only need three things: (1) my own transaction ID, (2) the version's transaction ID, and (3) the transaction IDs in my MID_FLIGHT_TRANSACTION_LIST.
   • If the version's TrxID is in the list → the transaction that created it hadn't committed yet when my list was built, so it's still invisible to me.
   • If the version's TrxID is not in my list, but it's larger than my own TrxID → that version didn't even exist yet when my list was built, so it's invisible to me.
   • If the version's TrxID is not in my list, and it's smaller than my own TrxID → that version was already committed when my list was built, so it's visible to me.

Let's walk through a simple example.

In the diagram above, row ID 1 has two versions already created and committed by other transactions (the first by TrxID=2, the second by TrxID=3).

Let's focus on transaction B's lifecycle:

1. At T=25, transaction B reads row ID 1 for the first time. It builds the list MID_FLIGHT_TRANSACTION_LIST={4}, since transaction A is currently running. It then walks the version chain:
   • V2, TrxID=3: not created by me, not in the list, and my TrxID (5) is greater than it — I can read it.
2. At T=35, transaction B reads row ID 1 again. It walks the version chain once more (the list is only built once, then frozen and reused for the rest of the transaction):
   • V3, TrxID=4: created by transaction A, and it's in the list — skip it.
   • V2, TrxID=3: not created by me, not in the list, and my TrxID (5) is greater than it — I can read it.

Two things worth noting here:

• If transaction B had only read once, at T=35, its list would have been built at that point — by then transaction A had already committed, so the list would be empty, and V3 would be visible, since its TrxID (4) is smaller than transaction B's (5).
• The fact that the list is built once, at the start, and frozen for the rest of the transaction is exactly why MVCC eliminates the non-repeatable read anomaly.

 

Update Logic

Now that we understand how reads work, let's look at what happens when a transaction updates a record:

1. Acquire the row's lock (blocking if someone else already holds it).
2. Check whether the latest version is visible to me, or whether I created it myself.
   • If yes, create a new version and append it to the version chain, keeping the lock held until the transaction commits or aborts.
   • If no, abort the transaction.

Let's look at another example.

Focusing on transaction B's lifecycle:

• At T=15, transaction B starts, immediately reads row ID 1, and builds its list (it reads the V2 version, following the same logic as before).
• At T=20, transaction B tries to acquire the lock on row ID 1, but fails — someone else is holding it — so it blocks and waits.
• At T=30, transaction A commits and creates a new version, V3. Transaction B now successfully acquires the lock, checks that the latest version is V3, and evaluates its visibility:
  • V3, TrxID=5: not in my list, and larger than my own TrxID (4) — so transaction B has to be aborted.

This is where you can really see why MVCC is powerful: if we'd simply updated the record based on whatever the latest version happened to be at T=30, we'd have run straight into the lost update anomaly.

 

Hopefully you now have a solid understanding of how MVCC actually works.

One of the biggest benefits of MVCC is its efficiency: readers never block writers, and writers never block readers. A read doesn't need a lock — it just walks the chain and picks the version it's allowed to see. A writer isn't competing with a reader for anything, because it's appending a new version rather than touching the one the reader is looking at.

Before we move on to the different isolation levels, it's worth pointing out what MVCC already gives us by default: dirty reads, non-repeatable reads (once the list is frozen), and lost updates are all eliminated. But as we'll see in the Serializable section, MVCC alone isn't enough to guarantee full serializability — some anomalies still slip through, and Postgres needs an extra layer to close that gap.

 

Read Committed (Default)

Read Committed is the default isolation level in Postgres. The core idea is that a transaction always reads and updates the latest visible version. The overall logic for reads and updates is similar to the base MVCC behavior we just covered, but with a few subtle differences.

The read logic is basically identical to the MVCC process explained above. The only difference is when the list gets built. In our earlier MVCC example, the list was built once, at the transaction's first statement. Here, in Read Committed, a fresh list is built on every statement, and that new list is used to walk the version chain each time.

Let's walk through the same example as before, but this time under Read Committed.

Focusing on transaction B's lifecycle again:

1. At T=25, transaction B reads row ID 1 for the first time. It builds the list MID_FLIGHT_TRANSACTION_LIST={4}, since transaction A is currently running. It walks the version chain:
   • V2, TrxID=3: not created by me, not in the list, and my TrxID (5) is greater than it — I can read it.
2. At T=35, transaction B reads row ID 1 again. This time it builds a brand new list, MID_FLIGHT_TRANSACTION_LIST={}, since no transaction is currently running. It walks the version chain:
   • V3, TrxID=4: not created by me, not in the list, and my TrxID (5) is greater than it — I can read it.

This is exactly the non-repeatable read anomaly we mentioned earlier. Because the list is rebuilt on every statement, a single transaction can read different versions of the same row over time. If transaction A had updated the row to a different value, transaction B would see one value on its first read and a different value on its second — that's non-repeatable read in action.

 

Now let's look at the update logic. Recall that the base MVCC update process has two steps: (1) acquire the lock, (2) check version visibility. In Read Committed, when the latest version turns out not to be visible in step 2, the transaction doesn't abort right away — instead, it refetches the latest committed version and re-evaluates the SQL's WHERE clause against it.

Let's reuse the same example from the base MVCC update section.

• At T=30, once transaction B successfully acquires the lock, it refetches the latest version, V3. This time, it doesn't matter whether that version would normally be visible to transaction B — it just works directly on it, running the SQL statement against that latest version.

This is quite different from the base MVCC logic. In Read Committed, once a transaction acquires a lock after being blocked, it does not apply the usual visibility rule — instead, it works directly on the latest version. Postgres calls this mechanism EvalPlanQual.

EvalPlanQual is what prevents the lost update anomaly for a single update statement. Suppose the update statement at T=30 looks like this:

UPDATE posts SET like_count = like_count + 1 WHERE id = 1;

EvalPlanQual makes sure transaction B refetches the latest version and computes its update relative to that version — so transaction A's committed change is preserved instead of being silently overwritten.

However, if you have a typical application-level read-then-decide-then-write pattern spanning multiple statements, the lost update problem still occurs. Say transaction B runs these two statements:

-- statement 1
SELECT like_count FROM posts WHERE id = 1;  -- app reads 10

-- (app logic decides, based on 10, that the new value should be 11)

-- transaction A updates the value to 13

-- statement 2, some time later
UPDATE posts SET like_count = 11 WHERE id = 1;

In this case, transaction B will simply overwrite the value with 11, and transaction A's update disappears.

 

Repeatable Read

In Repeatable Read, Postgres's implementation is exactly the same as the base MVCC concept we covered earlier. For reads, the list is built once, at the first statement. For updates, once a transaction acquires the lock after being blocked, if the latest version isn't visible to it, the transaction is aborted immediately.

One thing worth calling out here: in the earlier article introducing isolation levels, we learned that the lost update anomaly can still occur at the Repeatable Read level. However, we just saw that this isn't an issue under the base MVCC logic. Since Postgres implements Repeatable Read using exactly that logic, this turns out to be a key difference between Postgres and MySQL: Postgres's Repeatable Read has no lost update problem, while MySQL's Repeatable Read does, because MySQL implements it differently.

 

Serializable

If Postgres's Repeatable Read already avoids the lost update anomaly, what's left for Serializable to solve?

Let's look at an example that shows what can still go wrong under plain MVCC.

Suppose we have this simple table:

+-----+-----------+
| id  | value     |
+-----+-----------+
|   1 | true      |
|   2 | true      |
+-----+-----------+

And two transactions running similar logic:

Trx1:                            Trx2:
  read rows[1].value             read rows[2].value
  if true:                       if true:
    set rows[2].value = false      set rows[1].value = false

A serializable result should end with exactly one row set to true:

Now let's trace through what actually happens under MVCC (here, transaction A runs Trx1's logic, and transaction B runs Trx2's logic):

1. At T=15, transaction A reads row ID 1. It builds an empty list and reads V1, getting true.
2. At T=20, transaction B reads row ID 2. It builds the list {2} and reads V1, getting true.
3. At T=25, transaction A acquires the lock successfully, confirms the latest version is visible to it, and updates and commits row 2 immediately. Row ID 2's latest version is now V2, with value false.
4. At T=30, transaction B acquires the lock successfully, confirms the latest version is visible to it, and updates and commits row 1 immediately. Row ID 1's latest version is now V2, with value false.

Look at the end result: both rows end up false, which isn't a valid serializable outcome. This is called the write skew anomaly.

The root cause is the very nature of MVCC — readers never block writers, and writers never block readers. At both T=25 and T=30, when a transaction wants to update a row, it doesn't care whether another transaction is still reading it; it only checks whether another transaction is currently updating it, and whether the version it's about to update is still visible.

 

To solve this problem, Postgres uses a protocol called Serializable Snapshot Isolation (SSI). SSI builds on top of MVCC but adds an extra layer of constraints.

For every read, a transaction acquires a special lock called a predicate lock. This lock doesn't block any exclusive (write) lock — it exists purely as a record, so Postgres knows which rows a transaction has read.

For every update, after all the usual steps pass (acquiring the lock, checking visibility), Postgres runs a few additional checks:

• It asks: "Is there any transaction that has read the version I'm about to update?"
• If yes, it records a dependency: the other transaction depends on me (other -> me).
• It then checks whether this creates a cycle in the dependency graph. If it does, that means there's a conflict, and one of the transactions gets aborted.

Let's walk through the same scenario again, this time with SSI in place.

1. At T=15, transaction A reads row ID 1 and acquires a predicate lock — now Postgres knows transaction A has read row ID 1.
2. At T=20, transaction B reads row ID 2 and acquires a predicate lock — now Postgres knows transaction B has read row ID 2.
3. At T=25, transaction A wants to update row ID 2. After all the usual checks pass, Postgres finds that transaction B has read this row, so it records a dependency (B -> A). No cycle exists yet, so the update succeeds.
4. At T=30, transaction B wants to update row ID 1. After all the usual checks pass, Postgres finds that transaction A has read this row, so it records a dependency (A -> B). This closes the loop — B -> A -> B — so Postgres detects a cycle and aborts transaction B.

So, three things worth taking away from this:

1. MVCC alone doesn't actually guarantee a serializable result.
2. Postgres achieves serializability using the SSI protocol, which builds on top of MVCC.
3. In its actual implementation, Postgres doesn't literally construct a dependency graph — it uses a few flags to detect conflicts instead. The underlying idea is the same, but the dependency graph model is easier to explain.

 

Read Uncommitted

At this point, you might be wondering why we haven't covered Read Uncommitted. That's because Postgres doesn't actually have this level. Since Postgres relies on MVCC for concurrency control, there's simply no scenario where a transaction would end up reading an uncommitted version — the version-visibility logic naturally prevents it. And since allowing that would offer no real benefit, Postgres never bothered implementing this level.

 

Storage Cost and VACUUM

By now, we have a solid understanding of what MVCC is and how it works in Postgres. Before we wrap up, there's one more thing worth touching on.

Since Postgres stores each version with the full set of column data, a row with 100 versions means 100x the storage for that single row. You can imagine how quickly disk space would fill up without some kind of cleanup mechanism. To handle this, Postgres runs a background process that periodically scans all versions and removes the ones no transaction will ever need to read again — this is VACUUM.

Even with this cleanup mechanism in place, naively appending a new version on every update still isn't a great design at scale. This is part of the reason Notion ended up sharding their Postgres database, and part of the reason Uber migrated from Postgres to MySQL.

I won't dig into those two cases here, since they're outside the scope of this article, but if you're interested, I highly recommend reading up on them for more detail.

 

Summary

MVCC is a protocol for handling concurrent transactions without letting readers and writers block each other. It's built around never mutating a row in place — instead, it appends new versions and uses a visibility rule to decide what each transaction can see. On its own, it eliminates dirty reads, non-repeatable reads, and lost updates — but not every anomaly that breaks serializability, as we saw in the Serializable section.

• Read Committed
  • Builds the list on every statement.
  • After acquiring the lock following a block, it refetches the latest version and works on that directly.
  • Non-repeatable read and lost update (in the read-then-decide-then-write pattern) can still occur at this level.
• Repeatable Read
  • Applies exactly the same logic as base MVCC.
  • Builds the list once, at the first statement, and freezes it for the rest of the transaction's lifetime.
  • After acquiring the lock following a block, if the latest version isn't visible to the transaction, it gets aborted.
  • Write skew can still occur (MVCC alone can't prevent it).
• Serializable
  • Postgres implements the SSI protocol, which builds on top of MVCC.
  • Each read acquires a predicate lock, which doesn't block any other transaction — it just records which rows were read.
  • Each update builds the dependency graph and checks for cycles.

 

Reference

• Postgres Official Document
• #19 - Multi-Version Concurrency Control (CMU Intro to Database Systems)

 
 

I'm not a database engineer—just a software engineer who loves learning and sharing what I've figured out. If you spot any mistakes or have suggestions, please let me know in the comments. I genuinely appreciate the feedback and want to make sure this is as accurate and helpful as possible.