Transactions and MVCC

MVCC: row versions

PostgreSQL never changes a row in place. Every INSERT, UPDATE and DELETE leaves stamped row versions behind, and each transaction picks the versions it is allowed to see.

In plain words

Imagine a shared notebook where nobody ever erases anything. To change a line, you cross it out (writing your name next to the cross) and write the new line below it, again with your name. Someone who started reading before you made the change simply ignores your crossing-out and your new line. That is MVCC, multi-version concurrency control.

In PostgreSQL each row version (a tuple) carries two stamps: xmin, the transaction that created it, and xmax, the transaction that deleted or replaced it. INSERT sets xmin. DELETE sets xmax. UPDATE does both: it sets xmax on the old version and writes a new version with a new xmin.

Because old versions stay in place, a reader never waits for a writer and a writer never waits for a reader. The price is cleanup: versions that no transaction can see any more are dead tuples. They take space until VACUUM removes them. Too many of them is what people call bloat.

For DBAs and operators

What is in a tuple header

Every heap tuple starts with a 23-byte header: t_xmin, t_xmax, t_cid (command id inside the transaction), t_ctid (pointer to itself or to its newer version), and the t_infomask bits (hint bits, lock bits, "updated" flags). You can see the stamps of live rows with hidden system columns, and every version on a page with pageinspect:

SELECT ctid, xmin, xmax, * FROM accounts;

CREATE EXTENSION pageinspect;
SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask::bit(16)
  FROM heap_page_items(get_raw_page('accounts', 0));

After one UPDATE you will see two line pointers: the old one with t_xmax set and t_ctid pointing at the new one. A non-zero xmax on a visible row is normal: it can mean an aborted delete or just a row lock (SELECT … FOR UPDATE stores the locker in xmax too, see Locking).

Who decides visibility

Stamps alone mean nothing: a reader combines them with its snapshot (which transactions were still running when it looked) and the commit log (did that transaction commit or abort?). Read-only transactions do not even get a transaction id; they use a virtual xid, so pure SELECT traffic does not consume XIDs.

Consequences you meet in support

Bloat: an UPDATE-heavy table grows even if the row count is flat. n_dead_tup in pg_stat_user_tables shows dead versions waiting for vacuum.
Long transactions hold back cleanup: VACUUM can only remove a version when no snapshot anywhere could still see it. One session sitting idle in transaction for hours while holding a snapshot or an XID (or a replica with hot_standby_feedback, or a stale replication slot) keeps every dead tuple created since then. VACUUM VERBOSE reports them as "dead but not yet removable".
HOT updates: if no indexed column changes and the page has room, the new version stays on the same page and indexes are not touched (HOT updates).
XIDs are finite: 32-bit stamps must eventually be frozen (XID wraparound).

-- tables with most dead versions
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
  FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- who holds back the cleanup horizon?
SELECT pid, state, backend_xmin, age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_age, left(query, 60)
  FROM pg_stat_activity
 WHERE backend_xmin IS NOT NULL
 ORDER BY age(backend_xmin) DESC LIMIT 5;

A VACUUM log line such as tuples: 0 removed, 120000 remain, 118000 are dead but not yet removable together with removable cutoff: 1203344, which was 4502113 XIDs old when operation ended almost always points at a long-running transaction, an orphaned prepared transaction or a replication slot.

For developers: inside the source

Where it lives

htup_details.h: HeapTupleHeaderData and HeapTupleFields (t_xmin, t_xmax, and a union of t_cid/t_xvac), t_ctid, t_infomask2, t_infomask, t_hoff; the flag bits (HEAP_XMIN_COMMITTED, HEAP_XMAX_LOCK_ONLY, HEAP_XMAX_IS_MULTI, HEAP_COMBOCID, HEAP_HOT_UPDATED, HEAP_ONLY_TUPLE…); and accessors such as HeapTupleHeaderGetRawXmax() versus HeapTupleHeaderGetUpdateXid(), which resolves a MultiXact to the updating xid.
heapam.c: heap_insert() (stamps xmin/cmin in heap_prepare_insert()), heap_delete(), heap_update(), heap_lock_tuple().
heapam_handler.c wires these into the table AM API; combocid.c packs cmin and cmax into one field when a transaction deletes a row it inserted; varsup.c hands out xids in GetNewTransactionId(), called lazily from AssignTransactionId() on the first write.

What heap_update() does

heapam_tuple_update() → heap_update()
  HeapDetermineColumnsInfo()          which indexed columns changed?
  HeapTupleSatisfiesUpdate()          TM_Ok / TM_BeingModified / TM_Updated ...
    TM_BeingModified: XactLockTableWait() or MultiXactIdWait(), recheck
  compute_new_xmax_infomask()
  RelationGetBufferForTuple()         only if the new version does not fit
  CheckForSerializableConflictIn()
  START_CRIT_SECTION()
    HeapTupleSetHotUpdated() / HeapTupleSetHeapOnly()   if HOT
    RelationPutHeapTuple()            new version, new xmin
    old: t_xmax = xid, t_ctid = new TID
    MarkBufferDirty(); log_heap_update(); PageSetLSN()
  END_CRIT_SECTION()

The result codes (TM_Result in tableam.h) travel back to the executor, which decides between waiting, EvalPlanQual or a serialization error (see Isolation levels). The page is modified under an exclusive buffer content lock; waiting for another writer uses heavyweight locks on the tuple and on the other transaction's xid, described in README.tuplock. README.HOT covers HOT chains and heap_page_prune_opt(); the "Transaction and Subtransaction Numbering" section of access/transam/README explains lazy xid assignment.

Observe and experiment

pageinspect: heap_page_items() plus heap_tuple_infomask_flags(t_infomask, t_infomask2) decodes the flag bits by name.
pg_visibility shows the all-visible/all-frozen bits; amcheck's verify_heapam() checks xmin/xmax/ctid consistency.
Isolation specs in src/test/isolation/specs (for example eval-plan-qual.spec) script concurrent sessions step by step; see isolation/README. The injection point heap_update-before-pin lets a test pause inside heap_update().

Extension hooks

The table access method API (TableAmRoutine: tuple_update, tuple_delete, tuple_lock, tuple_satisfies_snapshot…) is where an extension can plug in a different row-versioning scheme; heap is just the built-in implementation.

Parameters that shape this

default_transaction_isolation vacuum_freeze_min_age idle_in_transaction_session_timeout hot_standby_feedback