Storage Layout
The cluster on disk (base directories, OIDs, relfilenodes, tablespaces, forks), the 8 KB page byte by byte, heap tuple headers with xmin, xmax, ctid and infomask bits, TOAST, the free space map and visibility map, fillfactor and HOT updates, and reading real pages with pageinspect.
Files, forks and the 8 KB page
-- where a table lives
SELECT pg_relation_filepath('accounts'); -- base/16384/24601 (database OID / relfilenode)
-- each relation has forks: 24601 (main), 24601_fsm (free space map), 24601_vm (visibility map)
-- files are split into 1 GB segments: 24601, 24601.1, 24601.2 ...
CREATE EXTENSION pageinspect;
SELECT lower, upper, special, pagesize FROM page_header(get_raw_page('accounts', 0));
SELECT lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid, t_infomask::bit(16)
FROM heap_page_items(get_raw_page('accounts', 0));A relfilenode changes when a table is rewritten (VACUUM FULL, CLUSTER, some ALTER TABLEs, TRUNCATE): the old file is dropped and a new one written, which is why those operations need an exclusive lock and double the disk space while they run. Tablespaces are directories elsewhere, symlinked from pg_tblspc.
Tuple headers, TOAST, FSM and VM
| header field | meaning |
|---|---|
t_xmin | transaction that inserted this version |
t_xmax | transaction that deleted or locked it (0 if live) |
t_cid | command id within the transaction (for seeing your own earlier statements) |
t_ctid | pointer to the newer version, or itself if current |
t_infomask | hint bits: xmin committed/aborted, xmax committed, has nulls, has varlena, HOT updated, ... |
Hint bits cache the commit status of xmin and xmax on the tuple, so later readers do not consult the commit log. Setting them dirties the page: the first SELECT after a bulk load can write a lot, which surprises people.
TOAST: when a row exceeds about 2 KB (TOAST_TUPLE_THRESHOLD), large variable-length values are first compressed (pglz, or lz4 if configured) and, if still too big, moved out of line into the table's TOAST table in ~2 KB chunks, leaving an 18-byte pointer. Selecting only the columns you need avoids detoasting big values at all.
Free space map (FSM, the _fsm fork) records approximately how much free space each page has, so inserts find room quickly. Visibility map (_vm) keeps two bits per page: all-visible (every tuple visible to everyone, so vacuum can skip it and index-only scans need not visit the heap) and all-frozen (no tuple needs freezing).
Fillfactor and HOT updates
CREATE TABLE accounts (id bigint PRIMARY KEY, owner_id bigint, balance_kobo bigint, updated_at timestamptz) WITH (fillfactor = 85); CREATE INDEX ON accounts (owner_id); -- do NOT index balance_kobo or updated_at if they change often 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 10;