Inside PostgreSQL: The Life of a Write and a Read

Postmaster and backends, the background workers, shared buffers and clock-sweep, heap page and tuple layout, TOAST, the WAL-before-data rule, full-page writes and torn pages, checkpoints and crash recovery, the commit path, the read path through planner, executor and visibility checks, and the tunables that matter when it hurts in production.

About 120 minutes · 16 exam questions · 11 interview questions · 2 code labs

What you will learn

  1. The process model: postmaster, backends and one shared-memory segment
  2. The background crew: who writes what, and what breaks when each falls behind
  3. Shared buffers and the clock-sweep replacement algorithm
  4. Anatomy of a heap page: header, line pointers and free space
  5. The tuple header: xmin, xmax, ctid and the infomask hint bits
  6. TOAST: what happens to a 60 KB jsonb value
  7. The life of a write: from UPDATE to bytes on disk
  8. Torn pages and full_page_writes: why the first change after a checkpoint is expensive
  9. Checkpoints and crash recovery: bounding the replay
  10. The commit path in depth: what each synchronous_commit level actually waits for
  11. The life of a read: parser, planner, executor, buffer lookup and visibility
  12. Where it hurts in production: symptoms, internal causes and tunables

Previous: Database Firefighting: Six Production Scenarios