Skip to content

CONCEPT Cited by 1 source

xmin / xmax row versioning (Postgres)

In Postgres, every row version (tuple) on disk carries two hidden metadata columns used to determine which transactions may see it: xmin and xmax. Together with a transaction's own snapshot they form the visibility oracle for Postgres's multi-version concurrency control (MVCC).

Definition

From PlanetScale's explainer (Source: sources/2026-02-22-planetscale-what-is-a-database-transaction):

  • xmin — the transaction ID that created this row version (either via INSERT or by being the replacement tuple left behind by an UPDATE).
  • xmax — the transaction ID that superseded this row version, either by UPDATE (writing a new version) or by DELETE. xmax = 0 (or null) means the version is current and has not been replaced.

Worked example

Transaction 10 updates user id=4's name from liz → aly:

  1. The old row (name=liz) gets its xmax set to 10 — "superseded by transaction 10."
  2. A new row (name=aly) is inserted with xmin set to 10 — "created by transaction 10."
  3. Both versions now coexist on disk. Which version a given transaction sees is a function of its own ID and its snapshot vs. the xmin/xmax of each candidate version.

Before transaction 10 commits, Session B looking at user 4 sees liz because it cannot see uncommitted data — independent of xmin/xmax at this stage. After transaction 10 commits, Session B (now at a higher transaction ID) sees aly because its snapshot permits it.

Concurrent writers

When two transactions both modify rows, each transaction sees its own changes but not the other's until commit; xmin and xmax along with per-snapshot visibility rules make this bookkeeping uniform from 2 concurrent transactions up to hundreds on a busy cluster.

The row-accumulation problem

Because every UPDATE creates a new version rather than overwriting in place, a heavily-updated table accumulates duplicated row versions (dead tuples) over time. VACUUM FULL purges versions so old that no running transaction can still need them and compacts the table, reclaiming disk space. The lighter-weight autovacuum (see concepts/postgres-autovacuum) does this incrementally in the background.

Contrast with MySQL

MySQL/InnoDB also provides consistent reads at REPEATABLE READ, but with a completely different mechanism: rather than keeping multiple row versions on the main heap, it overwrites in place and reconstructs older versions from an undo log on demand. The two approaches solve the same problem (transaction-consistent reads) with inverse tradeoffs: Postgres spends disk space and occasional vacuum overhead; MySQL spends redo/undo complexity and log lookups for concurrent readers.

Seen in

Last updated · 517 distilled / 1,221 read