Mastering PostgreSQL 12 - Third Edition

Optimizing the transaction log

Checkpoints happen automatically and are triggered by the server. However, there are configuration settings that decide when a checkpoint is initiated. The following parameters in the postgresql.conf file are in charge of handling checkpoints:

#checkpoint_timeout = 5min           # range 30s-1d#max_wal_size = 1GB#min_wal_size = 80MB

There are two reasons to initiate a checkpoint:

• We can run out of time or we can run out of space.
• The maximum time between two checkpoints is defined by the checkpoint_timeout variables.

The amount of space provided to store transaction logs will vary between the min_wal_size and max_wal_size variables. PostgreSQL will automatically trigger checkpoints in a way that the amount of space ...