ConcurrencyAdvanced
How does MVCC work, and why does VACUUM exist?
“Explain multi-version concurrency control and its maintenance cost.”
What this tests
- Row versions
- Snapshots
- Bloat and VACUUM
Answers by level
Read the beginner answer first and notice what is missing.
An update does not overwrite a row; it writes a new version and marks the old one superseded (xmin/xmax). Each transaction takes a snapshot and sees only versions valid for it, so readers never block writers and writers never block readers — writers conflict only with writers on the same row.
Old versions become dead once no snapshot can see them; VACUUM reclaims their space and index entries. Without it the table bloats.
Green flags · Red flags
Strong green flag · Names idle-in-transaction as the classic VACUUM blocker.
Green flags
- Versions not overwrites
- Readers do not block writers
- Links bloat to long transactions
Red flags
- Thinks updates overwrite in place
- No idea why a table bloats
Follow-up questions
F1
A table is 40 GB with 2M live rows. What do you check first?
Scenario
After adding a long-running reporting connection, a hot table’s disk usage climbs steadily though its row count is flat. Why?