Skip to content
Tutorials Center
My learning

Topics

Lesson 45 of 90 ยท Transactions, MVCC and Concurrency

MVCC: How PostgreSQL Keeps Versions

Understand PostgreSQL MVCC: row versions, xmin and xmax, snapshots and visibility, dead tuples, long-running transactions and transaction ID wraparound.

Most of PostgreSQL's behavior that surprises newcomers, such as tables that grow after updates, VACUUM, "idle in transaction" warnings and readers that never block writers, comes from one design choice: multiversion concurrency control (MVCC). Instead of changing a row in place and making everyone wait, PostgreSQL keeps several versions of a row and lets every transaction see the version that was current when its snapshot was taken.

In this lesson you will learn what a row version looks like, how xmin and xmax decide who can see it, why dead versions pile up, and why PostgreSQL needs to "freeze" old rows before its 32-bit transaction counter wraps around. The server internals appear as read-only blocks. The two TryIt examples build a small simulation of the visibility rules in plain SQL, so you can see the logic run.

Rows are never changed in place

INSERT Writes a new version stamped with your transaction ID.
UPDATE Marks the old version as ended and writes a new one.
DELETE Only marks the version as ended. The bytes stay.
1 / 3

What a row version looks like

Every row stored in a table is really one tuple (row version) with a small header. Two header fields carry the MVCC logic:

  • xmin: the ID of the transaction that created this version.
  • xmax: the ID of the transaction that deleted this version, or replaced it with a newer one. It is 0 while the version is still current.

Transaction IDs (xids) are numbers that PostgreSQL hands out in increasing order, one per transaction that writes something. Rows also have a physical address, the ctid (page number and slot, such as (0,1)). These are system columns: they exist on every table but SELECT * does not show them, so ask for them by name:

SQL
CREATE TABLE accounts (id int PRIMARY KEY, owner text, balance int);
INSERT INTO accounts VALUES (1, 'Amara', 1000);

SELECT xmin, xmax, ctid, * FROM accounts;
TEXT
 xmin | xmax | ctid  | id | owner | balance
------+------+-------+----+-------+---------
  801 |    0 | (0,1) |  1 | Amara |    1000

Now update the row in another transaction and look again:

SQL
UPDATE accounts SET balance = 700 WHERE id = 1;

SELECT xmin, xmax, ctid, * FROM accounts;
TEXT
 xmin | xmax | ctid  | id | owner | balance
------+------+-------+----+-------+---------
  802 |    0 | (0,2) |  1 | Amara |     700

The query shows the new version: created by transaction 802, located in slot 2 of the page. The old version is still on the page. Its xmax was set to 802, so it is now an ended version, which no new snapshot will return. You can see both versions with the pageinspect extension (heap_page_items), which is a debugging tool for superusers:

TEXT
 lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
  1 |    801 |    802 | (0,2)      <- old version, ended by 802, points to the new one
  2 |    802 |      0 | (0,2)      <- current version

That is all an UPDATE is in PostgreSQL: end the old version, write a new one. A DELETE sets xmax and writes nothing new. If the transaction later rolls back, nothing needs undoing: PostgreSQL just records that transaction 802 aborted, and its new versions become invisible while its xmax marks on old versions are ignored. This is why rollbacks are fast, and also why aborted transactions leave garbage behind.

Snapshots and visibility

A transaction does not read "the table", it reads "the table as of my snapshot". A snapshot records which transactions are visible:

  • xmin of the snapshot: every transaction below it has finished.
  • xmax of the snapshot: the first transaction ID that had not yet been assigned. Everything from here on is invisible.
  • A list of transactions that were still running when the snapshot was taken (they are invisible too, even though their IDs lie between the two numbers).

You can look at your own snapshot:

SQL
SELECT pg_current_snapshot();
TEXT
 pg_current_snapshot
---------------------
 803:806:803,805

That reads: all transactions below 803 are finished, nothing from 806 up is visible yet, and 803 and 805 were still running. (804 had finished, so a committed 804 is visible, an aborted one is not.)

