On Thu, Apr 21, 2022 at 8:14 PM Senor <frio_cervesa@hotmail.com> wrote:
> Are the autovacuum_vacuum_cost_* settings handled any differently for
> 'to avoid wraparound' vacuums? I understand that it won't give up a lock
> but I was expecting it to still back off due to cost and allow the query
> with conflicting lock to proceed.
In general, no. For the most part an antiwraparound autovacuum does
exactly the same work as any other autovacuum. Or any other aggressive
VACUUM, at least. But even the extra work that it does over what
non-aggressive VACUUM is still work that any VACUUM might do, if the
circumstances were right.
We still freeze in regular VACUUMs, provided we scan pages with XIDs
that are sufficiently old. The most important difference between it
and aggressive VACUUM is that the former can skip all-visible pages
that have unfrozen XIDs, putting that work off. This can sometimes
lead to a big balloon payment later on, when you finally have an
aggressive VACUUM. I think that that's a design flaw that ought to be
fixed.
Currently non-aggressive VACUUMs always skip all-visible pages. They
should probably freeze some older all-visible pages eagerly, rather
than skipping them, so that the system never gets too far behind on
freezing.
> Is there any benefit to manually running a vacuum every so many inserts
> as opposed to using autovacuum_freeze_max_age. And in this case should
> it be a vacuum freeze.
Given your restrictions, this is probably the best option available.
But maybe you should just set vacuum_freeze_min_age to 0 at the table
level, instead of using vacuum freeze (so you freeze more without
doing aggressive vacuuming all the time, which FREEZE also forces).
Users understandably think that there are several different flavors of
vacuum, but that's not really true (apart from VACUUM FULL, which
really is quite different). The difference between aggressive and
non-aggressive can be big in practice due to an accumulation of
unfrozen pages over multiple non-aggressive vacuums.
--
Peter Geoghegan
