PostgreSQL vacuum starting too late (every 100k instead of 10k dead rows)

autovacuumpostgresqlpostgresql-11

I have a large table (50M) and autovacuum seems to work properly in general. However I started monitoring it with Datadog and I have noticed something strange:

select relname, reloptions from pg_class where relname='subscriptions';

{
  autovacuum_vacuum_scale_factor=0,
  autovacuum_analyze_scale_factor=0,
  autovacuum_vacuum_threshold=10000,
  autovacuum_analyze_threshold=10000
}

So you expect the table to be cleaned every time it reaches 10k dead rows.

However the graph of dead rows shows that vacuum starts much later, at about 100k or even 200k dead rows:

the graph of dead rows

Why? How can I make the vacuum run more frequently (every 10k dead rows)?

Best Answer

Autovacuum will start right away, but it takes some time to process the table. During that time, you don't see a decrease in the number of dead tuples.

To make autovacuum be more aggressive, don't make it start sooner, but make it faster. A threshold of 10000 with a scale factor of 0 seems insane to me, it will cause autovacuum to permanently process this table (at a snail's pace), and you don't end up with significantly less bloat.

Every table needs a certain amount of bloat, and the 20% you have by default are typically fine.

So I recommend these settings:

ALTER TABLE subscriptions
   RESET (autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor),
   SET (autovacuum_analyze_threshold = 50000, autovacuum_vacuum_cost_delay = 0);

That will cause autovacuum to process the table as fast as possible, and you won't end up with much more than 20% bloat. Autoanalyze will run very frequently.

If you really don't want 20% of bloat in spite of what I said, consider setting autovacuum_vacuum_scale_factor to 0.1. I wouldn't go lower than that.

Related Question