Multi-Version Concurrency Control

How keeping several versions of each row lets readers and writers work at once: snapshots, visibility, PostgreSQL versus InnoDB storage, and the cleanup problems that follow.

Overview

Multi-version concurrency control (MVCC) lets a database keep more than one version of a row at a time. When a transaction updates a row, it writes a new version instead of overwriting the old one. A transaction that started earlier keeps reading the old version; later transactions see the new one. Each transaction reads from a snapshot: the set of changes that were committed when the snapshot was taken.

The payoff is that reading and writing stop getting in each other's way. PostgreSQL's documentation puts it as: reading never blocks writing, and writing never blocks reading. A long report can run while orders keep arriving, and neither waits for the other. Writers still wait for other writers that change the same row.

The cost is the old versions. They take space, they make some reads do extra work, and something must remove them once no transaction can see them. Where the old versions live, and how they are cleaned up, is the big difference between PostgreSQL and MySQL's InnoDB, and it explains most MVCC problems you meet in production: table bloat, a growing undo log, long transactions that block cleanup, and transaction ID wraparound.

What you will be able to do

  • Explain what a snapshot is and decide whether a given row version is visible to it.
  • Describe how PostgreSQL stores versions (xmin, xmax, ctid) and how InnoDB stores them (undo log, roll pointer).
  • Explain why long transactions cause bloat, and how VACUUM and InnoDB purge clean up.
  • Explain how MVCC interacts with indexes: HOT updates, the visibility map and index-only scans.
  • Say what MVCC does not solve on its own: write conflicts and write skew.

Key concepts

  • Row versions
  • Snapshot
  • Visibility rule
  • xmin and xmax
  • Undo log and roll pointer
  • VACUUM and purge
  • Bloat
  • HOT updates
  • Visibility map
  • Transaction ID wraparound

Interview questions

  • What is MVCC, and why do databases use it?
  • How does PostgreSQL decide whether a row is visible to a transaction?
  • How does InnoDB's MVCC differ from PostgreSQL's?
  • Why does a long-running transaction cause bloat?
  • What is a HOT update?
  • What is transaction ID wraparound, and how is it prevented?
  • Does MVCC prevent lost updates?
  • An index-only scan in PostgreSQL still reads the table. Why?

Sign up free to read the full topic

More System Design topics

Start free on Cariara · Coding problems · System design studio