Pages & Heap Files

PAGES & HEAP FILES (01) -- how a row actually sits on disk the disk speaks in blocks; the engine invents the PAGE -- the single 8KB unit in which EVERYTHING (rows, indexes, free space) is read, cached, and written. 1 . WHY A PAGE AT ALL you cannot read one row from a disk. you read a block. so the engine deals ONLY in fixed 8KB pages: read(row 42) IMPOSSIBLE read(page 7) one I/O page 7 holds rows 380..441 8KB is a fork: bigger pages -> fewer I/Os for scans, more write amplification per tiny change. PG picked 8KB, InnoDB 16KB, SQLite 4KB. 2 . THE 8KB PAGE, X-RAYED (a real heap page of shop's users table) PAGE (8192 bytes) PAGE HEADER (24 B) pd_lsn (last WAL touching me!) . pd_checksum pd_lower / pd_upper . pd_special LINE POINTER ARRAY (4 B each, grows DOWN) lp1(off,len) lp2 lp3 lp4 ... FREE SPACE (the gap) TUPLES (grow UP from the bottom) tuple: header 23B + null bitmap + data t_xmin t_xmax t_ctid t_infomask t_hoff id=42 | email='g@..' | plan='pro' each COLUMN packed by alignment rules ...tuple 3, tuple 4 (~60 rows/page for a ~130-byte row) a row's ADDRESS = ctid (page#, line#) e.g. (7,2) = page 7, line pointer 2 the line pointer is an extra hop ON PURPOSE: the tuple can move inside the page (compaction, HOT chains) without every index changing -- indexes store the ctid, ctid points at the SLOT. pd_lsn is the page's WAL bookmark -- board 03 shows how recovery uses it ('is this page already newer than this WAL record? skip it'). t_xmin / t_xmax = which transaction CREATED / DELETED this row version. that pair IS MVCC -- board 05 runs on it. why 'SELECT * on wide tables hurts': the page holds FEWER fat rows, so the same query touches MORE pages = more I/O. columns you did not ask for still ride along. 3 . THE HEAP: a table is a BAG of pages pg 0 pg 1 pg 2 pg 3 pg 4 pg 5 users -> file '16384' on disk, split into 1GB segment files. page# * 8192 = byte offset. NO order: new rows go wherever the FREE SPACE MAP (FSM) finds room -- that is why it is called a HEAP. sidecar files: _fsm (free space per page) _vm (visibility map -- 'all rows on this page visible to everyone': fuels index-only scans, lets vacuum skip pages) 4 . TOAST: rows bigger than the page a tuple must fit in one page. a 2MB JSON blob does not. TOAST (The Oversized-Attribute Storage Technique): row: id | doc(2MB) 1. compress (pglz/lz4) pg_toast_16384 (a hidden side table) chunk_id | seq | 2KB slice chunk_id | seq | 2KB slice ... main row keeps only a POINTER (18 B) threshold ~2KB (TOAST_TUPLE_THRESHOLD). moral: fat columns cost you only when SELECTed -- another reason SELECT * is a tax. shop's session JSON lives in TOAST without you ever knowing. THE FORK: heap (Postgres) vs index-organized (InnoDB/MySQL) POSTGRES: rows live in an unordered HEAP + all indexes are equal citizens (all store ctid) + inserts are cheap (any page with room) - every index update on row move (fixed by HOT, board 04) - pk lookups take index -> heap hop (2 page reads) INNODB: the table IS the primary-key B-tree + pk range scans are sequential heaven + pk lookup = 1 structure - secondary indexes store the PK (double lookup) - random-uuid PKs shred insert locality (page splits everywhere) same SQL, different physics. this is why 'use sequential PKs' is InnoDB advice, and mostly noise on Postgres. THE ONE THING a table is not a spreadsheet -- it is a bag of 8KB pages, each a tiny self-describing filesystem (header, slot directory, free gap, records). every cost you will ever measure (I/O, bloat, index width, SELECT *) is really the question: HOW MANY PAGES DID I TOUCH?