Automatic vacuuming category parameters

This document describes the parameters related to the automatic cleanup category in the system configuration parameters.

Notes!
To ensure system stability and security, please exercise caution when manually modifying relevant parameters.

autovacuum

Control whether the server runs the autovacuum background process.

This parameter is disabled by default, but track_counts must also be enabled for autovacuum to work properly. If track_counts is set to off, autovacuum will not take effect on its own.
Automatic cleanup can be disabled for a table by modifying the table storage parameters.
Note: Even if this parameter is disabled, the system will initiate a cleanup process when necessary to prevent transaction ID rollback.

Data Type	Default Value	Setting Category
boolean	off	master; system; reload

autovacuum_analyze_scale_factor

Specify a fraction of the table size to be added to autovacuum_analyze_threshold when determining whether to trigger ANALYZE.

The default value is 0.1 (10% of the table size). However, this setting can be overridden for individual tables by modifying the table storage parameters.

Data type	Default value	Value range	Setting category
Floating point	0.1	0.0 ~ 100.0	Segments; Session; Reload

autovacuum_analyze_threshold

Specify the minimum number of tuples that can be inserted, updated, or deleted on a table to trigger ANALYZE.

This setting can be overridden for individual tables by modifying the table storage parameters.

Data type	Default value	Value range	Setting category
int	50	0 ~ INT_MAX	segments；system；reload

autovacuum_freeze_max_age

The maximum age (in transactions) that a table's pg_class.relfrozenxid field can maintain before a VACUUM operation is forced to prevent transaction ID rollback in the table.

Note: Even if automatic cleanup is disabled, the system will initiate an automatic cleanup process to prevent rollbacks.
Cleanup also allows old files to be removed from the pg_xact subdirectory, which is why the default value is set to a relatively low 200 million transactions.
This parameter can only be set at server startup, but for individual tables, the setting can be lowered by modifying the table storage parameters.

Data type	Default value	Value range	Setting category
int	200000000	100000 ~ 200000000	segments；system；restart

autovacuum_max_workers

Specify the maximum number of automatic cleanup processes (other than the automatic cleanup launcher) that can run simultaneously.

This parameter can only be set when the server is started.

Data type	Default value	Value range	Setting category
int	3	1 ～ 262143	segments；system；restart

autovacuum_multixact_freeze_max_age

The maximum age (multi-transaction) that a table's pg_class.relminmxid field can maintain before a VACUUM operation is enforced to prevent multi-transaction ID rollbacks in the table.

Note: Even if automatic cleanup is disabled, the system will initiate an automatic cleanup process to prevent rollbacks.
Multitransaction cleanup also allows old files to be removed from the pg_multixact/members and pg_multixact/offsets subdirectories, which is why the default value is set to a relatively low 400 million transactions.
This parameter can only be set at server startup, but for individual tables, the setting can be lowered by modifying the table storage parameters.

Data type	Default value	Value range	Setting category
int	400000000	10000 ～ 2000000000	segments；system；restart

autovacuum_naptime

When the autovacuum parameter is enabled, this parameter specifies the minimum delay time (in seconds) for automatic cleanup to run on any given database.

In each cleanup cycle, the background process checks the database and issues VACUUM and ANALYZE commands for tables in the database based on the needs of system catalogs and auxiliary tables.

Data type	Default value	Value range	Setting category
int	60	1 ~ （INT_MAX/1000）	master；system；restart

autovacuum_vacuum_cost_delay

Specifies the cost delay value (in milliseconds) used in automatic VACUUM operations.

If -1 is specified, the vacuum_cost_delay value is used.
If no unit is specified when specifying a value, it is in milliseconds. The default value is 2 milliseconds.
This setting can be overridden for individual tables by modifying the table storage parameters.

Data type	Default value	Value range	Setting category
floating point	2	-1 ～ 100	segments；system；reload

autovacuum_vacuum_cost_limit

Specifies the cost limit value used in automatic VACUUM operations.

If -1 (the default value) is specified, the vacuum_cost_limit value is used.
Note: This value is proportionally distributed among the running automatic cleanup workers (if there are multiple), so the sum of the limit values for each worker does not exceed the value in this variable.
This setting can be overridden for individual tables by modifying the table storage parameters.

Data type	Default value	Value range	Setting category
int	-1	-1 ~ 10000	segments；system；reload

autovacuum_vacuum_scale_factor

Specify a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger VACUUM.

The default value is 0.2 (20% of the table size).
This setting can be overridden for individual tables by modifying the table storage parameters.

Data type	Default value	Value range	Setting category
floating point	0.2	0.0 ~ 1.0	segments；system；reload

autovacuum_vacuum_threshold

Specifies the minimum number of updated or deleted tuples that can trigger a VACUUM operation on a table.

This setting can be overridden for individual tables by modifying the table storage parameters.

Data type	Default value	Value range	Setting category
int	50	1 ~ INT_MAX	segments；system；reload

log_autovacuum_min_duration

If the automatic cleanup operation runs for at least the time specified by this parameter value, every action performed by the automatic cleanup will be logged.

Setting this parameter to 0 will log all automatic cleanup actions.
-1 (default value) will disable logging of automatic cleanup actions.
For example, if you set it to 250ms, all automatic cleanup and analysis operations that run for 250ms or longer will be logged.
Additionally, when this parameter is set to any value other than -1, if an automatic cleanup action is skipped due to a lock conflict or concurrent deletion of a related object, a message will be logged for this.
Enabling this parameter is very useful for tracking automatic cleanup activities.
This setting can be overridden for individual tables by modifying the table's storage parameters.

Data type	Default value	Value range	Setting category
int	-1	-1 ～ INT_MAX	segments；system；reload

track_counts

Control the statistics of database activity collection.

| Data type | Default value | Value range | Setting category | | --- | --- | --- | | boolean | true | master；session；reload；superuser |