Re: [HACKERS] GUC for cleanup indexes threshold. - Mailing list pgsql-hackers

On Fri, Mar 3, 2017 at 11:49 AM, Peter Geoghegan <pg@bowt.ie> wrote:
>> Please verify my understanding of your thought process: We don't have
>> to freeze indexes at all, ever, so if we see index bloat as a separate
>> problem, we also see that there is no need to *link* index needs to
>> the need for freezing. XID burn rate is a very bad proxy for how
>> bloated an index may be. Besides, we already have a separate trigger
>> for the thing that *actually* matters to indexes (the vacuum threshold
>> stuff).

Wait, I have this wrong. You're only ever avoiding
lazy_cleanup_index(), never lazy_vacuum_index() (I repeated Kuntal's
mistake). So, you're focused on avoiding recycling pages in the case
that no actual bloat is in the index. That hasn't changed from your
earliest version, where there was no new GUC. You are not proposing to
increase VACUUM's tolerance of "the bad kind of bloat". It seems that
this is much less risky than I first thought, then.

> Another thing I wonder about: It would be okay to use the number of
> unset freeze map bits as a reasonable proxy for how much bloat is in
> the index the first time we vacuum. But, don't we then set the freeze
> map bits, losing any record of having skipped indexes?

Still seems like we may want to remember that we skipped the
btvacuumcleanup() calls, to build back-pressure, though. That is, we
might want to do what Simon suggests: something may be stored in the
B-Tree meta-page (for example) to remember that we tolerated not doing
a btvacuumcleanup() when we could have and maybe should have. It would
remember the last number of pages deleted by the last VACUUM. That's
the only thing I can think of that matters. I believe this is what
Simon meant when he said "half dead pages".

OTOH, I see no reason to insist on this meta-page stuff. The only
thing that can delete a B-Tree page is VACUUM. Hopefully, a subsequent
VACUUM will occur that goes on to recycle those deleted pages. But,
it's not as if a "recycling-orientated B-Tree VACUUM" (a
btvacuumcleanup() call to btvacuumscan()) occurs because of a build-up
of back-pressure that has any relationship to recycling. Back pressure
is actually created by "real" bloat, or maybe using up XIDs, which is
not the same thing. Bloat cannot occur within B-Tree pages that are
already fully deleted and (almost?) eligible to be recycled.

In other words, the number of B-Tree pages that the last VACUUM
deleted, and thus made eligible to recycle by the next VACUUM has no
relationship with the number of pages the next VACUUM will itself end
up deleting, in general, or how long it will be before that next
VACUUM comes, if it comes at all, or anything else that seems at all
relevant. In other other words, you are only preventing recycling that
would have occurred pretty much by accident before now, due to
concerns that had nothing to do with recycling in particular.

The really important thing is that you have bound the amount of
recycling that VACUUM can now fail to do to only one "round" of
VACUUM, because newly deleted pages can only occur due to another
VACUUM, which will always do however much recycling it can manage to
do as it scans a B-Tree index. I'm not very worried about VACUUM not
being super-eager about reclaiming disk space for the FSM if there is
a natural limit to that.

It would be intolerable for it to ever be possible for
recyclable-but-not-recycled pages to grow indefinitely, but I see no
risk of that here.

-- 
Peter Geoghegan