Every UPDATE writes a new tuple version and every query sees a snapshot, which explains dead tuples, HOT updates and why long transactions hurt.
The problem
Readers never block writers in PostgreSQL, yet a table that is updated all day grows, a `SELECT` after a bulk load can write pages, and one forgotten transaction can make every table bloat. All of that follows from one design: rows are immutable versions, and each query decides which version it may see.
How it works
- Tuple header: every row version in the heap carries `xmin` (the transaction ID that created it), `xmax` (the ID that deleted, updated or row-locked it, 0 if none) and `ctid` (its own page and offset, or the location of the newer version after an UPDATE). `SELECT ctid, xmin, xmax, * FROM t` shows them.
- Snapshot: a statement (Read Committed) or a whole transaction (Repeatable Read, Serializable) takes a snapshot of which transaction IDs were still running. A version is visible if its `xmin` committed before the snapshot and its `xmax` is unset, aborted, or not yet visible to the snapshot. Readers need no row locks.
- UPDATE is insert plus mark: it writes a complete new tuple and stamps `xmax` on the old one; DELETE only stamps `xmax`. Old versions stay in the table until no snapshot can see them and vacuum removes them, so update-heavy tables need vacuum to keep their size stable.
- HOT updates: if no indexed column changes and the new version fits on the same page, PostgreSQL skips new index entries and chains the versions inside the page, which cheap page pruning can reclaim. A lower `fillfactor` (for example 80) leaves room for this; watch `n_tup_hot_upd` against `n_tup_upd` in `pg_stat_user_tables`.
- Hint bits: commit or abort status lives in `pg_xact`; the first reader to check a tuple caches the answer in a hint bit on the page. So the first `SELECT` after a bulk load can dirty many pages and generate I/O; running `VACUUM` after a large load does that work up front.
- Wraparound and freezing: transaction IDs are 32-bit and compared in a circle, so a very old row would eventually look like it is in the future. Vacuum freezes old tuples (marks them visible to everyone); autovacuum forces an aggressive pass at `autovacuum_freeze_max_age` (200 million by default) and PostgreSQL finally refuses new IDs to protect data, so watch `age(datfrozenxid)`.
- Long transactions hurt: an open transaction with a snapshot or an assigned ID pins the oldest-visible horizon (`backend_xmin` / `backend_xid` in `pg_stat_activity`). Vacuum cannot remove any dead version newer than that, in every table of that database, including tables the transaction never touched (other databases are unaffected, but replication slots and standby feedback hold back the whole cluster). Abandoned replication slots and prepared transactions pin it the same way.
- Row locks are tuple writes: `SELECT ... FOR UPDATE` records the locker in `xmax`, which dirties the page and can emit WAL, so locking many rows just to read them is not free.
Examples
Watch an UPDATE create a new tuple version and a row lock appear in xmax, on a scratch table and without extensions.
CREATE TABLE mvcc_demo (id int PRIMARY KEY, note text) WITH (fillfactor = 70);
INSERT INTO mvcc_demo VALUES (1, 'v1');
SELECT ctid, xmin, xmax, * FROM mvcc_demo;
UPDATE mvcc_demo SET note = 'v2' WHERE id = 1;
-- the visible row moved to a new ctid and has a new xmin
SELECT ctid, xmin, xmax, * FROM mvcc_demo;
BEGIN;
SELECT pg_current_xact_id() AS my_xid;
SELECT id FROM mvcc_demo WHERE id = 1 FOR UPDATE;
-- the row lock is now recorded in xmax
SELECT ctid, xmin, xmax, * FROM mvcc_demo;
ROLLBACK;Measure how many updates are HOT, then lower fillfactor for update-heavy tables.
-- HOT ratio: a low value on a hot table suggests indexed-column updates or a full page
SELECT relname, n_tup_upd, n_tup_hot_upd,
round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct,
n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 10;
-- reserve room on each page so new versions can stay on the same page
-- (only affects newly written pages; rewrite with VACUUM FULL or pg_repack to apply to old ones)
ALTER TABLE orders SET (fillfactor = 85);Find the sessions that pin the vacuum horizon and check how close tables are to a forced freeze.
-- who is holding the horizon back (oldest first)
SELECT pid, state, xact_start, now() - xact_start AS xact_age,
backend_xid, backend_xmin, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY age(coalesce(backend_xid, backend_xmin)) DESC NULLS LAST
LIMIT 10;
-- wraparound distance per database and the oldest tables
SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY 2 DESC;
SELECT c.oid::regclass AS rel, age(c.relfrozenxid) AS xid_age
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY 2 DESC
LIMIT 10;In practice
- pageinspect — A contrib extension (superuser) whose `heap_page_items()` shows raw tuple headers, hint bits and HOT chains; use it on a scratch table when the `ctid, xmin, xmax` view is not enough.
- InnoDB and Oracle — Keep old row versions in a separate undo log and rewrite the row in place; PostgreSQL keeps them in the heap itself, which makes rollback instant but puts the cleanup burden on vacuum.
- In practice — A frequent cause of unexplained bloat is a single `idle in transaction` session or a stale replication slot; check the horizon holders before tuning autovacuum.
Trade-offs
Pros
- Readers and writers do not block each other, so mixed read-write load scales well
- Rollback is free: an aborted transaction's versions are simply invisible
- Consistent snapshots come cheaply, which also gives snapshot-based backups and long reports
Cons
- Dead versions cause bloat and require vacuum and monitoring
- Updates are write-heavy: full new tuple plus index entries unless HOT applies
- 32-bit transaction IDs add freeze and wraparound as an operational concern