Tuple Versions, xmin/xmax, Snapshots, Visibility, and the MVCC Mental Model

Learning outcomes

A ServiceHub operator reports an apparent contradiction: one session has updated an account balance, yet another session still reads the old value. Nothing is “stale” in the cache and no read lock is blocking the writer. The missing mental model is PostgreSQL's Multi-Version Concurrency Control (MVCC): readers evaluate tuple versions against a snapshot instead of treating a row as one mutable storage slot.

1. The MVCC mental model: a logical row can have multiple tuple versions

In relational SQL you think in logical rows. PostgreSQL's heap storage can temporarily contain multiple physical versions of the same logical row. An INSERT creates a tuple version. An UPDATE normally creates a new tuple version and retires the old one. A DELETE retires a tuple version. Whether a session may see a given version is decided using transaction state and that statement or transaction's snapshot.

The tuple header includes system columns. xmin records the inserting transaction ID for that tuple version. xmax participates in recording deletion/update or row-lock state. In ordinary examples a freshly visible tuple often shows xmax = 0, but treating “nonzero xmax = deleted” as a universal rule is wrong because MultiXact and lock state can also be represented there.

2. Set up a disposable MVCC table

Use two terminals connected to the same disposable servicehub_lab database. Run setup as servicehub_owner or the role that owns schema app. Keep autocommit enabled except where BEGIN is shown explicitly.

SELECT current_database(), current_user;DROP TABLE IF EXISTS app.ch07_mvcc_account;CREATE TABLE app.ch07_mvcc_account (    account_id integer PRIMARY KEY,    owner_name text NOT NULL,    balance numeric(12,2) NOT NULL CHECK (balance >= 0));

3. An uncommitted insert is physically present but invisible to another snapshot

Session A begins a transaction and inserts a row without committing. Calling pg_current_xact_id() forces/returns a permanent transaction ID for the current transaction. The returned ID is dynamic.

BEGIN;INSERT INTO app.ch07_mvcc_account(account_id, owner_name, balance)VALUES (1, 'Ava', 500.00)RETURNING account_id, balance, xmin::text, xmax::text, ctid;SELECT pg_current_xact_id()::text AS xid_a,       pg_current_snapshot()::text AS snapshot_a;-- Leave this transaction open for the next observation.

account_id | balance | xmin    | xmax | ctid-----------+---------+---------+------+-------1          | 500.00  | <xid_A> | 0    | <tid>xid_a   | snapshot_a--------+-------------------------<xid_A> | <xmin>:<xmax>:<xip...>

Now Session B queries the same table. Under the default Read Committed isolation level, this statement's snapshot does not consider A's uncommitted insertion visible.

SHOW transaction_isolation;SELECT * FROM app.ch07_mvcc_account WHERE account_id = 1;SELECT pid, state, backend_xid, xact_start, query_startFROM pg_stat_activityWHERE datname = current_database()  AND pid <> pg_backend_pid()ORDER BY xact_start NULLS LAST;

transaction_isolation---------------------read committedaccount_id | owner_name | balance-----------+------------+--------(0 rows)-- pg_stat_activity is observational and dynamic. One row should show-- Session A as active or idle in transaction, depending on timing.

This proves visibility behavior, not absence from storage. Chapter 08 will inspect physical heap/page internals. Normal SQL deliberately returns only versions visible to the query.

4. Commit changes visibility for a later Read Committed statement

-- Session ACOMMIT;-- Session B: a new statement gets a new Read Committed snapshotSELECT account_id, owner_name, balance, xmin::text, xmax::text, ctidFROM app.ch07_mvcc_accountWHERE account_id = 1;

account_id | owner_name | balance | xmin    | xmax | ctid-----------+------------+---------+---------+------+------1          | Ava        | 500.00  | <xid_A> | 0    | <tid>

Nothing about this output says “the row moved from Session A to Session B.” Instead, B's later statement snapshot now considers A's inserting transaction committed and therefore the tuple version visible.

5. UPDATE creates a new visible version while old snapshots may still need the old one

Start another update in Session A and leave it uncommitted. Session A sees its own new version. Session B still sees the old committed value. This is the heart of why PostgreSQL can make ordinary readers and writers coexist without the reader blocking the writer.

BEGIN;UPDATE app.ch07_mvcc_accountSET balance = 425.00WHERE account_id = 1RETURNING account_id, balance, xmin::text, xmax::text, ctid;SELECT pg_current_xact_id()::text AS update_xid;-- Keep the transaction open.

SELECT account_id, balance, xmin::text, xmax::text, ctidFROM app.ch07_mvcc_accountWHERE account_id = 1;

Session A sees balance 425.00 with xmin = <update_xid>.Session B still sees balance 500.00 from the previously committed version.In this controlled update, the old tuple header may expose the updater in xmax,but do not generalize xmax into a business-safe deletion field.

