Horton

Re: New strategies for freezing, advancing relfrozenxid early

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, Jeff Davis <pgsql@j-davis.com>, Matthias van de Meent <boekewurm+postgres@gmail.com>, John Naylor <john.naylor@enterprisedb.com>, Justin Pryzby <pryzby@telsasoft.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-01-26T21:06:31Z
Lists: pgsql-hackers 
On Thu, Jan 26, 2023 at 12:54 PM Robert Haas <robertmhaas@gmail.com> wrote:
> > The overwhelming cost is usually FPIs in any case. If you're not
> > mostly focussing on that, you're focussing on the wrong thing. At
> > least with larger tables. You just have to focus on the picture over
> > time, across multiple VACUUM operations.
>
> I think that's all mostly true, but the cases where being more
> aggressive can cause *extra* FPIs are worthy of just as much attention
> as the cases where we can reduce them.

It's a question of our exposure to real problems, in no small part.
What can we afford to be wrong about? What problem can be fixed by the
user more or less as it emerges, and what problem doesn't have that
quality?

There is very good reason to believe that the large majority of all
data that people store in a system like Postgres is extremely cold
data:

https://www.microsoft.com/en-us/research/video/cost-performance-in-modern-data-stores-how-data-cashing-systems-succeed/
https://brandur.org/fragments/events

Having a separate aggressive step that rewrites an entire large table,
apparently at random, is just a huge burden to users. You've said that
you agree that it sucks, but somehow I still can't shake the feeling
that you don't fully understand just how much it sucks.

-- 
Peter Geoghegan

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