Multi-Version Concurrency Control

Two things that every engineer assumes about a database are in tension: a long analytical query should see a consistent picture of the data, and a transactional write should not have to wait for it. Under pure locking, one of them loses - either the reader blocks the writer or the reader sees a half-updated world.

Multi-version concurrency control resolves it by not overwriting anything. An update writes a new version of the row and marks the old one as superseded by a given transaction. A delete marks rather than removes. Every transaction carries a snapshot - in practice, the set of transactions that had committed when it started - and row visibility is computed per reader by comparing the versions' creating and superseding transaction ids against that snapshot.

Readers never block writers and writers never block readers. The cost does not disappear; it moves to the end of the row's life, where superseded versions have to be found and reclaimed.

Why it matters

Almost every mainstream database a working architect touches - Postgres, Oracle, MySQL's InnoDB, SQL Server's snapshot isolation, and the distributed stores built on their ideas - is an MVCC engine. Three operational facts follow directly from the mechanism:

• A table can be twenty times larger than its live data. Nothing is corrupt; the superseded versions have not yet been reclaimed.
• An UPDATE that changes one column rewrites the whole row and every index entry that points at it, so a wide table with ten indexes has far more write amplification than the statement suggests.
• A long-running read can stop cleanup for the entire database, because reclamation must respect the oldest snapshot that could still need an old version.

Teams that do not know this diagnose each symptom separately and never connect them.

Implementation patterns

• Reclamation as a background process - autovacuum in Postgres, the purge thread in InnoDB, the undo segment in Oracle - sized to the write rate rather than left at defaults on a busy table.
• Bound every transaction's lifetime. idle_in_transaction_session_timeout and statement_timeout are the controls that stop one forgotten session from pinning the horizon for days.
• Monitor the horizon directly: oldest transaction age, dead-tuple ratio per large table, and the holders - sessions idle in transaction, prepared transactions, and abandoned replication slots, which pin it identically and appear in no session list.
• Design updates to be narrow. Splitting a hot, frequently-updated counter column out of a wide row turns a full-row rewrite plus ten index updates into a small one.
• Leave fill-factor headroom on heavily updated tables, so an update that touches no indexed column can be written in place and avoid index writes entirely.

Industry example

Postgres is the clearest documented case because its mechanism is exposed rather than hidden: the system views report live and dead tuple counts per table, the xmin horizon is queryable, and the documentation is explicit that vacuum can only remove versions no running snapshot can see.

The consequence shows up repeatedly in public write-ups of scaling incidents, in the same shape each time: a table that is mostly dead tuples, a scan reading twenty times the pages it needs, and one long-lived transaction or forgotten replication slot behind it. The cost of MVCC is not paid at write time, so it is invisible in the metrics teams normally watch - it appears as gradually slowing scans weeks later.

Failure scenarios

• Bloat from a pinned horizon: a 40 GB table holding 2 GB of live rows, every sequential scan twenty times more expensive, autovacuum running constantly and correctly reclaiming nothing.
• Transaction-id exhaustion. If the horizon never advances, 32-bit ids cannot be retired and the engine eventually refuses writes to protect visibility - a hard stop, not a slowdown.
• Index bloat independent of table bloat, since index entries for dead versions are removed on a different schedule.
• Replica conflicts. A long query on a physical replica can be cancelled when the primary reclaims a version the query still needs, which looks like a random query failure on the replica.
• Write amplification nobody attributes to MVCC: a one-column update producing far more I/O than expected because the whole row and all its index entries were rewritten.

Trade-offs

The honest summary: MVCC trades a space-and-cleanup problem for a blocking problem, and the space-and-cleanup problem is the better one to have - as long as somebody is watching the cleanup.

When not to use it

The choice is rarely yours - the engine decides. Where it is a choice, the case against is narrow: a workload of very high-rate updates to a small, hot set of rows generates versions faster than cleanup retires them, and an engine that updates in place with short locks can be the better fit. The usual resolution is to move that specific hot counter out of the relational store altogether rather than to change engines.

The bigger practical "when not" is in your own schema design: do not use a relational MVCC table as a high-frequency mutable counter or a job-queue row updated thousands of times a second. That pattern fights the mechanism and loses, and the fix is a different structure - an append-only log that is aggregated, or a purpose-built queue - rather than better vacuum tuning.

Interview question

Q: A 50 GB table contains 3 GB of live rows and scans have been getting slower for a month. Autovacuum is running. Walk me through the mechanism, the thing you would check first, and what you would change so it cannot recur.

What a strong answer covers: updates and deletes create versions rather than overwriting; reclamation is bounded by the oldest snapshot, so the first check is oldest-transaction age and the holder behind it - including replication slots, not just sessions; plain vacuum returns space for reuse but does not shrink the file, so reclaiming 47 GB needs a rewrite or an online repack; prevention through statement and idle-transaction timeouts plus alerting on the horizon; and the escalation to transaction-id freezing if the horizon stays pinned.

Quick check

Quiz: Under MVCC, what single value determines whether a superseded row version can be reclaimed? The oldest snapshot still in use - no version visible to any running transaction can be removed, whatever the cleanup process's throughput.

Flashcard: Why does an update to one column of a wide, heavily indexed row cost so much more than it looks? Because MVCC writes a whole new row version and new entries in every index that points at it, rather than modifying the column in place.