How to tune autovacuum in PostgreSQL

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.

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        
----------------+------------+------------+------------+-------------------------------+-------------------------------
 orders         | 13 MB      |     100000 |       5000 | 2025-12-29 10:51:16.244331+00 | 2025-12-29 10:51:16.297402+00
 customers      | 136 kB     |        800 |        200 |                               | 2025-12-29 10:51:16.298494+00
 users          | 16 kB      |          0 |          0 |                               | 
 events_2025_03 | 16 kB      |          2 |          0 |                               | 
 events_default | 16 kB      |          2 |          0 |                               | 
 events_2025_02 | 16 kB      |          2 |          0 |                               | 
 events         | 0 bytes    |          0 |          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|f

Trigger 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 
------+----------+----------+--------+-----------------+------------
 3483 | 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 
----------------+------------+-------------------------------+------------------+-------------------------------+-------------------
 orders         |       5000 | 2025-12-29 10:51:16.244331+00 |                1 | 2025-12-29 10:51:16.297402+00 |                 1
 customers      |        200 |                               |                0 | 2025-12-29 10:54:16.249019+00 |                 2
 users          |          0 |                               |                0 |                               |                 0
 events_2025_03 |          0 |                               |                0 |                               |                 0
 events_default |          0 |                               |                0 |                               |                 0
 events_2025_02 |          0 |                               |                0 |                               |                 0
 events         |          0 |                               |                0 |                               |                 0
(7 rows)