Tuning database parameters related to buffering and checkpoints in PostgreSQL is often considered more magic than science. With regular snapshots of the background writer statistics, it's possible to bring a more formal iterative method to tuning adjustments:
- Start collecting regular pg_stat_bgwriter snapshots so you have a performance baseline. Every time you make a change, make sure to generate a new snapshot point after starting the server with the new values.
- Increase checkpoint_segments until most checkpoints are time-driven, instead of requested because the segment threshold has been crossed. Eventually, 90% or more should be time-based, and the avg_checkpoint_interval figure should be close ...
