Storage, Records & Pages

A table is a description; what exists on disk is a file of fixed-size pages, each holding records made of a header, a NULL bitmap, fixed-size values, offsets and variable-size bytes. Seven layers from `SELECT` to the SSD, and why the page in the middle is the unit everything else is measured in.

One row as the engine writes it: a header, a NULL bitmap, the fixed-size columns at computed positions, offsets to the variable-size columns, then their bytes. Then the two real layouts — PostgreSQL's heap tuple with `t_xmin`/`t_xmax`/`t_ctid`, and InnoDB's row with its hidden `DB_TRX_ID` and `DB_ROLL_PTR`.

Why the engine never reads a row: the file is `Page 0, Page 1, …`, page N is at byte N × 8192, and a record is addressed as (page, slot). Page reads, page writes, torn writes and checksums — and the cost model that follows: the page is the unit of I/O and of the buffer pool.

Variable-size records inside a fixed-size page: a slot directory growing down from the header, records growing up from the end, free space in the middle. Records move, slots stay — which is what keeps every index entry pointing at (page, slot) valid. Then fragmentation, compaction, and what happens when an update no longer fits.