Rillence
Login

← All bricks

PostgreSQL · Advanced

MVCC and VACUUM

See PostgreSQL MVCC through xmin, xmax, and ctid — then watch dead tuples accumulate and learn to reclaim that space with VACUUM and VACUUM FULL.

Every UPDATE and DELETE in PostgreSQL leaves behind a dead row version — an invisible ghost that occupies real disk space until VACUUM arrives. This course makes that behaviour tangible. You will query the hidden system columns xmin, xmax, and ctid directly, watch ctid change position after an UPDATE, see xmax filled in after a DELETE, and measure dead tuple counts rising in pg_stat_user_tables. Then you will run VACUUM and VACUUM FULL, compare the before-and-after sizes, and read the VERBOSE output line by line. By the end you will understand why PostgreSQL tables grow, when regular VACUUM is enough, and when VACUUM FULL is necessary — and what it costs.

What you'll build

  • Read xmin, xmax, and ctid from a live table and explain what each value means
  • Observe how an UPDATE creates a new row version and leaves the old one as a dead tuple
  • Track n_dead_tup rising in pg_stat_user_tables after repeated writes
  • Run VACUUM and verify dead tuples are removed without shrinking the file
  • Interpret VACUUM VERBOSE output to understand what the engine actually cleaned
  • Run VACUUM FULL and confirm the file size returned to the OS
  • Choose between VACUUM and VACUUM FULL based on locking requirements and bloat severity

Contents

  1. Isolated PostgreSQL Stand
  2. Start it
  3. Connect with psql
  4. Accounts and Orders Tables
  5. Events and Profiles Tables
  6. Seeding Accounts
  7. Seeding Orders
  8. Seeding Events
  9. Seeding Profiles
  10. Creating the Tables
  11. Loading Seed Data
  12. Single-Threaded Plans and Fresh Statistics
  13. Confirming the PostgreSQL Version
  14. Hidden System Columns
  15. Reading the System Columns
  16. UPDATE Writes a New Row Version
  17. The Row Moved
  18. A Second Dead Version
  19. Preparing a Row for Deletion
  20. DELETE Makes the Row Invisible, Not Gone
  21. Monitoring Table Health
  22. Clean Starting Point
  23. Simulating Write Traffic
  24. First Churn Run
  25. Dead Tuples Appear
  26. Scaling Up the Churn
  27. Two Thousand Dead Tuples
  28. Measuring the Bloat
  29. What VACUUM Does
  30. Running VACUUM
  31. Dead Tuples Gone
  32. File Size Unchanged After VACUUM
  33. Reading the VACUUM Report
  34. Rebuilding the Dead Tuple Backlog
  35. Interpreting the Verbose Report
  36. Append-Only vs Write-Heavy Tables
  37. A Separate Churn Script for Profiles
  38. Churning the Profiles Table
  39. Profiles Is Now Bloated
  40. File Size Reflects the Bloat
  41. VACUUM FULL Rewrites the Table
  42. The Table Was Rewritten
  43. Space Returned to the OS
  44. Four Tables, Three Stories
  45. Ranking Tables by Size
  46. Size in Perspective
  47. Long Transactions Block VACUUM
  48. Back to the Beginning
  49. Healthy Rows Look Like This
  50. All Tables Clean
  51. Tearing Down the Stand
  52. Production Monitoring Cheatsheet