Checkpoints

checkpoint_segments

Parameter description: Specifies the minimum number of WAL segment files in the period specified by checkpoint_timeout. The size of each log file is 16 MB.

Type: SIGHUP

Value range: an integer. The minimum value is 1.

Default value: 64

Increasing the value of this parameter speeds up the export of big data. Set this parameter based on checkpoint_timeout and shared_buffers. This parameter affects the number of WAL log segment files that can be reused. Generally, the maximum number of reused files in the pg_xlog folder is twice the number of checkpoint segments. The reused files are not deleted and are renamed to the WAL log segment files which will be later used.

checkpoint_timeout

Parameter description: Specifies the maximum time between automatic WAL checkpoints.

Type: SIGHUP

Value range: an integer ranging from 30 to 3600 (s)

Default value: 15min

If the value of checkpoint_segments is increased, you need to increase the value of this parameter. The increase of them further requires the increase of shared_buffers. Consider all these parameters during setting.

checkpoint_completion_target

Parameter description: Specifies the target of checkpoint completion, as a fraction of total time between checkpoints.

Type: SIGHUP

Value range: 0.0 to 1.0. The default value 0.5 indicates that each checkpoint must be completed within 50% of the checkpoint interval.

Default value: 0.5

checkpoint_warning

Parameter description: Specifies a time in seconds. If the checkpoint interval is close to this time due to filling of checkpoint segment files, a message is sent to the server log to increase the value of checkpoint_segments.

Type: SIGHUP

Value range: an integer (unit: s). 0 indicates that warning is disabled.

Default value: 5min

Recommended value: 5min

checkpoint_wait_timeout

Parameter description: Specifies the longest time that the checkpoint waits for the checkpointer thread to start.

Type: SIGHUP

Value range: an integer ranging from 2 to 3600 (s)

Default value: 1min