Why PostgreSQL keeps old row versions · PostgreSQL

Everything difficult about operating PostgreSQL — bloat, autovacuum tuning, wraparound, long transactions, replication lag from hot_standby_feedback — descends from one design decision made decades ago and never reversed.

PostgreSQL does not modify rows. It writes new versions of them.

This lesson establishes what that means concretely, because the rest of Parts VII, VIII and IX are consequences of it.

Two sessions, one row

The demonstration is short and the result is the whole point. Session A opens a transaction, updates a row, and does not commit.

Read-only / Safesession A: update, hold, do not commit— Captured on 18.6 in a disposable cluster. The transaction is left open deliberately.

$ psql -U postgres -c "BEGIN" -c "SELECT pg_backend_pid(), pg_current_xact_id()" -c "UPDATE mvcc SET balance = 999 WHERE id = 1"

 session_a_pid | session_a_xid
---------------+---------------
        1605 |        683645

 who   | id | balance
---------+----+---------
A sees: |  1 |     999
A sees: |  2 |     200

While A holds that transaction open, session B reads the same row.

Read-only / Safesession B, concurrently— Returned immediately. No wait, no lock, no error.

$ psql -U postgres -c "SELECT 'B sees:' AS who, id, balance FROM mvcc ORDER BY id"

   who   | id | balance
---------+----+---------
B sees: |  1 |     100
B sees: |  2 |     200

Two sessions, two different answers, both correct, neither waiting. On a database that updates rows in place, B has three options: read the uncommitted value and be wrong, wait for A, or read from a separate copy of the old value that the system maintained on the side. PostgreSQL takes a fourth: the old value is still there, in the table, as a row of its own.

What the row headers show

The mechanism is visible in the tuple headers from Part VI.

Read-only / Safethe same rows, with their headers, read by B— Captured while A's transaction was still open and uncommitted.

$ psql -U postgres -c "SELECT id, balance, xmin, xmax, ctid FROM mvcc ORDER BY id"

 id | balance |  xmin  |  xmax  | ctid
----+---------+--------+--------+-------
1 |     100 | 683644 | 683645 | (0,1)
2 |     200 | 683644 |      0 | (0,2)
(2 rows)

Row 1 already carries xmax = 683645 — session A’s transaction id — even though A has not committed. That is not a bug and not a half-applied change.

The rule is simple once stated: a version’s headers record which transactions bracket its life, and whether those transactions committed is looked up separately, at read time. B sees xmax = 683645, checks the status of transaction 683645, finds it still in progress, and concludes that this version has not yet been superseded as far as B is concerned.

Field	Records
`xmin`	The transaction that created this version
`xmax`	The transaction that deleted, superseded or locked it
`ctid`	This version’s physical address

A non-zero xmax does not mean the row is dead

xmax is also used as a row-lock marker, so a perfectly live row can carry one. Reproduced on 18.6:

BEGIN;
  UPDATE mvcc2 SET balance=300 WHERE id=1;
  SELECT ... FROM mvcc2 FOR UPDATE;      -- xmax reads 0 here
COMMIT;

SELECT xmin, xmax, ctid FROM mvcc2;
  xmin  |  xmax  | ctid
--------+--------+-------
 683654 | 683654 | (0,3)

The row is current, readable and correct, and its xmax equals its xmin.

The consequence matters because the mistake is common: a query along the lines of WHERE xmax <> 0 does not find dead rows. It finds rows that have been deleted, superseded, or locked. Part VIII covers the supported ways to measure dead rows and bloat.

What this buys

Readers never block writers, and writers never block readers. No read lock is taken on ordinary SELECT, so a long reporting query cannot stall a write workload and a write workload cannot stall the report. This is the property that makes PostgreSQL comfortable with mixed workloads that would deadlock elsewhere.

A consistent view without freezing the database. Every statement, or every transaction depending on isolation level, works from a fixed point in time. The next lesson but one covers how that point is represented.

Rollback is nearly free. Aborting does not undo anything: the versions the transaction wrote are simply never visible to anyone, because the transaction’s status says aborted. A transaction that modified ten million rows and rolls back does so in about the time it takes to write one status record.

What it costs

Dead versions accumulate. Every update and every delete leaves something behind. Nothing removes it as part of the operation itself.

Something has to reclaim them. That is vacuum, and Part VIII is entirely about it. This is the source of most PostgreSQL operational work.

Reclamation is blocked by anything that might still need the old version. A transaction that opened a snapshot an hour ago may still read a version deleted fifty-nine minutes ago, so that version cannot be removed. Lesson VII-05 measures what this costs; the short version is that one idle session turned a 1.7 MB table into a 19 MB one in this course’s own test cluster.

Indexes point at versions, not at rows. A new version generally needs a new entry in every index on the table. Lesson VII-02 measures that too, and it is expensive.

Where other databases put the old versions, and what that changes

The alternative design keeps one current version of each row in the table and writes the pre-change image into a separate structure — Oracle calls it undo, MySQL’s InnoDB calls it the undo log. A reader that needs an older view reconstructs it by walking backwards through that structure.

The trade is real in both directions, and it is worth understanding because it explains why advice does not transfer between systems.

Undo-based storage keeps tables compact, because the table holds only current rows. Its cost is that reads of old data get slower the further back they reach, and the undo structure itself has to be sized and can run out — a long query can fail because the version it needed was already discarded.

PostgreSQL’s design keeps all versions in the table. Reads never get slower for being old, and a long query cannot fail for lack of history. The cost is that the table carries the history, so it grows, and something must clean it up.

That difference is why a DBA arriving from an undo-based system finds PostgreSQL’s tables surprisingly large and its vacuum requirements surprisingly demanding, and why “just run a long report against production, it’s only a read” is safe advice in one world and expensive advice in the other.

What to take from this

An UPDATE writes a new version and stamps the old one. Nothing is overwritten.
Visibility is decided by looking up transaction status at read time, not by locking.
xmax records deletion, supersession or locking, so it is not a test for a dead row.
Rollback is cheap; cleaning up after it is not, and happens later.
Every vacuum topic in this course is downstream of this one decision.

Cross-course references

Linux for Production Sysadmins — Part XLI (Storage Performance) covers measuring the write amplification old row versions cause.
Observability for Production Sysadmins — Part LIX (Database observability) covers exporting dead-tuple counts as a series rather than reading them once.