PostgreSQL Internals - Module 3: MVCC & Transaction Isolation Levels

In the previous blogs, we explored the concepts related to PostgreSQL Architecture and Storage layer.

Now that we know what xmin, xmax, ctid and cid look like on disk, we use them to understand how concurrency works in PostgreSQL.

This module is about the single most important question PostgreSQL asks millions of times per second:

"Is this tuple visible to this transaction?"

Part 1: What is a Transaction ID (XID)?

Every transaction in PostgreSQL that writes something gets a transaction ID, a 32-bit unsigned integer which increases monotonically.

-- See your current transaction's XID
-- (PG 13+ name: pg_current_xact_id(); txid_current() still works)
SELECT txid_current();
-- Returns 9093
-- Note: this call ASSIGNS an XID if the transaction didn't have one yet

-- A real XID is only assigned when the transaction writes something
-- (or calls txid_current()). Every transaction has a "virtual XID";
-- read-only ones never get a real XID.
-- Run this in a fresh transaction, before any write:
SELECT txid_current_if_assigned();
-- NULL - no XID assigned yet

The XID Space

• 0 = Invalid TransactionID

• 1 = Bootstrap TransactionID (system init)

• 2 = Frozen TransactionID (always visible)

• 3...2³²-1 = Normal Transaction IDs, assigned sequentially

• 2³² - 1 = 4,294,967,295 (~4 billion). After that the counter wraps back to 3.

Note: XIDs are compared modulo 2^32, so every transaction sees about 2^31 (~2.1 billion) XIDs as “in the past” and 2^31 as “in the future”. If a tuple’s xmin gets older than ~2^31 transactions, it would suddenly look like it’s in the future. This is the Transaction Wraparound problem, which we will discuss later in this blog.

Part 2: The Snapshot - PostgreSQL's view of NOW

A snapshot records which transactions are still running at the moment it’s taken, and it is used to make all the tuple visibility decisions for the statements that use it. When it’s taken depends on the isolation level (Part 4): at the first statement of the transaction in REPEATABLE READ, at every statement in READ COMMITTED.

What a Snapshot contains

typedef struct SnapshotData {
    TransactionId xmin;   /* oldest XID still running */
    TransactionId xmax;   /* one past the highest completed XID */
    TransactionId *xip;   /* in-progress XIDs[] between xmin & xmax */
    uint32        xcnt;   /* number of in-progress XIDs */
} SnapshotData;

In simple terms:

• xmin = any XID below this is finished (either committed or aborted).

• xmax = any XID at or above this had not finished when the snapshot was taken. So invisible.

• xip[] = XIDs between xmin and xmax that are still in progress at the time of taking this snapshot. So these are also invisible.

-- See the current snapshot
SELECT pg_current_snapshot();
-- Returns something like: 1041:1045:1041,1043
--   xmin = 1041  (oldest still running, so it also appears in xip)
--   xmax = 1045  (one past the highest completed XID)
--   xip  = {1041, 1043}  (currently running)

Snapshot Construction - Step by step

PostgreSQL builds this snapshot by scanning the ProcArray, the shared memory array of all active backends we covered in Module 1. xmax = latestCompletedXid + 1, xmin = the oldest XID still running, and xip[] = the running XIDs below xmax. Your own XID is skipped.

Transactions at snapshot time:
  XID 1040 — committed
  XID 1041 — IN PROGRESS  ← goes into xip[]
  XID 1042 — YOUR transaction (own XID is skipped, never in xip[])
  XID 1043 — committed    ← latestCompletedXid
  XID 1044 — not started yet

Snapshot result:
  xmin = 1041   (oldest still running)
  xmax = 1044   (latestCompletedXid + 1)
  xip  = [1041]

Part 3: Visibility Rules - Exact Algorithm

Given a tuple with xmin, xmax, and a snapshot, here is the complete visibility check PostgreSQL runs. This will be done in the HeapTupleSatisfiesMVCC() in src/backend/access/heap/heapam_visibility.c.

The function asks two questions in order: did the insert happen, for me? and then did the delete happen, for me? For each XID it checks, always in this order: is it me, is it still running per my snapshot, and only then what CLOG (pg_xact) says.

Algorithm

