The buffer pool is where the database spends its memory and where almost all of its latency is decided. It is also simpler than its reputation suggests: four data structures and a handful of rules, each of which exists to satisfy a constraint that a general-purpose cache does not have.

The frames, which are just memory

A fixed-size array of page-sized slots, allocated once at startup. Each frame holds one page at a time. The array is fixed because allocating and releasing memory during operation would fragment the address space and make the pool's footprint unpredictable.

Allocating up front also makes the pool's memory use an honest number. A database configured for sixteen gigabytes of buffer pool consumes sixteen gigabytes immediately rather than growing into it, which is less friendly on a shared machine and far easier to reason about, and the reasoning matters more than the friendliness.

The page table, which maps identity to location

A hash table from page identifier, usually a relation plus a block number, to the frame currently holding it. Every page access begins with a lookup here, so this structure is consulted more often than anything else in the engine.

Its access rate is what makes it a design problem rather than a detail. A system performing a million page accesses per second performs a million lookups in this table, every one of which must be safe against concurrent insertion and removal, and the obvious implementation with one lock becomes the system's bottleneck long before anything else does.

The per-frame metadata

Each frame carries state: which page it holds, whether that page has been modified since it was read, how many operations are currently using it, a latch protecting its contents, and whatever the replacement policy needs to make its decision.

This metadata is small but it is the whole of the pool's intelligence. The dirty flag decides whether eviction requires a write. The pin count decides whether eviction is permitted at all. The latch decides who may read or modify the page. The policy state decides who gets evicted. Four fields, four separate concerns.

The pin count and why it is a correctness mechanism

When an operator is reading a page it pins the frame, incrementing a count, and unpins it when finished. A frame with a nonzero count cannot be evicted no matter how strongly the replacement policy would prefer to evict it.

This is not an optimisation and it is worth insisting on the distinction. Evicting a pinned page would replace the bytes an executing operator is in the middle of reading, producing wrong results or a crash. The replacement policy expresses a preference; the pin count expresses a prohibition, and no generic cache offers a way to express a prohibition.

The dirty flag and the obligation it creates

A page modified in memory differs from the copy on disk, and the frame records that. Evicting a dirty frame requires writing it out first, which turns a cheap eviction into one that waits on storage.

The obligation propagates further than it first appears. Because write-ahead logging requires the log records for a modification to be durable before the modified page is written, evicting a dirty page may first require flushing the log, which means an eviction can wait on two storage operations in sequence. This is why a pool entirely full of dirty pages behaves badly.

The free list, and what happens when it empties

At startup every frame is free. As pages are read in, frames leave the free list. Once it is empty, every subsequent read must first evict something, so the pool transitions from a growth phase to a steady state where a read implies an eviction.

This transition is visible in a newly started database as a period of good performance followed by a step change, and it is the reason benchmark results from the first minutes of a run are worthless. Nothing is wrong; the pool simply stopped being able to accept new pages for free.

The latch on a frame, and why it is not a lock

A latch protects the physical consistency of a page's bytes while they are being read or written. It is held for microseconds, is not tracked for deadlock, and has nothing to do with transactions.

The distinction from a lock is one of the ones worth getting right early, because the vocabulary is overloaded and the concepts are unrelated. A lock protects logical data for the length of a transaction and is subject to deadlock detection. A latch protects bytes for the length of an instruction sequence. Conflating them makes concurrency discussions incoherent.

Reading a page, step by step

Look up the identifier in the page table. On a hit, pin the frame and return it. On a miss, find a victim frame, write it out if dirty, remove its old entry from the page table, read the wanted page into it, insert the new entry, pin it and return it.

Writing out that sequence makes the cost asymmetry obvious. A hit is a hash lookup and an atomic increment, measured in nanoseconds. A miss is an eviction decision, possibly a page write, possibly a log flush and certainly a page read, measured in hundreds of microseconds or more. A factor of a thousand separates the two paths.

Why the hit rate is the number that matters

Average page access latency is the hit rate times the hit cost plus the miss rate times the miss cost. Because the costs differ by a thousandfold, the average is dominated by the miss rate across the entire useful range.

The arithmetic is worth doing once. Moving from ninety-nine percent to ninety-nine point nine percent hits divides the miss count by ten and therefore divides average latency by nearly ten, while looking like a trivial improvement expressed as a percentage. This is why hit rate must be read as miss rate, and why a pool that appears to be performing well at ninety-five percent is doing five times the I/O of one at ninety-nine.

Prefetching into the pool

When an access pattern is predictable the pool can read pages before they are demanded, turning a sequence of waits into a stream. Sequential scans are the obvious case and index scans over ordered leaves are the other.

This is where the pool stops being a passive cache and becomes an active participant in query performance. A scan with effective prefetching runs at the device's sequential bandwidth; the same scan issuing one demand read at a time runs at one read per round trip, which on a remote volume can be fifty times slower. The difference is entirely in whether the pattern was recognised.

What the pool does not do

It does not understand rows, transactions or queries. It caches pages identified by number and knows nothing about their contents. Every piece of semantic knowledge lives above it.

That ignorance is deliberate and it is what makes the pool reusable across index pages, data pages, metadata pages and large object pages without special cases. It is also its limitation, since the pool cannot know that this page is an index root worth keeping forever and that one is a scan leaf about to become worthless, which is exactly the knowledge a replacement policy needs and cannot have.

What to take from the pool's structures

Three things. The pool is four structures, frames, a page table, per-frame metadata and a free list, and each piece of per-frame metadata exists for a distinct reason. The pin count is a prohibition rather than a preference, which is the constraint that rules out using a generic cache. And a hit and a miss differ in cost by about a thousandfold, which is why hit rate should always be read as miss rate, where a change from ninety-nine to ninety-nine point nine percent is a tenfold improvement rather than a trivial one.