Tuning using background writer statistics

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.

1. 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.
2. 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 to 5 minutes (or whatever you've set checkpoint_timeout to).
3. Increase shared_buffers by a large amount—25% or more. If your current value is less than 1/6th of the total RAM in a dedicated server, try a value of at least that amount...