Buffer Pool
The database's cache of disk pages. Replacement policy, dirty pages, and checkpoints.
Level: advanced
What is it?
A buffer pool is the database-managed memory cache for database pages read from persistent storage. It tracks page frames, dirty state, pin/reference information, replacement candidates, and coordination so many query operators can reuse pages safely.
Why does it matter?
Disk access is much slower than memory access. A buffer pool reduces physical reads, amortizes repeated work, and mediates the timing of page writes, but an undersized or polluted pool can cause churn.
Mental model
Treat the buffer pool as a bounded cache of fixed-size pages. A query pins pages it needs while using them; replacement can reuse unpinned frames, and dirty pages need proper writeback before their frames are reused.
How it works
- A page hit avoids a physical read when the page is already resident.
- Dirty pages contain changes not yet written to the main data file.
- Replacement policy selects eligible victim pages based on recency, frequency, or other workload signals.
- WAL or another recovery method constrains when dirty pages may safely reach storage.
- Pinning, latching, and transaction locks solve distinct problems: frame lifetime, in-memory consistency, and logical concurrency.
Steps
- A query requests a database page.
- The buffer manager finds it in memory or reads it into a free/victim frame.
- The query pins the page and synchronizes access while inspecting data.
- Dirty pages are written back according to recovery and checkpoint rules; unpinned frames can then be reused.
Real-world usage
- Explaining why repeated queries can be faster after warm-up.
- Diagnosing buffer cache hit rate, dirty-page pressure, and read amplification.
- Understanding the interaction between checkpoints, WAL, and storage.
Common mistakes
- Assuming a high cache hit rate alone proves queries are efficient.
- Evicting a page that is still pinned by an active operation.
- Confusing an in-memory page latch with a transaction-level row lock.
Interview questions
What is the difference between a buffer-pool hit and a cache hit at the application level?
A buffer-pool hit means the database page is resident in the DBMS cache; application caches may avoid the database query entirely.
Why can dirty pages not simply be discarded?
Their changes may be the only current copy of committed or uncommitted page state; the recovery and writeback protocol must preserve correctness before reuse.