Postgres UPDATE is really an insert plus a delete: how MVCC and table bloat work

An UPDATE that never touches the old row

The intuitive mental model of a database update — find the row, seek to the column, overwrite the bytes — is not how PostgreSQL works. According to a detailed walkthrough published on dev.to, Postgres never modifies a row in place. Instead, every UPDATE is split into two operations: a new physical copy of the row is written as though it were an INSERT, and the original row is left untouched on disk but marked as expired, which amounts to a soft delete.

The author traces this with a concrete example. A row inserted by transaction 100 lands at physical address (0,1) with xmin set to 100 and xmax set to 0. When transaction 105 later changes a column, Postgres writes a completely fresh tuple at (0,2) carrying xmin 105, and then stamps the old tuple: its xmax becomes 105 and its t_ctid field is rewired to point forward to the replacement at (0,2). Both versions of the row now sit on the same disk page at the same time.

What sits inside an 8KB page

The walkthrough grounds the mechanism in storage layout. Tables are divided into fixed 8KB blocks called pages. Each page carries a 24-byte header, an array of 4-byte line pointers that grows downward from the top, and the actual tuple data, which grows upward from the bottom, with free space between them.

Every tuple is prefixed by a 23-byte header holding metadata most SQL users never see: t_xmin, the transaction ID that created the version; t_xmax, the transaction ID that deleted or replaced it (zero while it is still live); t_ctid, the tuple's own physical location or a forward pointer to a newer version; and infomask bit flags recording commit status and Heap-Only Tuple properties.

Snapshots instead of read locks

The payoff for this seemingly wasteful design is concurrency without locking. A long-running transaction that started before transaction 105 — the article uses transaction 102 running a report — evaluates the old tuple and finds that its xmin committed before the transaction's snapshot began, while the xmax of 105 is not yet visible to it. It therefore reads the old version cleanly, with no shared lock taken on the row.

A transaction that starts after 105 commits sees the opposite: the old tuple's xmax belongs to a committed transaction, so that version is dead. It follows the forward pointer to the new tuple and reads the updated data. Aborts are handled just as cheaply. If transaction 105 had rolled back, Postgres would simply record the abort in the pg_xact commit log, instantly making the new copy invisible and the old copy live again — no undo log to apply.

The secondary index problem

The cost of this design shows up in indexing. Unlike MySQL's InnoDB, where secondary indexes reference the primary key, Postgres indexes store direct physical pointers (ctids) to heap tuples. The article sketches a table with five indexes; if updates naively created index entries for every new row version, changing a single column would require writes to all five B-Trees, even though four of the indexed values never changed. On a large, write-heavy table, the article warns, that means ballooning index writes, B-Tree page splits, and heavy disk I/O.

This is the problem Heap-Only Tuples (HOT) were designed to blunt: the mechanism the walkthrough builds toward as the way Postgres avoids touching indexes when an update does not need to.

Why it matters

Understanding that an UPDATE is physically an insert plus an expiration explains several everyday Postgres behaviors. Dead row versions accumulate until space is reclaimed, which is why write-heavy tables bloat and why routine maintenance like vacuuming matters. It explains why indexes on frequently updated tables degrade and grow over time. And it reframes practical decisions — how many indexes to build, which columns to index, and how often rows get rewritten — as decisions with direct physical consequences. The dev.to piece is a useful reminder that concurrency guarantees in Postgres are not free: they are paid for in disk pages, and knowing the bill is itemized this way makes performance problems far easier to diagnose.