Re: autovacuum_freeze_max_age on append-only tables

On Wed, Apr 20, 2022 at 4:06 PM senor <frio_cervesa(at)hotmail(dot)com> wrote:
> I'm attempting to mimic a new feature in version 13 where INSERTS will trigger vacuum for an append-only table.

The problem with that idea is that you need to express the idea that
the table needs to be vacuumed now in terms of its "age", denominated
in XIDs -- but XIDs consumed by the entire system, not just those XIDs
that happen to modify your append-only table. It will likely be very
hard for you to figure out a way to relate these logical units (XIDs)
to some kind of physical cost that captures how far behind you are on
freezing (like blocks, or even tuples). Maybe you'll find something
that works through trial and error, but I wouldn't count on it.

> I'm apparently needing an education on how this "to avoid wraparound" vacuum differs from any other. I've seen it referenced as "more aggressive" but I'd like details. An upgrade to 13 is "right around the corner".

It's complicated -- more complicated than it really should be.
Technically an anti-wraparound autovacuum and an aggressive vacuum are
two different things. In practice anti-wraparound autovacuums are
virtually guaranteed to be aggressive, though an aggressive autovacuum
may not be an antiwraparound VACUUM (sometimes we do aggressive
vacuuming because autovacuum launched a worker before
age(relfrozenxid) reached autovacuum_freeze_max_age, but after
age(relfrozenxid) reached vacuum_freeze_table_age).

See my recent response to a similar question here:

https://postgr.es/m/CAH2-WzkFQ-okvVXizpy4dCEVq75N-Qykh=crhZaO-eaJfLVOPQ@mail.gmail.com

--
Peter Geoghegan