Module 1 · Storage

Episode 7 The Buffer Pool

9 min · SQL · Postgres

Title card of Episode 7: The Buffer Pool

▶ This video is coming to YouTube soon.

Why does the second run of a query read nothing from disk? This episode builds a buffer pool frame by frame: the page table, a page fault step by step, dirty pages written back once, and pins that keep pages in place. Then it checks every claim in Postgres with pg_buffercache and EXPLAIN (BUFFERS).

Chapters

  1. 0:00Intro
  2. 0:1484 reads, then none
  3. 0:34Without a cache, every access is I/O
  4. 1:26Frames, the page table, and a hit
  5. 2:15Writes stay in memory: dirty pages
  6. 2:55A miss, step by step
  7. 4:15Pinned pages can't be evicted
  8. 4:46The code: fetch and unpin
  9. 5:25Inside Postgres's buffer manager
  10. 6:20Proof: 16,384 frames, a miss, then a hit
  11. 7:14Proof: dirty pages and pins
  12. 8:03Trade-offs
  13. 8:40Recap
  14. 9:06Next: Replacement Policies