Re: Managing autovacuum freezing

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

> 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.
>

Is there anything I could/should do to get my nightly manual vacuum jobs to
do more freezing? It doesn't look like I can set vacuum_freeze_min_age to a
per-table value, except for autovacuum_freeze_min_age which the name would
suggest only applies to autovacuum. Am I wrong in thinking that regular
autovacuuming will do more proactive freezing that my nightly vacuuming
doesn't?

Would a nightly "vacuum freeze" be overkill/unreasonable as an alternative?
I know it would be a lot more I/O but at least we'd limit it to off-peak
hours.

Don.

--
Don Seiler
www.seiler.us