DBMS deep dive
MVCC (Multi-Version Concurrency Control): deep dive
MVCC keeps several versions of a row so that readers see a stable snapshot while writers keep going. The interview-level skill is tracing a version chain: knowing which version a given transaction sees, and when the old ones can be thrown away.
Row versions in PostgreSQL: xmin and xmax
Every PostgreSQL tuple carries two hidden system columns. xmin is the ID of the transaction that created the version, and xmax is the ID of the transaction that deleted or replaced it (0 if none). An UPDATE never overwrites a row in place. It sets xmax on the current version and inserts a new version whose xmin is the updater's ID. A DELETE only sets xmax.
Walkthrough: one row, three versions
-- Tx 100
INSERT INTO accounts (id, balance) VALUES (1, 100); -- v1: xmin=100, xmax=0
-- Tx 105
UPDATE accounts SET balance = 80 WHERE id = 1; -- v1.xmax=105; v2: xmin=105
-- Tx 110
UPDATE accounts SET balance = 50 WHERE id = 1; -- v2.xmax=110; v3: xmin=110
SELECT xmin, xmax, ctid, balance FROM accounts WHERE id = 1;
-- Only the version visible to your snapshot is returned| Version | balance | xmin | xmax | Visible to a snapshot that... |
|---|---|---|---|---|
| v1 | 100 | 100 | 105 | sees 100 committed but not 105 |
| v2 | 80 | 105 | 110 | sees 105 committed but not 110 |
| v3 | 50 | 110 | 0 | sees 110 committed |
A snapshot records the lowest transaction ID still running, the next ID to be assigned, and the list of IDs in progress. A version is visible if its xmin committed before the snapshot, and its xmax is empty, aborted or not yet visible to the snapshot. Suppose a long report started while Tx 105 was still running. It keeps seeing v1 (balance 100) for its whole life, even after 110 commits. That is how REPEATABLE READ gets a stable view without read locks.
Vacuum: cleaning up the chain
Once no running snapshot can see v1 or v2, they are dead tuples. Plain VACUUM (usually run by autovacuum) marks their space reusable and updates the visibility map and free space map. It does not shrink the file. VACUUM FULL rewrites the table compactly, but it takes an exclusive lock. Vacuum also freezes old tuples, marking them visible to everyone. That stops the 32-bit transaction counter from wrapping around and making old rows look like they are in the future.
One idle-in-transaction session holding an old snapshot blocks cleanup of every dead tuple newer than its snapshot, in every table of the database, not just the tables it touched. That is why bloat often traces back to one forgotten session or a stale replication slot.
PostgreSQL vs InnoDB: two ways to keep versions
| PostgreSQL | MySQL InnoDB / Oracle | |
|---|---|---|
| Where old versions live | In the table heap, next to the current version | In the undo log (rollback segments) |
| UPDATE | Writes a new full tuple; old tuple stays | Updates in place; the old values go to undo |
| Chain direction | Old version points to the newer one (ctid) | Current row points back to older versions (roll pointer) |
| Cleanup | VACUUM / autovacuum | Purge threads discard undo no longer needed |
| Main pain point | Table and index bloat | Long transactions grow the undo history; old reads get slower |
PostgreSQL softens its write cost with HOT (heap-only tuple) updates. If no indexed column changes and the page has free space, the new version stays on the same page and indexes are not touched. Leaving free space with a lower fillfactor makes HOT updates more likely on frequently updated tables.
What MVCC does not solve
MVCC removes read-write blocking, but writers still block writers. Two transactions updating the same row still serialize on a row lock: the second waits, then either proceeds (READ COMMITTED) or fails (PostgreSQL REPEATABLE READ). MVCC also does not stop write skew on its own.
Common mistakes
- Turning off autovacuum on busy tables to 'save I/O', then hitting bloat and an emergency anti-wraparound vacuum.
- Expecting
DELETEto return disk space immediately. It only creates dead tuples. - Updating every row of a large table in one transaction, which doubles its size until vacuum runs. Batch the update instead.
- Assuming
SELECT count(*)is O(1). Under MVCC each transaction may see a different count, so the engine has to scan.
Interview questions
›What do xmin and xmax mean in PostgreSQL?
xmin is the transaction ID that created a tuple version. xmax is the ID of the transaction that deleted or superseded it, or locked it with FOR UPDATE. Together with commit status from pg_xact, they decide whether a version is visible to a snapshot.
›Why can't PostgreSQL do an index-only scan on a freshly updated table?
Indexes do not store visibility, so the engine checks the visibility map. If the page is not marked all-visible, which vacuum sets, it must fetch the heap tuple to check xmin and xmax.
›What is transaction ID wraparound?
Transaction IDs are 32-bit and compared in a circular way. Without freezing, about 2 billion transactions later old rows would look like they are in the future and become invisible. Vacuum freezes old tuples to prevent this, and the database forces an aggressive vacuum, or eventually refuses writes, if freezing falls behind.
›How do MVCC readers in InnoDB see an old value?
Each row stores the ID of the last transaction that modified it and a roll pointer into the undo log. If that transaction is not visible to the reader's read view, InnoDB follows the pointer and applies undo records until it reaches a visible version.
›What is a HOT update?
A heap-only tuple update puts the new version on the same page and skips index updates, because no indexed column changed. Index entries keep pointing to the original tuple, which redirects to the newest version. This cuts index bloat and write I/O.