Lesson 0001 · Storage & MVCC

The heap & MVCC

Postgres never updates a row in place — it writes a new version and leaves the old one behind. Get this one fact and VACUUM, bloat, and "every index is secondary" all derive themselves.

~9 minRetrieval quiz + InnoDB contrastCheat sheet: here

You already know InnoDB cold: the table is a B+tree clustered by primary key, an UPDATE edits the row in place, and the old image is tucked into the undo log so other transactions can still read it. Postgres makes the opposite choice at every step — and this first lesson is the keystone the whole course hangs off. Get it and a surprising amount derives itself: why a table can bloat when you only ever run UPDATE, why VACUUM and autovacuum exist at all, why Postgres has no clustered index, and why an index lookup often still has to touch the table.

The one idea

In Postgres a table is a heap: an unordered pile of pages holding row versions (called tuples). Concurrency is handled by MVCC — the database keeps multiple versions of a row so that each statement sees a consistent snapshot. — PostgreSQL Docs: "data consistency is maintained by using a multiversion model… each SQL statement sees a snapshot of data… as it was some time ago, regardless of the current state of the underlying data."

The consequence that changes everything: an UPDATE or DELETE does not overwrite or remove the old row. It writes a new version and marks the old one as expired. — PostgreSQL Docs: "In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row… the row version must not be deleted while it is still potentially visible to other transactions."

Why readers never block writers

Because the old version physically survives, a transaction that started earlier can keep reading it while a newer transaction writes a new version beside it. Neither waits on the other. — PostgreSQL Docs: "reading never blocks writing and writing never blocks reading." You met this exact guarantee in MySQL lesson 0005 — a plain SELECT is a lock-free snapshot. Same idea; Postgres just keeps the versions in the heap instead of reconstructing them from undo.

The first consequence: bloat

Now a beginner surprise becomes obvious instead of mysterious. Run a million UPDATEs against the same thousand rows and the table can grow to many times its logical size — every update left a dead tuple behind, and until VACUUM reclaims them the pages stay allocated. This is bloat, and it is a direct, derivable consequence of "never update in place." InnoDB's in-place model simply doesn't have this failure mode (it pays elsewhere, in undo-log pressure and purge).

The second consequence: no clustered index — and every index is "secondary"

InnoDB must keep the table physically ordered by primary key, because the table is that B+tree. A heap has no such order. Postgres will physically reorder a table for you with CLUSTER — but it is a one-time operation and is not maintained as new rows arrive. — PostgreSQL Docs: "Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered… no attempt is made to store new or updated rows according to their index order."

So there is no "the row lives in the primary-key tree" shortcut. Every Postgres index is a separate structure off to the side that points into the heap. — PostgreSQL Docs: "All indexes in PostgreSQL are secondary indexes, meaning that each index is stored separately from the table's main data area (which is called the table's heap in PostgreSQL terminology)." Contrast this sharply with what you learned in MySQL lesson 0001: InnoDB has exactly one clustered index (the table) and the rest are secondary. Postgres has zero clustered indexes — they are all secondary.

Postgres vs InnoDB — the keystone contrast

Check yourself

Answer from memory — the effortful recall is what builds retention. Feedback is immediate.

CREATE TABLE t (id int PRIMARY KEY, name text);
INSERT INTO t VALUES (42,'Ann');
SELECT ctid, id, name FROM t;          -- ctid = (page, slot): the tuple's physical address
UPDATE t SET name='Ann2' WHERE id=42;
SELECT ctid, id, name FROM t;          -- ctid CHANGED — it's a NEW tuple, not an edit
SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname='t';  -- 1 dead tuple
VACUUM t;                              -- reclaim it; n_dead_tup drops back to 0