Optimizing Postgres for write heavy workloads ft. Checkpoint and WAL configs
summary
Explains concrete knobs and measurements: autovacuum threshold defaults to 0.2 and often needs lowering to 0.02 or 0.002 for heavy writes; WAL tuning recommends checkpoint_timeout 5m default but 15–30m common and setting max_wal_size roughly 2–3× the WAL generated per timeout so time‑based checkpoints dominate. Recommends wal_buffers increase (16MB → ~128MB), enable WAL compression, watch full_page_writes (can raise WAL volume by an order of magnitude), and tune bgwriter (bgwriter_delay, lru_multiplier, bgwriter_lru_maxpages) to shift IO off critical paths.
AI-written summary · about
chapters (7)
- 0:00Introduction
- 1:41Making sure Postgres setup is good
- 3:31Writing performant applications for PG
- 6:07Optimizing writes in Postgres
- 8:10Autovacuum, Checkpointer, and Bgwriter
- 20:33WAL tuning parameters
- 24:04Tuning Background Writer
related talks
57mDurability in Postgres
34mTuning PostgreSQL to work even better
33mTuning PostgreSQL to work even better
35mUnderstanding and Optimizing commit sychronization for maximum performance
43mTuning PostgreSQL for High Write Workloads
31mPostgreSQL WAL and Commit Synchronization, Optimization Opportunities
33mPostgreSQL Observed—and Explained
25mScaling PostgreSQL Beyond Query Optimization