6.5. Autovacuum Daemon

Vacuum processing has been automated with the autovacuum daemon, making the operation of PostgreSQL extremely easy.

The autovacuum daemon periodically invokes several autovacuum_worker processes. By default, it wakes every 1 minute (defined by autovacuum_naptime) and invokes three workers (defined by autovacuum_max_works).

The autovacuum workers invoked by the autovacuum daemon perform vacuum processing concurrently for respective tables, gradually and with minimal impact on database activity.

6.5.1. Conditions for autovacuum to run

The autovacuum process runs for a target table if any of the following conditions are satisfied:

1. The current txid precedes the following expression:
   $$ \text{relfrozenxid} + \text{autovacuum_freeze_max_age} $$ where $\text{relfrozenxid}$ is the relfrozenxid value of the target table that is defined in the pg_class, and autovacuum_freeze_max_age (the default is 200,000,000) is a configuration parameter.
   If this condition is satisfied, the autovacuum process runs for the target table to perform freeze processing.

2. The number of dead tuples is greater than the following expression:
   $$ \text{autovacuum_vacuum_threshold} + \text{autovacuum_vacuum_scale_factor} \times \text{reltuples} $$ where autovacuum_vacuum_threshold (the default is 50) and autovacuum_vacuum_scale_factor (the default is 0.2) are configuration parameters, $\text{reltuples}$ is the number of tuples in the target table.
   
   For example, if the target table has 10,000 tuples and 2,100 dead tuples, the autovacuum process runs for the target table, because $$ 2100 \gt 50 + 0.2 \times 10000. $$

3. The number of inserted tuples in the target table is greater than the following expression:
   $$ \text{autovacuum_vacuum_insert_threshold} + \text{autovacuum_vacuum_insert_scale_factor} \times \text{reltuples} $$ where autovacuum_vacuum_insert_threshold (the default is 1000) and autovacuum_vacuum_insert_scale_factor (the default is 0.2) are configuration parameters, $\text{reltuples}$ is the number of tuples in the target table.
   
   For example, if the target table has 10,000 tuples and 3,010 inserted tuples, the autovacuum process runs for the target table, because $$ 3010 \gt 1000 + 0.2 \times 10000. $$
   This condition has been added since version 13.

In addition, if the following condition is satisfied for the target table, the autovacuum process will also perform analyze processing.

where $\text{mod_since_analyze}$ is the number of modified tuples (by INSERT, DELETE or UPDATE) since the previous analyze processing, autovacuum_analyze_threshold (the default is 50) and autovacuum_analyze_scale_factor (the default is 0.1) are configuration parameters, $\text{reltuples}$ is the number of tuples in the target table.

For example, if the target table has 10,000 tuples and 1,100 modified tuples since the previous analyze processing, the autovacuum process will run, because $$ 1100 \gt 50 + 0.1 \times 10000. $$

6.5.2. Maintenance tips

As frequently mentioned, table bloat is one of the most annoying things in managing PostgreSQL. Several things can cause that problem, and Autovacuum is one of them.

The autovacuum runs when the number of dead tuples is greater than: 250 for 1,000 relations, 20,050 for 100,000 relations, and 20,000,050 for 100,000,000 relations. It is clear from these examples that the more tuples a table has, the less often autovacuum runs.

A good known tip is to reduce the autovacuum_vacuum_scale_factor value. In fact, its default value (0.2) is too large for big tables.

PostgreSQL can set an appropriate autovacuum_vacuum_scale_factor in each table using ALTER TABLE command. For example, I show how to set the new value of autovacuum_vacuum_scale_factor for the table pgbench_accounts.

postgres=# ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE

If you need that Autovacuum runs for the target tables without depending on the number of their tuples, you can also do it.

For example, assume that you need that Autovacuum processing whenever the number of dead tuples reaches 10,000. In this case, by setting the following storage parameters for the table, the Autovacuum process will perform the vacuum processing each time it reaches 10,000:

postgres=# ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 10000);
ALTER TABLE
postgres=# ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE