Updated on 2025-05-29 GMT+08:00

Autovacuum

The autovacuum thread includes the VACUUM and ANALYZE processes to recycle the record space marked as deleted and update statistics about the table. The autovacuum_naptime parameter specifies the interval between them. The default interval is 10 minutes. You can set the interval based on the actual situation. The interval cannot be completely accurate. It depends on the hardware conditions and load of the environment. If a large amount of data needs to be vacuumed, the heavy load may delay the process. It also depends on the values of autovacuum_naptime and autovacuum_max_workers. autovacuum_naptime specifies the execution interval. A smaller value indicates a shorter execution interval. However, data vacuuming and statistics calculation involved cleaning up the CPU usage, memory usage, and I/O overhead. autovacuum_max_workers specifies the maximum number of concurrent automatic cleanup threads. A larger value makes the parameter more compatible with autovacuum_naptime, but the CPU usage, memory usage, and I/O overhead also increase.

In addition, you can set the immediate_analyze_threshold parameter to trigger ANALYZE when the amount of new data exceeds the threshold. For details about how to calculate the threshold, see the description of immediate_analyze_threshold.

autovacuum

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

Parameter type: Boolean.

Unit: none

Value range:

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

Default value: on

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

Setting suggestion:

  • 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.

Risks and impacts of improper settings: If this parameter is disabled in an Astore table, too many inactive rows will exist in the table, which may affect the query performance.

autovacuum_mode

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

Parameter type: enumerated type

Unit: none

Value range:

  • 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 autoanalyze nor autovacuum is performed.

Default value: mix

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 you fully understand the parameter meaning and test the parameter.

autoanalyze_timeout

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

Parameter type: integer.

Unit: second

Value range: 0 to 2147483. The value 0 indicates that no timeout occurs.

Default value: 300

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

Setting suggestion: Set the value depending on the database load and performance requirements. If the database load is light, you can set autoanalyze_timeout to a larger value so that the automatic analysis operation can be completed in sufficient time. If the database is heavily loaded, set autoanalyze_timeout to a smaller value so that automatic analysis can be completed in a short period of time. This prevents other important database operations from being affected.

Risks and impacts of improper settings: The AUTOANALYZE operation consumes certain system resources. If the value is too large, system resources may be excessively consumed, affecting database performance. If the value is too small, the AUTOANALYZE operation may fail to be completed. As a result, the statistics may be inaccurate, affecting the execution plan and performance of the query.

autovacuum_io_limits

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

Parameter type: integer.

Unit: none

Value range: –1 to 1073741823. –1 indicates that the default Cgroup is used.

Default value: –1

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

Setting suggestion:

  • If the disk I/O resources of server are sufficient, you can set autovacuum_io_limits to a large value, which allows the autovacuum thread to complete the cleanup operation more quickly.
  • If the disk I/O resources of the server are limited, you can set autovacuum_io_limits to a small value, which limits the disk I/O resources used by the autovacuum thread to avoid impact on other threads.

Risks and impacts of improper settings:

  • If the value is too large, the autovacuum thread may use too many disk I/O resources, affecting the performance of other threads.
  • If the value is too small, the autovacuum thread may fail to effectively clear table data. As a result, table data is excessively expanded, affecting database performance.

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.

For example, set the log_autovacuum_min_duration parameter to 250ms to record the information about the autovacuum commands running longer than or equal to 250 ms.

Parameter type: integer.

Unit: ms

Value range: –1 to 2147483647

  • 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 greater than or equal to 0 indicates that a message is recorded when an autovacuum action is skipped due to a lock conflict. (The reason for skipping autovacuum is recorded for audit.)

Default value: –1

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. For example, if the value is 600 without a unit, log_autovacuum_min_duration indicates 600 ms. If the value is 10min, log_autovacuum_min_duration indicates 10 minutes. The unit must be ms, s, min, h, or d if required.

Setting suggestion: The default value is –1. If you want to monitor the performance of automatic VACUUM operations, set log_autovacuum_min_duration to a value greater than 0 to record the automatic VACUUM operations whose duration exceeds the value of this parameter.

Risks and impacts of improper settings: If this parameter is set to 0 or the value is too small, a large number of logs may be recorded, affecting system performance. If this value is too large, the system may not detect that the automatic clearance time is too long in a timely manner, causing insufficient clearance speed or exceptions.

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.

Parameter type: integer.

Unit: none

