MIME-Version: 1.0
From: Graham Hay <grahamrhay@gmail.com>
Date: Thu, 19 Dec 2024 13:37:04 +0000
Message-ID: 
 <CAABECY33tbZr3fNvSTM6oA7W9G_PTOLrMqM1hJ1UwP20pRc8MA@mail.gmail.com>
Subject: Aggressive vacuum
To: pgsql-performance@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAABECY33tbZr3fNvSTM6oA7W9G_PTOLrMqM1hJ1UwP20pRc8MA%40mail.gmail.com>
Precedence: bulk

Can anyone enlighten me on exactly what triggers an "aggressive"
vacuum? I have read the docs multiple times (and watched several
videos!), but the changes I make do not have the expected outcomes; so
I'm clearly missing something.

https://www.postgresql.org/docs/15/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

I have a fairly large table, that has outgrown the vacuum defaults. We
started by setting:

        - autovacuum_vacuum_scale_factor=0
        - autovacuum_vacuum_threshold=100000 # 100K

(10% was a lot of tuples). This was definitely an improvement, but
when the big vacuum (freeze) kicked off there was a noticeable
io/latency spike. We then tried setting:

      - autovacuum_freeze_table_age=100000000 # 100M

which seemed to make it happen more frequently (and one would hope,
with less work to do). We reduced this to 50M, and again it was more
frequent (but not the 2x I expected). And when I dropped it to 10M,
nothing changed.

After re-reading the docs, I got the impression I also needed to set:

        - autovacuum_freeze_min_age=10000000 # 10M

As the system default was 50M. And again, this had an effect, but not
the 5x I was expecting. The docs say:

> all-visible but not all-frozen pages are scanned if the number of transactions that have passed since the last such scan is greater than vacuum_freeze_table_age minus vacuum_freeze_min_age

but wouldn't that have been 10M - 50M? i.e. -40M. Is there some other
setting I'm missing?

All suggestions welcome!

Thanks,

Graham