The Buffer Pool

The room every page has to pass through on its way in or out, and the rule that keeps a crash from lying about what was saved.

Before you start. Pages & Heap Files showed you that a table on disk is a pile of fixed 8 KB pages, and every page holds its rows behind a slot list at the top of it. This lesson puts a room between that pile and the program reading it, and asks what happens the day the room fills up.

The idea in 30 seconds

A table is stored as fixed 8 KB chunks called pages. Every read or write goes through the buffer pool, a fixed set of page slots in memory, never straight to disk.
A page is dirty if it has changed since it was read in, and pinned if someone is reading or writing it right this instant. Either can be true without the other.
“Evict whichever page was touched longest ago” fails on one sequential scan (reading a whole table start to end): it touches every page once and evicts everything anyone else was using.
The database Postgres picks what to evict with a clock sweep instead: each page slot has a small usage counter, and a pointer circling the slots counts each one down and evicts the first it finds at zero. No shared list to fight over.
The write-ahead log is a separate file recording every change in order. A changed page may never reach disk before the log entry describing that change does. That single ordering rule is what makes recovering from a crash possible at all.

Read the story · Skip to the numbers