What is MVCC in PostgreSQL, and why don't readers block writers?

Think of a college notice board. When the exam timetable changes, the office does not scratch out the old sheet while students are reading it. They pin a new sheet next to it with a note saying "valid from today". A student who started reading the old sheet can finish it. Anyone who walks up later reads the new one. Once nobody is looking at the old sheet any more, a peon takes it down.

PostgreSQL works the same way. An UPDATE does not change a row in place. It writes a new copy of the row and marks the old copy as "replaced by transaction X". Each transaction reads from a snapshot, a picture of which transactions had committed when it started looking. It simply picks the row version that is visible in its snapshot. The cleanup person is VACUUM.

Two sessions work on the same bank account at the same time.

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- 2000 becomes 1500
-- not committed yet

-- Session B (runs while A is still open)
SELECT balance FROM accounts WHERE id = 1;
-- Returns 2000 immediately. B does not wait, and it does not see A's uncommitted change.

-- Session A
COMMIT;

-- Session B, new statement
SELECT balance FROM accounts WHERE id = 1;
-- Returns 1500

Session B never waited. It read the old version while A was writing the new one.

Every row version (PostgreSQL calls it a tuple) carries two hidden system columns:

• xmin: the ID of the transaction that created this version.
• xmax: the ID of the transaction that deleted or replaced it (empty if it is still current).

When a query reads a row, PostgreSQL checks xmin and xmax against the query's snapshot. A version is visible if its creator had committed before the snapshot and its deleter had not. An UPDATE is really "mark the old version's xmax, insert a new version". A DELETE only sets xmax.

Because nobody overwrites data in place, a reader never needs a lock on the row. Writers still need locks against other writers: if two transactions update the same row, the second one waits until the first commits or rolls back.

The cost is that old versions pile up as "dead tuples". VACUUM (usually run automatically by autovacuum) later marks that space as reusable.

• A long sales report on a Swiggy-style orders table can run for minutes without freezing order updates, because the report reads from its own snapshot.
• pg_dump takes a consistent backup of a live database for the same reason: it reads one snapshot while the application keeps writing.

• Saying "PostgreSQL has no locks". It does. Writers lock rows against other writers, and DDL such as ALTER TABLE takes heavy table locks.
• Thinking an UPDATE changes the row in place. It creates a new version, which is why update-heavy tables grow and need vacuuming.
• Leaving transactions open for hours ("idle in transaction"). VACUUM cannot remove row versions that an old snapshot might still need, so tables bloat.

• What does VACUUM do, and what happens if it never runs?
• When is the snapshot taken under Read Committed versus Repeatable Read?
• Why does a long-running transaction cause table bloat?