Before understanding the buffer pool, you need to understand what the database is actually reading and writing. This is the physical layer that everything else builds on.
Pages: The Atomic Unit of I/O
A database does not read or write individual rows. It reads and writes pages (also called blocks). A page is a fixed-size chunk of data — typically 8 KB in PostgreSQL, 16 KB in MySQL InnoDB.
Think of a page like a physical page in a book. Even if you only want to read one sentence, you still have to turn to the whole page. Similarly, even if you only want one row, the database reads the entire 8 KB page that contains it into memory.
Heap Files: Where Rows Live
Table data is stored in heap files — a collection of pages stored on disk. The word "heap" here means unordered: rows are not stored in any particular order by default. When you insert a row, it goes into the first available free space in any page.
Each page has a standard layout:
- Page header: Metadata about the page (free space, checksum, LSN for WAL correlation).
- Item pointers (line pointers): An array of (offset, length) pairs pointing to where each row tuple starts within the page.
- Row data (tuples): The actual row data, stored from the bottom of the page upward.
- Free space: The gap between item pointers (growing down) and row data (growing up).
Why This Matters for DBREs
Understanding pages explains many real-world phenomena:
- Page bloat: A page with many deleted rows has lots of free space but the page still occupies disk. Vacuum reclaims this.
- Page splits: When a page is full and a new row must be inserted, the database may split the page, causing write amplification.
- FILLFACTOR: PostgreSQL lets you configure pages to only be filled to, say, 70% capacity, leaving room for UPDATEs to land on the same page (avoiding page splits and keeping HOT updates possible).