PostgreSQL MVCC Explained (xmin, xmax, snapshots, visibility)

PostgreSQL's concurrency model is MVCC — multi-version concurrency control. It's why a long report doesn't block writes, why a write doesn't block readers, and also why tables bloat, why VACUUM exists, and why an idle-in-transaction session can quietly degrade a whole database. Once you can see the row versions, all of these become one idea.

The core idea: updates don't overwrite

In Postgres, a row on disk is a tuple, and tuples are never modified in place for normal updates. Instead:

INSERT writes a new tuple.
DELETE marks the existing tuple as deleted (it stays on disk).
UPDATE = mark the old tuple deleted and write a new tuple with the new values.

So at any moment, a table may contain several versions of the "same" row. Each transaction decides which version it can see.

xmin and xmax

Every tuple carries hidden system columns:

xmin — the ID of the transaction that created this version.
xmax — the ID of the transaction that deleted or replaced it (0 if none).
ctid — its physical location (page, offset).

You can look at them:

CREATE TABLE demo (id int PRIMARY KEY, val text);
INSERT INTO demo VALUES (1, 'a');

SELECT ctid, xmin, xmax, * FROM demo;
--  ctid  | xmin | xmax | id | val
-- (0,1)  | 1001 |    0 |  1 | a

UPDATE demo SET val = 'b' WHERE id = 1;

SELECT ctid, xmin, xmax, * FROM demo;
--  ctid  | xmin | xmax | id | val
-- (0,2)  | 1002 |    0 |  1 | b

The visible row moved from (0,1) to (0,2). The old tuple at (0,1) is still on the page, with xmax = 1002. (With the pageinspect extension you can see it directly.)

Snapshots and visibility

When a statement (in READ COMMITTED) or transaction (in REPEATABLE READ and SERIALIZABLE) starts, it takes a snapshot: which transaction IDs had committed, and which were still in progress.

A tuple is visible to you if, roughly:

its xmin transaction committed before your snapshot, and
its xmax is empty, or that deleting transaction hadn't committed as of your snapshot (or aborted).

That's how a reader sees a consistent picture while writers carry on. Session A starts a long SELECT; session B updates and commits; A keeps seeing the old version (still on disk) until it finishes. Nobody waits.

Commit status lives in the commit log (CLOG / pg_xact). To avoid checking it repeatedly, Postgres sets hint bits on tuples once their fate is known — which is why the first read after a big write can itself cause writes.

Isolation levels in MVCC terms

READ COMMITTED (default): new snapshot per statement. Two SELECTs in one transaction can see different data.
REPEATABLE READ: one snapshot for the whole transaction. Concurrent updates to the same row raise a serialization error instead of silently proceeding.
SERIALIZABLE: repeatable read plus tracking of read/write dependencies (SSI), aborting transactions that would produce non-serializable results.

Writers still conflict with writers: updating a row someone else has updated but not committed waits for them (row locks are recorded in the tuple's xmax). (Transactions explained, optimistic vs pessimistic locking)

The bill: dead tuples

Old versions that no running transaction can see anymore are dead tuples. They take space, slow scans, and bloat indexes (each version may have its own index entries).

VACUUM reclaims them: it finds tuples dead to every snapshot, marks their space reusable, cleans index entries, and updates the visibility map (enabling index-only scans) and free space map. Autovacuum does this automatically based on how many rows changed. (Postgres VACUUM and table bloat)

Watch dead tuples:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

Why long transactions hurt everyone

VACUUM can only remove a tuple once it's dead to the oldest snapshot still in use anywhere. One session sitting idle in transaction for six hours — a forgotten BEGIN in a console, a leaked connection in a pool, a long analytics query on the primary — pins the "horizon," and nothing deleted or updated since it started can be cleaned up, in any table. Bloat grows across the database.

Find them:

SELECT pid, state, xact_start, now() - xact_start AS age, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start LIMIT 10;

Defences: idle_in_transaction_session_timeout, statement timeouts, short transactions in app code, and moving long reports to a replica (where hot_standby_feedback has its own trade-off). Abandoned replication slots and old prepared transactions hold the horizon back the same way. (Read replicas)

HOT updates

If an update changes no indexed columns and there's free space on the same page, Postgres can do a heap-only tuple (HOT) update: the new version goes on the same page, linked from the old one, and no index entries are added. That's much cheaper and produces less bloat. Dead HOT chains can even be pruned during normal page access, without VACUUM.

Encourage HOT updates by not indexing frequently updated columns unnecessarily, and by lowering fillfactor (e.g. 90) on heavily updated tables to leave room on each page. Check n_tup_hot_upd vs n_tup_upd in pg_stat_user_tables.

Transaction ID wraparound

Transaction IDs are 32-bit and wrap around. To keep "older than" comparisons valid, VACUUM freezes old tuples, marking them visible to all. If freezing falls too far behind (long transactions, autovacuum unable to keep up), Postgres warns and eventually refuses new writes to protect data. Monitor age(datfrozenxid):

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Values approaching hundreds of millions deserve attention; autovacuum's anti-wraparound runs will kick in, and they can't be skipped.

Practical consequences

Updates cost about as much as inserts — design hot counters carefully (batch them, or keep them in a narrow table).
UPDATE on a whole big table doubles its size until vacuumed; batch large updates. (Postgres migrations on large tables)
count(*) can't use a stored row count — each transaction may see a different number.
Keep transactions short, and never hold one open across a user's think-time or a slow API call.

The summary

Updates write new tuple versions; deletes mark old ones; nothing is overwritten in place.
xmin/xmax plus your snapshot decide what you see — readers and writers don't block.
Dead tuples are MVCC's cost; VACUUM reclaims them.
The oldest open snapshot limits cleanup database-wide — hunt long transactions.
HOT updates and freezing are MVCC optimisations you can tune for.

EasySpawn servers come with PostgreSQL configured with sensible autovacuum and timeout defaults, daily backups, and a terminal where Claude Code can inspect pg_stat_* views with you. See how it works or join the waitlist.

Postgres MVCC Explained: Tuples, xmin/xmax, Snapshots and Why VACUUM Exists