FUNCTION is_visible(tuple, snapshot):
  ── 1. Did the insert happen, for me? (xmin) ──
  IF hint XMIN_INVALID: 
      RETURN false                   ← inserter aborted
  IF hint XMIN_COMMITTED not set:
      IF xmin == my_xid:
          IF cmin >= snapshot.curcid: 
              RETURN false     ← inserted by this or a later command
      ELSE IF running_for_me(xmin): 
          RETURN false       ← snapshot FIRST
      ELSE IF clog(xmin) == COMMITTED: 
          set hint XMIN_COMMITTED
      ELSE: 
          set hint XMIN_INVALID; 
          RETURN false        ← aborted or crashed
  ELSE IF not frozen AND running_for_me(xmin):
      RETURN false                                     ← committed, but not for my snapshot

  ── 2. Did the delete happen, for me? (xmax) ──
  IF hint XMAX_INVALID OR xmax is lock-only: 
      RETURN true
  IF xmax == my_xid: 
      RETURN cmax >= snapshot.curcid    ← deleted by this/later command → still visible
  IF running_for_me(xmax): 
      RETURN true
  IF hint XMAX_COMMITTED not set:
      IF clog(xmax) != COMMITTED: 
          set hint XMAX_INVALID; 
          RETURN true
      set hint XMAX_COMMITTED
      RETURN false                                         ← delete committed before my snapshot

FUNCTION running_for_me(xid):
  xid <  snapshot.xmin → false
  xid >= snapshot.xmax → true
  otherwise            → xid in snapshot.xip[]

Two things to notice:

• The snapshot is checked before CLOG. A transaction is marked committed in CLOG slightly before it leaves the ProcArray, so checking CLOG first could show a commit that this snapshot must still treat as running.

• Your own changes are decided by command ID (cid), not commit status. A command never sees rows it inserted itself, and still sees rows it deleted itself.

This runs for every tuple a query touches. The hint bits are what make it fast: once set, CLOG is never consulted again for that tuple, but the snapshot check still runs.

For a line-by-line walk through the real function, see Reading PostgreSQL: MVCC Internals Explained from Source Code.

Part 4: Isolation Levels

PostgreSQL Isolation levels are implemented by controlling when the snapshot is taken in transactions.

Different Isolation Levels:

• Read Uncommitted (accepted, but behaves exactly like Read Committed in PostgreSQL)

• Read Committed (Default)

• Repeatable Read

• Serializable

READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

A new snapshot is taken at the start of each statement. That means, even in a single transaction, different statements see different snapshots, and so different data.

Example:

T1: BEGIN; (XID = 1054)
T1: SELECT balance FROM accounts WHERE id=1; --Sees $100(Snapshot A)

T2: BEGIN; (XID = 1055)
T2: UPDATE accounts SET balance=200 WHERE id=1;
T2: COMMIT; (Committed XID 1055)

T1: SELECT balance FROM accounts WHERE id=1; --Sees $200(Snapshot B)
T1: COMMIT;

T1 got two different answers for the same query within one transaction. This is a non-repeatable read, allowed under READ COMMITTED.

REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

The snapshot is taken once at the first statement and held for the entire transaction.

Example:

T1: BEGIN; (XID = 1056)
T1: SELECT balance FROM accounts WHERE id=1;--Snapshot taken,sees $100

T2: BEGIN; (XID = 1057)
T2: UPDATE accounts SET balance=200 WHERE id=1;
T2: COMMIT;

T1: SELECT balance FROM accounts WHERE id=1; -- Still sees $100
T1: COMMIT;

T1 is completely isolated from T2’s commit. This prevents non-repeatable reads AND phantom reads; PostgreSQL’s REPEATABLE READ is actually stronger than the SQL standard requires.

One catch: if T1 tries to update a row that T2 changed after T1’s snapshot, T1 can’t silently overwrite it. It fails with:

ERROR: could not serialize access due to concurrent update

and the application has to retry the transaction.

SERIALIZABLE

Uses Serializable Snapshot Isolation (SSI): on top of REPEATABLE READ’s snapshot, it tracks read/write dependencies between transactions to detect serialization anomalies and aborts one of the “dangerous” transactions. Much deeper than just snapshot timing.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- May raise: ERROR: could not serialize access due to read/write dependencies among transactions

