MVCC: why an update writes a new row

Multiversion concurrency control is how PostgreSQL lets readers and writers run at the same time without waiting for each other. A write does not overwrite anything: it appends a new version of the row and stamps the previous version as ending at that transaction. Each transaction reads through a snapshot that decides which versions it is allowed to see. Readers never block writers, writers never block readers, and the price is that superseded versions stay on disk until something removes them.

Everything awkward about Postgres storage starts here

An update is physically an insert, so it costs roughly what an insert costs, including new entries in most of the indexes on the table. A delete makes the table larger before it makes it smaller. A count of rows cannot be answered from an index alone, because the index does not know which versions your snapshot can see. And a transaction that stays open holds a snapshot, which is what stops old versions being cleaned up anywhere in the cluster.

Those are not defects layered on top of the design. They are the design, and the compensating machinery, from vacuum through the visibility map to the update optimisation that avoids touching indexes, exists to pay for the property that reads are never blocked. Engines that overwrite in place and keep the old image somewhere else buy the same property with a different bill, usually paid at read time instead.

Looking at both versions on the page

Two system columns show the transaction that created a version and the one that ended it, and a heap page read directly shows the old version still sitting there.

CREATE EXTENSION pageinspect;
CREATE TABLE account (id int PRIMARY KEY, balance numeric);
INSERT INTO account VALUES (1, 100), (2, 250);
UPDATE account SET balance = balance - 25 WHERE id = 1;
SELECT ctid, xmin, xmax, balance FROM account ORDER BY id;
SELECT lp, t_ctid, t_xmin, t_xmax FROM heap_page_items(get_raw_page('account', 0));

CREATE EXTENSION
CREATE TABLE
INSERT 0 2
UPDATE 1
 ctid  | xmin | xmax | balance 
-------+------+------+---------
 (0,3) |  770 |    0 |      75
 (0,2) |  769 |    0 |     250
(2 rows)

 lp | t_ctid | t_xmin | t_xmax 
----+--------+--------+--------
  1 | (0,3)  |    769 |    770
  2 | (0,2)  |    769 |      0
  3 | (0,3)  |    770 |      0
(3 rows)

The row the query returns has moved from the first slot on the page to the third. The first slot is still occupied: its ending transaction is set, and it points forward at its replacement, which is how a session running under an older snapshot still finds the balance of 100. Two slots hold one visible row, and that is the state vacuum exists to resolve.

Living with the bill

The practical work is keeping the cleanup ahead of the churn and keeping transactions short enough that it is allowed to happen, which is the subject of autovacuum and table bloat. The single fact that explains most cases where cleanup runs and achieves nothing is the xmin horizon, and the optimisation that lets an update skip the index work is a HOT update.