MVCC in PostgreSQL: xmin, xmax and snapshots Intermediate

The problem

One request changes a balance from 100 to 70. At the same time, another request opens a page showing the balance.

If there were only one memory cell for this number, the reader would have to choose: wait for the writer to finish, or risk seeing 70 even if the writer rolls back.

PostgreSQL takes a third path: MVCC. Several versions of the same row coexist for a while; each reader picks the version valid for its read point. The consequence: a plain SELECT does not wait for writers, but every UPDATE leaves garbage for VACUUM to clean up.

The model: versions, snapshots, visibility

• Each UPDATE creates a new tuple in the heap instead of overwriting the old one. Its fixed header (about 23 bytes) holds xmin (XID of the transaction that created the version), xmax (XID of the transaction that deleted, updated or locked it), and t_ctid pointing to the newer version.
• A snapshot answers “at read time, which transactions had committed and which were still running?”. It is not a copy of the table.
• A tuple’s visibility is the result of checking xmin/xmax against the snapshot (and the commit status of those XIDs).

A heap page after one UPDATE (illustrative XIDs)

Heap page of table accounts, row id = 1
  slot 1  v1  balance = 100   xmin = 100   xmax = 205   t_ctid -> slot 2
  slot 2  v2  balance =  70   xmin = 205   xmax =   0   t_ctid -> slot 2
          (transaction 205 is the one running the UPDATE)

Who sees which version:

Reader	State of tx 205	Sees	Reason
Tx 205 itself	running	v2 = 70	A transaction always sees its own changes
Snapshot taken before 205 commits	not committed	v1 = 100	xmin = 100 is committed; xmax = 205 is not committed, so it has no effect yet
Snapshot taken after 205 commits	committed	v2 = 70	xmax = 205 is committed, so v1 counts as replaced; xmin = 205 is committed, so v2 is valid
Anyone, if 205 rolls back	aborted	v1 = 100	xmax = 205 from an aborted transaction is ignored; v2 never appears

The simplified rule (enough to reason with, skipping edge cases like subtransactions): a version is visible when xmin is committed per the snapshot (or is your own transaction) and xmax is 0, not yet committed, or aborted.

How it works

One UPDATE creates a new version; readers choose by snapshot

Enlarge diagram

Versions → Reader sessionv1 = 100

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

Initially the row has a committed version v1 = 100. The reader reads v1.

1. Initially there is only v1 = 100, committed.
2. UPDATE creates v2 = 70. v1 is not physically deleted; the writer sees v2.
3. Before COMMIT, plain readers still pick v1, so they never see uncommitted data.
4. After COMMIT, a SELECT starting afterwards under Read Committed sees 70. A SELECT that started earlier, or a Repeatable Read transaction holding an old snapshot, still sees 100.
5. v1 is only reclaimed once no snapshot needs it anymore. A long-open reading transaction blocks this, even if it only reads.

Point	Writer sees	New SELECT under Read Committed	Repeatable Read reader (snapshot taken before the UPDATE)
Before UPDATE	100	100	100
UPDATE done, not committed	70	100	100
After COMMIT	70	70	100

“Committed but I still see the old value” is usually about snapshots, not replication lag: check the isolation level and when the snapshot was taken first. A Repeatable Read snapshot is taken at the first statement that is not a transaction-control command, not at the word BEGIN.

MVCC does not mean no locks

• Two writers modifying the same row still wait for each other (see row-level locks).
• SELECT ... FOR UPDATE writes to the row to mark it locked, so it causes disk writes even though you are only “reading” (docs). Using it on many hot rows dirties pages and increases WAL.
• This is why a non-zero xmax does not mean the row was deleted: it may record a row lock or belong to a rolled-back transaction. Do not write business logic on xmin/xmax; they are not modification times and not an audit log.

Why PostgreSQL tables bloat

Old versions live right in the heap, alongside live data. Some other engines (InnoDB, Oracle) keep old versions in a separate undo area; that architectural difference explains why write-heavy PostgreSQL tables need frequent VACUUM. The concrete costs and tuning are in VACUUM, autovacuum and bloat.

Real-world example

