The Speed Gap
Here is the fundamental physical problem databases must solve:
- CPU and RAM (Memory): Incredibly fast — nanosecond access times — but volatile. Data is lost when power goes out.
- Disk (Storage): Persistent, but orders of magnitude slower. An SSD read takes microseconds; a spinning disk read takes milliseconds. RAM is still 10–100x faster than even the best NVMe SSD for random access.
If a database had to read from disk for every single query, it would be uselessly slow. A busy OLTP system might execute thousands of queries per second — each touching dozens of pages. Reading all of those from disk on every request is physically impossible at the required speed.
The Solution: The Buffer Pool
To bridge this gap, the database designates a large chunk of RAM as a Buffer Pool (called shared_buffers in PostgreSQL, innodb_buffer_pool_size in MySQL). This is a cache of database pages.
Reading data:
1. The database checks if the requested page is already in the buffer pool.
2. Cache Hit: The page is in RAM — return it instantly. No disk I/O.
3. Cache Miss: The page is not in RAM — read it from disk, load it into the buffer pool, then return it. This is slow, but subsequent reads of the same page will be fast.
Writing data:
1. The database finds the page in the buffer pool (loading it from disk if needed).
2. It modifies the page in RAM. This page is now called a dirty page — it has been changed in memory but not yet written back to disk.
3. A background process called the checkpointer periodically flushes dirty pages to disk.
Buffer Pool Eviction: The Clock Sweep
The buffer pool has finite size. When it's full and a new page needs to be loaded, something must be evicted. PostgreSQL uses a clock sweep algorithm (a simplified LRU variant). Pages that have been accessed recently get a "usage count" that protects them from eviction. Cold pages (not recently accessed) are evicted first.
Key DBRE insight: If your buffer pool is too small, you'll have a high cache miss rate, causing constant disk I/O and slow queries. Monitoring the buffer hit ratio (percentage of page reads served from RAM, computed from pg_statio_user_tables) is a fundamental DBRE task. A healthy OLTP system should have a hit ratio above 99%; a result below 95% is a warning sign that shared_buffers may need to be increased, or that a query is doing unnecessary full table scans.