Pages: The Unit of Everything

Open a PostgreSQL table file and there is nothing to see: no magic number, no directory, no record separators. It is Page 0, Page 1, Page 2, …, each exactly 8192 bytes, and the only way to know anything about it is to read a page and interpret its header. That emptiness is the design. A page id is an address — page 4821 starts at byte 4821 × 8192 = 39,493,632 — so getting to a page costs one multiplication and one pread, and appending a page costs writing 8192 bytes at the end. The file grows in page-sized steps and never has partial pages.

Because pages are the same size, the engine can plan around them without reading them: a table of 12,800 pages is 100 MB and a full scan is 12,800 sequential reads, before a single byte is fetched. That number, relpages, is in the catalog and is the first thing the planner looks at.

A record's address in the rest of the engine is never a byte offset in the file. It is a pair: page id and slot number — PostgreSQL's ctid, printed as (4821,3). To resolve it: compute the page's byte offset, read the page (or find it already in the buffer pool), read slot 3 of the slot directory to get the record's offset *within the page*, and follow it. Three steps, one I/O at most, and no searching.

The indirection through the slot is deliberate. If an index stored byte offsets, every record that moved — because a neighbour grew, because the page was compacted — would invalidate index entries all over the tree. With (page, slot), the page is free to shuffle bytes internally as long as slot 3 keeps pointing at the right record. Slotted Pages is entirely about that freedom.

The engine has exactly two operations against the file: read page N, write page N. A page read is a request for 8192 bytes at N × 8192 into a buffer-pool frame; after it, every record on the page is in memory whether it was wanted or not. SELECT name FROM users WHERE id = 42 reads one page and ignores ~100 records on it. A page write is the reverse — the whole frame back to N × 8192 — and is issued not when a row changes but when the buffer pool decides the dirty page must go (eviction, checkpoint). Ten updates to ten rows on the same page are one write.

This has a consequence people find counter-intuitive: the engine never reads a single row and never writes a single row. Cost is paid per page. A query that returns 5 rows from 5 pages costs the same I/O as one that returns 500 rows from 5 pages. And "small" updates are not small: changing one byte dirties 8192 and, after a checkpoint, logs all 8192 to the WAL as well.

The device does not know what a page is. An 8 KB page is two 4 KB blocks, and the storage stack promises atomicity per block at best. Lose power between the two halves and the page on disk is the new first half glued to the old second half: a torn page. Its slot directory may point at records that are not there any more; a record may be half of two versions. Nothing in the bytes says so.

A checksum in the page header is the detector. Computed over the page when it is written, verified when it is read, it turns silent corruption into a loud error — WARNING: page verification failed — at the cost of a few microseconds per I/O. PostgreSQL stores a 16-bit checksum in pd_checksum (opt-in at initdb --data-checksums, or pg_checksums later); InnoDB stores CRC-32C in the FIL header and repeats it in the trailer, so a torn page fails the trailer comparison even before the CRC.

Detection is not repair. To make a torn page whole again, the engine needs a complete good copy from somewhere: PostgreSQL writes a full-page image into the WAL the first time a page is modified after each checkpoint, and recovery restores that image before replaying the smaller changes; InnoDB writes each page to a doublewrite buffer and fsyncs it before writing it in place, so one of the two copies is always intact. Both are page-sized costs paid to survive a block-sized guarantee. Crash Recovery walks through the restart.

Once the page is the unit of I/O it becomes the unit of everything built on I/O. The planner estimates cost in page reads: seq_page_cost (1.0) for pages read in order, random_page_cost (4.0) for pages fetched by pointer, plus small per-row CPU terms. A sequential scan of 12,800 pages costs ~12,800; an index lookup costs ~4 random pages, ~16 units. Rows only enter as multipliers. EXPLAIN (ANALYZE, BUFFERS) reports the truth in the same unit: Buffers: shared hit=3 read=1 means four pages, three already in memory.

The The Buffer Pool is a cache *of pages*: a hash table from (file, page id) to a frame, with an eviction policy over frames. Its hit ratio is a ratio of pages. Its capacity — shared_buffers, innodb_buffer_pool_size — is a number of pages. The WAL records changes by (page id, offset, bytes). Locks on data are page latches around a tuple lock. Understanding the page means understanding what every one of those numbers counts.