MVCC Internals: How PostgreSQL Stores Row Versions

One thing first

An UPDATE in PostgreSQL is not an edit; it is an insert plus a tombstone. Once that clicks, bloat, vacuum, long-transaction hazards, and isolation levels all stop being surprising. The whole mechanism lives in the on-disk tuple header, understand that struct and you understand MVCC.

Heap tuple header

Every row stored in a heap page is prefixed by a HeapTupleHeaderData struct, defined in src/include/access/htup_details.h. The fields that drive visibility are:

• t_xmin, the transaction ID (XID) that inserted this version.
• t_xmax, the deleting, updating, or locking XID/MultiXactId. t_infomask distinguishes a retiring change from lock-only state; non-zero does not by itself mean dead.
• t_cid, command ID (cmin, cmax, or a combo CID), ordering changes made by different commands inside the same transaction.
• t_ctid, a (block, offset) pointer to the next version of this row (used for UPDATE chains).
• t_infomask / t_infomask2, bit flags caching commit status and other metadata.

You can see these directly with the pageinspect extension:

CREATE EXTENSION IF NOT EXISTS pageinspect;

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

lp  | t_xmin | t_xmax | t_ctid  |    t_infomask    
-----+--------+--------+---------+------------------
   1 |   1140 |      0 | (0,1)   | 0000100000000010
   2 |   1140 |      0 | (0,2)   | 0000100000000010
   3 |   1140 |      0 | (0,3)   | 0000100000000010
   4 |   1140 |      0 | (0,4)   | 0000100000000010
   5 |   1140 |      0 | (0,5)   | 0000100000000010
   6 |   1140 |      0 | (0,6)   | 0000100000000010
   7 |   1140 |      0 | (0,7)   | 0000100000000010
   8 |   1140 |      0 | (0,8)   | 0000100000000010
   9 |   1140 |      0 | (0,9)   | 0000100000000010
  10 |   1140 |      0 | (0,10)  | 0000100000000010
  11 |   1140 |      0 | (0,11)  | 0000100000000010
  12 |   1140 |      0 | (0,12)  | 0000100000000010
  13 |   1140 |      0 | (0,13)  | 0000100000000010
  14 |   1140 |      0 | (0,14)  | 0000100000000010
  15 |   1140 |      0 | (0,15)  | 0000100000000010
  16 |   1140 |      0 | (0,16)  | 0000100000000010
  17 |   1140 |      0 | (0,17)  | 0000100000000010
  18 |   1140 |      0 | (0,18)  | 0000100000000010
  19 |   1140 |      0 | (0,19)  | 0000100000000010
  20 |   1140 |      0 | (0,20)  | 0000100000000010
  21 |   1140 |      0 | (0,21)  | 0000100000000010
  22 |   1140 |      0 | (0,22)  | 0000100000000010
  23 |   1140 |      0 | (0,23)  | 0000100000000010
  24 |   1140 |      0 | (0,24)  | 0000100000000010
  25 |   1140 |      0 | (0,25)  | 0000100000000010
  26 |   1140 |      0 | (0,26)  | 0000100000000010
  27 |   1140 |      0 | (0,27)  | 0000100000000010
  28 |   1140 |      0 | (0,28)  | 0000100000000010
  29 |   1140 |      0 | (0,29)  | 0000100000000010
  30 |   1140 |      0 | (0,30)  | 0000100000000010
  31 |   1140 |      0 | (0,31)  | 0000100000000010
  32 |   1140 |      0 | (0,32)  | 0000100000000010
  33 |   1140 |      0 | (0,33)  | 0000100000000010
  34 |   1140 |      0 | (0,34)  | 0000100000000010
  35 |   1140 |      0 | (0,35)  | 0000100000000010
  36 |   1140 |

What an UPDATE actually does

An UPDATE is not an in-place edit. In heap_update() (src/backend/access/heap/heapam.c) PostgreSQL:

• Stamps the old tuple's t_xmax with the current transaction's XID, marking it deleted as of this transaction.
• Writes a new tuple with t_xmin = current XID.
• Sets the old tuple's t_ctid to point at the new tuple, forming an update chain.

This is why a heavily updated table accumulates dead tuples and why VACUUM is mandatory: the old versions remain physically present until no transaction can still see them.

What the snapshot records

A snapshot (SnapshotData in src/include/utils/snapshot.h) records xmin (oldest still-running XID), xmax (next XID to be assigned), and the list of in-progress XIDs. That triple is enough to classify every tuple as visible or invisible without any locking.

Hint-bit optimization

Checking commit status via the commit log (pg_xact, formerly pg_clog) on every read would be expensive. PostgreSQL caches the answer in t_infomask using bits such as HEAP_XMIN_COMMITTED and HEAP_XMAX_COMMITTED. The first reader that resolves the status sets the hint bit; later readers skip the commit-log lookup. This is why a plain SELECT can dirty pages and generate I/O, it is writing hint bits back.

Keeping version churn under control

• Bloat is structural, not a bug. Dead versions are the price of lock-free reads; autovacuum reclaims them. Tune it per table rather than fighting MVCC.
• Cleanup is incremental. Opportunistic page pruning can reclaim dead versions during ordinary access without waiting for VACUUM, but only after no snapshot can still need them.
• Long-running transactions hold back cleanup. VACUUM cannot reclaim row versions that an older snapshot might still see in that database. Shared catalogs use a cluster-wide horizon; replication slots and hot_standby_feedback can also delay cleanup. Hunt blockers with pg_stat_activity.backend_xmin.
• HOT updates (Heap-Only Tuples) avoid touching indexes when no indexed column changes, keeping the update chain inside one page. Lower fillfactor on update-heavy tables to make HOT more likely. See the HEAP_HOT_UPDATED flag.

Scope note (locks are separate)

MVCC removes reader/writer blocking for snapshot reads and non-locking writes. SELECT FOR UPDATE, advisory locks, object locks, and unique-index insertion still go through the lock manager (see the lock-manager lesson). Keep that boundary clear in interviews.