Tuning with background writer statistics

In this recipe, we will be discussing how PostgreSQL's background writer (bgwriter) process plays an important role in fine tuning PostgreSQL memory and checkpoint related information.

Getting ready

The bgwriter process is a mandatory background process in a PostgreSQL instance. Its main responsibility is to flush the buffers from memory to disk. Besides, it will also keep the shared buffers ready by flushing the least used dirty buffers from memory to disk, based on bgwriter_lru_maxpages, bgwriter_lru_multiplier parameter settings. In PostgreSQL, checkpoint activity is a heavy process, as it is going to flush many dirty buffers into the physical disk, which will increase a great utilization in I/O.

When an explicit/implicit checkpoint is invoked, the bgwriter process will do most of the work. The pg_stat_bgwriter catalog view will show the cumulative statistics of the bgwriter and checkpoint statistics. If we can store this statistical information into...