Value range: 0 (indicating that autovacuum is not automatically performed) to 1024. If you set this parameter to a value greater than 1024, the system automatically changes the value to 1024. The actual maximum value is a dynamic value calculated by the following formula: Maximum value = 262143 – Value of max_inner_tool_connections – Value of max_connections – Value of max_concurrent_autonomous_transactions – Value of job_queue_processesNumber of auxiliary threadsNumber 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 in the current version are 20 and 2, respectively.

Default value: 3

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: If the value is too large, more autovacuum processes are created and more CPU and memory resources are occupied. Therefore, you are advised not to set this parameter to a large value. Otherwise, memory cannot be allocated or too many CPU resources are occupied, causing database startup errors or affecting services. If the value is too small, the autovacuum thread may fail to clear and recycle tablespaces that are no longer used in a timely manner, causing database expansion and performance deterioration.

autovacuum_naptime

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

Parameter type: integer.

Unit: second

Value range: 1 to 2147483.

Default value: 600 (that is, 10 minutes)

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1. For example, if the value is 600 without a unit, autovacuum_naptime indicates 600s. If the value is 10min, autovacuum_naptime indicates 10 minutes. The unit must be s, min, h, or d if required.

Setting suggestion: Retain the default value when only Ustore tables are used. For Astore tables, the setting value depends on the user's database environment and application load. If a large number of tables are updated or deleted in the database environment, you are advised to set this parameter to a short interval to ensure that useless data is deleted in a timely manner and the database space is sufficient. If there are few update or delete operations in the database environment, you can set this parameter to a long interval to reduce the overhead of the automatic cleanup thread.

Risks and impacts of improper settings:

  • If autovacuum_naptime is set to a short interval, the overhead of the autovacuum thread may be too high, affecting the database performance.
  • If autovacuum_naptime is set to a long interval, useless data may be accumulated, occupying database space and affecting database performance.

autovacuum_vacuum_threshold

Parameter description: Specifies the minimum number of deleted and updated tuples that trigger a VACUUM operation on an Astore table. Threshold for triggering VACUUM is calculated as follows: Value of autovacuum_vacuum_threshold + Value of reltuples (Number of tuples in the table) x autovacuum_vacuum_scale_factor. When the number of deleted or updated records in a table exceeds the threshold, the VACUUM operation is executed on this table.

Parameter type: integer.

Unit: none

Value range: 0 to 2147483647

Default value: 50

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: In an Astore table:

  • If the value is too small, VACUUM operations are frequently performed, increasing system overhead and deteriorating system performance.
  • If the value is too large, too many dead rows exist in the table, affecting the query performance.

autovacuum_analyze_threshold

Parameter description: Specifies the minimum number of deleted, inserted, and updated tuples that trigger an ANALYZE operation on an Astore table. Threshold for triggering ANALYZE is calculated as follows: Value of autovacuum_analyze_threshold + Value of reltuples (Number of tuples in the table) x autovacuum_analyze_scale_factor. When the number of deleted, inserted, or updated records in a table exceeds the threshold, the ANALYZE operation is executed on this table.

Parameter type: integer.

Unit: none

Value range: 0 to 2147483647

Default value: 50

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:

  • If the value is too small, ANALYZE operations are frequently performed, increasing system overhead and deteriorating system performance.
  • If the value is too large, the statistics in the table may be outdated. As a result, the query plan is inaccurate and the query performance is affected.

autovacuum_vacuum_scale_factor

Parameter description: Specifies the scale factor of a table when VACUUM is performed. It is used to calculate the threshold for triggering VACUUM. Threshold for triggering VACUUM is calculated as follows: Value of autovacuum_vacuum_threshold + Value of reltuples (Number of tuples in the table) x autovacuum_vacuum_scale_factor. When the number of deleted or updated records in a table exceeds the threshold, the VACUUM operation is executed on this table.

Parameter type: floating point.

Unit: none

Value range: 0.0 to 100.0

Default value: 0.2

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

Setting suggestion: The value of this parameter depends on the load and usage of an Astore table. If the write load is high, set this parameter to a smaller value so that the table can be cleared more frequently to prevent performance deterioration. If the read load is high, set this parameter to a larger value to avoid unnecessary waste of resources during read.

Risks and impacts of improper settings: In an Astore table:

  • If the value is too small, the autovacuum thread may clear tables too frequently, causing performance deterioration.
  • If the value is too large, the table may not be cleared in a timely manner. As a result, the table size becomes large and occupies too much disk space.

autovacuum_analyze_scale_factor

Parameter description: Specifies the scale factor of a table when ANALYZE is performed. It is used to calculate the threshold for triggering ANALYZE. Threshold for triggering ANALYZE is calculated as follows: Value of autovacuum_analyze_threshold + Value of reltuples (Number of tuples in the table) x autovacuum_analyze_scale_factor. When the number of deleted, inserted, or updated records in a table exceeds the threshold, the ANALYZE operation is executed on this table.

