Storage Layout: Pages, Tuples, TOAST and HOT

In one line

PostgreSQL stores each table as a heap of 8 KB pages; each page holds a header, an array of line pointers and tuples growing from the end. Values over ~2 KB are compressed and moved out-of-line to a TOAST table. HOT (heap-only tuple) updates keep new versions on the same page and skip index updates when no indexed column changed.

0/5 · 0%

Think of it like this

A notebook with numbered pages. Each page has an index of line numbers at the top pointing to entries written from the bottom up. Very long entries (a pasted essay) go into a separate appendix, with a note on the page saying where.

Key ideas

01
Hierarchy: cluster → database → schema → table (a heap file, split into 1 GB segments) → 8 KB pages → tuples. Each tuple has a 23-byte header (xmin, xmax, ctid, flags) plus alignment padding, so small rows have noticeable overhead.
02
ctid = (page, line pointer) is the physical address; indexes point to ctids. It changes on UPDATE, so never use it as an identifier.
03
TOAST: when a row exceeds ~2 KB, large variable-length values (text, jsonb, bytea) are compressed (pglz or lz4 since PG 14) and/or split into chunks in a side table. Reading a TOASTed column costs extra lookups; SELECT * fetches them even when unneeded.
04
HOT updates: if the new version fits on the same page and no indexed column changed, PostgreSQL chains it from the old tuple and doesn't touch any index. Leaving free space (fillfactor = 80–90) on frequently updated tables raises the HOT ratio.
05
Column order matters a little: alignment padding means ordering columns from 8-byte to 1-byte types can save a few bytes per row, which adds up at billions of rows.

Code & diagrams

page-anatomy.txttext

8 KB heap page
+---------------------------------------------------------------+
| PageHeader (24 B): LSN, checksum, lower, upper, flags         |
| ItemId[1] ItemId[2] ItemId[3] ...  (4 B each, grow down ->)   |
|                                                               |
|                 free space  (lower .. upper)                  |
|                                                               |
|          <- tuples grow up:  Tuple3 | Tuple2 | Tuple1         |
+---------------------------------------------------------------+
Tuple = HeapTupleHeader (23 B: xmin, xmax, ctid, infomask, ...) + null bitmap + data

hot.sqlsql

ALTER TABLE session SET (fillfactor = 80);   -- leave 20% free per page for HOT

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables ORDER BY n_tup_upd DESC LIMIT 5;
--  relname | n_tup_upd | n_tup_hot_upd | hot_pct
--  session |  9120331  |    8702114    |   95.4

SELECT pg_size_pretty(pg_table_size('document')) AS heap_plus_toast,
       pg_size_pretty(pg_relation_size('document')) AS heap_only;

Interview problem

The problem

Why do updates to `last_seen_at` hurt so much?

users (50M rows, 12 indexes) updates last_seen_at on every request. Writes are slow, WAL volume is huge and indexes bloat. last_seen_at is also indexed for an admin report. Explain and fix.

When it breaks

SELECT * on tables with large TOASTed columns

What you see

A list endpoint fetches 200 KB JSON documents per row it never displays; latency and network usage balloon.

Fix & prevent

Select only needed columns; move large blobs to a separate table or object storage.

Explain it without notes

What conditions make an update HOT, and why does it matter?

Practice

Measure the per-row overhead of a table with one int column by inserting 1M rows and checking pg_relation_size.

Trade-offs

↔
Lower fillfactor uses more disk but enables HOT updates; wide rows are convenient but increase TOAST and I/O.

Done when you can

I can describe page and tuple layout, TOAST and HOT, and tune a table for heavy updates.

Command Palette