# Pages, Files and the Buffer Pool — Database Fundamentals

Source: https://www.skillbyai.com/en/database-fundamentals/s-storage

> Describe how a DBMS stores rows on disk and caches them in memory.

## From rows to disk blocks

A DBMS stores tables in files divided into fixed-size **pages** (blocks), commonly 4 to 16 KB (PostgreSQL uses 8 KB, InnoDB 16 KB by default). A page holds a header, several **records** (rows) and a slot directory pointing to them, so rows can move within the page. A **heap file** stores rows in no particular order; finding a value without an index means a **full scan** of every page. Rows are identified by a **record ID** (page number plus slot). Since disk access is slow compared with memory, the DBMS keeps recently used pages in a **buffer pool** in RAM, managed by a replacement policy such as LRU variants or clock; a page changed in memory is **dirty** until written back. Reading a page already in the buffer pool is a hit; a miss requires I/O. Because I/O dominates cost, query performance is largely about **how many pages** a query must read, which is why indexes, good row widths and selective queries matter.

## Buffer pool between queries and disk

Queries read pages from memory when possible; misses fetch pages from disk into the buffer pool.

![A row of query arrows into a rectangle of page slots in memory, some slots shaded as dirty, connected below to a stack of disk platters.](assets/figures/database-fundamentals/section-7-map.svg) — Figure 7.1 — Pages cached in the buffer pool.

## Estimating I/O for a full scan

Why a missing index is expensive on large tables.

```text
table: 10,000,000 rows, average row 200 bytes, page 8 KB
rows per page   ~= 8192 / 200 ~= 40
pages           ~= 10,000,000 / 40 = 250,000 pages ~= 2 GB

full scan reads ~250,000 pages
B+ tree lookup reads ~3-4 index pages + 1 data page

if half the table is in the buffer pool, the scan still needs ~125,000 disk reads
```

## Wide rows cost reads

Fewer rows fit on a page when rows are wide, so scans read more pages. Keep large, rarely used data (long text, blobs) in separate tables or columns that the database can store out of line.

**Quiz:** What is the role of the buffer pool?

- [x] To cache disk pages in memory to reduce I/O
- [ ] To store backups
- [ ] To hold the SQL parser
- [ ] To encrypt data

*Answer:* To cache disk pages in memory to reduce I/O. The buffer pool keeps frequently used pages in RAM so most reads avoid disk I/O.