Buffer Management
The page exists but reading it again is expensive: the buffer pool, hits and misses, dirty pages, pinning, LRU and Clock — then follow one read and one write through it.
A B+ tree finds the right page in four reads — but four SSD reads are 400 µs, and the same four pages are wanted ten thousand times a second. The buffer pool keeps recently used pages in RAM frames, maps page ids to frames with a hash table, tracks which frames are dirty or pinned, and evicts under its own rules instead of the operating system's.
When every frame is full, one page has to go. Least-recently-used is the obvious answer and it fails twice: its exact list costs a lock on every hit, and one sequential scan through a large table evicts the entire working set. Clock approximates LRU with a bit per frame; ring buffers, midpoint insertion and LRU-K keep a scan from flooding the pool.
`SELECT * FROM users WHERE id = 42` is nine stages from text to row: parse, plan, three index pages, the buffer pool decision on each, one heap page, one slot, one tuple. Where each page comes from — pool or storage — decides whether the query takes 4 µs or 400.
`UPDATE accounts SET balance = balance - 100 WHERE id = 42` finds the row like a read, then logs the change, modifies the page in memory, touches an index only if an indexed column changed, marks the page dirty, fsyncs the log at COMMIT — and writes the data page minutes later. The gap between COMMIT and that flush is where durability is decided.