STORAGE · MVCC · VACUUM
Postgres never
overwrites a row.
A table is a file, that file is an array of 8 KB pages, and the row a SELECT returns is really one version stored inside one of those pages. Following that chain explains the ctid, what an index actually holds, why two connections can see different prices, and why a transaction left open for hours degrades the whole database.
- 01 FILEA table is one or more files on disk, split into 1 GB segments.↓
- 02 PAGEThe file is an array of 8 KB blocks: fixed size, computable offset.↓
- 03 LINE POINTERInside the page, a pointer says where each tuple begins.↓
- 04 TUPLEThe physical data: one version of the row, carrying xmin and xmax.
TABLEVERSION
01 / FROM FILE TO PAGE
A table is a file
of fixed-size blocks.
Postgres does not read individual rows from disk: it reads whole pages. That bureaucratic-sounding detail defines the cost of almost everything else.
SEE IT IN YOUR DATABASE
All of this is queryable from psql.
The file, the block size, the ctid, and the system columns are not hidden internals: any instance will show them to you.
- 01 · FILE
SELECT pg_relation_filepath('items'); - 02 · BLOCK
SHOW block_size; - 03 · VERSIONS
SELECT ctid, xmin, xmax, price FROM items; - 04 · HEALTH
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'items';
Six pieces of the storage model
File and segments
Every table and index has its own file, split into 1 GB segments inside the cluster directory.
8 KB page
The block is fixed: 8192 bytes by default, set at compile time. The engine reasons in pages, not rows.
Computable offset
Page N begins at N × 8192. Nothing is searched: the engine jumps straight there and loads the block.
Shared buffers
Pages are read and modified in shared memory; the disk receives the WAL first and the pages later.
TOAST
A value too large for a page is compressed and stored out of line, in an associated table.
WAL
Durability does not depend on writing the page: it depends on the change record reaching the log first.
02 / INSIDE ONE PAGE
The page carries
its own directory.
A page is not a flat block of rows. It has a header, an array of pointers that grows forward, and tuples written from the end backwards. Free space is what remains in between.
24 bytes: checksum, LSN, and where free space starts and ends.
pointers grow forward · tuples are written from the end
The ctid is the pair (page number, pointer index). (0,1) means page 0, first pointer. With that, finding a tuple is arithmetic rather than search.
The pointer is the indirection
Moving a tuple within its page touches nothing else: updating the line pointer is enough.
A ctid locates a version
It changes on every UPDATE and can change during VACUUM FULL or CLUSTER. Use it to inspect, never to identify.
Free space is managed
fillfactor reserves room on each page for future versions, and the free space map remembers where room is left.
RULEA ctid locates a version. The primary key identifies a row.
03 / THE HEAP AND ITS INDEXES
An index does not store rows.
It stores addresses.
The heap is the set of pages where tuples live. A B-tree index is a separate structure with its own pages, and its leaves hold a key plus the ctid it points to.
Ordered descent
From the root to the leaf, comparing keys at each level.
Key → ctid
The leaf does not hold the price: it holds the address of one version.
Page fetch
Postgres loads page 0, follows pointer 2, and only then reads the tuple.
Visibility is not in the index: it lives in the tuple. That is why an index scan almost always ends up reading the heap, and why the visibility map exists — it enables index-only scans once a whole page is visible to everyone.
04 / UPDATE DOES NOT OVERWRITE
Every change writes
a new version.
Postgres implements MVCC by keeping versions inside the same table. An UPDATE looks for free space, writes the new tuple, and marks the previous one as obsolete from a given transaction onward.
| CTID | XMIN | XMAX | PRICE | STATE |
|---|---|---|---|---|
| (0,1) | 712 | 964 | 10.00 | previous version · dead candidate |
| (0,2) | 964 | 0 | 20.00 | current version |
xmin is the transaction that created the tuple; xmax is the one that deleted it or made it obsolete by updating it, and stays at 0 while the version is current. They are system columns: readable, never writable.
INSERT
Writes a tuple with xmin set to your transaction and xmax at 0.If the chosen page has no room another is used, and if none has room the file is extended.
UPDATE
Writes a new tuple and sets xmax on the previous one.The old version keeps occupying space until vacuum recycles it.
DELETE
Erases nothing: it only writes xmax on the current version.Space is released later, once no transaction can still see that tuple.
05 / VISIBILITY
Two connections,
two valid truths.
When a query reaches the heap it may find several versions of the same row. Choosing which one to return is not a global decision: it depends on that transaction’s snapshot and on the commit status behind xmin and xmax.
Under Read Committed each statement takes a fresh snapshot, so two SELECTs inside one transaction can legitimately see different values. Under Repeatable Read the snapshot is taken at the first statement and does not move until the end.
06 / THE EXCEPTION: HOT
Not every UPDATE
touches the indexes.
The short version — “an UPDATE forces every index to be updated” — is the one most worth correcting. When no indexed column changes and the new version fits on the same page, Postgres writes a heap-only tuple: it chains the old pointer to the new one and the indexes stay untouched.
A new index entry
If an indexed column changed, or the page has no room, the new address must be written into every affected index.
A redirect inside the page
The old pointer redirects to the new one and the index keeps pointing at the same ctid. Pruning can also reclaim that space without waiting for a full vacuum.
No indexed column modified, plus free space on the same page. fillfactor reserves that space; indexing columns that change often disables the HOT path in practice.
07 / DEAD TUPLES, BLOAT, VACUUM
Nothing can be cleaned
while someone might read it.
An old version is dead only when no running transaction — and none that could start — can need it. That boundary is the horizon, and the oldest transaction still open sets it.
While T1 stays open, autovacuum can run, find the old versions, and be unable to remove them. The table and its indexes grow, scans read more pages, and latency rises without a single query having changed.
Six terms that show up in every incident
Dead tuple
A version no longer visible to any possible transaction; its space can be recycled.
Horizon
Set by the oldest open transaction. Replication slots and standbys with hot_standby_feedback hold it back too.
autovacuum
Runs on per-table thresholds; heavily written tables rarely do well on the global defaults.
VACUUM and VACUUM FULL
VACUUM recycles space inside pages; VACUUM FULL rewrites the table, returns space to the system, and takes an exclusive lock.
Visibility map
Marks all-visible pages: it enables index-only scans and saves work for the next vacuum.
Freeze
Vacuum also freezes old tuples to prevent wraparound of the 32-bit transaction counter.
08 / WHAT TO DO WITH THIS
From a mental model
to operational decisions.
Understanding pages, tuples, and MVCC pays off when it changes what you do in design and in daily operations.
- 01
Keep transactions short
Open, do the work, close. Never wait on human input or a network call with a BEGIN held open.
- 02
Watch the pending work
n_dead_tup in pg_stat_user_tables and the age of the oldest transaction in pg_stat_activity explain most bloat incidents.
- 03
Tune autovacuum per table
Hot tables are rarely served well by the global default thresholds.
- 04
Reserve room where updates happen
Lowering fillfactor on heavily updated tables makes the HOT path more likely.
- 05
Index deliberately
Every index on a frequently updated column cancels HOT and adds writes.
- 06
Never treat ctid as an identifier
It is a physical address: it changes on updates, VACUUM FULL, and CLUSTER.
DATABASE REVIEW
Questions for a database review
- 01What is the oldest open transaction right now?
- 02How many dead tuples do the most written tables hold?
- 03Does autovacuum keep up, or fall behind during peaks?
- 04Which indexes sit on frequently updated columns?
- 05Are inactive replication slots holding the horizon back?
- 06Did size on disk grow while the row count did not?
- 07Does any part of the code store or compare a ctid?
THE MODEL IN ONE LINE
Page, pointer, version.
Everything else follows.
The ctid, indexes pointing into the heap, two connections seeing different prices, bloat, and vacuum are not separate topics: they are one design decision seen from different angles. Postgres would rather write a new version than overwrite the previous one, and its entire operational model follows from that.
09 / SOURCES
Keep going
deeper.
- 01Videoyou won’t forget how postgres works after this
The video that sparked this note · Hussein Nasser ↗
- 02PostgreSQLDatabase Page Layout
Header, line pointers, and physical layout ↗
- 03PostgreSQLHeap-Only Tuples (HOT)
Updates that leave the indexes alone ↗
- 04PostgreSQLConcurrency Control: Introduction
Snapshots, xmin, xmax, and concurrency control ↗
- 05PostgreSQLTransaction Isolation
Read Committed, Repeatable Read, and Serializable ↗
- 06PostgreSQLRoutine Vacuuming
Dead tuples, horizon, freeze, and wraparound ↗
- 07PostgreSQLSystem Columns
ctid, xmin, xmax, and the other system columns ↗