MVCC
Multi-Version Concurrency Control (MVCC) is how PostgreSQL, MySQL/InnoDB, Oracle, and SQL Server (with RCSI) handle concurrent reads and writes without locking readers. Each transaction sees a consistent snapshot of committed data as of its start time; writers create new versions of rows instead of overwriting in place. Readers never block writers and writers never block readers — a major concurrency win over the lock-based alternatives.
Foundational.
How it works
MVCC solves a fundamental tension in concurrent databases: readers want a stable view of the data, writers want to mutate it. With traditional locking, you have to choose — either readers block writers (read locks) or writers block readers (write locks), or both.
MVCC takes a different approach: each transaction sees a snapshot of the database as of its start time. When a transaction modifies a row, the database doesn't overwrite the existing version — it creates a new version and marks the old one as historical. Other transactions running concurrently continue to see the old version (matching their snapshot). Only transactions that start after the writer commits see the new version.
The result: readers never block writers, and writers never block readers. Both can proceed in parallel, each working with their own consistent view. This is why PostgreSQL can run a 30-second analytics query against a busy OLTP table without blocking writes — the analytics query sees its snapshot from start-of-transaction.
The cost: rows accumulate versions, and the database must periodically clean up old versions no longer visible to any active transaction. This cleanup is called VACUUM in PostgreSQL (autovacuum runs continuously) and purge in MySQL/InnoDB.
PostgreSQL's MVCC implementation is the most pedagogically useful to understand. Each row carries two hidden system columns:
- xmin — the transaction ID (xid) that created (inserted) this version of the row.
- xmax — the transaction ID that deleted or updated this version (NULL if it's the current version).
When a transaction modifies a row, PostgreSQL:
- Sets
xmaxon the old version to the current transaction's xid (marking it as deleted by this transaction). - Inserts a new row with
xmin= current xid,xmax= NULL.
So updates are really delete-then-insert under the hood. The old version stays on disk until VACUUM reclaims it.
When a transaction reads, PostgreSQL decides for each row version whether it's visible by checking:
- Was the creating transaction (xmin) committed before my snapshot started?
- Was the deleting transaction (xmax) committed before my snapshot started, or is it still running (in which case the row is still visible to me)?
The 'snapshot' is a list of transactions that were in-progress when the current transaction started. PostgreSQL uses this to compute visibility without taking any locks on the rows themselves. The snapshot also tracks a xmin boundary (the oldest active xid) and a xmax boundary (the next xid to be assigned), enabling fast visibility checks.
This is the magic: visibility is computed from transaction IDs and commit status, not from locks on rows. Readers don't block writers; writers don't block readers.
MVCC is the implementation; isolation levels are the API. The difference between Read Committed and Repeatable Read in PostgreSQL is entirely about when the snapshot is taken: Read Committed takes a new snapshot at the start of each statement; Repeatable Read takes a snapshot at the start of the transaction and uses it for all statements. Same MVCC machinery, different snapshot refresh policy. Serializable adds conflict detection on top of Repeatable Read's snapshot.
The cost of MVCC is version bloat. Every update creates a new version; the old version isn't reclaimed until no active transaction can see it. A table that's heavily updated will accumulate dead tuples (the old versions) until they're cleaned up. This bloats the table and its indexes, slows scans, and wastes memory.
PostgreSQL's cleanup mechanism is VACUUM:
- Identifies dead tuples (versions with xmax set, committed, and not visible to any active transaction).
- Marks their space as reusable in the table's free-space map.
- Optionally reclaims space back to the OS (
VACUUM FULL, which rewrites the table and locks it). - Also runs
ANALYZEto refresh planner statistics.
Autovacuum runs continuously in the background, parameterized by thresholds (e.g., 'VACUUM when 20% of rows have changed'). Most production databases run with autovacuum on; tuning its aggressiveness is one of the most common PostgreSQL operational tasks.
Why bloat matters:
- A table with 1M live rows and 9M dead tuples scans 10M rows for every Seq Scan.
- Indexes grow proportionally; index-only scans become impossible because the visibility map is stale.
- Cache hit ratio drops as the buffer cache fills with dead tuples.
This is the unique operational tax of MVCC: you must vacuum. Skip it and performance collapses; let it run too aggressively and it competes with your workload. The right balance depends on write rate and table size.
Why MVCC dominates:
- Readers don't block writers — long-running analytics queries don't stall OLTP writes.
- Writers don't block readers — concurrent updates don't freeze reads.
- Snapshot isolation for free — every transaction gets a consistent view without explicit locks.
- Locks are smaller and shorter — only write-write conflicts require row locks; reads take none.
- Non-blocking backups —
pg_dumpuses a snapshot and doesn't lock the database.
Costs:
- Storage bloat — old versions persist until vacuumed. Tables and indexes can grow 5-10× larger than their logical size.
- Vacuum overhead — background cleanup consumes CPU and I/O.
- Transaction ID wraparound — xids are 32-bit in PostgreSQL; if autovacuum can't keep up, the database can hit xid wraparound and refuse writes until vacuumed (a real production incident). Modern PostgreSQL mitigates this but the risk exists.
- Write amplification — every UPDATE writes a new version, updating every index on the table (HOT updates optimize this when possible).
- Long-running transactions are dangerous — they pin the vacuum horizon, preventing dead-tuple cleanup and causing cascading bloat.
The biggest production footgun is the long-running transaction: a psql session left open for an hour, a stuck background worker holding a transaction open. Until that transaction ends, vacuum cannot reclaim any dead tuple that became dead while it was active. The result: exponential bloat that can take hours to recover from. Monitoring for long-running transactions is essential in any PostgreSQL deployment.
In PostgreSQL, transaction A reads `balance = 100`. Transaction B updates the same row to `balance = 150` and commits. Transaction A reads the same row again. At Read Committed isolation, what does A see, and why doesn't B's write block A's read?
Pick one answer.
What happens in PostgreSQL when you run `UPDATE accounts SET balance = balance + 100 WHERE id = 1`?
Pick one answer.
Your PostgreSQL database is slowing down over weeks. Tables and indexes are much larger than their logical row count. What's likely happening, and what's the fix?
Pick one answer.
Why is a long-running transaction in PostgreSQL dangerous, even if it does nothing?
Pick one answer.
Engineering mental model
Mental model. Think of MVCC as a deliberate boundary in a system. The boundary exists because something becomes harder to manage when everything is done in one place: latency, scale, failure isolation, consistency, cost, or team ownership. The useful question is not “what does MVCC mean?” but “what pressure makes this boundary worth introducing, and what new failure mode does it create?”
Before choosing MVCC, name the workload, the critical user path, the dominant bottleneck, the failure you are trying to absorb, and the trade-off you are willing to accept. If you cannot name those five things, the design is probably premature.
// Pseudocode
request = receive()
result = mvcc(request)
return result
// Production questions:
// 1. What happens on timeout?
// 2. Can this operation be retried safely?
// 3. What is the bottleneck?Back-of-the-envelope reasoning
Numerical lens: write down traffic, payload size, read/write ratio, peak multiplier and durability target before choosing a component. The numbers should justify the architecture.
Interactive thought experiment: MVCC
Change the variables below and predict what breaks first in MVCC. The production lab can later reuse these same inputs.
Change one variable at a time. Predict the failure mode first, then move the slider and see whether your mental model matches the simplified system response.
If you are stuck on MVCC, start by drawing the request path and marking every network hop, stateful component, queue, cache and failure boundary. Then estimate where the system will saturate.
You increase traffic by 10× in a system using MVCC. What should you inspect first?
Pick one answer.
Which statement is the safest engineering habit when using MVCC?
Pick one answer.
You have dashboards for traffic, latency, errors and saturation. You can change the architecture, but every change has operational cost.
Production scenario: your system uses MVCC, traffic suddenly spikes, and p99 latency doubles. What is your first move?
Interview drill
Answer this without notes: When would you choose MVCC, and when would you intentionally avoid it? Mention at least one bottleneck it addresses, one failure mode it introduces, and one alternative. Then quantify the workload you are designing for.
For MVCC, start with access patterns rather than brand names. Identify the dominant reads/writes, data relationships, consistency requirements, partition key, hot keys and failure behavior before choosing a storage strategy.
Numerical sanity check
A rough capacity check: required write throughput ≈ peak writes/s × average record size. At 5,000 writes/s and 2 KB average payloads, the raw incoming data stream is about 10 MB/s before indexes, replication and overhead.
Do not optimize for a memorized definition. Reason from the workload and failure mode.
A team proposes MVCC because it 'scales'. What workload characteristic would make that choice a poor fit?
Pick one answer.
What you gain, what you pay
- +Readers never block writers, writers never block readers — high concurrency.
- +Snapshot isolation provides consistent reads without read locks.
- +Enables non-blocking backups (pg_dump uses a snapshot).
- +Foundation for Repeatable Read and Serializable isolation levels.
- +Long-running analytics queries don't stall OLTP writes.
- −Storage bloat — old row versions persist until vacuumed.
- −VACUUM overhead — background cleanup consumes CPU and I/O.
- −Write amplification — every UPDATE writes a new version and updates indexes.
- −Transaction ID wraparound risk if autovacuum falls behind.
- −Long-running transactions pin the vacuum horizon, causing cascading bloat.
How this breaks in production
- Bloat — dead tuples accumulate when autovacuum can't keep up.
- Transaction ID wraparound — rare but catastrophic; database refuses writes.
- Long-running transactions pinning vacuum horizon — cascading bloat.
- Snapshot too old errors — when the snapshot's xmin is older than retained undo (Oracle specifically).
- Hot standby replication lag — read replicas replay WAL but queries may need older snapshots than the replica has retained.
- Write amplification on heavily-indexed tables — each UPDATE touches every index.
Don't fall into these traps
- •Disabling autovacuum — works for a while, then explodes in bloat.
- •Leaving idle-in-transaction sessions open — pins the vacuum horizon.
- •Running VACUUM FULL in production — takes an exclusive table lock; use pg_repack instead.
- •Forgetting that UPDATE writes a new version — designing as if updates were free.
- •Not monitoring n_dead_tup — bloat is silent until it isn't.
- •Assuming MVCC means no locking at all — write-write conflicts still take row locks.
Real systems using this
How real systems implement this
- PostgreSQL — MVCC is foundational — every transaction takes a snapshot at start. Updates create new row versions (new tuples) with xmin/xmax transaction IDs. Autovacuum reclaims dead tuples continuously. Long-running transactions are the most common operational footgun because they pin the vacuum horizon.
- MySQL / InnoDB — Uses MVCC with undo logs to reconstruct older row versions. Each transaction sees rows visible according to its snapshot — Repeatable Read uses the transaction-start snapshot, Read Committed refreshes per statement. Purge threads clean up old undo log entries.
- Oracle — MVCC via undo segments — when a row is updated, the old version goes to an undo segment, and other transactions reconstruct older versions from undo. Long-running queries can fail with 'ORA-01555: snapshot too old' if the undo they need has been overwritten.
- CockroachDB — Distributed MVCC with HLC (Hybrid Logical Clocks) timestamps. Each transaction reads at a timestamp and sees a consistent snapshot across the cluster. Enables serializable transactions across datacenters, at the cost of write latency for coordination.
Practice saying it out loud
- Q1How does MVCC work, and why is it such a big deal for concurrency?
- Q2Explain what happens on disk when PostgreSQL executes an UPDATE.
- Q3Why does PostgreSQL need VACUUM? What happens if it falls behind?
- Q4What's the difference between Read Committed and Repeatable Read in PostgreSQL, given that both use MVCC?
- Q5Why is an idle-in-transaction session dangerous in PostgreSQL?
- Q6What is transaction ID wraparound, and how do you prevent it?
Further reading & references
Core explanations are original NO CAP material. External references are provided for deeper study and standards.
What next?
Mark as understood once the mental model clicks.
Next recommended
Transactions & ACID