MVCC Explained: Why Postgres Readers Never Block Writers

MVCC explained in one sentence: the database never overwrites a row, it writes a new version and lets each transaction read the version that was current when its snapshot was taken. That is why a long-running report in Postgres doesn’t stop an UPDATE on the same table, and why your UPDATE doesn’t stop the report. Reads and writes don’t block each other.

It also explains the thing that bites production teams a year later: table bloat. Same mechanism, other side of the coin.

Row versions you can see yourself

In Postgres, every UPDATE writes a brand new row version and stamps the old one as expired, instead of changing it in place. Each transaction then reads the versions that were current for its snapshot. You can see this directly through hidden columns. Open psql and run this (Postgres docs):

CREATE TABLE account (id int PRIMARY KEY, balance int);
INSERT INTO account VALUES (1, 100);

SELECT xmin, xmax, ctid, * FROM account;

You’ll see something like xmin = 748, xmax = 0, ctid = (0,1). Those first three are hidden system columns. xmin is the ID of the transaction that created this row version. xmax is 0 because nobody has deleted or replaced it. ctid is its physical location: page 0, slot 1.

Now update it and look again:

UPDATE account SET balance = 150 WHERE id = 1;
SELECT xmin, xmax, ctid, * FROM account;

The row now has a new xmin, and its ctid is (0,2). The UPDATE didn’t change the old tuple’s data. It wrote a second tuple at slot 2 and stamped the first one’s xmax with the updating transaction’s ID. The old version is still sitting on the page, invisible to new snapshots.

(If you want to look at the corpse, the pageinspect extension has heap_page_items(), which shows both tuples.)

How a transaction decides what it can see

When a transaction takes a snapshot, Postgres records which transaction IDs were still in progress at that moment. For each row version it then asks two questions:

1. Was the creating transaction (xmin) committed and visible to my snapshot?
2. Is the row still alive, meaning xmax is unset, or set by a transaction that I can’t see as committed?

If the answer to the first is yes and to the second is “still alive,” the row is visible. That’s the whole rule. Everything else is bookkeeping and hint bits.

Which snapshot you get depends on isolation level:

Isolation level	When the snapshot is taken	Practical effect
READ COMMITTED (default)	At the start of each statement	Two identical SELECTs in one transaction can see different data
REPEATABLE READ	At the first statement of the transaction	Same data throughout. Concurrent update of a row you changed can abort you with a serialization error
SERIALIZABLE	Like repeatable read, plus conflict tracking	Strongest guarantees, more retries

So when someone says “Postgres readers don’t block writers,” what they mean is that reads take a snapshot and look at old versions. They don’t take row locks, so there’s nothing for a writer to wait on.

What MVCC does not fix

MVCC separates readers from writers. It doesn’t separate writers from each other. Try this with two psql sessions:

-- session A
BEGIN;
UPDATE account SET balance = balance - 10 WHERE id = 1;

-- session B (blocks until A commits or rolls back)
UPDATE account SET balance = balance + 10 WHERE id = 1;

Session B waits on a row lock. Under READ COMMITTED it then re-reads the latest version and applies its change. Under REPEATABLE READ it fails with could not serialize access due to concurrent update, and your application has to retry the whole transaction. If your code assumes it never needs to retry, this is where it breaks. Our guide to idempotency keys and safe API retries covers the other half of that problem.

Deadlocks are untouched too. Two transactions locking rows in opposite orders will deadlock regardless of how many versions exist.

The bill: dead tuples and VACUUM

Every UPDATE leaves a dead version behind, and every DELETE leaves one too. Those versions take space and make scans read more pages than they need to. VACUUM reclaims them, marking the space reusable for new rows. Autovacuum runs it in the background based on how many rows have changed.

The rule VACUUM follows is conservative: it can only remove a dead version if no running transaction could still need it. Which brings us to the usual production incident.

Imagine someone opens a transaction in a notebook, runs one SELECT, and goes to lunch. Their snapshot is now the oldest one in the system. Every row version that died after it must be kept, because that transaction might still read it. Autovacuum runs, finds nothing it’s allowed to remove, and the table keeps growing. A few hours later queries slow down and nobody has deployed anything.

To find the culprit:

SELECT pid, state, xact_start, now() - xact_start AS age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

Look for old xact_start values and sessions in idle in transaction. Also check pg_replication_slots: an abandoned replication slot can pin an old xmin just as effectively. Setting idle_in_transaction_session_timeout is a cheap safety net.

There’s one more limit worth knowing. Transaction IDs are 32 bits, so Postgres has to periodically “freeze” old rows to avoid wraparound (the horizon is about 2 billion transactions). Vacuum does that too, which is one more reason you don’t want it blocked for days.

HOT updates, briefly

Not every update has to touch every index. If an UPDATE changes no indexed column and the new version fits on the same page, Postgres can do a heap-only tuple (HOT) update and skip creating new index entries. Leaving some free space on a page (fillfactor below 100) makes HOT updates more likely on frequently updated tables. It’s an optimization, not something to tune on day one. For how the index side works, see database indexes and B-trees.

What to do with this

• Keep transactions short. Open late, commit early, and don’t hold one across a network call or a human.
• Set idle_in_transaction_session_timeout to something sane (minutes, not hours).
• When a table bloats, look for the oldest snapshot before you reach for VACUUM FULL.
• Write retry logic for serialization failures if you use REPEATABLE READ or SERIALIZABLE.
• Remember that changing a schema under load interacts with all of this. Our zero-downtime migrations guide covers the lock side.

The one habit that prevents most MVCC pain: treat every open transaction as a snapshot that is costing you disk.