Relational storage engines read and write their data files in fixed-size pages, commonly 4 to 16 KB, rather than fetching individual rows. Why is the page the unit of I/O and caching, and what follows from that choice?
answer
- hardware charges per request, not per byte
- offset equals page number times page size
- interchangeable buffer frames, no fragmentation
- rows per page drives scan cost
- larger pages: fewer requests, more waste and write amplification
basics
~20 sStorage devices and the operating system move data in blocks, and per-request overhead dominates, so reading one row would cost about the same as reading a whole page. Fixed size also makes addressing trivial: page number times page size gives the byte offset. Consequences: caching, logging and locking all work in page units, and reading one row pulls in its neighbours.
solid answer
~60 sTwo forces set the design. **Hardware and OS.** Disks and SSDs transfer in blocks, and the cost of a request is dominated by fixed overhead rather than by the bytes moved. Fetching a 200-byte row costs roughly what fetching 8 KB costs, so the engine may as well take the whole page and keep it. **Bookkeeping simplicity.** With uniform page size, page number N sits at offset N times page size: no directory lookup, no fragmentation of variable-size extents, and the buffer pool becomes a pool of interchangeable fixed-size frames that can hold any page. Consequences that show up in practice: - The buffer pool caches, evicts and pins whole pages, so cache hit ratios are about page locality, not row locality. - Rows physically near each other are effectively free once one is read, which is why clustering and sequential scans are efficient. - A row must fit within a page, so oversized values go off-page. - Wasted space inside a page (padding, low fill) directly inflates I/O and memory.
go deeper
State that hardware moves data in blocks and per-request overhead dominates, so the engine reads and caches whole fixed-size pages.
Add the addressing and allocation simplifications of a fixed size, and connect rows-per-page to scan cost and to the need for off-page storage of large values.
Discuss the page-size tradeoff explicitly (scan efficiency and index height versus internal fragmentation, latch granularity and write amplification) and how coarse caching hurts scattered point lookups.
Reason about workload fit: page size and row width as capacity-planning inputs, effective cache size under scattered access, and schema decisions such as splitting wide cold columns to raise page density.
## The page as the universal unit A relational engine stores each table's data in one or more files, and those files are carved into fixed-size **pages** (also called blocks). Typical sizes are 4, 8 or 16 KB, chosen at compile time or at cluster or tablespace creation and rarely changed afterwards. Every read from disk brings in a whole page, every write sends out a whole page, and the memory cache (buffer pool) is an array of frames each exactly one page long. ## Why not read a single row **Per-request cost dominates.** A read request costs a system call, a trip through the I/O stack, device queuing and, on spinning media, seek and rotational latency. Against that, transferring 8 KB instead of 200 bytes is nearly free. Even on NVMe, where seek time is gone, the device's own minimum transfer granularity and the per-command overhead mean small reads waste most of the operation. **Locality pays off.** Rows inserted around the same time, or clustered by a key, tend to be accessed together. Reading a page speculatively caches the neighbours; a range scan then walks many rows per I/O instead of one. **Alignment with the layers below.** The filesystem and device already work in blocks (commonly 4 KB). Reading a half-block still costs a full block underneath, so the engine gains nothing by being finer-grained and loses the ability to control what is cached. ## Why fixed size rather than variable Uniform size buys several simplifications at once. - **Address arithmetic.** The byte offset of page N is simply N times the page size. There is no map from page identifier to location, and identifiers stay small and stable. - **No external fragmentation.** Any free frame in memory can hold any page; any freed page in the file can be reused for any other page. Variable-size allocations would need best-fit or buddy allocation and would fragment over time. - **Simple recovery and replication.** Log records refer to page numbers, checksums cover a page, and torn-write protection can be defined against a known unit. - **Predictable memory accounting.** Buffer pool size divided by page size is the number of cacheable pages, which makes capacity reasoning straightforward. The cost of uniformity is **internal fragmentation**: leftover bytes in a page that are too small to hold another row. That waste is accepted because it is bounded and small relative to the simplicity gained. ## What the choice implies downstream **Rows must fit in a page.** A row larger than the page cannot be stored inline, so engines move oversized column values out of line into overflow storage and leave a small pointer behind. This is why a table with large text or binary columns behaves differently from a narrow one. **Row width sets rows per page.** A 100-byte row yields roughly 80 rows per 8 KB page; a 2 KB row yields four. A scan of the same number of rows therefore costs 20 times the I/O in the second case. Narrow tables scan dramatically faster, which is the physical reason behind advice like avoiding SELECT star on wide tables when only a few columns are needed, and behind splitting rarely-read wide columns into a side table. **Caching granularity is coarse.** If a workload touches one row per page across a huge table, the buffer pool fills with mostly-unwanted bytes and the effective cache size collapses. Random point lookups over a large table are expensive for this reason, not because index traversal is slow. **Page size is a tradeoff, not a free knob.** Larger pages amortize per-request overhead better and shrink index height, helping scans; they also waste more on partially filled pages, make the unit of locking or latching coarser, and increase write amplification, since changing one row rewrites the whole page (and, where full-page images are logged, writes more log too). Smaller pages do the reverse. Defaults in the 8 to 16 KB range are the industry's settled compromise for mixed workloads. **Everything else inherits the unit.** Checksums are per page. Free-space tracking is per page. Latches taken during a read or write are per page. Prefetching and readahead are expressed in pages. Buffer replacement policies count page references. When you read documentation about shared buffers, cache hit ratio, or blocks read, the number is always in pages. ## Summary The page exists because storage hardware charges per request rather than per byte, and it is fixed-size because uniformity makes addressing, allocation, caching and recovery all simpler. The visible consequence for anyone tuning a database is that I/O cost tracks pages touched, not rows returned.
- What changes if the page size is raised from 8 KB to 32 KB?Fewer, larger I/O requests, which helps sequential scans and reduces index height slightly, and less per-page header overhead relative to data. Against that, more space is wasted in partially filled pages, latching and buffer replacement become coarser-grained, and every single-row update now rewrites 32 KB, increasing write amplification and often log volume. It tends to help scan-heavy analytical work and hurt random-update OLTP.
- Why does a table with very wide rows scan so much more slowly than a narrow one holding the same number of rows?Because I/O is counted in pages. If rows are 2 KB, roughly four fit per 8 KB page, whereas 100-byte rows fit about 80. Scanning a million rows therefore touches around 250,000 pages in the first case and about 12,500 in the second. The row count is identical; the pages touched, and hence the time, differ by an order of magnitude.
Ordering delivery: the courier's fee is the same whether you order one item or fill the box, so you fill the box and keep the rest on the shelf in case you need it.
saying these in an interview costs you the question
- Claiming the engine reads exactly the bytes of the requested row from disk
- Believing page size can be changed freely on an existing database with no rebuild
- Assuming a bigger page is always better because it means fewer reads
- Thinking the buffer pool caches individual rows rather than whole pages