Re: Managing autovacuum freezing

On Thu, Feb 11, 2021 at 11:06 AM Don Seiler <don@seiler.us> wrote:
> Thanks for the response, Peter. This table *does* have 14 indexes on it as well, including on GIN index (rest are
btree,some are partial indexes). I've had a separate task on the back burner to try to identify any redundant ones.
 
>
> In the scenario you describe, would we re-enable the routine autovacuuming? I'm assuming so but wanted to make it
clear.

I'm not sure that you should re-enable av, actually -- you should at
least be careful with combing it with vacuum_index_cleanup=off. The
problem with the vacuum_index_cleanup table storage param that
controls this behavior is that it will apply generally -- unless you
override it using the VACUUM option each time. I strongly doubt that
it could ever make sense to completely avoid index vacuuming forever
here, so you certainly don't want to let that happen. The
vacuum_index_cleanup table param makes that extreme approach a
possibility, at least on Postgres 12+, but it's probably only
something that makes sense with an append-only table.

It might well not have made sense to disable AV here (it's often not a
good idea I find), though running VACUUM at night time probably was a
good idea. But vacuum_index_cleanup doesn't have granular options
about when and how skipping indexes applies as a matter of policy,
which makes it a bit tricky.

--
Peter Geoghegan