Transactions and MVCC

MVCC — how PostgreSQL avoids read locks#

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to let readers and writers operate on the same rows simultaneously without blocking each other. The core idea: instead of updating a row in place and making concurrent readers wait for a lock, PostgreSQL keeps multiple versions of a row and gives every transaction a consistent snapshot of the data as of a specific point in time.

When a transaction updates a row, PostgreSQL doesn't overwrite it — it inserts a new version of the row and marks the old version as no longer current (but doesn't delete it immediately). Every transaction, when it starts, effectively sees "the version of every row that was current as of my snapshot," regardless of what other transactions are doing concurrently:

Row for product_id=1, over time:

version 1 (stock=150)  created by TX 100, ended by TX 105
version 2 (stock=148)  created by TX 105, still current

TX 101 started before TX 105 committed -> still sees version 1 (stock=150)
TX 106 started after  TX 105 committed -> sees version 2 (stock=148)

The practical payoff: a long-running SELECT never blocks a concurrent UPDATE, and a concurrent UPDATE never blocks a SELECT — readers and writers simply don't contend for the same lock, because a reader is looking at a snapshot, not the live, currently-being-modified row. This is a meaningfully different model from a database relying on read locks (shared locks) to guarantee a reader sees a fully committed value.

Isolation levels in PostgreSQL#

PostgreSQL supports the standard SQL isolation levels, implemented via MVCC snapshots rather than traditional locking for the read side:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT stock FROM products WHERE id = 1;   -- sees a snapshot, say stock=150

-- (meanwhile, another transaction updates and commits stock=148)

SELECT stock FROM products WHERE id = 1;   -- still returns 150 — same snapshot
COMMIT;

Notice PostgreSQL's default (READ COMMITTED) is not the same behavior as MySQL's default (REPEATABLE READ) — the MySQL page in this track covers that difference from the other side. A team moving an application between the two databases without setting isolation levels explicitly can hit genuinely different concurrent-read behavior by surprise.

VACUUM — cleaning up dead tuples#

Because MVCC never overwrites a row in place, every UPDATE and DELETE leaves the old row version behind as a dead tuple — it's no longer visible to any new transaction, but it still physically occupies space on disk until something removes it. Left unchecked, a table with heavy update/delete traffic accumulates dead tuples faster than they're reclaimed, bloating table size and slowing down scans that must skip past them.

VACUUM is PostgreSQL's process for reclaiming that space:

VACUUM products;          -- reclaim dead tuple space for reuse (doesn't shrink the file on disk)
VACUUM ANALYZE products;  -- also refresh the planner's statistics for that table
VACUUM FULL products;     -- rewrites the table to reclaim disk space physically, but takes an exclusive lock

In normal operation, PostgreSQL runs autovacuum automatically in the background on tables that accumulate enough dead tuples, so manual VACUUM is rarely needed day to day. It becomes a hands-on concern on tables with very high update/delete churn, where autovacuum's default thresholds may need tuning, or after a very large bulk delete where an immediate manual VACUUM ANALYZE gets statistics back up to date sooner than waiting for the next scheduled autovacuum pass.

Common mistakes#

• Assuming "PostgreSQL doesn't lock for reads" means it never locks at all — writers still lock against other writers on the same row; MVCC specifically removes the reader-vs-writer contention, not all locking.
• Disabling or drastically de-tuning autovacuum "because it seemed to be using resources," then discovering months later that a heavily-updated table has bloated to several times its logical data size.
• Assuming PostgreSQL's REPEATABLE READ behaves exactly like MySQL's REPEATABLE READ — PostgreSQL's version raises a serialization error on a conflicting concurrent update rather than silently succeeding, requiring the application to retry the transaction.
• Running VACUUM FULL on a large, live production table during peak hours without realizing it takes an exclusive lock for its duration, effectively blocking all access to that table until it finishes.