Re: Managing autovacuum freezing

On Thu, Feb 11, 2021 at 11:49 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Thu, Feb 11, 2021 at 9:13 AM Don Seiler <don(at)seiler(dot)us> wrote:
> > My understanding is that these manual VACUUM ANALYZE jobs are not
> freezing rows that regular autovacuuming would otherwise be doing, which
> leads up to the big anti-wraparound job.
>
> They will freeze rows, but not aggressively. The antiwraparound vacuum
> might block on acquiring buffer pins, low level stuff like that.
>
> Perhaps you should change the vacuum_index_cleanup reloption to 'off'
> for the table, but make the scripted overnight vacuums directly
> specify INDEX_CLEANUP=on. That way index cleanup would still be
> performed for the vacuums that run overnight, though not for the
> antiwraparound vacuums, where the overhead may be a real issue.
>

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.

Cheers,
Don.

--
Don Seiler
www.seiler.us