Re: Managing autovacuum freezing

On Thu, Feb 11, 2021 at 1:21 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Thu, Feb 11, 2021 at 11:06 AM Don Seiler <don(at)seiler(dot)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.
>

Right. I was thinking we'd still do the nightly manual vacuum, adding in
the INDEX_CLEANUP option after disabling the attribute at the table level.
But was wondering if we should still consider enabling autovacuum on that
table to hopefully lessen the work needed by the wraparound-prevention
aggressive autovac.

Don.

--
Don Seiler
www.seiler.us