How to tune autovacuum in PostgreSQL

High-update PostgreSQL tables accumulate dead row versions when autovacuum starts too late for the write rate. The visible symptoms are growing table and index files, stale planner statistics, and queries that become slower even though application SQL has not changed.

The autovacuum launcher starts worker processes that run VACUUM and ANALYZE in the background. For ordinary update and delete churn, the vacuum trigger is capped by autovacuum_vacuum_max_threshold and otherwise uses autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor times the table row estimate. Insert-heavy tables also have insert thresholds, while ANALYZE uses its own threshold and scale factor.

Tune from table statistics instead of lowering every setting at once. Use ALTER SYSTEM for cautious cluster defaults, use ALTER TABLE storage parameters for the few tables that churn constantly, then verify the result with dead tuple counts, autovacuum timestamps, table storage parameters, and long-running transaction checks.

1. Identify tables where dead tuples accumulate faster than background cleanup.

   $ 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  | 712 kB     |       8800 |       1200 |                 | 
   (1 row)

   n_live_tup and n_dead_tup are planner statistics estimates, so use them to spot trends and candidates rather than exact row accounting.

2. Review the active autovacuum settings, whether each setting can reload, and whether a restart is pending.

   $ sudo -u postgres psql -Atc "SELECT name,
          setting,
          COALESCE(unit,'') AS unit,
          context,
          pending_restart
   FROM pg_settings
   WHERE name IN (
     'autovacuum',
     'autovacuum_max_workers',
     'autovacuum_worker_slots',
     'autovacuum_vacuum_scale_factor',
     'autovacuum_vacuum_threshold',
     'autovacuum_vacuum_max_threshold',
     'autovacuum_vacuum_insert_scale_factor',
     'autovacuum_vacuum_insert_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_max_workers|3||sighup|f
   autovacuum_naptime|60|s|sighup|f
   autovacuum_vacuum_insert_scale_factor|0.2||sighup|f
   autovacuum_vacuum_insert_threshold|1000||sighup|f
   autovacuum_vacuum_max_threshold|100000000||sighup|f
   autovacuum_vacuum_scale_factor|0.2||sighup|f
   autovacuum_vacuum_threshold|50||sighup|f
   autovacuum_worker_slots|16||postmaster|f
   log_autovacuum_min_duration|600000|ms|sighup|f

   For update and delete churn, current PostgreSQL uses Minimum(autovacuum_vacuum_max_threshold, autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * table_rows). For append-heavy tables, review autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor as a separate trigger path.

3. Log slower autovacuum runs before changing thresholds so the effect is visible.

   $ sudo -u postgres psql -c "ALTER SYSTEM SET log_autovacuum_min_duration = '5s';"
   ALTER SYSTEM

   Very low log_autovacuum_min_duration values can grow logs quickly on busy clusters and may expose table names from sensitive schemas.

4. Reduce the cluster-wide vacuum scale factor when many large tables wait too long before cleanup.

   $ sudo -u postgres psql -c "ALTER SYSTEM SET autovacuum_vacuum_scale_factor = '0.05';"
   ALTER SYSTEM

   Lowering autovacuum_vacuum_scale_factor makes update/delete cleanup start after a smaller percentage of estimated rows become obsolete. Keep the change incremental because it increases background I/O.

5. Reduce the cluster-wide analyze scale factor when plans drift before statistics refresh.

   $ sudo -u postgres psql -c "ALTER SYSTEM SET autovacuum_analyze_scale_factor = '0.02';"
   ALTER SYSTEM

   More frequent ANALYZE helps write-heavy tables whose row distribution changes before the default threshold is reached.

6. Reload PostgreSQL configuration to apply reloadable settings.

   $ sudo -u postgres psql -Atc "SELECT pg_reload_conf();"
   t

   Settings with context set to postmaster, such as autovacuum_worker_slots in current PostgreSQL, require a restart even when other autovacuum settings reload immediately.

7. Set tighter autovacuum parameters on a single high-churn table when the cluster defaults should remain conservative.

   $ 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 storage parameters override cluster defaults for that table. Remove them with ALTER TABLE public.orders RESET (autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor);.

   For append-heavy tables that rarely update or delete rows, tune autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor on that table instead of only lowering update/delete thresholds.

8. Confirm the table-specific override was stored.

   $ sudo -u postgres psql -d appdb -c "SELECT relname, reloptions FROM pg_class WHERE oid = 'public.orders'::regclass;"
    relname |                                 reloptions                                 
   ---------+----------------------------------------------------------------------------
    orders  | {autovacuum_vacuum_scale_factor=0.02,autovacuum_analyze_scale_factor=0.01}
   (1 row)

9. Check for long-running transactions that can keep dead tuples visible after autovacuum runs.

   $ 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, old prepared transactions, and old replication slots can prevent tuple cleanup even when autovacuum launches on schedule.

10. Verify the next maintenance cycle with dead tuple counts, autovacuum timestamps, and worker counters.

    $ 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  |        120 | 2026-06-07 04:54:16.244331+00 |                2 | 2026-06-07 04:54:16.297402+00 |                 2
    (1 row)

    Expect counts and timestamps to move after the table crosses the new trigger again. A table that still accumulates dead tuples needs a tighter per-table threshold, more worker capacity, shorter blocking transactions, or manual maintenance for the exceptional workload.