Module 1 · Storage

Episode 9 Lab: I Deleted Half the Table and It Got Bigger

8 min · SQL · Postgres

Title card of Episode 9: Lab: I Deleted Half the Table and It Got Bigger

▶ This video is coming to YouTube soon.

We updated every row of a 100,000-row Postgres table, deleted half of them, and watched it grow from 5 MB to 11 MB. This lab reproduces it, shows the dead tuples page by page, then fixes it twice: VACUUM makes the space reusable (proved with pg_freespacemap), and VACUUM FULL shrinks the table to 3 MB under an ACCESS EXCLUSIVE lock that blocks every other query.

Chapters

  1. 0:00Intro
  2. 0:14The symptom
  3. 0:34Reproduce it
  4. 1:20Diagnose: UPDATE writes a second copy
  5. 2:18Diagnose: DELETE only marks
  6. 2:40Why the file can't just shrink
  7. 3:11Fix 1: VACUUM makes the space reusable
  8. 3:59Fix 1 on Postgres: VACUUM
  9. 4:29Fix 2: VACUUM FULL rewrites the table
  10. 5:08Fix 2 on Postgres: VACUUM FULL
  11. 5:19The price: ACCESS EXCLUSIVE
  12. 6:04Before and after
  13. 6:24Which fix, when
  14. 6:58Recap
  15. 7:25Next: B+ Trees: Search and Insert