Message-ID: <34fbcae89ed533bb4a47b0be949b0115bb83446c.camel@j-davis.com>
Subject: Re: New strategies for freezing, advancing relfrozenxid early
From: Jeff Davis <pgsql@j-davis.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: Tue, 04 Oct 2022 19:59:49 -0700
In-Reply-To: 
 <CAH2-WzkrZYtDRLL5cWbAxmL=0Tf9=HvbkSxu+FpOkHuR_2Xhsw@mail.gmail.com>
References: 
	<CAH2-WzkFok_6EAHuK39GaW4FjEFQsY=3J0AAd6FXk93u-Xq3Fg@mail.gmail.com>
	 <1892cc797f86924e31f00fd3c703ca464936c65e.camel@j-davis.com>
	 <CAH2-WznREt2q3P+D7bq4b0ytG_z6EOQUPNyoLB+CtEactZJPiA@mail.gmail.com>
	 <d0480c1e584150863fdecd9f83ed56a8b4e88a72.camel@j-davis.com>
	 <CAH2-WzmXErgxLmYrz9M90D79VjGq4OU8Fp52ECrqRyN_OF=f+A@mail.gmail.com>
	 <CAH2-Wzn6dhY3M4MomiABKZDxVC4m=7ub3A8cy3_13j2pJTv1=w@mail.gmail.com>
	 <424877f4840495235b9be5cacc65a2b84d000566.camel@j-davis.com>
	 <CAH2-Wz=zNsPN_kOHiV852tj7bqGUCXZPVbCgo9iwxgQNmL4WjQ@mail.gmail.com>
	 <9bc85fb435df6f5fc2ececc00c0a086d41b755b4.camel@j-davis.com>
	 <CAH2-WznBEThxagQ7zy7=bfK+ev887BXpGAc4NfLG+hK+VPDZXw@mail.gmail.com>
	 <78aecdaa345ea8cb1554ff2dbc08eb0e2262130a.camel@j-davis.com>
	 <CAH2-WzkrZYtDRLL5cWbAxmL=0Tf9=HvbkSxu+FpOkHuR_2Xhsw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.44.1-0ubuntu1 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/34fbcae89ed533bb4a47b0be949b0115bb83446c.camel%40j-davis.com>
Precedence: bulk

On Tue, 2022-10-04 at 11:09 -0700, Peter Geoghegan wrote:
> So a simplistic threshold
> (combined with dynamic per-page decisions about freezing) should be
> enough to avoid most of the downside of eager freezing.

...

> I want to keep
> the cost as low as possible (often "negative cost" relative to
> Postgres 15), but overall I am consciously making a trade-off. There
> are downsides.

I am fine with that, but I'd like us all to understand what the
downsides are.

If I understand correctly:

1. Eager freezing (meaning to freeze at the same time as setting all-
visible) causes a modest amount of WAL traffic, hopefully before the
next checkpoint so we can avoid FPIs. Lazy freezing (meaning set all-
visible but don't freeze) defers the work, and it might never need to
be done; but if it does, it can cause spikes at unfortunate times and
is more likely to generate more FPIs.

2. You're trying to mitigate the downsides of eager freezing by:
  a. when freezing a tuple, eagerly freeze other tuples on that page
  b. optimize WAL freeze records

3. You're trying to capture the trade-off in #1 by using the table size
as a proxy. Deferred work is only really a problem for big tables, so
that's where you use eager freezing. But maybe we can just always use
eager freezing?:
  a. You're mitigating the WAL work for freezing.
  b. A lot of people run with checksums on, meaning that setting the
all-visible bit requires WAL work anyway, and often FPIs.
  c. All-visible is conceptually similar to freezing, but less
important, and it feels more and more like the design concept of all-
visible isn't carrying its weight.
  d. (tangent) I had an old patch[1] that actually removed
PD_ALL_VISIBLE (the page bit, not the VM bit), which was rejected, but
perhaps its time has come?

Regards,
	Jeff Davis


[1]
https://www.postgresql.org/message-id/1353551097.11440.128.camel%40sussancw=
s0025