Parameter type: floating point.

Unit: none

Value range: 0.0 to 100.0

Default value: 0.1

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:

  • If the value is too small, ANALYZE operations are frequently performed, increasing system overhead and deteriorating system performance.
  • If the value is too large, the frequency of performing ANALYZE operations may be too low and the statistics in the table may be outdated. As a result, the query plan is inaccurate and the query performance is affected.

autovacuum_freeze_max_age

Parameter description: Specifies the number of transactions after which the VACUUM operation is forcibly performed for the pg_class.relfrozenxid column in an Astore table. The system starts the AUTOVACUUM thread even if automatic cleanup is disabled. The cleanup operation also allows you to delete old files from the pg_clog/ subdirectory.

Parameter type: integer.

Unit: none

Value range: 100000 to 2000000000

Default value: 4000000000

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

Setting suggestion: Retain the default value. If a large number of update and deletion operations are frequently performed on the Astore table in the database, you are advised to set this parameter to a small value to recycle garbage in a timely manner. If the Astore table in the database is seldom updated or deleted, you can set this parameter to a large value to reduce the frequency of automatic space recycling.

Risks and impacts of improper settings: In an Astore table:

  • If the value is too small, automatic space recycling is frequently performed, increasing system overhead and deteriorating system performance.
  • If this parameter is set to a large value, automatic space recycling is delayed. As a result, a large amount of invalid data may occupy disk space, affecting database performance.

autovacuum_vacuum_cost_delay

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

Parameter type: integer.

Unit: ms

Value range: –1 to 100. –1 indicates that the regular vacuum_cost_delay is used.

Default value: 20

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 you fully understand the parameter meaning and test the parameter.

autovacuum_vacuum_cost_limit

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

Parameter type: integer.

Unit: none

Value range: –1 to 10000. –1 indicates that the regular vacuum_cost_limit is used.

Default value: –1

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 you fully understand the parameter meaning and test the parameter.

cstore_prefetch_quantity

Parameter description: Specifies whether to enable VACUUM optimization and TID Store cache optimization for partitioned tables. This parameter is a temporary reuse parameter and takes effect only for the current node.

Parameter type: integer.

Unit: KB

Value range: 1024 to 1048576

  • [1024,100000): The TID Store cache optimization function and the partitioned table VACUUM optimization function are disabled at the same time.
  • [100000,200000): The TID Store cache optimization function is disabled, and the VACUUM optimization function of the partitioned table is enabled.
  • [200000,300000): Both the TID Store cache optimization function and the partitioned table VACUUM optimization function are enabled.
  • [300000,1048576]: The TID Store cache optimization function is enabled, and the VACUUM optimization function of the partitioned table is disabled.

Default value: 32768

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

Setting suggestion: Retain the default value 32768. In this case, the TID Store cache optimization function and the VACUUM optimization function for partitioned tables are disabled at the same time.

Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

cstore_backwrite_max_threshold

Parameter description: Specifies whether to enable automatic primary/standby verification for all-zero pages of the primary heap table. This parameter is temporarily reused and takes effect only for the current node.

Parameter type: integer.

Unit: KB

Value range: 4096 to INT_MAX/2.

  • [4096,3000000): The all-zero page verification function is enabled.
  • [3000000,INT_MAX/2]: The all-zero page verification function is disabled.

Default value: 2097152

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

Setting suggestion: Retain the default value. In this case, the all-zero page verification function is enabled. To disable this function, set this parameter to a value greater than or equal to 3000000. This function is enabled only for newly installed database instances by default. For database instances upgraded to the current version, this function is automatically disabled during the upgrade. After this function is enabled, it takes effect only on the current node and affects the performance during the first sequential scan.

Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

defer_csn_cleanup_time

Parameter description: Specifies the local recycling interval. It specifies when the system deletes expired CSN information and specifies how long the system retains expired CSN information.

Parameter type: integer.

Unit: ms

Value range: 0 to 2147483647

Default value: 5000 (5s)

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

Setting suggestion: Retain the default value. If the system load is light, you can set defer_csn_cleanup_time to a long time. If the system load is heavy, you can set defer_csn_cleanup_time to a short time. In this case, the system can maintain good performance even when it is busy.

Risks and impacts of improper settings: If the value is too small, the system may frequently delete expired CSN information, which deteriorates system performance. If the value is too large, the system may occupy too much memory. As a result, the system slows down or breaks down.