PostgreSQL Automatic Vacuum
The vacuum of PostgreSQL needs to be executed regularly for the following reasons.
Free up and reuse disk space occupied by updates or delete updates.
Update statistics for the PostgreSQL query plan.
Avoid resetting the transaction ID and causing very old data loss.
The execution of VACUUM’s standard SQL text and other actual operations on the database can be processed in parallel. SELECT, INSERT, UPDATE, DELETE and other commands can continue to be executed as usual. However, while VACUUM is being processed, commands such as ALTER TABLE ADD COLUMN cannot redefine the table. In addition, because there are a lot of I/O operations when performing VACUUM, other operations may have lower performance, such as a very slow query response. In order to reduce the impact on performance, it can be adjusted through parameters.
autovacuum (boolean):
Whether the database server is set to auto vacuum. The default is automatic vacuum. But if you want vacuum to work properly, you must enable track_counts. The track_counts parameter is set in the postgresql.conf configuration file or set by commands.
Even if the setting is not automatic vacuum. The system will automatically start when it is necessary to prevent the reset of the transaction ID
log_autovacuum_min_duration (integer ):
Set how long the execution time exceeds the vacuum before outputting the log. Time unit in milliseconds.
If this parameter is set to 0, all vacuum-related logs are output.
If this parameter is set to -1, this is also the default setting. The log output is invalid, that is, all vacuum-related logs are not output.
The setting of this parameter can modify the postgresql.conf configuration file, and can also be set by commands.
autovacuum_max_workers (integer):
Set the maximum number of vacuum processes that can be executed simultaneously. The default is 3.
The setting of this parameter can modify the postgresql.conf configuration file, and can also be set by commands.
autovacuum_naptime (integer ):
Set the minimum delay for the database to execute vacuum. The unit of time is minutes. The default is 1 minute.
The setting of this parameter can modify the postgresql.conf configuration file, and can also be set by commands.
autovacuum_vacuum_threshold (integer):
Set any table to trigger the update of VACUUM and delete the minimum number of tuples. The default is 50.
The setting of this parameter can modify the postgresql.conf configuration file, and can also be set by commands. This setting can cover each table through the pg_autovacuum project.
autovacuum_analyze_threshold (integer):
Set any table to trigger the update of ANALYZE and delete the minimum number of tuples. The default is 50.
The setting of this parameter can modify the postgresql.conf configuration file, and can also be set by commands. This setting can cover each table through the pg_autovacuum project.
autovacuum_vacuum_scale_factor (floating point):
Whether to trigger the judgment of VACUUM, set autovacuum_vacuum_threshold to add additional table capacity fragments. The default is 0.2 (ie 20%).
The setting of this parameter can modify the postgresql.conf configuration file, and can also be set by commands. This setting can cover each table through the pg_autovacuum project.
autovacuum_analyze_scale_factor (floating point): When
triggering the ANALYZE judgment, set autovacuum_vacuum_threshold to add the table capacity fragment. The default is 0.1 (ie 10%).
The setting of this parameter can modify the postgresql.conf configuration file, and can also be set by commands. This setting can cover each table through the pg_autovacuum project.
autovacuum_freeze_max_age (integer ):
In order to prevent the reset of the transaction ID, before the VACUUM force operation, set the maximum value of the pg_class .relfrozenxid field of the table. The default is 200 million.
This parameter can only be set at startup.
autovacuum_vacuum_cost_delay (integer):
Set the cost delay in automatic vacuum operation. The default is 20 milliseconds. If set to -1, the value of vacuum_cost_delay is used.
The setting of this parameter can modify the postgresql.conf configuration file, and can also be set by commands. This setting can cover each table through the pg_autovacuum project.
autovacuum_vacuum_cost_limit (integer):
Set the maximum limit value of cost in automatic vacuum operation. The default is -1, at this time the value of vacuum_cost_limit is used. The setting of this parameter can modify the postgresql.conf configuration file, and can also be set by commands. This setting can cover each table through the pg_autovacuum project
Thanks for reading this post.
