On Thu, Feb 11, 2021 at 1:32 PM Peter Geoghegan <pg@bowt.ie> wrote:
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 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.