The purpose of the PostgreSQL autovacuum feature is to automate the execution of VACUUM and ANALYZE commands. This feature helps prevent database table bloating.
Autovacuum configuration
PostgreSQL has the autovacuum feature enabled by default with the following settings (can be found in c:\Program Files\PostgreSQL\16\data\postgresql.conf):
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
# (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_vacuum_insert_threshold = 1000 # min number of row inserts
# before vacuum; -1 disables insert
# vacuums
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_vacuum_insert_scale_factor = 0.2 # fraction of inserts over table
# size before insert vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
# before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 2ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
By default, Autovacuum is triggered based on two main factors:
-
The number of dead tuples (deleted or updated rows)
-
The percentage of dead tuples relative to the table size
Autovacuum starts when dead tuples exceed this threshold:
autovacuum_vacuum_threshold = 50 # Minimum number of dead tuples to trigger autovacuum
autovacuum_vacuum_scale_factor = 0.2 # 20% of table size
Autovacuum can be triggered as follows:
vacuum_trigger = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * number_of_live_rows)
Useful SQL requests to check dead tuples
Check dead tuples count:
SELECT relname AS table_name,
n_dead_tup AS dead_tuples
FROM pg_stat_all_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;
Check how much space dead tuples occupy in each table:
SELECT relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
n_dead_tup AS dead_tuples
FROM pg_stat_all_tables
ORDER BY n_dead_tup DESC;
Check if autovacuum is running properly:
SELECT relname, last_autovacuum, last_vacuum, autovacuum_count
FROM pg_stat_all_tables
WHERE autovacuum_count = 0;
More information about this topic can be found on the PostgreSQL wiki: https://wiki.postgresql.org/wiki/VACUUM_FULL