Autovacuum is the quiet janitor that keeps PostgreSQL from drowning in dead rows and stale statistics. When it falls behind, tables and indexes expand, cache hit rates drop, and query plans start guessing with outdated numbers.
The autovacuum launcher periodically starts workers that run VACUUM and ANALYZE in the background. For each table, automatic vacuum and analyze triggers are calculated from a fixed threshold plus a scale factor based on table size, which makes the same configuration behave very differently on small versus large relations. Cluster-wide defaults can be set with ALTER SYSTEM, while per-table overrides are set as storage parameters with ALTER TABLE for the few “hot” tables that churn constantly.
Tuning is a balancing act: lower thresholds vacuum sooner but increase background maintenance work, while higher thresholds reduce maintenance load at the cost of bloat and “surprise” cleanup later. Long-running transactions can block tuple cleanup regardless of settings by keeping old row versions visible to concurrent snapshots. Apply changes incrementally, prefer per-table tuning for high-churn tables, and validate results using dead tuple counts plus autovacuum timestamps.
Related: How to vacuum and analyze tables in PostgreSQL \\
Related: How to optimize PostgreSQL performance
Steps to tune autovacuum in PostgreSQL:
- Identify tables with high dead tuple counts and stale autovacuum timestamps.
$ sudo -u postgres psql -d appdb -c "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;" relname | total_size | n_live_tup | n_dead_tup | last_autovacuum | last_autoanalyze ----------------+------------+------------+------------+------------------------------+------------------------------- users | 16 kB | 0 | 0 | | customers | 136 kB | 1000 | 0 | | 2025-12-24 13:57:09.339666+00 events | 0 bytes | 0 | 0 | | orders | 13 MB | 100000 | 0 | 2025-12-24 13:57:09.36741+00 | 2025-12-24 13:57:09.418944+00 events_2025_02 | 64 kB | 1 | 0 | | events_2025_03 | 32 kB | 0 | 0 | | events_default | 64 kB | 1 | 0 | | (7 rows) - Review current server-wide autovacuum thresholds, contexts, and restart requirements.
$ sudo -u postgres psql -Atc "SELECT name, setting, COALESCE(unit,'') AS unit, context, pending_restart FROM pg_settings WHERE name IN ( 'autovacuum', 'autovacuum_vacuum_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_analyze_scale_factor', 'autovacuum_analyze_threshold', 'autovacuum_naptime', 'log_autovacuum_min_duration' ) ORDER BY name;" autovacuum|on||sighup|f autovacuum_analyze_scale_factor|0.1||sighup|f autovacuum_analyze_threshold|50||sighup|f autovacuum_naptime|60|s|sighup|f autovacuum_vacuum_scale_factor|0.2||sighup|f autovacuum_vacuum_threshold|50||sighup|f log_autovacuum_min_duration|600000|ms|sighup|fTrigger for vacuum is autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × estimated rows, with a separate threshold + scale factor pair for ANALYZE.
- Log slower autovacuum runs to make tuning changes observable.
$ sudo -u postgres psql -c "ALTER SYSTEM SET log_autovacuum_min_duration = '5s';" ALTER SYSTEM
Setting log_autovacuum_min_duration too low can grow logs quickly on busy clusters.
- Reduce the default autovacuum_vacuum_scale_factor to vacuum large tables sooner.
$ sudo -u postgres psql -c "ALTER SYSTEM SET autovacuum_vacuum_scale_factor = '0.05';" ALTER SYSTEM
Lowering the scale factor triggers autovacuum earlier as table size grows.
- Reduce the default autovacuum_analyze_scale_factor to refresh planner statistics more often.
$ sudo -u postgres psql -c "ALTER SYSTEM SET autovacuum_analyze_scale_factor = '0.02';" ALTER SYSTEM
More frequent ANALYZE reduces plan drift on rapidly changing tables.
- Reload PostgreSQL configuration to apply the updated autovacuum defaults.
$ sudo -u postgres psql -Atc "SELECT pg_reload_conf();" t
Restart required when pending_restart is t for a changed setting in pg_settings.
- Override autovacuum settings for a single high-churn table.
$ sudo -u postgres psql -d appdb -c "ALTER TABLE public.orders SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_analyze_scale_factor = 0.01);" ALTER TABLE
Per-table settings override cluster defaults and can be cleared with ALTER TABLE public.orders RESET (autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor);.
- List long-running transactions that can prevent VACUUM from reclaiming space.
$ sudo -u postgres psql -d appdb -c "SELECT pid, usename, now() - xact_start AS xact_age, state, wait_event_type, wait_event FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_age DESC LIMIT 10;" pid | usename | xact_age | state | wait_event_type | wait_event -----+----------+----------+--------+-----------------+------------ 136 | postgres | 00:00:00 | active | | (1 row)Long-running transactions can keep dead tuples visible, preventing space reuse even when autovacuum runs frequently.
- Verify autovacuum activity using dead tuple counts plus timestamps on busy tables.
$ sudo -u postgres psql -d appdb -c "SELECT relname, n_dead_tup, last_autovacuum, autovacuum_count, last_autoanalyze, autoanalyze_count FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;" relname | n_dead_tup | last_autovacuum | autovacuum_count | last_autoanalyze | autoanalyze_count ----------------+------------+------------------------------+------------------+-------------------------------+------------------- users | 0 | | 0 | | 0 customers | 0 | | 0 | 2025-12-24 13:57:09.339666+00 | 1 events | 0 | | 0 | | 0 orders | 0 | 2025-12-24 13:57:09.36741+00 | 1 | 2025-12-24 13:57:09.418944+00 | 1 events_2025_02 | 0 | | 0 | | 0 events_2025_03 | 0 | | 0 | | 0 events_default | 0 | | 0 | | 0 (7 rows)
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.
