Module 1 · Storage

Episode 3 Files, Pages and Heap Files

10 min · SQL · Postgres

Title card of Episode 3: Files, Pages and Heap Files

▶ This video is coming to YouTube soon.

How a table becomes a file: fixed 8 KB pages, rows in no order, every row's (page, slot) address, and Postgres's free space map finding room for the next insert. Every number is checked against the real customers table with pg_freespace and ctid.

Chapters

  1. 0:00Intro
  2. 0:15Every row has an address
  3. 0:37Why not one long file of rows?
  4. 1:50The customers file: 84 pages
  5. 2:40A heap file in four rules
  6. 3:14Finding room: the free space map
  7. 4:52Deletes free nothing until VACUUM
  8. 5:48The code: insert into a heap
  9. 6:25Proof: the customers file in Postgres
  10. 8:08Three ways to track free space
  11. 8:38Trade-offs: where rows live
  12. 9:10Recap
  13. 9:40Next: Slotted Pages