skip to content

Tuple Visibility & Snapshots

PostgreSQL stamps each tuple version with xmin and xmax and checks it against a snapshot and the commit log. Interviewers ask how a row is judged visible and why many savepoints slow things down.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

In PostgreSQL, does an UPDATE overwrite the row in place, and what happens to a table's size after you update every row?

level: juniorimportance: must knowfreq 62%

answer

  1. think versions, not cells
  2. the old image is not erased
  3. who still needs the old one?
  4. ctid moves on each update

basics

~20 s

No. A PostgreSQL UPDATE writes a complete new row version and stamps the old one's xmax with the updating transaction's ID; the old version stays until VACUUM reclaims it, so updating every row can roughly double the table.

solid answer

~50 s

PostgreSQL never edits a row's data where it sits. An `UPDATE` writes a new tuple holding every column, sets the old tuple's `xmax` to the updating transaction's XID and links the old version to the new one; the new tuple's `xmin` is that same XID. Snapshots taken before the commit keep reading the old version, which is why readers never wait for writers. The old version becomes dead only when no snapshot can still need it, and `VACUUM` (normally autovacuum) then marks its space reusable but does not, as a rule, hand it back to the operating system. So updating every row of a 10 GB table typically leaves a file of about 20 GB, half of it reusable free space. Each update also gives the row a new `ctid`, its physical address, which is why `ctid` must never be stored as a row identifier.

code

sql · 16 lines
sql
CREATE TABLE order_import_line (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku  text    NOT NULL,
  qty  integer NOT NULL
);
INSERT INTO order_import_line (sku, qty) VALUES ('A-100', 5), ('B-200', 2);

SELECT ctid, xmin, xmax, id, qty FROM order_import_line;
-- (0,1) | 1001 | 0 | 1 | 5
-- (0,2) | 1001 | 0 | 2 | 2

UPDATE order_import_line SET qty = 6 WHERE id = 1;

SELECT ctid, xmin, xmax, id, qty FROM order_import_line;
-- (0,2) | 1001 | 0 | 2 | 2
-- (0,3) | 1002 | 0 | 1 | 6   <- new version, new ctid, new xmin

go deeper

for a junior

Remember that an UPDATE writes a whole new row version and leaves the old one behind until VACUUM cleans it up. That single fact explains table growth and why ctid changes.

for a middle

Explain the xmin and xmax stamps an UPDATE writes, why readers keep seeing the old version, and why plain VACUUM marks space reusable rather than shrinking the file.

for a senior

Show you plan mass updates around dead tuples: batching, skipping no-op updates, leaving room for HOT, and checking that the freed space is reused rather than the file growing.

for a principal

Frame the append-new-version design as a trade: cheap rollback and non-blocking reads bought with write amplification and a permanent cleanup duty that capacity planning must budget for.

