MVCC (Multi-Version Concurrency Control)

MVCC stands for Multi-Version Concurrency Control. It’s the reason modern databases are fast — readers don’t block writers, and writers don’t block readers.

Without MVCC, if we’re reading a row while someone else is updating it, one of us has to wait. With MVCC, the database keeps multiple versions of each row, so both operations can happen at the same time.

Think of it like Google Docs version history. While someone is editing the document, we can still read the previous version. Nobody waits.

How MVCC Works (High Level)

Instead of modifying data in place, the database creates a new version of the row. Old versions stick around so that in-flight transactions can still see the data as it was when they started.

Each transaction gets a snapshot — a frozen-in-time view of the database. Changes made by other transactions after our snapshot was taken are invisible to us.

PostgreSQL’s MVCC Implementation

PostgreSQL stores multiple versions of each row directly in the table (called the heap). Each row version (tuple) has two hidden columns:

• xmin — the transaction ID that created this version
• xmax — the transaction ID that deleted/updated this version (0 if still alive)

When we UPDATE a row, PostgreSQL doesn’t modify the existing tuple. Instead, it:

1. Marks the old tuple as dead (sets its xmax)
2. Inserts a brand new tuple with the updated values (new xmin)

-- Let's say our transaction ID is 100
INSERT INTO users (name) VALUES ('Manish');
-- Creates tuple: xmin=100, xmax=0, name="Manish"

-- Transaction 105 updates the row
UPDATE users SET name = 'Manish P' WHERE name = 'Manish';
-- Old tuple: xmin=100, xmax=105 (marked dead)
-- New tuple: xmin=105, xmax=0, name="Manish P"

When a transaction reads data, it checks the xmin and xmax of each tuple against its own snapshot to decide which version to see.

MySQL/InnoDB’s MVCC Implementation

MySQL takes a different approach. Instead of keeping old versions in the table itself, it uses an undo log.

When a row is updated:

1. The current version is modified in place
2. The old version is saved in the undo log
3. A pointer from the row links to the previous version in the undo log

When a transaction needs to see an older version, it follows these pointers back through the undo log until it finds the version that was current at its snapshot time.

MySQL also uses read views — a snapshot of which transactions are active. This lets each transaction know which row versions are visible to it.

Snapshot Isolation

Snapshot isolation is the natural result of MVCC. Each transaction sees a consistent snapshot of the database as of the moment it started (or as of each statement, depending on the isolation level).

-- Read Committed: snapshot per STATEMENT
BEGIN;
SELECT count(*) FROM orders;  -- snapshot taken NOW, sees 100
-- Another transaction inserts 5 orders and commits
SELECT count(*) FROM orders;  -- NEW snapshot, sees 105
COMMIT;

-- Repeatable Read: snapshot per TRANSACTION
BEGIN;
SELECT count(*) FROM orders;  -- snapshot taken NOW, sees 100
-- Another transaction inserts 5 orders and commits
SELECT count(*) FROM orders;  -- SAME snapshot, still sees 100
COMMIT;

VACUUM in PostgreSQL

Remember those old tuple versions? They pile up. PostgreSQL needs a way to clean them up once no running transaction can see them anymore. That’s what VACUUM does.

VACUUM scans tables and marks dead tuples as reusable space. Without VACUUM, tables grow endlessly (called “table bloat”).

-- Manual vacuum (rarely needed — autovacuum handles this)
VACUUM users;

-- Vacuum with analysis (updates statistics too)
VACUUM ANALYZE users;

-- Full vacuum (rewrites the entire table, requires exclusive lock)
-- Only use this for severe bloat
VACUUM FULL users;

-- Check autovacuum status
SELECT relname, last_vacuum, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Autovacuum runs automatically in the background. In most cases, we should just let it do its thing. But long-running transactions can prevent VACUUM from cleaning up tuples (because those transactions might still need the old versions), which is another reason to keep transactions short.

MySQL’s Purge Thread

MySQL’s equivalent of VACUUM is the purge thread. It runs in the background and removes old versions from the undo log once no active transaction needs them. This is automatic and requires no manual intervention.

Why MVCC Matters

Before MVCC, databases used strict two-phase locking (2PL) — readers blocked writers and writers blocked readers. This was correct but slow.

MVCC changed the game:

• Reads never wait for writes — readers just see an older version
• Writes rarely wait for reads — writers create new versions without blocking readers
• Only write-write conflicts need locks — two transactions updating the same row still need coordination

This is why PostgreSQL and MySQL can handle thousands of concurrent connections efficiently. Without MVCC, most web applications would be painfully slow.

In simple language, MVCC keeps the database fast by maintaining multiple versions of data. Instead of making everyone wait in line, each transaction gets its own consistent snapshot. The trade-off is extra storage for old versions and the need for cleanup (VACUUM in PostgreSQL, purge thread in MySQL). But that’s a small price to pay for the massive concurrency win.