An admin page reads orders while a worker changes their status

A worker changes status from pending to paid; the admin page runs a SELECT at the same time. Under Read Committed the page sees pending or paid depending on when the SELECT starts, never an uncommitted state.

If the page needs several queries at the same point in time, open BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY. In exchange, that transaction holds its snapshot until it ends and blocks VACUUM; set statement_timeout and do not leave it open for hours.

Single-session demo (TEMP table, leaves nothing behind):

docker compose -f examples/docker-compose.yml up -d postgres
docker compose -f examples/docker-compose.yml exec -T postgres psql -U hub -d hub < examples/src/postgres-mvcc/mvcc-demo.sql

The script prints balance, xmin, xmax and ctid before the UPDATE, inside the transaction, after rollback and after commit. XIDs and ctid change every run; just check the sequence 100, 70 inside the transaction, 100 after rollback, 70 after commit.

To see two snapshots at once, use a regular table (not TEMP, since each session has its own temp tables):

-- A
CREATE TABLE mvcc_shared (id int PRIMARY KEY, balance int);
INSERT INTO mvcc_shared VALUES (1, 100);
BEGIN; UPDATE mvcc_shared SET balance = 70 WHERE id = 1;   -- stop here, no COMMIT yet

-- B
SELECT balance FROM mvcc_shared WHERE id = 1;              -- 100

-- A
COMMIT;
-- B
SELECT balance FROM mvcc_shared WHERE id = 1;              -- 70 (Read Committed)
DROP TABLE mvcc_shared;

Trade-offs

What MVCC gives you

• Plain readers read the committed version while a writer has not committed: reads do not block writes, writes do not block reads.
• A snapshot describes exactly what data a statement or transaction sees.
• A rollback never forces any reader to correct results already returned.

Costs and limits

• UPDATE/DELETE create versions that need cleanup: VACUUM and bloat are a permanent cost.
• Long-held snapshots slow down reclamation of old versions.
• MVCC does not replace locks for two writers on the same row, and does not prevent write skew under Repeatable Read.

Common mistakes

Common pitfall

“A non-zero xmax means the row was deleted.” xmax may also record a row lock or belong to a rolled-back transaction. Use a SELECT under the relevant isolation level to know whether a row is visible.

Common pitfall

“BEGIN is when the Repeatable Read snapshot is fixed.” It is the first non-transaction-control statement. When reproducing concurrency, run the first SELECT before the other session commits.

Common pitfall

“MVCC means no locks.” A plain SELECT does not wait, but an UPDATE of the same row still waits for the earlier writer, and FOR UPDATE actively locks.

Code review checklist

1. No business logic reads xmin/xmax as a modification time, deletion flag or audit log.
2. Transactions (even read-only ones) are not left open for long; reports use Repeatable Read with statement_timeout.
3. Seeing “committed but still reading the old value”: check the isolation level and snapshot timing before suspecting replication lag.
4. Several queries needing one point in time: wrap them in BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY, rather than stitching results from several Read Committed snapshots.
5. Do not sprinkle SELECT ... FOR UPDATE over many hot rows just to “read safely”: it writes to the rows, dirties pages and increases WAL.

Summary

Key takeaways

• One logical row has several physical versions; UPDATE creates a new version instead of overwriting.
• The snapshot plus the commit status of xmin/xmax decides which version is visible; xmin/xmax are not an audit log.
• Read Committed takes a snapshot per statement; Repeatable Read keeps one from the first real statement.
• VACUUM only reclaims old versions once no snapshot needs them; long-open transactions, even read-only ones, bloat tables.

Self-check: (1) A writer updates 100 to 70 but has not committed: what do the writer and a plain reader see, and what is v1’s xmax?

(2) Why can a Repeatable Read reader still see 100 after the writer commits? (3) Why does a read-only transaction left open for a long time still bloat the table?

References

• Introduction to MVCC — PostgreSQL 17
• Transaction Isolation — PostgreSQL 17
• System Columns — PostgreSQL 17
• Database Page Layout — PostgreSQL 17
• Explicit Locking — PostgreSQL 17
• Routine Vacuuming — PostgreSQL 17