Re: Eager page freeze criteria clarification

On Wed, Sep 27, 2023 at 5:27 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>
> On Wed, Sep 27, 2023 at 1:45 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > 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.
>
> What about my idea of holding back when some tuples are already frozen
> from before? Admittedly that's still a fairly raw idea, but something
> along those lines seems promising.

Originally, when you proposed this, the idea was to avoid freezing a
page that has some tuples already frozen because that would mean we
have frozen it and it was unfrozen after.

But, today, I was thinking about what heuristics to combine with one
that considers the average amount of time pages in a table spend
frozen [1], and I think it might be interesting to use the "some
tuples on the page are frozen" test in the opposite way than it was
originally proposed.

Take a case where you insert a row and then update it once and repeat
forever. Let's say you freeze the page before you've filled it up,
and, on the next update, the page is unfrozen. Most of the tuples on
the page will still be frozen. The next time the page is vacuumed, the
fact that the page has a lot of frozen tuples actually means that it
is probably worth freezing the remaining few tuples and marking the
page frozen.

Basically, I'm wondering if we can use the page-is-partially-frozen
test to catch cases where we are willing to freeze a page a couple of
times to make sure it gets frozen.

- Melanie

[1] https://www.postgresql.org/message-id/CAAKRu_Y0nLmQ%3DYS1c2ORzLi7bu3eWjdx%2B32BuFc0Tho2o7E3rw%40mail.gmail.com