6.5. Autovacuum Daemon

PostgreSQL automates vacuum processing using the autovacuum daemon. This automation greatly simplifies database maintenance.

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

These workers perform vacuum processing concurrently for their respective tables. This activity occurs gradually to ensure minimal impact on database performance.

6.5.1. Conditions for autovacuum to run

Autovacuum runs for a target table if any of the following conditions are satisfied:

6.5.1.1. Condition 1

The current txid exceeds the following threshold:

$$ \text{relfrozenxid} + \text{autovacuum_freeze_max_age} $$

Where:

relfrozenxid is the value defined for the target table in pg_class.
autovacuum_freeze_max_age is a configuration parameter (default: 200,000,000).

When this condition is met, autovacuum performs freeze processing for the target table.

6.5.1.2. Condition 2

The number of dead tuples exceeds the following threshold:

$$ \text{autovacuum_vacuum_threshold} + \text{autovacuum_vacuum_scale_factor} \times \text{reltuples} $$

Where:

autovacuum_vacuum_threshold (default: 50) and autovacuum_vacuum_scale_factor (default: 0.2) are configuration parameters.
reltuples is the total number of tuples in the target table.

For example, if a table has 10,000 tuples and 2,100 dead tuples, autovacuum runs because:

$$ 2100 \gt 50 + 0.2 \times 10000. $$

6.5.1.3. Condition 3 (version 13 or later)

The number of inserted tuples in the target table exceeds the following threshold:

$$ \text{autovacuum_vacuum_insert_threshold} + \text{autovacuum_vacuum_insert_scale_factor} \times \text{reltuples} $$

Where:

autovacuum_vacuum_insert_threshold (default: 1000) and autovacuum_vacuum_insert_threshold (default: 0.2) are configuration parameters.
reltuples is the number of tuples in the target table.

For example, if a table has 10,000 tuples and 3,010 inserted tuples, autovacuum runs because:

$$ 3010 \gt 1000 + 0.2 \times 10000. $$

This condition was added in version 13.

6.5.1.4. Condition 4

Autovacuum also performs analyze processing if the following condition is satisfied:

$$ \begin{align} \text{mod_since_analyze} \gt \text{autovacuum_analyze_threshold} + \text{autovacuum_analyze_scale_factor} \times \text{reltuples} \end{align} $$

Where:

mod_since_analyze is the number of modified tuples (via INSERT, DELETE, or UPDATE) since the previous analyze operation.
autovacuum_analyze_threshold (default: 50) and autovacuum_analyze_scale_factor (default: 0.1) are configuration parameters.
reltuples is the number of tuples in the target table.

For example, if a table has 10,000 tuples and 1,100 modified tuples since the last analyze, autovacuum runs because:

$$ 1100 \gt 50 + 0.1 \times 10000. $$

Info

The relation_needs_vacanalyze() function determines whether target tables require vacuum or analyze operations.

6.5.2. Maintenance tips

As frequently mentioned, table bloat is a significant challenge in managing PostgreSQL. Several factors cause this problem, and Autovacuum is one of them.

Autovacuum runs when the number of dead tuples exceeds specific thresholds: 250 for a table with 1,000 rows, 20,050 for 100,000 rows, and 20,000,050 for 100,000,000 rows. These examples show that Autovacuum runs less frequently as the number of tuples in a table increases.

A common tip is to reduce the autovacuum_vacuum_scale_factor value. The default value (0.2) is often too large for large tables.

PostgreSQL can set an appropriate autovacuum_vacuum_scale_factor for each table using the ALTER TABLE. The following example sets the new value for the pgbench_accounts table:

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

Specific settings can ensure that Autovacuum runs independently of the total number of tuples.

For instance, if vacuuming is required whenever dead tuples reach 50,000, the following storage parameters can be configured. With these settings, Autovacuum triggers vacuuming each time the 10,000 dead tuple threshold is reached:

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