Three page concepts stacked on each other
| Level | Size | Notes |
|---|---|---|
| Hardware page | 4 KB for historical reasons | Largest atomic write the device guarantees (36:39) |
| OS page | 4 KB by default (x86 legacy); huge pages of 2 MB or 1 GB available | Virtual-memory bookkeeping (38:18, 38:34) |
| Database page | Varies per system | Built on top of the OS page (38:51) |
Atomic write, defined in class
- All-or-nothing: the block either lands completely or not at all (
37:01,37:29). - Write one 4 KB block, get an acknowledgement, crash, come back — the block should be there. Write 8 KB split across two blocks and the hardware guarantees nothing about both landing: the first may be written and the second lost (
37:29,37:42,37:59). - Both the hardware and the OS will lie about durability — covered next class (
37:59,38:18).
Database page sizes by system
- SQLite: 4 KB, configurable down to 512 bytes (
38:51,39:07). - RocksDB and WiredTiger (MongoDB's storage engine): 4 KB (
39:07). - SQL Server and Postgres: 8 KB (
39:07). - MySQL: 16 KB by default (
39:07). - DB2 and other enterprise systems: page size settable per table, e.g. 64 KB for one table and 4 KB for another (
39:07,39:34). - Q&A: with OS huge pages you simply pack multiple database pages inside one OS page. Transparent huge pages — the OS silently converting pages — was "a disaster" and is covered later (
39:34,39:57).
Choosing a page size
- Depends on data structure and on workload; "there's no free lunch" (
40:29,41:41). - Larger pages (
41:41,41:59):- Good for read-heavy sequential scans — one fetch brings in a lot of rows, then rip through it.
- Bad for small updates: updating 1 KB inside a 64 KB page forces writing the entire 64 KB back, because block storage cannot write partial pages and the whole page must be consistent (
41:11).
- Smaller pages (
40:49,42:16):- Good for finer-granularity reads and many small writes — you still pay random-write cost but avoid dragging in useless data.
- Teaser for next week: if data were stored by column rather than by row, a page could hold a single column — a different design axis entirely (
42:30,42:42).