gQuest

MVCC and row versions

The Insert, update, delete, upsert lesson mentioned that Postgres never overwrites a row in place — that's MVCC (Multi-Version Concurrency Control). This lesson goes deeper: every row secretly carries the two system columns that make MVCC work, xmin and xmax, and we'll read them directly to watch a row version get created, live, inside a real BEGIN ... COMMIT.

The migration

One table, accounts. Empty, as far as MVCC goes — there is no xmin or xmax column declared anywhere in here.

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  balance INTEGER NOT NULL
);

The seed

This is what creates checking and savings, the two rows we'll be watching.

INSERT INTO accounts (name, balance)
VALUES
  ('checking', 1000),
  ('savings', 5000);

A normal SELECT

Nothing unusual — just the columns declared in the migration above.

Loaded Database:mvcc.database-init
SELECT id, name, balance
FROM accounts
ORDER BY id;
idnamebalance
1checking1000
2savings5000

Select the hidden columns

Even though the migration never declared them, every row secretly carries xmin and xmax — Postgres adds them to every table automatically, and you can select them like any other column.

  • xmin — the id of the transaction that created this row version.
  • xmax — the id of the transaction that ended it. 0 means nothing has, so this is the live version.
  • ctid — this row version's physical location on disk.
Loaded Database:mvcc.database-init
SELECT xmin, xmax, ctid, id, name, balance
FROM accounts
ORDER BY id;
xminxmaxctididnamebalance
7520(0,1)1checking1000
7520(0,2)2savings5000

(0,1) isn't a transaction id — that's ctid, formatted as (page number, position on that page). checking landed in slot 1 of page 0, savings in slot 2 of the same page. They differ because they're two different rows, each with its own physical slot — not because two transactions ran. Both actually share the same xmin above, since the seed's single INSERT statement created them both in one transaction.

Two inserts, one transaction

Two separate INSERT statements, but one transaction: both new rows come back with the same xmin. xmin is stamped by the transaction, not the statement — anything that same transaction creates or changes shares it.

Loaded Database:mvcc.database-init
BEGIN;

INSERT INTO accounts (name, balance) VALUES ('payroll', 2000);
INSERT INTO accounts (name, balance) VALUES ('reserve', 3000);

COMMIT;

SELECT xmin, xmax, name, balance
FROM accounts
WHERE name IN ('payroll', 'reserve')
ORDER BY name;
xminxmaxnamebalance
7530payroll2000
7530reserve3000

payroll and reserve come back with the same xmin — proof it tracks the transaction, not the statement. Without the explicit BEGIN/COMMIT wrapping them, each INSERT would run as its own implicit, auto-committed transaction and get a different xmin — the same way checking and savings differ in ctid above but not in xmin, because that seed was one statement too.

xmin changes on UPDATE

UPDATE doesn't touch the row you had — it writes a brand new row version stamped with the current transaction's id as its xmin. Compare the number below to checking's xmin above: it's a new, higher transaction id, even though id and name didn't change.

Loaded Database:mvcc.database-init
UPDATE accounts
SET balance = balance - 100
WHERE name = 'checking'
RETURNING xmin, xmax, name, balance;
xminxmaxnamebalance
7530checking900

Two concurrent transactions

Two transactions are about to touch the same row, in the same migration and seed as above. Session A opens a transaction, updates checking's balance, and holds that change open for a moment before committing. Session B just runs plain SELECTs against that same row, once while Session A's change is still uncommitted and once after it commits — watch what changes between those two checks.

Both sessions below are real — two genuinely separate, concurrently-open connections to one real Postgres server, exactly like two psql windows. Session A's steps run in order on its connection — a real BEGIN, an uncommitted UPDATE, a real COMMIT. Session B's checks run on a second connection while Session A's transaction is still open, so its results are whatever Postgres's own MVCC visibility rules actually hand back — nothing simulated or narrated. (This only works server-side, at build time — the browser's playground runs on PGlite, a single-connection engine, so both sessions' full transcripts are precomputed once against a real Postgres and baked into this page rather than re-run on every visit.)

Session AStep 0Before either session does anything — the committed baseline
SELECT xmin, xmax, name, balance FROM accounts WHERE name = 'checking';
xminxmaxnamebalance
7510checking1000
Session BStep 0Same baseline, from Session B
SELECT xmin, xmax, name, balance FROM accounts WHERE name = 'checking';
xminxmaxnamebalance
7510checking1000

Same starting point for both — before Session A touches anything, this is the one committed row version that exists, and both sessions agree on it.

Session AStep 1Open a transaction, update the row — not committed yet
BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE name = 'checking'
RETURNING xmin, xmax, name, balance;

-- No COMMIT here — the transaction stays open (uncommitted) so Session B can see it
-- mid-flight in the next step.
xminxmaxnamebalance
7520checking900
Session BStep 2Meanwhile — checks the same row
SELECT xmin, xmax, name, balance FROM accounts WHERE name = 'checking';
xminxmaxnamebalance
751752checking1000

Session A's new row version already exists on disk, xmin and all, but MVCC keeps it invisible to every other session until it commits — this is what stops dirty reads.

Session AStep 3Still inside the same open transaction
SELECT xmin, xmax, name, balance FROM accounts WHERE name = 'checking';
xminxmaxnamebalance
7520checking900
Session AStep 4Commit
COMMIT;

No rows

Session AStep 5A fresh read, now that it's committed
SELECT xmin, xmax, name, balance FROM accounts WHERE name = 'checking';
xminxmaxnamebalance
7520checking900
Session BStep 6Checks again, after the commit
SELECT xmin, xmax, name, balance FROM accounts WHERE name = 'checking';
xminxmaxnamebalance
7520checking900

Now that Session A has committed, a fresh SELECT gets a fresh snapshot. A SELECT issued earlier, inside an open REPEATABLE READ or SERIALIZABLE transaction, would keep seeing the old row — that's the subject of the isolation-levels lesson.

Readers never wait on writers, and writers never wait on readers — Session A and Session B were never blocked by each other. They were just looking at different row versions of the same row.

Try Yourself

Loaded Database:mvcc.database-init

The database for this lesson is already loaded. Write any query you want and run it directly in your browser.

Exercises

0 OF 2 DONE
1.Return id, name, balance, and xmin for every account, ordered by id, so you can see each row's creating transaction.
2.Deposit 250 into the savings account (balance = balance + 250) and return the updated row, including xmin and xmax.

What We Learned

  • MVCCMulti-Version Concurrency Control: Postgres never overwrites a row in place. Every write creates a new row version, so readers never block writers and writers never block readers.
  • xminA hidden column on every row: the id of the transaction that created this row version.
  • xmaxA hidden column on every row: the id of the transaction that ended this row version — 0 while it's still the live version.
  • Dead tupleA row version nothing can see anymore once every transaction that could still need it is gone — reclaimed by VACUUM.