PostgreSQL 10 High Performance - Third Edition

Checkpoint timing

Checkpoints are valuable because they reduce the amount of time required to recover from a crash, so you want to perform them as often as you can stand. However, the overhead of both the checkpoint writes and the subsequent full page writes to the WAL is expensive, which means you don't want checkpoints to happen often.

You can adjust how often checkpoints occur by modifying two database tunables that control the two primary ways by which a checkpoint starts:

• Each time a 16 MB WAL segment is filled, and a new WAL segment is allocated, the database counts how many WAL segments have been used since the last checkpoint. When this number reaches the max_wal_size tunable, another checkpoint is requested. Checkpoints triggered ...