Updated on 2025-06-12 GMT+08:00

Automatic Cleanup

The automatic cleanup process in the system automatically runs the VACUUM and ANALYZE statements to reclaim the record space marked as deleted and update statistics about the table.

autovacuum_max_workers

Parameter description: Specifies the maximum number of automatic cleanup threads running at the same time.

Type: SIGHUP

Value range: an integer ranging from 0 to 128. 0 indicates that autovacuum is disabled.

Default value: 3

  • This parameter works with autovacuum. The rules for clearing system catalogs and user tables are as follows:
    • When autovacuum_max_workers is set to 0, autovacuum is disabled and no tables are cleared.
    • If autovacuum_max_workers > 0 and autovacuum = off are configured, the system only clears the system catalogs and column-store tables with delta tables enabled (such as vacuum delta tables, vacuum cudesc tables, and delta merge).
    • When autovacuum_max_workers is set to a value greater than zero and autovacuum is enabled, all tables will be cleared.
  • In 8.1.3, column-store primary tables are not cleared by default. You need to set the colvacuum_threshold_scale_factor parameter to enable this function.

autovacuum_max_workers_hstore

Parameter description: Specifies the maximum number of concurrent automatic cleanup threads used for HStore tables in autovacuum_max_workers.

Type: SIGHUP

Value range: an integer

Default value: 0

To use HStore tables, set the following parameters, or the HStore performance will deteriorate severely. The recommended settings are as follows:

autovacuum_max_workers_hstore=3, autovacuum_max_workers=6, autovacuum=true

autovacuum_naptime

Parameter description: Specifies the interval between two automatic cleanup operations.

Type: SIGHUP

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

Default value: 60s

autovacuum_vacuum_cost_delay

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

Type: SIGHUP

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: 2ms