PostgreSQL Reference Manual - Volume 3 - Server Administration Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 204 pages ISBN 0954612043 RRP £13.95 ($24.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL Reference Manual Volume 3 Buy the book here! >>> only $24.95

9.1.4 The auto-vacuum daemon

Beginning in PostgreSQL 8.1, there is a separate optional server process called the autovacuum daemon, whose purpose is to automate the execution of VACUUM and ANALYZE commands. When enabled, the autovacuum daemon runs periodically and checks for tables that have had a large number of inserted, updated or deleted tuples. These checks use the row-level statistics collection facility; therefore, the autovacuum daemon cannot be used unless stats_start_collector and stats_row_level are set to true. Also, it's important to allow a slot for the autovacuum process when choosing the value of superuser_reserved_connections.

The autovacuum daemon, when enabled, runs every autovacuum_naptime seconds. On each run, it selects one database to process and checks each table within that database. VACUUM or ANALYZE commands are issued as needed.

Tables whose relfrozenxid value is more than autovacuum_freeze_max_age transactions old are always vacuumed. Otherwise, two conditions are used to determine which operation(s) to apply. If the number of obsolete tuples since the last VACUUM exceeds the “vacuum threshold”, the table is vacuumed. The vacuum threshold is defined as:

vacuum threshold = vacuum base threshold + vacuum scale
 factor * number of tuples

where the vacuum base threshold is autovacuum_vacuum_threshold, the vacuum scale factor is autovacuum_vacuum_scale_factor, and the number of tuples is pg_class.reltuples. The number of obsolete tuples is obtained from the statistics collector; it is a semi-accurate count updated by each UPDATE and DELETE operation. (It is only semi-accurate because some information may be lost under heavy load.) For analyze, a similar condition is used: the threshold, defined as

analyze threshold = analyze base threshold + analyze scale
 factor * number of tuples

is compared to the total number of tuples inserted, updated, or deleted since the last ANALYZE.

The default thresholds and scale factors are taken from ‘postgresql.conf’, but it is possible to override them on a table-by-table basis by making entries in the system catalog pg_autovacuum. If a pg_autovacuum row exists for a particular table, the settings it specifies are applied; otherwise the global settings are used. See section 4.9 Automatic Vacuuming for more details on the global settings.

Besides the base threshold values and scale factors, there are five more parameters that can be set for each table in pg_autovacuum. The first, pg_autovacuum.enabled, can be set to false to instruct the autovacuum daemon to skip that particular table entirely. In this case autovacuum will only touch the table if it must do so to prevent transaction ID wraparound. The next two parameters, the vacuum cost delay (pg_autovacuum.vac_cost_delay) and the vacuum cost limit (pg_autovacuum.vac_cost_limit), are used to set table-specific values for the section 4.4.4 Cost-Based Vacuum Delay feature. The last two parameters, (pg_autovacuum.freeze_min_age) and (pg_autovacuum.freeze_max_age), are used to set table-specific values for vacuum_freeze_min_age and autovacuum_freeze_max_age respectively.

If any of the values in pg_autovacuum are set to a negative number, or if a row is not present at all in pg_autovacuum for any particular table, the corresponding values from ‘postgresql.conf’ are used.

There is not currently any support for making pg_autovacuum entries, except by doing manual INSERTs into the catalog. This feature will be improved in future releases, and it is likely that the catalog definition will change.

Caution: The contents of the pg_autovacuum system catalog are currently not saved in database dumps created by the tools pg_dump and pg_dumpall. If you want to preserve them across a dump/reload cycle, make sure you dump the catalog manually.