Anamolies

1. Dirty Read - You read uncommitted data from another transaction

   1. Not possible in any isolation level. The visibility rules are designed so that uncommitted tuples are never visible to other transactions.

2. Non-Repeatable Read - Same row returns different values due to updates in another transaction

   1. Possible in READ COMMITTED

3. Phantom Read - Same query returns different rows due to inserts/deletes in another transaction

   1. Possible in READ COMMITTED

4. Serialization Anomaly (e.g. write skew) - Two transactions each read data, then write based on what they read, and the combined result could not have happened if they had run one after the other

   1. Possible in READ COMMITTED and REPEATABLE READ. Only SERIALIZABLE prevents it, which is why it exists.

Part 5: XID WrapAround Problem

XIDs are 32-bit and compared modulo 2^32, so each transaction sees about 2^31 (~2.1 billion) XIDs as past and 2^31 as future. Once a tuple’s xmin is more than ~2^31 transactions old, it flips to looking like it’s in the future, and PostgreSQL would treat the row as invisible.

Rows would suddenly disappear, which is effectively data loss.

How PostgreSQL solves it: Freezing

VACUUM marks old tuples as frozen. Since PostgreSQL 9.4 it does this by setting the HEAP_XMIN_FROZEN bits (HEAP_XMIN_COMMITTED | HEAP_XMIN_INVALID) in t_infomask and leaves t_xmin as it is. Before 9.4, it overwrote t_xmin with FrozenTransactionID (2). Either way, frozen tuples are always visible to everyone and no snapshot check is needed.

Normal tuple:  xmin=1000000                     → visible only if XID 1M is finished per my snapshot
Frozen tuple:  xmin=1000000 + HEAP_XMIN_FROZEN  → ALWAYS visible, no snapshot check

When does a tuple get frozen?

• A tuple is eligible for freezing when its xmin is older than vacuum_freeze_min_age transactions (default: 50 million).

• When a table’s age(relfrozenxid) passes vacuum_freeze_table_age (default: 150 million), VACUUM runs aggressively: it scans every page that isn’t already all-frozen.

• When it passes autovacuum_freeze_max_age (default: 200 million), autovacuum forces an anti-wraparound vacuum on that table, even if autovacuum is disabled.

-- Monitor freeze health
SELECT datname,
       age(datfrozenxid)          AS xid_age,
       2^32 - age(datfrozenxid)   AS xids_until_wraparound
FROM pg_database
ORDER BY xid_age DESC;

-- Per-table freeze age
SELECT relname,
       age(relfrozenxid)          AS table_xid_age,
       pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

If anti-wraparound vacuums can’t keep up, PostgreSQL starts warning when about 40 million XIDs are left before wraparound, and refuses to assign new XIDs when about 3 million are left. It doesn’t shut down: read-only queries still work, but anything that needs a new XID fails until the database is vacuumed. This is the wraparound safety circuit.

If it hits 2^32 - 1, PostgreSQL will refuse to accept new transactions and shut down and this we can call as the wraparound safety circuit.

The all_frozen VM bit

When VACUUM freezes every tuple on a page, it sets the all_frozen bit in the visibility map. Later aggressive VACUUMs skip that page entirely since they know there's nothing left to freeze. This is why keeping tables well-vacuumed doesn't just prevent bloat, it keeps freeze overhead manageable.

Part 6: HOT Updates - Avoiding Index Churn

Normal UPDATE writes a new tuple version and requires updating every index that covers the table. HOT (Heap Only Tuple) avoids this when:

1. The updated columns are not part of any index

2. There is free space on the same page as the old tuple

CREATE TABLE orders (id int PRIMARY KEY, status text, notes text);
-- index only on id

INSERT INTO orders VALUES (1, 'pending', 'rush');
UPDATE orders SET notes = 'very rush'; -- HOT: notes not indexed, same page

What happens physically:

Right after the UPDATE:
  ItemId[1]: xmin=100, xmax=101, t_infomask2=HEAP_HOT_UPDATED, t_ctid=(0,2)  ← index points here
  ItemId[2]: xmin=101, xmax=0,   t_infomask2=HEAP_ONLY_TUPLE                 ← new tuple, no index entry

