Checkpoints

checkpoint_segments

Parameter description: Specifies the maximum number of WAL files that can be retained in the period specified by checkpoint_timeout. The size of each log file is 16 MB.

Parameter type: integer.

Unit: none

Value range: 1 to 2147483646

Default value: 1024

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value. Increasing the value of this parameter speeds up the import of a large amount of data. Set this parameter based on checkpoint_timeout and shared_buffers. This parameter affects the number of WAL files that can be reused. Generally, the maximum number of reused files in the pg_xlog folder is twice the value of checkpoint_segments. The reused files are not deleted and are renamed to the WAL segment files which will be later used.

Risks and impacts of improper settings: If the value is too small, checkpoints are frequently triggered. If the value is too large, the RTO may be increased, the number of WAL files to be retained increases, and more disk space is occupied.

checkpoint_timeout

Parameter description: Specifies the maximum time between automatic checkpoints.

Parameter type: integer.

Unit: second

Value range: 30–3600

Default value: 900 (that is, 15 minutes)

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value. If the value of checkpoint_segments is increased, you need to increase the value of this parameter. The increase of these two parameters further requires the increase of shared_buffers. Consider all these parameters during setting.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

checkpoint_completion_target

Parameter description: Specifies the ratio of the estimated completion time of each checkpoint to the value of checkpoint_timeout.

Parameter type: floating point

Unit: none

Value range: 0.0 to 1.0

Default value: 0.5

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

checkpoint_warning

Parameter description: If the checkpoint interval is less than the value of this parameter because the checkpoint file is written, a warning message is written, indicating that the value of checkpoint_segments needs to be increased.

Parameter type: integer.

Unit: second

Value range: 0 to 2147483647. 0 indicates that the warning is disabled.

Default value: 300

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

checkpoint_wait_timeout

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

Parameter type: integer.

Unit: second

Value range: 2 to 3600

Default value: 1min

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_incremental_checkpoint

Parameter description: Specifies whether to enable incremental checkpoint. This parameter must be enabled for the multi-tenant database feature (enable_mtd).

Parameter type: Boolean.

Unit: none

Value range:

• on: The incremental checkpoint function is enabled.
• off: The incremental checkpoint function is disabled.

Default value: on

Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Setting suggestion: You are advised to disable this function when the memory is small or the disk performance is poor. In other scenarios, you are advised to enable this function by default.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

When enable_incremental_checkpoint is disabled and the checkpoint is manually executed, full_page_writes is used to generate the first Xlog. When backup and restoration are triggered, the log containing "xlog contains no data" may be generated.

enable_double_write

Parameter description: Specifies whether to enable dual-write feature. When the incremental checkpoint function enable_incremental_checkpoint is enabled and enable_double_write is enabled, the enable_double_write dual-write feature is used for protection and full_page_writes is not used to prevent partial page writes.

Parameter type: Boolean.

Unit: none

Value range:

• on: The dual-write function is enabled.
• off: The dual-write function is disabled.

Default value: on

Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Set this parameter to on in incremental checkpoint mode.

Risks and impacts of improper settings: Partial page writes may occur when this parameter is disabled and the incremental checkpoint function is enabled. As a result, persistent data is unavailable.

incremental_checkpoint_timeout

Parameter description: Specifies the maximum interval between automatic WAL checkpoints when the incremental checkpoint (enable_incremental_checkpoint) is enabled.

Parameter type: integer.

Unit: second

Value range: 1 to 3600

Default value: 60

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_xlog_prune

Parameter description: Specifies whether the primary node recycles logs if the size of Xlogs exceeds the value of max_size_for_xlog_prune when any standby node is disconnected.

Parameter type: Boolean.

Unit: none

Value range:

• If this parameter is set to on, the primary node recycles logs when any standby node is disconnected.
• If this parameter is set to off, the primary node does not recycle logs when any standby node is disconnected.

Default value: on

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

max_size_for_xlog_prune

Parameter type: integer

Unit: KB

Value range: 0 to 2199023255551

Default value: 256GB

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. If the value of the parameter does not contain a unit, the default unit is KB. The unit can also be MB or GB.

Setting suggestion: If the disk space is small, you are advised to set this parameter to a small value. The maximum value is 256GB.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

max_redo_log_size

Parameter description: Standby DN indicates the expected log volume between the latest log replay point and the latest checkpoint position. Primary DN indicates the expected log volume between the latest log insertion point and the latest checkpoint position. This parameter is used to specify the page refreshing speed of checkpoints.

Parameter type: integer.

Unit: KB

Value range: 163840 to 2147483647

Default value: 1048576

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value. It is recommended that this parameter be set to a small value if the RTO is concerned.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.