PostgreSQL Checkpoint Settings

Today in the study of checkpoint process problems, by the way review the checkpoint set up the problem, there are new doubts.

Checkpoint also known as checkpoint, the occurrence of checkpoint in Oracle means that the previous dirty data is all written back to disk, and the database achieves consistency and data integrity. Oracle performs transaction roll-forward with the closest checkpoint as the reference point when implementing media recovery. Checkpoint plays the same role in PostgreSQL: Write dirty data, and complete database integrity checks.

Checkpoints related parameters:
Checkpoint_segments:
The maximum number of WAL logs, the system default value is 3. The larger the value, the greater the amount of data processed when the media recovery is performed, and the longer the time.
Checkpoint_timeout:
The system automatically executes the maximum time interval between checkpoint, and the longer the media is recovering at the same interval. The system default value is 5 minutes.
Checkpoint_completion_target:
This parameter represents the completion target of the checkpoint, the system default value is 0.5, which means that each checkpoint needs to be completed within 50% of the checkpoints interval.
Checkpoint_warning:
The system default value is 30 seconds, and if the actual interval of checkpoints is less than this parameter, a related information is written to the server log. You can write to disable information by setting it to 0.

Checkpoint execution Control:
1, when the data volume reaches checkpoint_segments*16m, the system automatically triggers;
2, when the time interval reaches checkpoint_timeout parameter value;
3, when the user issues the checkpoint command.

Checkpoints parameter adjustment:
The correct and appropriate parameter values can always benefit the system, and a reasonable configuration of the checkpoints parameter not only reduces the system IO write blocking, but also reduces the pressure on the system when the IO peak.
The amount of data written by the system can be estimated by observing the log written by the checkpoint_warning parameter: In general, the value of checkpoint_warning parameter is less than checkpoint_timeout;
Estimating formula: checkpoint_segments*16m* (60s/m)/checkpoint_warning= Approximate amount of data per minute, the amount of data written per minute (all of which are estimates, based on a reasonable set of warning parameters).

　　--Why should we remove checkpoint_warning?

Reasonable configuration: Checkpoint_segments*16m*checkpoint_timeout (m) is slightly larger than the above value.

　　Why take checkpoint_timeout, should not be apart?

With the above formula as the basis, the configuration of Checkpoint_segments and Checkpoint_timeout, two parameters should be as far as possible to balance a large enough and small enough value.
In the case of unusually high data volumes, the relationship between disk bandwidth and the amount of data in checkpoint should be considered.

Personal Opinion:
If the checkpoint_segments parameter is the threshold, you can calculate the bandwidth required at peak (seconds): checkpoint_segments*16m/(checkpoint_warning*checkpoint_completion _target).
　　

If the Checkpoint_timeout parameter is the threshold value, the checkpoint_warning value is preferably greater than or equal to checkpoint_timeout, and the amount of data written by the Monitoring System data dictionary is total.
total/(checkpoint_timeout*checkpoint_completion_target) Gets the IO bandwidth requirements.

　　-This is easy to understand.

　　

PostgreSQL checkpoint settings (RPM)