Horton

Re: New strategies for freezing, advancing relfrozenxid early

Jeff Davis <pgsql@j-davis.com>

View thread View on postgr.es
From: Jeff Davis <pgsql@j-davis.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-10-05T02:59:49Z
Lists: pgsql-hackers 
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%40sussancws0025

Commits

  1. Revert "Add eager and lazy freezing strategies to VACUUM."

  2. Add eager and lazy freezing strategies to VACUUM.

  3. Refine the definition of page-level freezing.

  4. Avoid special XID snapshotConflictHorizon values.

  5. Add page-level freezing to VACUUM.

  6. Remove overzealous MultiXact freeze assertion.

  7. Refactor how VACUUM passes around its XID cutoffs.

  8. Deduplicate freeze plans in freeze WAL records.

  9. Set relfrozenxid to oldest extant XID seen by VACUUM.

  10. Only skip pages marked as clean in the visibility map, if the last 32

  11. Add vacuum_freeze_table_age GUC option, to control when VACUUM should