After Session A commits, a new Read Committed statement in B sees 425.00. The old physical tuple does not instantly disappear; it may remain necessary to older snapshots and later becomes reclaimable when no relevant snapshot can see it. VACUUM performs that lifecycle work, which Chapters 08–09 explore in detail.

-- Session ACOMMIT;-- Session BSELECT account_id, balance, xmin::text, xmax::text, ctidFROM app.ch07_mvcc_accountWHERE account_id = 1;

6. Read a snapshot without inventing meanings

pg_current_snapshot() returns a pg_snapshot value. PostgreSQL documents its textual form as xmin:xmax:xip_list. Here snapshot xmin/xmax are snapshot bounds, not the tuple system columns with the same names.

WITH s AS (SELECT pg_current_snapshot() AS snap)SELECT snap::text,       pg_snapshot_xmin(snap) AS snapshot_xmin,       pg_snapshot_xmax(snap) AS snapshot_xmaxFROM s;SELECT *FROM pg_snapshot_xip(pg_current_snapshot())ORDER BY 1;

The in-progress list includes top-level transaction IDs relevant to the snapshot; subtransaction IDs are not represented the same way. The snapshot does not tell you which table rows each transaction changed. It is visibility metadata, not a change log.

7. Transaction IDs, virtual IDs, and what the evidence can actually prove

A PostgreSQL session starts with a virtual transaction ID (VXID) that is local to the backend. A permanent transaction ID (XID) is normally assigned when a transaction first needs one, especially when it modifies database state. This matters operationally: a read-only session can participate in snapshots and hold a virtual transaction while backend_xid remains NULL. Therefore, “no backend_xid” does not mean “no transaction” and certainly does not mean “the session cannot hold a snapshot that matters.”

Transaction identifiers also are not timestamps. XIDs advance across the whole database cluster rather than independently per database, and the internal xid type is finite and subject to wraparound semantics. PostgreSQL's visibility machinery, freezing, commit-status data, and VACUUM cooperate so old tuples remain interpretable. That is another reason an application must not use xmin as an everlasting creation number. If a business record needs a creation time or immutable event identifier, model those values explicitly.

BEGIN;SELECT pg_backend_pid() AS pid,       pg_current_snapshot()::text AS snapshot_before_write;SELECT pid, backend_xid, backend_xmin, stateFROM pg_stat_activityWHERE pid = pg_backend_pid();-- Force a permanent transaction ID for demonstration.SELECT pg_current_xact_id()::text AS permanent_xid;SELECT pid, backend_xid, backend_xmin, stateFROM pg_stat_activityWHERE pid = pg_backend_pid();ROLLBACK;

backend_xmin is especially useful when diagnosing cleanup horizons because it identifies the current backend's xmin horizon when one is advertised. It does not by itself tell you which table or tuple is preventing cleanup. Combine session age, transaction start time, query/application identity, and workload context before terminating anything.

Evidence ladder: from relational observation to internals

Use the least invasive evidence that answers the question. First confirm the exact relational result visible to each session. Next inspect transaction/isolation state and pg_stat_activity. Then inspect snapshots and lock/wait metadata. Only after those layers fail to explain the behavior should you descend into page-level tooling such as pageinspect in a disposable environment. Reading raw relation files or treating heap bytes as a stable public format is not an ordinary diagnostic workflow.

This evidence discipline becomes important in Chapter 08. Heap-page inspection can be extremely educational, but the correct question should already be precise before you inspect physical storage.

8. Lab verification and cleanup

SELECT account_id, owner_name, balance,       xmin::text AS inserting_xid,       xmax::text AS header_xmaxFROM app.ch07_mvcc_account;SELECT state, count(*)FROM pg_stat_activityWHERE datname = current_database()GROUP BY stateORDER BY state;

Before cleanup, make sure neither teaching session is still “idle in transaction.” Long-lived transactions can keep old snapshots relevant and later delay tuple cleanup. End every open transaction explicitly.

ROLLBACK; -- harmless if your current session has an open teaching transactionDROP TABLE IF EXISTS app.ch07_mvcc_account;

9. Production judgment and bridge

MVCC is the default concurrency mechanism, not an optional feature to “turn on.” Monitor unexpectedly long transactions and “idle in transaction” sessions because they can retain snapshots, locks, and cleanup horizons. Use system columns for diagnosis, never as schema-level identity. The next lesson changes one variable at a time—the isolation level—to show exactly when PostgreSQL refreshes a snapshot and which anomalies each level prevents.

Authoritative references

• PostgreSQL 18 — Introduction to MVCC
• PostgreSQL 18 — Transaction Isolation
• PostgreSQL 18 — System Columns
• PostgreSQL 18 — System Information Functions and Snapshot Functions
• PostgreSQL 18 — Transactions and Identifiers
• PostgreSQL 18 — pg_stat_activity