Re: Add last_vacuum_index_scans in pg_stat_all_tables

On Fri, Jul 8, 2022 at 10:47 AM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> Saving some sort of history would be much more useful, but of course a
> lot more work.

I think that storing a certain amount of history would be very useful,
for lots of reasons. Not just for instrumentation purposes; I envisage
a design where VACUUM itself makes certain decisions based on the
history of each VACUUM operation against the table. The direction that
things have taken suggests a certain amount about the direction that
things are going in, which we should try to influence.

The simplest and best example of how this could help is probably
freezing, and freeze debt. Currently, the visibility map interacts
with vacuum_freeze_min_age in a way that allows unfrozen all-visible
pages to accumulate. These pages won't be frozen until the next
aggressive VACUUM. But there is no fixed relationship between the
number of XIDs consumed by the system (per unit of wallclock time) and
the number of unfrozen all-visible pages (over the same duration). So
we might end up having to freeze an absolutely enormous number of
pages in the eventual aggressive vacuum. We also might not -- it's
really hard to predict, for reasons that just don't make much sense.

There are a few things we could do here, but having a sense of history
seems like the important part. If (say) the table exceeds a certain
size, and the number of all-visible pages grows and grows (without any
freezing taking place), then we should "proactively" freeze at least
some of the unfrozen all-visible pages in earlier VACUUM operations.
In other words, we should (at the very least) spread out the burden of
freezing those pages over time, while being careful to not pay too
much more than we would with the old approach if and when the workload
characteristics change again.

More generally, I think that we should blur the distinction between
aggressive and non-aggressive autovacuum. Sure, we'd still need VACUUM
to "behave aggressively" in some sense, but that could all happen
dynamically, without committing to a particular course of action until
the last moment -- being able to change our minds at the last minute
can be very valuable, even though we probably won't change our minds
too often.

--
Peter Geoghegan