How PostgreSQL Decides Row Visibility with MVCC

PostgreSQL implements MVCC (Multi-Version Concurrency Control) so that transactions see a consistent view of data while minimizing blocking.
This design is central to PostgreSQL’s ability to balance consistency with concurrency.

---

Tuple metadata: xmin and xmax

Every row version (tuple) in PostgreSQL has hidden system columns that store transactional metadata:

• xmin → the transaction ID that created the row.

• xmax → the transaction ID that deleted or updated the row (0 if still alive).

These fields let PostgreSQL know when a row was created and whether it has been deleted or replaced.

Example

-- Transaction 100 inserts a row
BEGIN; -- XID = 100
INSERT INTO accounts(id, balance) VALUES (1, 500);
COMMIT;

-- The row now has xmin = 100, xmax = 0

-- Transaction 105 updates the same row
BEGIN; -- XID = 105
UPDATE accounts SET balance = 600 WHERE id = 1;
COMMIT;

-- Old row: xmin = 100, xmax = 105 (dead)
-- New row: xmin = 105, xmax = 0 (alive)

-- Transaction 110 deletes the row
BEGIN; -- XID = 110
DELETE FROM accounts WHERE id = 1;
COMMIT;

-- Row: xmin = 105, xmax = 110 (dead)

Dead rows remain on disk until VACUUM reclaims the space.

---

Snapshots

Whether a row version is visible depends on the snapshot taken at query or transaction start (depending on isolation level).

A snapshot records three things:

1. xmin → the lowest still-active transaction ID at the time of the snapshot.

   • All transactions with IDs lower than this are guaranteed committed.

2. xmax → the next-to-be-assigned transaction ID.

   • All transactions with IDs equal to or above this have not started yet.

3. Active transaction list → a list of in-progress transactions between xmin and xmax.

   • Rows created by these are considered “not yet visible.”

Why all three are needed

• xmin → provides a lower bound. Anything before is safe to treat as committed.

• xmax → provides an upper bound. Anything after is invisible (future).

• Active list → resolves the uncertain middle range, marking exactly which transactions were in progress.

This design avoids having to check the commit status of every transaction individually.
Only transactions between xmin and xmax need explicit checking.

---

Row visibility rules

Given a snapshot, PostgreSQL decides visibility as follows:

1. Check the row’s xmin (creator):

   • If xmin < snapshot.xmin → creator definitely committed → row passes this check.

   • If xmin >= snapshot.xmax → creator is from the future → row is invisible.

   • If snapshot.xmin ≤ xmin < snapshot.xmax → creator might still be active.

     • If xmin is in the snapshot’s active list → row is invisible.

     • Otherwise, the creating transaction committed → row passes.

2. Check the row’s xmax (deleter/updater):

   • If xmax = 0 → row not deleted → row is visible.

   • If xmax < snapshot.xmin → deleter committed long before snapshot → row is invisible.

   • If xmax >= snapshot.xmax → deleter not started yet → ignore, row still visible.

   • If snapshot.xmin ≤ xmax < snapshot.xmax → deleter might be active.

     • If xmax is in the active list → row still visible.

     • Otherwise, deleter committed → row invisible.

Only if the row passes both checks is it visible in the snapshot.

---

Snapshot timing by isolation level

• READ COMMITTED (default)

  • Each statement takes a fresh snapshot.

  • A transaction can see rows committed by others between its own queries.

• REPEATABLE READ / SERIALIZABLE

  • One snapshot is taken at transaction start.

  • All queries in that transaction see the same consistent state, regardless of later commits.

Example

-- Session A
BEGIN;
UPDATE accounts SET balance = 700 WHERE id = 1;

-- Session B
BEGIN;
SELECT * FROM accounts WHERE id = 1; -- sees old version
COMMIT;

COMMIT; -- Session A commits

-- Session B runs again (new statement, new snapshot)
SELECT * FROM accounts WHERE id = 1; -- now sees updated version

In READ COMMITTED, Session B sees the change once Session A commits.
In REPEATABLE READ, Session B would keep seeing the old version until it commits.

---

Summary

MVCC in PostgreSQL is based on:

• Tuple metadata (xmin, xmax)

• Snapshots (xmin, xmax, active list) that define visibility

• UPDATE marks the old row dead (xmax = current XID) and inserts a new row version (xmin = current XID).

• DELETE marks the row dead by setting xmax.

This combination allows PostgreSQL to give each transaction a consistent view of data while supporting high concurrency.

---