The heap & MVCC

The keystone

A table is a heap — an unordered pile of pages of tuples (row versions).

UPDATE/DELETE never overwrite. They write a new version and mark the old one expired. The old version survives until VACUUM reclaims it.

The derivation chain (memorize this)

never update in place → old versions linger as dead tuples → they occupy space → bloat → VACUUM/autovacuum reclaims → space reused.

versions kept inline → readers see an old one while writers add a new one → readers don't block writers.

Indexes

Every Postgres index is secondary — stored apart from the heap, pointing into it by ctid (page, slot). There is no clustered index.

CLUSTER reorders once and is not maintained on later writes.

Visibility lives in the heap, not the index → an index scan usually visits the heap; the visibility map lets an index-only scan skip it when a page is all-visible.

Postgres vs InnoDB

Aspect	InnoDB	Postgres
Table is…	clustered B+tree	unordered heap
UPDATE	in place	new version
Old versions	undo log	in the heap
Cleanup	purge	VACUUM
Clustered idx	one	none

See it in 3 commands (later)

SELECT ctid,* FROM t; → note the ctid.

UPDATE t SET …; then re-select — ctid changed = new tuple.

SELECT n_dead_tup FROM pg_stat_user_tables; → 1, then VACUUM t; → 0.