Re: New IndexAM API controlling index vacuum strategies

On Fri, Mar 19, 2021 at 3:36 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>
> On Thu, Mar 18, 2021 at 3:32 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > If we have the constant threshold of 1 billion transactions, a vacuum
> > operation might not be an anti-wraparound vacuum and even not be an
> > aggressive vacuum, depending on autovacuum_freeze_max_age value. Given
> > the purpose of skipping index vacuuming in this case, I think it
> > doesn't make sense to have non-aggressive vacuum skip index vacuuming
> > since it might not be able to advance relfrozenxid. If we have a
> > constant threshold, 2 billion transactions, maximum value of
> > autovacuum_freeze_max_age, seems to work.
>
> I like the idea of not making the behavior a special thing that only
> happens with a certain variety of VACUUM operation (non-aggressive or
> anti-wraparound VACUUMs). Just having a very high threshold should be
> enough.
>
> Even if we're not going to be able to advance relfrozenxid, we'll
> still finish much earlier and let a new anti-wraparound vacuum take
> place that will do that -- and will be able to reuse much of the work
> of the original VACUUM. Of course this anti-wraparound vacuum will
> also skip index vacuuming from the start (whereas the first VACUUM may
> well have done some index vacuuming before deciding to end index
> vacuuming to hurry with finishing).

But we're not sure when the next anti-wraparound vacuum will take
place. Since the table is already vacuumed by a non-aggressive vacuum
with disabling index cleanup, an autovacuum will process the table
when the table gets modified enough or the table's relfrozenxid gets
older than autovacuum_vacuum_max_age. If the new threshold, probably a
new GUC, is much lower than autovacuum_vacuum_max_age and
vacuum_freeze_table_age, the table is continuously vacuumed without
advancing relfrozenxid, leading to unnecessarily index bloat. Given
the new threshold is for emergency purposes (i.g., advancing
relfrozenxid faster), I think it might be better to use
vacuum_freeze_table_age as the lower bound of the new threshold. What
do you think?

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/