Re: Managing autovacuum freezing

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

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