Right, let’s talk about autovacuum. This is the feature that saves you from yourself. You see, when you DELETE a row in PostgreSQL, it’s not actually deleted. When you UPDATE a row, it’s not updated in-place. In both cases, the old version of the row becomes a “dead tuple,” a ghost haunting your table, consuming space and making sequential scans slower until someone comes along to perform an exorcism. That exorcism is VACUUM. Doing this manually for every table is a nightmare, so thank the database gods for autovacuum, the automated janitor that cleans up this mess.

But here’s the thing: you can’t just set it and forget it. A poorly tuned autovacuum is like a janitor who only shows up once a month, by which time the place is a biohazard. Or one who’s so overzealous they’re constantly vacuuming an already-clean room, wasting everyone’s time. Your job is to understand how this janitor works and give them a schedule that fits your actual workload.

The Core Mechanics: What Triggers the Janitor?

Autovacuum doesn’t just run on a timer. It’s triggered by table-specific messiness. It wakes up, checks every single table, and asks: “Has this table surpassed its designated filth threshold?”

The formula it uses is deceptively simple. For a given table, it will consider a vacuum run when: number of dead tuples > autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * number of live tuples)

Let’s break that down because it’s crucial. The autovacuum_vacuum_threshold is a baseline number of dead tuples (default 50). The autovacuum_vacuum_scale_factor (default 0.2) is a multiplier of your live rows. So for a table with 10 million rows (pg_class.reltuples), the threshold becomes 50 + (0.2 * 10,000,000) = 2,000,050 dead tuples.

See the problem? On a large table, you have to accumulate two million dead tuples before it even considers cleaning up. That’s absurd. Your 10GB table can easily become 15GB of pure bloat before the janitor lifts a finger. This is the single biggest “questionable choice” in the default configuration. The fix is to lower the scale factor for large tables, which we’ll get to.

You can see the current state of play for yourself by querying the statistics collector:

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  n_dead_tup > av_threshold + (av_scale_factor * n_live_tup) AS needs_vacuum
FROM
  pg_stat_all_tables
CROSS JOIN
  (SELECT current_setting('autovacuum_vacuum_threshold')::int AS av_threshold,
          current_setting('autovacuum_vacuum_scale_factor')::numeric AS av_scale_factor) AS defaults
ORDER BY n_dead_tup DESC;

Monitoring: Is Your Janitor Actually Working?

Hope is not a strategy. You need to monitor this. The pg_stat_all_tables view (and its cousins pg_stat_user_tables and pg_stat_sys_tables) is your best friend here. The key columns are:

  • n_live_tup: Estimated number of live rows.
  • n_dead_tup: Estimated number of dead rows. This is the most direct “how messy is it?” metric.
  • last_vacuum / last_autovacuum: When the manual or automatic vacuum last ran.
  • vacuum_count / autovacuum_count: How many times each has run.

If you see n_dead_tup climbing relentlessly on an active table and last_autovacuum is stuck in the past, your janitor is either asleep, too slow, or configured wrong.

Another fantastic tool is pg_stat_progress_vacuum. Pop this in another session while a vacuum is running to see exactly what it’s doing, what phase it’s in (like scanning the heap, vacuuming indexes, truncating the end of the table), and how many dead tuples it’s actually reaped.

SELECT
  relid::regclass AS table,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  max_dead_tuples,
  num_dead_tuples
FROM pg_stat_progress_vacuum;

Tuning: Giving Your Janitor a Better Schedule

The defaults are, frankly, terrible for any sizable production system. You will need to override them. Don’t change the global postgresql.conf settings for autovacuum_vacuum_scale_factor; that’s a sledgehammer. Use storage parameters to tune individual tables. This is the way.

The most common fix for large tables is to drastically reduce the scale factor and increase the threshold. This tells autovacuum: “Don’t wait for 20% of the table to be dead. Just clean up every 50,000 tuples or so.”

-- For a large, heavily-updated events table
ALTER TABLE public.event_log SET (
  autovacuum_vacuum_scale_factor = 0.01, -- 1% of live tuples
  autovacuum_vacuum_threshold = 50000
);

-- For a small, slowly-updated config table, make it less aggressive
ALTER TABLE public.config SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_threshold = 1000
);

Other knobs you might twist:

  • autovacuum_vacuum_cost_limit: How much I/O impact autovacuum is allowed to have per round. The default is often too low, causing it to sleep too long and never finish on busy systems. Crank this up.
  • autovacuum_naptime: How long autovacuum sleeps between checks. Lowering this on very high-write systems can help it catch problems faster.

The best practice is to be proactive. Identify your large, high-transaction tables and set their storage parameters before they become a problem. Monitor n_dead_tup and make sure the last_autovacuum is recent. Autovacuum is brilliant, but it works for you, not the other way around. You still have to be its manager.