Re: Managing autovacuum freezing

On Thu, Feb 11, 2021 at 11:23 AM Don Seiler <don@seiler.us> wrote:
> Right. I was thinking we'd still do the nightly manual vacuum, adding in the INDEX_CLEANUP option after disabling the
attributeat the table level. But was wondering if we should still consider enabling autovacuum on that table to
hopefullylessen the work needed by the wraparound-prevention aggressive autovac. 

Regular autovacuum (any VACUUM) will freeze tuples, so that certainly
makes sense to me. Sometimes it can seem like it's skipping the work
done in an anti-wraparound vacuum entirely because only the latter
insists on doing cleanup of every heap page, if necessary by waiting
to get a buffer pin -- and that could be harder during a busy period.
And also because the visibility bits in the VM can allow regular
vacuum to skip a lot more work than anti-wraparound vacuum, which can
only skip pages with the freeze bit set.

An anti-wraparound vacuum is actually less different to a regular
autovacuum than most users intuit. Though I get it why it seems that
way -- the practical difference can be really noticeable in
production, even though the theoretical differences seem rather small
to me.

--
Peter Geoghegan