Monitoring gross activity in the database buffer cache was very difficult until PostgreSQL 8.3, where the pg_stat_bgwriter view was introduced. This allows tracking the general flow of every data page buffer that goes into or out of the cache, along with statistics about the related checkpoint process responsible for much of that. Some questions you can answer with this data include the following:
- What percentage of the time are checkpoints being requested based on activity instead of time passing?
- How much data does the average checkpoint write?
- What percentage of the data being written out happens from checkpoints and backends, respectively?
It's possible to compute these numbers easily enough right from the view. The only additional piece needed is the block size needed to hold a single buffer, available as one of the...