0

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)?

1
  • What we can't see on your chart is when the autovac is occuring. A new one can't start until the previous one is finished, so if they are already running back to back to back you can't get them to start earlier than they already are. You would have to make them run faster. – jjanes Mar 2 '20 at 13:06
2

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.

7
  • Wouldn't it also make sense to increase vacuum_cost_limit? – a_horse_with_no_name Mar 2 '20 at 8:35
  • @a_horse_with_no_name Fundamentally yes, but if you set autovacuum_vacuum_cost_delay, it shouldn't matter any more - it is no faster to not pause less often. – Laurenz Albe Mar 2 '20 at 8:38
  • Running vacuum when you reach 10M dead rows (0.2*50M) caused many issues to us (there are also many blog posts about that). That's why we changed the default. When we lowered the values some months ago we saw extreme benefits in the query performance. Maybe 10k is slightly low and we can increase that to 100k... but how can we run vacuum faster? Does it make sense to run it faster or it is correct to run it at a snail's pace? – collimarco Mar 2 '20 at 10:47
  • I gave you instructions how to make it run faster (autovacuum_vacuum_cost_delay = 0). I think that no scale_factor and a threshold of 100000 is too low, but can you describe your problems (best by editing your question)? – Laurenz Albe Mar 2 '20 at 12:18
  • 2
    "consider setting autovacuum_vacuum_scale_factor to 0.1. I wouldn't go lower than that." If you care about efficient index-only-scans, you might want to go much lower than that. Turning over 10% of the rows could dirty 100% of the blocks (several times over). – jjanes Mar 2 '20 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.