Regular Maintenance - PostgreSQL 9 Administration - page 212

How it works...
If autovacuum is set, then autovacuum will wake up every autovacuum_naptime seconds, and
decide whether to run VACUUM and/or ANALYZE commands.
There will never be more than autovacuum_max_workers maintenance processes running
at any one time. As these autovacuum slaves perform I/O, they accumulate cost points,
until they hit the autovacuum_vacuum_cost_limit, after which they sleep for autovacuum_
vacuum_cost_delay. This is designed to throttle the resource utilization of autovacuum to
prevent it from using all available disk performance, which it should never do. So, increasing
autovacuum_vacuum_cost_delay will slow down each VACUUM to reduce the impact on
user activity. Autovacuum will run an ANALYZE command when there have been at least
autovacuum_analyze_threshold changes, and a fraction of the table defined by autovacuum_
analyze_scale_factor has been inserted, updated, or deleted.
Autovacuum will run a VACUUM command when there have been at least autovacuum_vacuum_
threshold changes, and a fraction of the table defined by autovacuum_vacuum_scale_factor has
been updated or deleted.
If you set log_autovacuum_min_duration, then any autovacuum that runs for longer than this
value will be logged to the server log, like the following:
2010-04-29 01:33:55 BST (13130) LOG: automatic vacuum of table
"postgres.public.pgbench_accounts": index scans: 1
pages: 0 removed, 3279 remain
tuples: 100000 removed, 100000 remain
system usage: CPU 0.19s/0.36u sec elapsed 19.01 sec
2010-04-29 01:33:59 BST (13130) LOG: automatic analyze of table
"postgres.public.pgbench_accounts"
system usage: CPU 0.06s/0.18u sec elapsed 3.66 sec
Most of the preceding global parameters can also be set at the table level. For example, if you
think that you don't want a table to be autovacuumed, then you can set:
ALTER TABLE big_table SET (autovacuum_enabled = off);
It's also possible to set parameters for toast tables. The toast table is the location where
oversize column values get placed, which the documents refer to as "supplementary storage
tables". If there are no oversize values, then the toast table will occupy little space. Tables with
very wide values often have large toast tables. Toast (stands for the oversize attribute storage
technique) is optimized for UPDATE. If you have a heavily updated table, the toast table is
untouched, so it may makes sense to turn off autovacuuming of the toast table as follows:
ALTER TABLE pgbench_accounts
SET ( autovacuum_vacuum_cost_delay = 20
,toast.autovacuum_enabled = off);