Automatic Vacuuming

autovacuum

Parameter description: Specifies whether to start the autovacuum process in the database. Ensure that the track_counts parameter is set to on before starting the automatic cleanup process.

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

• Set the autovacuum parameter to on to automatically vacuum two-phase transactions after the system recovers from faults.
• If autovacuum is set to on and autovacuum_max_workers to 0, the autovacuum process is started only when the system recovers from faults to clean up abnormal two-phase transactions.
• If autovacuum is set to on and the value of autovacuum_max_workers is greater than 0, the system will automatically vacuum the two-phase transactions and processes after recovering from faults.

Value range: Boolean

• on indicates that the autovacuum process is started.
• off indicates that the autovacuum process is not started.

Default value: on

autovacuum_mode

Parameter description: Specifies whether the autoanalyze or autovacuum function is enabled. This parameter is valid only when autovacuum is set to on.

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

Value range: enumerated values

• analyze indicates that only autoanalyze is performed.
• vacuum indicates that only autovacuum is performed.
• mix indicates that both autoanalyze and autovacuum are performed.
• none indicates that neither of them is performed.

Default value: mix

autoanalyze_timeout

Parameter description: Specifies the timeout period of autoanalyze. If the duration of autoanalyze on a table exceeds the value of autoanalyze_timeout, the autoanalyze is automatically canceled.

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

Value range: an integer ranging from 0 to 2147483. The unit is s.

Default value: 5min (300s)

autovacuum_io_limits

Parameter description: Specifies the upper limit of I/Os triggered by the autovacuum process per second.

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

Value range: an integer. The value can be –1 or range from 0 to 1073741823. –1 indicates that the default cgroup is used.

Default value: –1

log_autovacuum_min_duration

Parameter description: Records each step performed by the autovacuum process to the server log when the execution time of the autovacuum process is greater than or equal to a certain value. This parameter helps track the autovacuum behavior.

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

A setting example is as follows:

Set the log_autovacuum_min_duration parameter to 250 ms to record the actions of autovacuum if it runs for 250 ms or longer.

Value range: an integer ranging from –1 to 2147483647. The unit is ms.

• 0 indicates that all autovacuum actions are recorded in the log.
• –1 indicates that all autovacuum actions are not recorded in the log.
• A value other than –1 indicates that a message is recorded when an autovacuum action is skipped due to a lock conflict.

Default value: –1

autovacuum_max_workers

Parameter description: Specifies the maximum number of autovacuum worker threads that can run at the same time. The upper limit of this parameter is related to the values of max_connections and job_queue_processes.

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

Value range: an integer. The minimum value is 0, indicating that autovacuum is not enabled. The theoretical maximum value is 262143, but the actual maximum value is a dynamic value calculated by the following formula: 262143 – max_inner_tool_connections – max_connections job_queue_processes – max_concurrent_autonomous_transactions – Number of auxiliary threads – Number of autovacuum launcher threads – 1. The number of auxiliary threads and the number of autovacuum launcher threads are specified by two macros. Their default values are 20 and 2 respectively.

Default value: 3

autovacuum_naptime

Parameter description: Specifies the interval between activity rounds for the autovacuum process.

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

Value range: an integer ranging from 1 to 2147483. The unit is s.

Default value: 10min (600s)

autovacuum_vacuum_threshold

Parameter description: Specifies the threshold for triggering the VACUUM operation. When the number of deleted or updated records in a table exceeds the specified threshold, the VACUUM operation is executed on this table.

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

Value range: an integer ranging from 0 to 2147483647.

Default value: 50

autovacuum_analyze_threshold

Parameter description: Specifies the threshold for triggering the ANALYZE operation. When the number of deleted, inserted, or updated records in a table exceeds the specified threshold, the ANALYZE operation is executed on this table.

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

Value range: an integer ranging from 0 to 2147483647.

Default value: 50

autovacuum_vacuum_scale_factor

Parameter description: Specifies a fraction of the table size added to the autovacuum_vacuum_threshold parameter when deciding whether to vacuum a table.

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

Value range: a floating-point number ranging from 0.0 to 100.0

Default value: 0.2

autovacuum_analyze_scale_factor

Parameter description: Specifies a fraction of the table size added to the autovacuum_analyze_threshold parameter when deciding whether to analyze a table.

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

Value range: a floating-point number ranging from 0.0 to 100.0

Default value: 0.1

autovacuum_freeze_max_age

Parameter description: Specifies the maximum age (in transactions) that a table's pg_class.relfrozenxid field can attain before a VACUUM operation is performed.

• The old files under the subdirectory of pg_clog/ can also be deleted by the VACUUM operation.
• Even if the autovacuum process is not started, the system will invoke the process to prevent transaction ID wraparound.

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

Value range: an integer ranging from 100000 to 576460752303423487

Default value: 4000000000

autovacuum_vacuum_cost_delay

Parameter description: Specifies the value of the cost delay used in the autovacuum operation.

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

Value range: an integer ranging from –1 to 100. The unit is ms. –1 indicates that the normal vacuum cost delay is used.

Default value: 20 ms

autovacuum_vacuum_cost_limit

Parameter description: Sets the value of the cost limit used in the autovacuum operation.

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

Value range: an integer ranging from –1 to 10000 –1 indicates that the normal vacuum cost limit is used.

Default value: –1

defer_csn_cleanup_time

Parameter description: Specifies the local recycling interval.

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

Value range: an integer ranging from 0 to 2147483647. The unit is ms.

Default value: 5000 ms (that is, 5s)