Module 6 · Logging and recovery

Episode 43 Lab: Pull the Plug

7 min · SQL · Postgres

Title card of Episode 43: Lab: Pull the Plug

▶ This video is coming to YouTube soon.

A hands-on crash lab. We kill -9 Postgres in the middle of thousands of bank transfers and count what survives, measure recovery time and WAL replayed, then turn synchronous_commit off: much faster commits, and a crash right after COMMIT really does lose the newest ones, without corrupting anything. Plus why fsync = off is never the answer.

Chapters

  1. 0:00Intro
  2. 0:14The symptom: commits are slow
  3. 0:35Reproduce: kill -9 in the middle of the transfers
  4. 1:12Measure: what survived, and how fast
  5. 1:46Diagnose: COMMIT waits for the disk
  6. 2:23Diagnose: why half a transfer never shows
  7. 2:58What decides recovery time
  8. 3:29The fix: synchronous_commit = off
  9. 4:02Diagnose: the risk window
  10. 4:37Measure: crash right after COMMIT, three times
  11. 5:26Two honest caveats, and one setting to never touch
  12. 5:59Before / after
  13. 6:29Recap
  14. 6:55Next: Season Finale