The Buffer Pool

Everything before this point made lookups cheap in page reads: a B+ tree of height 3 plus one heap page is four reads whatever the table size. But a read is not free. A page in RAM is reachable in ~100 ns; a random 8 KB read from an NVMe SSD is ~100 µs, from a SATA SSD ~200 µs, from a spinning disk ~10 ms. Four reads per lookup at ten thousand lookups per second is 40,000 page reads per second — 4 seconds of SSD time per wall-clock second. The correct page is being found; fetching it is the bottleneck.

The same pages are wanted over and over. The root page of users_pkey is read by every lookup on users. The internal pages number a few dozen. Even the leaf and heap pages are skewed: in almost every application a small fraction of rows receives most of the traffic. Reading from storage a page that was read a millisecond ago is the waste to eliminate, and the tool is a cache.

The buffer pool is a contiguous region of RAM divided into frames, each exactly one page (8 KB in PostgreSQL, 16 KB in InnoDB). A page table — a hash map from (file, page number) to frame index — answers "is this page in memory, and where?" in O(1). Each frame has a small descriptor: the page it holds, a dirty bit, a pin count, and the replacement policy's state.

A hit: the page table finds the frame; the pin count is incremented; the executor gets a pointer into the frame. A miss: the buffer manager chooses a frame to reuse, writes it back if it is dirty, reads the wanted page from storage into it, updates the page table, and pins it. The pin is a promise: while any operation holds a pin, the frame is not a candidate for eviction, because evicting a page out from under a running executor node would hand it garbage. Unpinning happens when the operation is done with the page — a scan that forgets to unpin, or a cursor left open, quietly removes frames from the pool.

Writes never go to storage directly. An UPDATE changes bytes inside a frame and sets its dirty bit; storage still holds the old page. The dirty page is written back — flushed — later: when its frame is chosen as a victim, when a background writer cleans frames ahead of demand, or when a checkpoint writes every dirty page. A page updated a hundred times between flushes is written once. This is the write-back behaviour that makes the pool a write cache as much as a read cache, and it has a consequence: between the update and the flush, the only durable record of the change is the write-ahead log, which is why Write-Ahead Logging is not optional in a write-back design.

Eviction is what happens on a miss when no frame is free: the replacement policy picks an unpinned frame, the manager writes it if dirty, and the frame is reused. A miss on a clean victim costs one read; on a dirty victim, one write and then one read — twice the I/O, on the critical path of the query that missed. Background writers exist to move that write off the critical path. Which frame to pick is a question large enough for its own lesson: Buffer Replacement: LRU, Clock and Scan Resistance.

The working set is the set of pages a workload touches in a representative interval. If it fits in the pool, the hit rate climbs toward 100 % after warm-up and storage is barely involved; if it does not, the pool can only hold a fraction of it and the hit rate drops toward pool size ÷ working set, whatever the replacement policy does. A hit rate of 99 % sounds excellent, but at four page requests per lookup it still means one lookup in 25 pays for a storage read; 99.9 % is ten times better in latency, not 0.9 % better.

Sizing is therefore a question about the working set, not about the data. A 500 GB database whose active rows and index upper levels amount to 20 GB is happy with a 32 GB pool; a 50 GB database scanned uniformly is not helped much by a 40 GB pool. The signal to watch is the miss rate under load and, in PostgreSQL, EXPLAIN (ANALYZE, BUFFERS): shared hit versus read per query tells you where each plan node's time went. Cold-cache latency after a restart or a failover is the same plan with every request missing — expect it, and warm the pool deliberately if it matters.

The operating system already caches file blocks. Why duplicate it? Four reasons. Double buffering: a page read through the OS cache exists twice in RAM — once in the kernel's page cache, once in the database's pool — halving the effective memory. Control over write ordering: the write-ahead rule says a data page may not reach storage before the log records describing it; the kernel flushes dirty file pages in whatever order suits it, so the database must either hold pages itself and write them explicitly or open files with O_DIRECT to bypass the cache. Pinning: the kernel gives no way to say "keep this page in memory while I hold a pointer into it"; a page cache eviction under a running executor is a crash. Knowledge: the engine knows that this read is a sequential scan of a 40 GB table and should not evict the index root, that this page is a B+ tree root and will be needed again, that a sort's temporary pages will never be read twice. The kernel sees a stream of read() calls.

The cost is that the engine is now responsible for everything the kernel would have done: replacement, write scheduling, read-ahead, and the accounting that tells an operator whether the pool is the right size.

PostgreSQL keeps its pool in shared memory (shared_buffers, default 128 MB, commonly set to ~25 % of RAM) and reads files through the ordinary kernel page cache, so a page that misses in shared_buffers may still be served from RAM by the OS — a two-layer cache with double buffering as the price. effective_cache_size tells the planner how much of both layers to expect, and affects plan choice, not memory allocation. Each buffer has a descriptor with a usage count (0–5) for the clock-sweep policy, a pin count, and a dirty flag; the background writer and the checkpointer flush dirty buffers on their own schedules.

The pg_buffercache extension exposes the pool page by page: which relation, which block, whether it is dirty, and its usage count. Aggregating it shows what the pool actually holds — often a surprise, and the most direct evidence in a "should we buy more RAM" conversation. EXPLAIN (ANALYZE, BUFFERS) shows per plan node how many pages hit in shared_buffers and how many were read (from the OS cache or the disk — PostgreSQL cannot tell which).

InnoDB's buffer pool (innodb_buffer_pool_size, typically 70–80 % of RAM on a dedicated server, split into innodb_buffer_pool_instances to reduce lock contention) holds 16 KB pages and, with innodb_flush_method = O_DIRECT, bypasses the OS cache entirely — one copy of each page, and the engine alone decides what stays. Because InnoDB tables are clustered indexes, the pool holds the B+ tree that *is* the table; there is no separate heap.

The list is an LRU with a midpoint: the young sublist (5/8) holds pages accessed more than once, the old sublist (3/8) receives newly read pages at its head. A page is promoted to young only if it is accessed again after innodb_old_blocks_time milliseconds (default 1000) — so a scan that reads a page once, or reads it twice within a millisecond, cannot push the working set out. SHOW ENGINE INNODB STATUS reports the young/old split, the hit rate and pages made young, which is the direct measure of whether the midpoint is protecting the working set. The change buffer, the adaptive hash index and the doublewrite buffer also live in or beside the pool; they are covered in InnoDB Internals: Clustered Index, Buffer Pool, Redo, Undo, Locks.