📢 Following Manual Vacuum, this post covers ① what Autovacuum is, ② the conditions under which Autovacuum runs, and ③ the Anti-Wraparound Vacuum performed by the Autovacuum process. Finally, we walk through ④ the overall Autovacuum process.
Autovacuum
Autovacuum is the feature that automates running Vacuum. It is carried out by multiple processes called the Autovacuum Daemon, and among them the Autovacuum Launcher process manages and directs the Worker processes.
For Autovacuum to run, the
AUTOVACUUM and TRACK_COUNTS parameters must be on.The Autovacuum Launcher process then wakes up to
AUTOVACUUM_MAX_WORKERS Worker processes every AUTOVACUUM_NAPTIME (sec) to perform Vacuum work.Parameter | Default Value |
autovacuum | on |
track_counts | on |
autovacuum_naptime | 60 |
autovacuum_max_workers | 3 |
📌 Note that if you run more than one (N) database in a cluster, the run interval and the processes can be split N ways. In other words, when you add a database, you should also consider increasing the related parameters from the start.
Trigger
A table becomes an Autovacuum target when the amount of change in the table exceeds a set threshold.
This is why
TRACK_COUNTS must be set to on: with this parameter enabled, PostgreSQL can track the changes made by DML and use them to decide whether a table is an Autovacuum target.With
TRACK_COUNTS on, you can query a table's total row count and DML volume as follows.When the measured
pg_stat_all_tables.n_tup_xxx values exceed the value calculated by the formulas below, the table is selected as an Autovacuum target.If you look closely at the threshold formulas, they split into one that includes the keyword
INSERT and one that does not.The reason is that before version 13 there was no Autovacuum parameter related to Insert operations. In other words, before version 13, Insert-Only Tables, which produce no dead tuples from Delete or Update, had a chronic problem: they could never become Autovacuum targets, so users had no choice but to manage them with Manual Vacuum.
But thanks to the
parameters AUTOVACUUM_VACUUM_INSERT_SCALE_FACTOR and AUTOVACUUM_VACUUM_INSERT_THRESHOLD introduced in version 13, Insert-Only Tables can now trigger Autovacuum as well.Parameter | Default Value |
autovacuum_vacuum_scale_factor | 0.2 |
autovacuum_vacuum_threshold | 50 |
autovacuum_vacuum_insert_scale_factor | 0.2 |
autovacuum_vacuum_insert_threshold | 10000 |
Anti-Wraparound Vacuum
Earlier we described
AUTOVACUUM and TRACK_COUNTS as prerequisites for using Autovacuum. However, even if you disable these parameters, automatic Vacuum by Autovacuum can still run when certain conditions are met.Suppose an environment where users never run Vacuum periodically and the Autovacuum feature is not used either. Setting aside the benefits of Vacuum for space management and performance, you still need at least a minimal safeguard to prevent XID Wraparound, which can compromise database consistency.
For this purpose, PostgreSQL automatically runs Anti-Wraparound Vacuum through the Autovacuum process when a specific situation occurs, even if the Autovacuum feature is not used. That condition is the point at which a table's Age exceeds
AUTOVACUUM_FREEZE_MAX_AGE.AUTOVACUUM_FREEZE_MAX_AGE is larger than VACUUM_FREEZE_TABLE_AGE, which we discussed earlier as the criterion for choosing between Eager Mode and Lazy Mode, and it can be seen as the last line of defense against XID Wraparound.Parameter | Default Value |
autovacuum_freeze_max_age | 200000000 |
vacuum_freeze_table_age | 150000000 |
📌 If vacuum_freeze_table_age could become larger than autovacuum_freeze_max_age, Manual Vacuum's Eager Mode would be meaningless. For this reason, no matter how large you set vacuum_freeze_table_age, it is capped at 95% of autovacuum_freeze_max_age.
Autovacuum Process
The Autovacuum process can be summarized as follows. It is similar to Manual Vacuum but is controlled by more parameters.
Parameter | Default Value |
vacuum_freeze_min_age | 50000000 |
vacuum_freeze_table_age | 150000000 |
autovacuum_freeze_max_age | 200000000 |
autovacuum | on |
track_count | on |
autovacuum_vacuum_scale_factor | 0.2 |
autovacuum_vacuum_threshold | 50 |
autovacuum_vacuum_insert_scale_factor | 0.2 |
autovacuum_vacuum_insert_threshold | 10000 |
Threshold formula
Related articles
