What is a database engine's buffer pool, and why does the engine maintain its own in-memory page cache instead of simply reading and writing files through the operating system?
answer
- frames = page-sized slots in shared memory
- hit vs miss, page table lookup
- pin = don't evict me, not a lock
- dirty = changed in RAM, log flushed first
- OS cache doesn't know index roots from scans
basics
~20 sA shared region of RAM holding fixed-size disk pages. All reads and writes go through it, so hot pages never touch disk. The engine caches itself because it knows access patterns and must control when a modified page may be written.
solid answer
~50 sThe buffer pool is a fixed-size region of shared memory divided into **frames**, each holding one disk page (commonly 4-16 KB). To read a row the engine locates its page: if the page is already in a frame that is a **hit**; otherwise it picks a victim frame, evicts it (writing it out first if it was modified), reads the page in, and that is a **miss**. Whoever uses a frame **pins** it so eviction cannot steal it, and unpins when done. Updates happen in memory and mark the frame **dirty**; the page is written back later by a background writer or at a checkpoint, and never before its log records are durable. The engine keeps its own cache because it knows things the OS does not: which pages are index roots versus one-off scan pages, which pages a transaction still has pinned, and the ordering constraint between log writes and data writes.
go deeper
Be able to say: shared RAM cache of disk pages, reads and writes go through it, hit vs miss, dirty means changed in memory.
Add the mechanics: page table lookup, pin/unpin, victim selection, write-ahead ordering, and why hits are orders of magnitude cheaper than misses.
Frame it operationally - working set versus pool size, what misses cost on your storage, and why the engine keeps its own cache instead of trusting kernel writeback.
Discuss it as a memory-budget and I/O-control decision: who owns writeback timing, direct I/O versus double caching, and the blast radius of getting the pool size wrong on a shared machine.
## What a buffer pool is A relational engine never fetches a single row from disk. Storage is organized into fixed-size **pages** (blocks) - typically 4 KB, 8 KB, or 16 KB - and the unit of I/O is an entire page. The **buffer pool** (buffer cache) is a large region of shared memory carved into **frames**, each frame exactly one page wide. It is *shared*: every session of the instance uses the same pool, so a page faulted in by one session is immediately cheap for all the others. Beside the frames the engine keeps bookkeeping structures: a hash table mapping page identity (file/tablespace + block number) to the frame that holds it, and per-frame metadata - a **pin count**, a **dirty flag**, a usage/reference counter used by the eviction policy, and a short-duration lock (latch) protecting the frame's bytes while they are read or modified. ## The path of one page access 1. The executor needs block 4711 of a table. It hashes the page identity and looks in the page table. 2. **Hit**: the frame is found. The engine pins it, latches it, copies out the tuple, unlatches, unpins. No I/O at all. 3. **Miss**: no frame holds it. The engine must free a frame - it runs the eviction policy to choose a **victim**. If the victim is dirty it must be written to disk first (and its log records must already be durable). The engine then issues a read of block 4711 into that frame, registers it in the page table, and proceeds as in the hit case. The cost difference is enormous: a hit is tens to hundreds of nanoseconds; a miss is tens of microseconds on NVMe and milliseconds on spinning disk. This is why the buffer pool is usually the single most important memory setting on a database server. ## Pinning A **pin** is a reference count saying "someone is using this page, do not evict it". It is not a lock: many readers can hold pins at once, and a pin says nothing about transactional visibility. Pins are held for the duration of an operation on a page, not for the duration of a transaction - a scan pins a page, reads the tuples it needs, and unpins before moving on. A frame with pin count greater than zero is simply not a candidate for eviction. Leaked pins are a real class of engine bug: they shrink the effective pool. ## Dirty pages and write ordering When a statement changes a row it does *not* write to disk. It writes a log record describing the change, modifies the page in its frame, and sets the frame's dirty flag. The page now differs from the on-disk copy. Two rules govern when that page may go back to disk: - **Write-ahead logging**: the log records covering a page's changes must be flushed to durable storage *before* the page itself is written. Otherwise a crash could leave a changed page on disk with no log record to explain or undo it. - **Nobody waits for the page write.** Commit durability is provided by flushing the log, not the page. The dirty page can drift in memory for minutes, absorbing many further updates, and is written out later by a background writer, by eviction pressure, or by a checkpoint. This is why an in-memory row can be updated a thousand times and produce one page write. ## Why not just use the OS page cache The operating system also caches file blocks, so it is fair to ask why the engine duplicates the effort. The engine has information the kernel cannot have: - **Semantics of the access.** It knows a page is a B-tree root touched by every query, or conversely that a page belongs to a one-shot sequential scan that should not displace hot data. - **Write ordering.** It must guarantee log-before-page. Through the OS cache, writeback timing is the kernel's decision; the engine would have to fsync constantly to regain control. - **Pinning.** The engine must be able to say "this page must stay resident and stable while I read a tuple from it". The kernel offers no such contract. - **Coordination with recovery, MVCC, and checkpoints**, all of which are expressed in terms of pages the engine owns. Most engines therefore run a real cache of their own and treat the OS cache as a secondary, dumber layer (some engines bypass it entirely with direct I/O to avoid caching the same page twice). ## What good looks like The headline metric is the **hit ratio**: reads served from the pool divided by total page reads. A healthy OLTP system usually sits very high (99%+) because the working set fits. But the ratio alone is a weak signal - it says nothing about *which* pages miss or how expensive each miss is - so it is read alongside physical read counts, I/O wait, and eviction/write-back activity.
- If a committed transaction's changed pages are still only in memory, how is the commit durable?Durability comes from the write-ahead log, not from the data pages. At commit the engine flushes the log records to stable storage; the dirty pages may stay in the buffer pool much longer. After a crash, recovery replays the log against the on-disk pages to reconstruct the committed state, so the page write can safely be deferred and batched.
- What is the difference between pinning a page and locking a row on that page?A pin is a short-lived, non-transactional reference count that only keeps the frame resident; many sessions can pin the same frame simultaneously and it is released as soon as the page access finishes. A row lock is a transactional construct held until commit or rollback and it controls concurrent modification, not residency. Confusing the two suggests no real model of the storage layer.
A workshop bench with a fixed number of slots. You fetch a heavy crate (page) from the warehouse, work on it at the bench, and mark it 'do not put away' (pin) while your hands are on it. Changes are chalked on a running notebook (the log) before the crate itself is carried back.
saying these in an interview costs you the question
- Saying the buffer pool caches rows or query results rather than fixed-size pages
- Claiming a commit must write the modified data pages to disk
- Treating a pin as a lock that provides isolation
- Assuming the OS page cache makes an engine-level cache redundant
- Believing a dirty page is written out immediately after the statement that changed it