44.10. pg_autovacuum

The catalog pg_autovacuum stores optional per-relation configuration parameters for the autovacuum daemon. If there is an entry here for a particular relation, the given parameters will be used for autovacuuming that table. If no entry is present, the system-wide defaults will be used. For more information about the autovacuum daemon, see Section 23.1.4.

Note: It is likely that pg_autovacuum will disappear in a future release, with the information instead being kept in pg_class.reloptions entries.

Table 44-10. pg_autovacuum Columns

NameTypeReferencesDescription
vacrelidoidpg_class.oidThe table this entry is for
enabledbool If false, this table will not be autovacuumed, except to prevent transaction ID wraparound
vac_base_threshinteger Minimum number of modified tuples before vacuum
vac_scale_factorfloat4 Multiplier for reltuples to add to vac_base_thresh
anl_base_threshinteger Minimum number of modified tuples before analyze
anl_scale_factorfloat4 Multiplier for reltuples to add to anl_base_thresh
vac_cost_delayinteger Custom vacuum_cost_delay parameter
vac_cost_limitinteger Custom vacuum_cost_limit parameter
freeze_min_ageinteger Custom vacuum_freeze_min_age parameter
freeze_max_ageinteger Custom autovacuum_freeze_max_age parameter

The autovacuum daemon will initiate a VACUUM operation on a particular table when the number of updated or deleted tuples exceeds vac_base_thresh plus vac_scale_factor times the number of live tuples currently estimated to be in the relation. Similarly, it will initiate an ANALYZE operation when the number of inserted, updated or deleted tuples exceeds anl_base_thresh plus anl_scale_factor times the number of live tuples currently estimated to be in the relation.

Also, the autovacuum daemon will perform a VACUUM operation to prevent transaction ID wraparound if the table's pg_class.relfrozenxid field attains an age of more than freeze_max_age transactions, whether the table has been changed or not, even if pg_autovacuum.enabled is set to false for it. The system will launch autovacuum to perform such VACUUMs even if autovacuum is otherwise disabled. See Section 23.1.3 for more about wraparound prevention.

Any of the numerical fields can contain -1 (or indeed any negative value) to indicate that the system-wide default should be used for this particular value. Observe that the vac_cost_delay variable inherits its default value from the autovacuum_vacuum_cost_delay configuration parameter, or from vacuum_cost_delay if the former is set to a negative value. The same applies to vac_cost_limit. Also, autovacuum will ignore attempts to set a per-table freeze_max_age larger than the system-wide setting (it can only be set smaller), and the freeze_min_age value will be limited to half the system-wide autovacuum_freeze_max_age setting. Note that while you can set freeze_max_age very small, or even zero, this is usually unwise since it will force frequent vacuuming.