Re: Eager page freeze criteria clarification

Hi,

On 2023-09-27 13:14:41 -0700, Peter Geoghegan wrote:
> On Wed, Sep 27, 2023 at 1:03 PM Andres Freund <andres@anarazel.de> wrote:
> > I suspect that medium term the better approach would be to be much more
> > aggressive about setting all-visible, including as part of page-level
> > visibility checks, and to deal with the concern of vacuum not processing such
> > pages soon by not just looking at unmarked pages, but also a portion of the
> > all-visible-but-not-frozen pages (the more all-visible-but-not-frozen pages
> > there are, the more of them each vacuum should process). I think all-visible
> > is too important for index only scans, for us to be able to remove it, or
> > delay setting it until freezing makes sense.
> >
> > My confidence in my gut feeling isn't all that high here, though.
> 
> I think that this is a bad idea. I see two main problems with
> "splitting the difference" like this:
> 
> 1. If we randomly scan some all-visible pages in non-aggressive
> VACUUMs, then we're sure to get FPIs in order to be able to freeze.
> 
> As a general rule, I think that we're better of gambling against
> future FPIs, and then pulling back if we go too far. The fact that we
> went one VACUUM operation without the workload unsetting an
> all-visible page isn't that much of a signal about what might happen
> to that page.

I think we can afford to be quite aggressive about opportunistically freezing
when doing so wouldn't emit an FPI. I am much more concerned about cases where
opportunistic freezing requires an FPI - it'll often *still* be the right
choice to freeze the page, but we need a way to prevent that from causing a
lot of WAL in worse cases.

I think the case where no-fpi-required-freezing is a problem are small,
frequently updated, tables, which are very frequently vacuumed.



> 2. Large tables (i.e. the tables where it really matters) just don't
> have that many VACUUM operations, relative to everything else.

I think we need to make vacuums on large tables much more aggressive than they
are now, independent of opportunistic freezing heuristics. It's idiotic that
on large tables we delay vacuuming until multi-pass vacuums are pretty much
guaranteed.  We don't have the stats to detect that, but if we had them, we
should trigger autovacuums dead items + dead tuples gets to a significant
fraction of what can be tracked in m_w_m.

The current logic made some sense when we didn't have the VM, but now
autovacuum scheduling is influenced by the portion of the table that that
vacuum will never look at, which makes no sense. One can argue that that
frozen-portion is a proxy for the size of the indexes that would need to be
scanned - but that also doesn't make much sense to me, because the number &
size of indexes depends a lot on the workload and because we skip index
vacuums when not necessary.

I guess we could just use (relation_size - skipped_pages) *
autovacuum_vacuum_scale_factor as the threshold, but we don't have a cheap way
to compute the number of frozen pages right now (we do have relallvisible for
non-aggressive vacuums).


> Who says we'll get more than one opportunity per page with these tables,
> even with this behavior of scanning all-visible pages in non-aggressive
> VACUUMs?  Big append-only tables simply won't get the opportunity to catch
> up in the next non-aggressive VACUUM if there simply isn't one.

I agree that we need to freeze pages in append only tables ASAP. I don't think
they're that hard a case to detect though. The harder case is the - IME very
common - case of tables that are largely immutable but have a moving tail
that's hotly updated.

Greetings,

Andres Freund