MVCC — Must Understand

What we're doing: Show a long-running reader and a concurrent writer both proceeding without blocking each other — the direct, observable consequence of MVCC.

-- Session A: opens a transaction and takes a snapshot, but does not commit yet
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- reads 1000

-- Session B, concurrently, updates and commits the SAME row --
-- this does NOT wait for Session A, and Session A's earlier SELECT did NOT block it:
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;

-- Session A, still in its original transaction:
SELECT balance FROM accounts WHERE id = 1;  -- still reads 1000 -- its own consistent snapshot

3

Session A's read acquires no lock that would block a writer -- this is MVCC's non-blocking read guarantee in action.

6–7

Session B's write completes immediately, entirely unblocked by Session A's still-open read transaction.

10

Session A's snapshot is unaffected by Session B's commit -- it is reading a different, still-valid row version.

-- Session B: UPDATE 1, COMMIT -- neither waited on Session A
-- Session A: still reads 1000

Why this works: A traditional locking model would have Session B either wait for Session A's read lock to release, or Session A's later read would be forced to see Session B's uncommitted or newly-committed change depending on the locking scheme — MVCC sidesteps both problems by simply keeping the old row version around long enough for Session A's snapshot to keep using it.

What we're doing: Query xmin/xmax directly before and after an UPDATE to observe the new row version being created, rather than the old one being modified in place.

SELECT xmin, xmax, balance FROM accounts WHERE id = 1;
-- xmin = 501, xmax = 0 (unset), balance = 1000

UPDATE accounts SET balance = 900 WHERE id = 1;

SELECT xmin, xmax, balance FROM accounts WHERE id = 1;
-- xmin = 502 (the UPDATE's own transaction id), xmax = 0, balance = 900
-- the row visible now is a DIFFERENT physical row version than before,
-- not the same one with its balance field overwritten

1–2

xmin = 501 identifies which transaction originally created this specific row version.

5–6

After the UPDATE, xmin has changed to 502 — this is observably a NEW row version, not the old one mutated.

 xmin | xmax | balance
------+------+---------
  501 |    0 |    1000

UPDATE 1

 xmin | xmax | balance
------+------+---------
  502 |    0 |     900

Why this works: The change in xmin from 501 to 502 is direct, observable proof that UPDATE created a physically new row version rather than modifying the original bytes in place — the row with xmin=501 still physically exists on disk (now with xmax=502 marking it superseded) until VACUUM eventually reclaims its space.

What we're doing: Contrast Read Committed (fresh snapshot per statement) with Repeatable Read (one snapshot for the whole transaction) using the same concurrent commit in between two reads.

-- Session A, Read Committed (default):
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- snapshot #1: reads 1000
-- (Session B commits an UPDATE setting balance to 900)
SELECT balance FROM accounts WHERE id = 1;  -- snapshot #2, taken fresh: reads 900
COMMIT;

-- Session A, Repeatable Read:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- the ONE snapshot for this whole transaction: reads 1000
-- (Session B commits the same UPDATE)
SELECT balance FROM accounts WHERE id = 1;  -- SAME snapshot reused: still reads 1000
COMMIT;

3

Read Committed: this statement gets its own, brand-new snapshot.

5

A different, later snapshot — taken fresh for this statement — legitimately sees the intervening commit.

10

Repeatable Read: this single snapshot is now fixed for the rest of the transaction.

12

Reusing the SAME snapshot object means this read cannot see the intervening commit, by construction — not by re-checking anything, simply because it consults the same fixed rule as before.

-- Read Committed: 1000, then 900
-- Repeatable Read: 1000, then 1000

Why this works: The difference between the two isolation levels is entirely explained by how many times a snapshot is taken, not by any different locking behavior — Read Committed's "freshness" and Repeatable Read's "stability" are both direct, mechanical consequences of snapshot scope.

What we're doing: Time a slow report query and a concurrent write together, confirming the write does not wait on the report.

-- Session A:
SELECT count(*), pg_sleep(5) FROM accounts;  -- simulates a slow report -- 5 seconds

-- Session B, started immediately after Session A, concurrently:
\timing
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- completes in milliseconds -- does NOT wait for Session A's 5-second query

2

Session A's read is deliberately slow, to make any blocking obvious if it happened.

6

Session B's write is not affected by Session A's still-running read at all -- it completes at normal speed.

UPDATE 1
Time: 3.417 ms   -- not ~5000ms, confirming no blocking occurred

Why this works: Session A's report query never needs Session B's row to stay unchanged — it is working from its own snapshot's view, entirely independent of whatever Session B does concurrently — so there is no reason for either to wait on the other, and the millisecond timing confirms it directly rather than assuming it.

What we're doing: Generate dead tuples with repeated UPDATEs and observe the dead tuple count grow via pg_stat_user_tables, then watch VACUUM reclaim them.

SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'accounts';
-- n_dead_tup = 0

UPDATE accounts SET balance = balance + 1 WHERE id = 1;  -- repeat several times
UPDATE accounts SET balance = balance + 1 WHERE id = 1;
UPDATE accounts SET balance = balance + 1 WHERE id = 1;

SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'accounts';
-- n_dead_tup = 3 -- each UPDATE left its previous version as a dead tuple

VACUUM accounts;
SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'accounts';
-- n_dead_tup = 0 -- VACUUM reclaimed them

4–6

Each UPDATE creates a new row version and leaves the previous one dead — three UPDATEs, three dead tuples.

10–12

VACUUM finds and reclaims exactly the dead tuples that accumulated — this is the mechanical link between UPDATE activity and the need to vacuum.

 n_dead_tup 
------------
          0

(3 updates)

 n_dead_tup 
------------
          3

VACUUM
 n_dead_tup 
------------
          0

Why this works: This is the direct, observable link between ordinary write activity and the need for regular vacuuming — every UPDATE (and DELETE) leaves behind a dead tuple once it is no longer needed by any open snapshot, and n_dead_tup is PostgreSQL's own running count of exactly how much cleanup work has accumulated.

What we're doing: Show that a standard VACUUM, after deleting many rows, reclaims space for reuse without shrinking the table's reported disk size — while VACUUM FULL does shrink it.

SELECT pg_size_pretty(pg_relation_size('accounts'));  -- e.g. 100 MB

DELETE FROM accounts WHERE created_at < now() - interval '1 year';  -- removes half the rows

VACUUM accounts;
SELECT pg_size_pretty(pg_relation_size('accounts'));  -- still ~100 MB --
                                                        -- space was freed for REUSE, not shrunk

VACUUM FULL accounts;
SELECT pg_size_pretty(pg_relation_size('accounts'));  -- now ~50 MB --
                                                        -- the table was actually rewritten smaller

5–6

Standard VACUUM ran and reclaimed the dead tuples, but the file size on disk did not shrink — the space is now free for future INSERTs to reuse instead.

10–11

Only VACUUM FULL actually rewrites the table into a smaller file and returns the difference to the operating system.

 pg_size_pretty 
----------------
 100 MB

(after VACUUM)
 100 MB

(after VACUUM FULL)
 50 MB

Why this works: This is exactly the trade-off standard VACUUM is designed around: it is cheap, non-blocking, and keeps the table healthy for ongoing writes by making dead space reusable, but a reader checking disk usage alone would wrongly conclude "nothing happened" — the real effect is on how much of that 100 MB is now free space available for the table's own future growth, not on the file size itself.