autovacuum

For DBAs and operators

The launcher runs permanently only when autovacuum = on and track_counts = on: the decisions are made from the counters in pg_stat_user_tables. With it off, anti-wraparound vacuums (triggered by autovacuum_freeze_max_age) are still launched, which surprises people who "disabled autovacuum" and still see autovacuum: VACUUM public.big (to prevent wraparound) in pg_stat_activity.query.

Better than turning it off

• Disable per table only where you really vacuum yourself: ALTER TABLE staging SET (autovacuum_enabled = false); (and toast.autovacuum_enabled).
• Make it gentler or more aggressive with the cost settings and per-table thresholds instead.
• If it "blocks" your DDL: autovacuum cancels itself when another session waits for a conflicting lock (ERROR: canceling autovacuum task), except for anti-wraparound runs, which do not yield.

Observe

SELECT pid, query, wait_event_type, wait_event, xact_start
  FROM pg_stat_activity WHERE backend_type IN ('autovacuum launcher','autovacuum worker');
SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze, n_ins_since_vacuum,
       last_autovacuum, last_autoanalyze, autovacuum_count
  FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;

Log every run with log_autovacuum_min_duration = 0 (default 10 min) to see lines such as LOG: automatic vacuum of table "app.public.orders": index scans: 1 followed by pages, tuples, frozen and buffer-usage details.

How to change it

Change in postgresql.conf or ALTER SYSTEM, then run SELECT pg_reload_conf().