## What an UPDATE actually writes PostgreSQL keeps table rows in a **heap**: an unordered set of 8 kB pages, each holding **tuples**, the physical versions of rows. An `UPDATE` never edits a tuple's column data in place. For each target row it: 1. Finds the current version of the row that its snapshot can see. 2. Writes a **new tuple** containing every column, changed and unchanged, wherever the heap has room. 3. Stamps the old tuple's `xmax` with the updating transaction's ID (its **XID**) and links the old version to the new one, so a later updater can follow the chain. 4. Gives the new tuple an `xmin` equal to that same XID. A `DELETE` does only step 3: it sets `xmax` on the current version and writes nothing new. An `INSERT` does only steps 2 and 4. Both versions now sit in the table at once. Which one a query sees is decided by comparing `xmin` and `xmax` with the reader's **snapshot** and with the commit log, `pg_xact`. A transaction whose snapshot was taken before the update committed keeps reading the old version; later snapshots read the new one. Nobody overwrote what the earlier reader needs, so the reader never waits for the writer. Rollback is just as cheap. If the updating transaction aborts, nothing in the heap is undone: the new tuple's `xmin` now names an aborted XID, so it is invisible to everyone, and the old tuple's `xmax` names an aborted XID, so it stays visible. Both facts come from `pg_xact`. ## Why the table grows The old version cannot be removed when the `UPDATE` commits, because an open snapshot might still need it. It becomes a **dead tuple** only once no running transaction can see it, and then `VACUUM`, usually started by autovacuum, reclaims it. Reclaiming means *mark the space reusable*, not *give it back to the operating system*. | Moment | Live versions | Dead versions | Typical file size | |---|---|---|---| | Before: 10 GB table, default fillfactor 100 | 10 GB | 0 | 10 GB | | After `UPDATE ... SET flag = true` on every row | 10 GB | 10 GB | about 20 GB | | After a plain `VACUUM` | 10 GB | 0, space marked free | still about 20 GB | A plain `VACUUM` returns space to the operating system only when whole pages at the end of the file become empty and it can briefly take an exclusive lock. After a full-table update the newest versions usually sit at the end, so the file stays large and later writes fill the freed space. Shrinking the file needs a rewrite, which is a bloat-handling topic rather than a visibility one. ## The ctid is an address, not an identity Every tuple has a hidden system column `ctid`, a pair `(page, item)` such as `(0,3)`. It is the physical location of that **version**. Because each update writes a new version elsewhere, the logical row's `ctid` changes on every `UPDATE`, and `VACUUM FULL` moves rows too. The manual is explicit that a primary key, not `ctid`, identifies logical rows. Using `ctid` inside one statement, for example to delete duplicate rows, is fine; storing it for later is a bug. ## Consequences you plan around - **Every update costs a full row write**, even when one small column changed, so wide rows that are updated often are expensive. - **No-op updates are not free.** `UPDATE ... SET status = status` still writes a new version. Filter with `WHERE status IS DISTINCT FROM 'shipped'`, or attach the built-in trigger function `suppress_redundant_updates_trigger()`. - **Indexes usually get a new entry.** Unless the update qualifies as a heap-only tuple (HOT) update, a storage optimisation tied to page free space and fillfactor, each index on the table gets an entry pointing at the new tuple. - **Mass updates create mass dead tuples.** Large backfills are run in batches so autovacuum can keep pace and the freed space is reused instead of the file growing. - **Open snapshots pin old versions.** A transaction left open keeps every version its snapshot might read from being reclaimed. ## How this differs from in-place engines Some engines change the row where it stands and keep the prior image in a separate undo area that readers rebuild old versions from. PostgreSQL has no undo log: the prior image simply stays in the heap as an ordinary tuple. The trade is cheap rollback and cheap reads of old versions, paid for with table growth and the need for `VACUUM` to come back and clean up.

  • In PostgreSQL, does UPDATE order_import_line SET qty = qty, which changes nothing, still write a new row version?
    Yes. PostgreSQL does not compare the old and new values before writing, so every matched row gets a new tuple, WAL and possibly new index entries. Add a predicate such as `WHERE qty IS DISTINCT FROM 6` to skip unchanged rows, or attach `suppress_redundant_updates_trigger()` as a `BEFORE UPDATE` row trigger when the application cannot be changed.
  • What does a PostgreSQL DELETE do physically to the row it removes?
    It writes no new tuple. It only stamps the current version's `xmax` with the deleting transaction's XID. Until that transaction commits, and for any snapshot taken before the commit, the row stays visible; afterwards it is a dead tuple whose space `VACUUM` later marks reusable. The file does not shrink when the `DELETE` commits.

  • An UPDATE overwrites the changed column values inside the existing row on disk.
  • The old row version is deleted the moment the UPDATE commits.
  • ctid is a stable row ID you can store and use to find the row later.
  • A plain VACUUM gives all the freed space back to the operating system.
  • PostgreSQL keeps the previous row image in a separate undo log.
open as a page

In PostgreSQL, a row returned by SELECT xmin, xmax, ctid has a nonzero xmax; what do these columns mean, and why is it visible?

level: middleimportance: should knowfreq 44%

basics

~20 s

In PostgreSQL, xmin is the creating XID, xmax the XID that deleted, updated or locked the version, and ctid its address. A visible row keeps a nonzero xmax when that transaction is still running, rolled back, only locked it, or committed after your snapshot.

open as a page

Right after a bulk load into a PostgreSQL table, why does the first full scan dirty pages and generate WAL while later scans do not?

level: seniorimportance: should knowfreq 32%

basics

~20 s

Freshly loaded tuples carry only the loader's XID. The first reader checks pg_xact and caches the commit status as hint bits in each tuple header, dirtying every page; with data checksums on, PostgreSQL 18's initdb default, that also writes full-page images to WAL.

open as a page

Four PostgreSQL import workers each wrap every one of 5,000 order lines in SAVEPOINT and RELEASE SAVEPOINT inside one transaction, and the cluster slows to a crawl; why, and what fixes it?

level: seniorimportance: should knowfreq 42%

basics

~20 s

In PostgreSQL each writing savepoint gets its own subtransaction XID, and a backend caches only 64. RELEASE keeps the slot, so past 64 the cache overflows and other sessions' snapshots resolve XIDs through pg_subtrans, contending on SubtransSLRU.

open as a page

In PostgreSQL, SELECT pg_current_snapshot() returns 742:750:742,745; what do the three parts mean, and whose changes does that snapshot see?

level: middleimportance: nice to knowfreq 22%

basics

~20 s

The format is xmin:xmax:xip_list. XIDs below 742 had finished, 742 and 745 were in progress, 743, 744 and 746 to 749 had finished, and 750 or higher had not. Finished XIDs are seen only if pg_xact says they committed.

open as a page