A version is visible to a snapshot when both of these are true:

  1. Its creator (xmin) committed, and is visible to the snapshot (not in the in-progress list, not at or past the snapshot's upper limit). Versions you made yourself in the same transaction are also visible to you.
  2. It has not been ended, meaning xmax is 0, or the transaction in xmax aborted, or it is not visible to the snapshot yet.

When is a snapshot taken? That depends on the isolation level, and it is the whole difference between them (see Isolation Levels and Anomalies). In the default Read Committed level, every statement takes a fresh snapshot. In Repeatable Read and Serializable, the transaction takes one snapshot on its first statement and uses it until the end.

PostgreSQL stores the commit status of each transaction in a small structure called pg_xact, and caches the answer in "hint bits" on the tuples, so the lookup happens only once per version.

sequenceDiagram
  participant R as Reader (snapshot)
  participant W as Writer (txn 802)
  participant T as Table pages
  R->>T: SELECT balance (sees v1, xmin 801)
  W->>T: UPDATE: v1.xmax=802, write v2 (xmin 802)
  R->>T: SELECT again (snapshot unchanged)
  T-->>R: still v1, because 802 was not committed at snapshot time
  W->>T: COMMIT
  Note over R,T: A new snapshot would now see v2

The writer never waited for the reader, and the reader never saw a half-finished change.

A simulation you can run

The next example rebuilds the visibility rule in SQL, with simplified rules (the snapshot is just "the highest committed transaction I can see"). A versions table plays the role of the heap, and a tx table plays the role of pg_xact:

SQL Row versions and snapshots, simulated

Press Run (or Ctrl + Enter) to see the result here.

The first result lists three physical versions (1000 ended by 802, 700 marked by the aborted 803, and the orphan 0 created by the aborted 803). The second result is the point: a snapshot taken after 801 sees 1000, a snapshot after 802 or 804 sees 700. The aborted transaction's version (0) is never visible, and its attempt to end the 700 version is ignored, because transaction 803 never committed. Nobody locked anything. The three snapshots could be three sessions reading at the same moment.

Dead tuples and the cost of MVCC

When is a version dead? When its xmax belongs to a committed transaction and no snapshot that is still in use can see it. (Versions created by aborted transactions are dead at once.) Dead versions take up space and make scans slower, and indexes still point to them. VACUUM is the process that finds them and marks their space as reusable (details in VACUUM, Autovacuum and Bloat).

The key word is "still in use". PostgreSQL computes a horizon: the oldest snapshot anywhere in the system. A dead version can only be removed if it became dead before that horizon. One forgotten long-running transaction holds the horizon back for the whole database:

SQL A long-running snapshot keeps garbage alive

Press Run (or Ctrl + Enter) to see the result here.

With no long transaction, all nine ended versions are removable. With a snapshot still open from transaction 903, only three are, and the rest must stay because that old snapshot might still need to see them. Seven versions of one row remain, for as long as the old transaction lives. In a real system, the same effect makes tables and indexes grow while the application keeps running fine.

Long snapshots that hold the horizon back come from several places: an application transaction left open ("idle in transaction", see Transactions and ACID), a slow report on a replica with hot_standby_feedback = on, a forgotten replication slot, or an abandoned prepared transaction. Look at them regularly:

SQL
SELECT pid, state, age(backend_xmin) AS xmin_age, now() - xact_start AS open_for
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

Transaction ID wraparound

Transaction IDs are 32-bit numbers, so they can only count about 4.3 billion before wrapping around. PostgreSQL compares IDs circularly: for any given ID, the roughly 2 billion IDs "behind" it are the past (visible), and the 2 billion "ahead" are the future (invisible). That works only if no row stays old for more than about 2 billion transactions. Otherwise, a very old, committed row would suddenly look as if it were created in the future and vanish from queries.

The cure is freezing. VACUUM marks sufficiently old row versions as frozen, which means "visible to everyone, regardless of IDs". A frozen row no longer depends on its xmin being comparable to the current counter, so it is safe forever.

  • Normal VACUUM freezes versions older than vacuum_freeze_min_age (default 50 million transactions).
  • When a table's oldest unfrozen ID reaches autovacuum_freeze_max_age (default 200 million), autovacuum starts an aggressive, anti-wraparound vacuum for it, even if autovacuum is switched off. These can run long on big tables, which is why you must not cancel them.
  • If vacuum falls far behind, a failsafe mode kicks in (vacuum_failsafe_age, default 1.6 billion), and finally, a few million transactions before wraparound, the server refuses to assign new IDs and stops accepting writes until a vacuum has caught up.

You watch the distance with age():

SQL
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 5;
TEXT
  datname  |  xid_age
-----------+-----------
 shop      | 142331802
 postgres  |  21093401

An age of 142 million is healthy against a 200 million trigger. Alert when it climbs well past autovacuum_freeze_max_age (for example at 1 billion): that means vacuum is blocked or starved by something such as an old replication slot or a stuck transaction. Do not let it get close to 2 billion.

What MVCC means for how you work

  • Readers and writers do not block each other, and a long report does not stop updates. You still pay for long transactions in dead tuples.
  • UPDATE is more expensive than it looks: a full new row version, plus new index entries unless the update is HOT (see Index Maintenance and Bloat). Updating a row ten times produces ten versions to clean up.
  • SELECT count(*) is not free, because the table cannot keep one number that is right for everyone: each snapshot may see a different set of rows, so PostgreSQL has to check visibility row by row.
  • Rollbacks are cheap, but they still leave dead versions.
  • Two writers on the same row do conflict: the second one waits for the first to finish (see Locks, Row Locking and Deadlocks).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In PostgreSQL, what does an UPDATE of one row physically do?

    UPDATE ends the old version by setting xmax and inserts a new version. The old one stays on the page until VACUUM reclaims it.
  2. 2. A transaction rolls back. What happens to the row versions it created?

    Rollback is just a status change in pg_xact. Versions made by an aborted transaction are never visible, and they are garbage for VACUUM.
  3. 3. Why can one old open transaction make tables grow even if nothing else is wrong?

    VACUUM can only remove versions that no active snapshot could see. The oldest snapshot sets the horizon, so a long transaction keeps dead versions alive.
  4. 4. What does freezing protect against?

    Transaction IDs are 32-bit and compared circularly. VACUUM freezes old versions so they stay visible regardless of how far the counter has moved.
  5. 5. Which statement about snapshots is correct?

    When the snapshot is taken is the main difference between the levels. A new snapshot for every statement allows non-repeatable reads, while a transaction-wide snapshot does not.

Summary

  • Every row version carries xmin (creator) and xmax (ender); UPDATE ends one version and writes another, and DELETE only sets xmax.
  • A snapshot decides which transactions count as committed for a reader; a version is visible if its creator is visible and its ender is not.
  • Readers and writers do not block each other, which is the main benefit of MVCC.
  • Versions that no snapshot can see are dead tuples; VACUUM reclaims them, but the oldest open snapshot limits what it can remove.
  • 32-bit transaction IDs wrap around, so VACUUM freezes old rows; monitor age(datfrozenxid) and age(relfrozenxid).

Next, see how the snapshot rules produce the isolation levels in Isolation Levels and Anomalies.