After page pruning (once the old version is dead to everyone):
  ItemId[1]: lp_flags=REDIRECT → 2   ← index still points here!
  ItemId[2]: xmin=101, xmax=0, t_infomask2=HEAP_ONLY_TUPLE

The index entry still points to ItemId[1]. When an index scan reaches it, it follows the HOT chain (through t_ctid before pruning, through the redirect after) to ItemId[2], the live version.

Part 7: Hands-On Lab

-- Setup
CREATE TABLE mvcc_demo (id int, val text);
INSERT INTO mvcc_demo VALUES (1, 'original');
-- Note the XID
SELECT xmin, xmax, ctid, * FROM mvcc_demo;
-- xmin | xmax | ctid  | id |   val    
-- 9581 |    0 | (0,1) |  1 | original

-- ── Terminal 1 ──
BEGIN;
SELECT txid_current(); -- XID = 9582

UPDATE mvcc_demo SET val='updated' WHERE id=1;

-- Before committing, open Terminal 2


-- ── Terminal 2 (while Terminal 1 is open) ───
-- Under READ COMMITTED (default):
SELECT xmin, xmax, ctid, * FROM mvcc_demo;
-- xmin | xmax | ctid  | id |   val    
-- 9581 | 9582 | (0,1) |  1 | original
-- xmax=9582 is in progress → old tuple still visible

-- ── Terminal 1 ──
COMMIT;

-- ── Terminal 2 ──
SELECT xmin, xmax, ctid, * FROM mvcc_demo;
-- NOW sees: xmin=9582, xmax=0, val='updated'
-- xmin | xmax | ctid  | id |   val   
-- 9582 |    0 | (0,2) |  1 | updated
-- New snapshot taken (READ COMMITTED), sees committed data

-- ── Inspect both tuple versions with pageinspect ────
SELECT lp, t_xmin, t_xmax, t_ctid,
       t_infomask,
       t_data
FROM heap_page_items(get_raw_page('mvcc_demo', 0));
-- Both tuples visible here: the dead one (xmax set) and the live one
-- lp | t_xmin | t_xmax | t_ctid | t_infomask | t_data            
--  1 |   9581 |   9582 | (0,2)  |       1282 | \x0100000013
--  2 |   9582 |      0 | (0,2)  |      10498 | \x0100000024


-- ── Watch XID age ───
SELECT age(relfrozenxid), relname
FROM pg_class WHERE relname = 'mvcc_demo';
-- age |  relname  
--   3 | mvcc_demo

-- ── Trigger a manual freeze ──
VACUUM FREEZE mvcc_demo;

SELECT lp, t_xmin, t_xmax, t_infomask
FROM heap_page_items(get_raw_page('mvcc_demo', 0));
-- lp | t_xmin | t_xmax | t_infomask
--  1 |        |        |
--  2 |   9582 |      0 |      11010

This is the key insight: since PostgreSQL 9.4, freezing does not rewrite t_xmin to 2 (FrozenTransactionId). It sets the HEAP_XMIN_FROZEN bits on the tuple and leaves t_xmin intact. That’s why you still see t_xmin = 9582 instead of 2. You can see it in t_infomask: it went from 10498 (0x2902) to 11010 (0x2B02). The added 0x0200 (HEAP_XMIN_INVALID), together with the existing 0x0100 (HEAP_XMIN_COMMITTED), is HEAP_XMIN_FROZEN (0x0300). On 9.3 and older, you would see t_xmin set to 2.

Part 8: Conclusion

In this module, we explored how MVCC and transaction isolation levels work in PostgreSQL, covering:

1. What a transaction ID (XID) is

2. How a snapshot is built and how it decides the visibility of a tuple

3. The isolation levels, what each one allows, and why SERIALIZABLE exists

4. XID wraparound and how freezing prevents it

5. HOT updates and how they avoid index churn

Finally, we got our hands dirty with a lab to see all of this on a real page.

What's Next → Module 4: WAL, Checkpoints and Crash Recovery

You now understand exactly how PostgreSQL decides what you can see. Module 4 goes into WAL (Write-Ahead Logging), the mechanism that makes every write durable, enables crash recovery, and powers replication.

We'll cover the WAL record format, LSNs, checkpoints, and what happens byte-by-byte when you COMMIT.