← All posts

UPDATE Doesn't Update: xmin, xmax, and the Ghost Rows in Your Tables

Giovanni Martinez·July 7, 2026·5 min read
postgresqlmvccinternalsperformancevacuumbloatdatabase internals


Here's a statement that sounds wrong until you check: PostgreSQL has never updated a row in its life.

When you run an UPDATE, Postgres writes a brand-new copy of the row and quietly marks the old one as expired. When you run a DELETE, it doesn't remove anything — it just marks the row as expired. The old versions stay right where they are, on disk, taking up space, until something else comes along to clean them up.

This isn't a bug or a shortcut. It's the core design decision behind PostgreSQL's concurrency model — MVCC, multi-version concurrency control — and once you see the machinery, a whole family of production mysteries stops being mysterious: table bloat, why VACUUM exists at all, why a table that "only has 10 million rows" occupies 200GB, and why long-running transactions can quietly poison a database they never touched.

Let's look at the machine.

Every row carries its own visibility passport

Postgres stamps every row version (the internal term is tuple) with two transaction IDs:

  • xmin — the ID of the transaction that created this version
  • xmax — the ID of the transaction that expired it (zero if it's still live)

You can see them right now. They're hidden columns on every table:

CREATE TABLE accounts (id int PRIMARY KEY, balance numeric);
INSERT INTO accounts VALUES (1, 100);
SELECT xmin, xmax, ctid, * FROM accounts;

 xmin | xmax | ctid  | id | balance
------+------+-------+----+---------
  871 |    0 | (0,1) |  1 |     100

xmin = 871 is the transaction that inserted the row. xmax = 0 means no one has expired it. And ctid = (0,1) is the row's physical address: page 0, line pointer 1.

Now update it:

UPDATE accounts SET balance = 90 WHERE id = 1;
SELECT xmin, xmax, ctid, * FROM accounts;

 xmin | xmax | ctid  | id | balance
------+------+-------+----+---------
  872 |    0 | (0,2) |  1 |      90

Look at the ctid. The row moved — from (0,1) to (0,2). That's not the same row with a new value. It's a new tuple, created by transaction 872, living at a new physical address.

So what happened to the old one?

The ghost is still on the page

The old version didn't go anywhere. Your SELECT can't see it, but it's still physically sitting on page 0. The pageinspect extension lets you look at the raw page:

CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('accounts', 0));

 lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
  1 |    871 |    872 | (0,2)
  2 |    872 |      0 | (0,2)

There they are. Line pointer 1 is the original row — and notice its t_xmax is now 872. The UPDATE didn't touch the old tuple's data; it just stamped it with "expired by transaction 872" and pointed it at its successor. Line pointer 2 is the new version, alive and well.

One logical row. Two physical tuples. Run the update a thousand times and — absent cleanup — you'd have a thousand and one.

The visibility algorithm (the simplified version)

When your query scans a table, every tuple it touches goes through a visibility check against your transaction's snapshot — the set of transactions your query considers "already happened." Stripped to its essentials:

A tuple is visible to you if its xmin committed before your snapshot, and its xmax is either zero, aborted, or belongs to a transaction your snapshot can't see yet.

That single rule is why PostgreSQL readers never block writers and writers never block readers. There's no lock to fight over, because there's nothing shared to protect — your query reads the version that was current when your snapshot was taken, and a concurrent writer creates a new version rather than modifying the one you're reading. Two transactions can look at the same logical row and correctly see different data, and both of them are right.

That's the entire trade Postgres made: concurrency paid for with disk space.

The bill arrives later

Because here's the other half of the deal. Those expired tuples — dead tuples, in the vocabulary — don't clean themselves up. They sit on pages, get read into the buffer cache alongside live data, and inflate every sequential scan. Indexes keep entries pointing at them. A table with heavy update traffic can end up mostly dead: 10 million live rows swimming in 90 million ghosts, which is how "only 10 million rows" becomes 200GB and a full-table scan becomes a coffee break.

In the book I walk through a case study I call The Runaway Bloat Incident — an update-heavy workload, a cleanup process that couldn't keep pace, and a table that grew 12x with zero growth in live data — but the short version is: every MVCC database owes a debt, and the collector is called VACUUM.

Which raises the operational question that actually matters: who runs vacuum, when, and why does it sometimes... not? If you read last week's issue of Vacuum & Analyze, you already know autovacuum's wake-up math has a scale problem that specifically punishes your largest tables. In two weeks I'm publishing the full deep-dive on that threshold formula here on the blog — the MVCC mechanics above are the "why" behind that entire story.

Where this goes deeper

This post is the surface of Chapter 4 of my book, PostgreSQL Internals Mastery, Volume I: Foundations & Core Concepts. The chapter continues into the parts that didn't fit here: hint bits and why your first SELECT after a bulk load writes to disk, the visibility map and how it makes vacuum (and index-only scans) cheap, transaction ID wraparound and why Postgres will eventually force the issue, and the full bloat incident with the diagnostics used to catch it.

Volume I covers the whole foundation layer in the same style — process model, memory architecture, storage and pages, buffer cache, WAL — verified against PostgreSQL 18, with exercises and diagnostic queries per chapter. It's built for the engineer who knows SQL but wants to stop treating the database as a black box.

Get it on Leanpub → https://leanpub.com/postgresqlinternalsmastery

Volume II — Concurrency & Maintenance — is in progress now, and chapters like this one are exactly the ground it builds on. Newsletter subscribers hear about it first: thepostgresguy.com.

Let's go deeper than the docs.

— Gio

Vacuum & Analyze — bi-weekly PostgreSQL internals, no spam.