Re: New strategies for freezing, advancing relfrozenxid early

Hi,

On 2023-01-25 18:43:10 -0800, Peter Geoghegan wrote:
> On Wed, Jan 25, 2023 at 6:33 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > As far as I can tell, with the eager strategy, the only thing
> > vacuum_freeze_min_age really influences is whether we'll block waiting for a
> > cleanup lock. IOW, VACUUM on a table > vacuum_freeze_strategy_threshold is
> > now a slightly less-blocking version of VACUUM FREEZE.
>
> That's simply not true, at all. I'm very surprised that you think
> that. The commit message very clearly addresses this.

It says something like that, but it's not really true:

Looking at the results of
DROP TABLE IF EXISTS frak;
-- autovac disabled so we see just the result of the vacuum below
CREATE TABLE frak WITH (autovacuum_enabled=0) AS SELECT generate_series(1, 10000000);
VACUUM frak;
SELECT pg_relation_size('frak') / 8192 AS relsize_pages, SUM(all_visible::int) all_vis_pages, SUM(all_frozen::int) all_frozen_pages FROM pg_visibility('frak');

across releases.

In < 16 you'll get:
┌───────────────┬───────────────┬──────────────────┐
│ relsize_pages │ all_vis_pages │ all_frozen_pages │
├───────────────┼───────────────┼──────────────────┤
│ 44248 │ 44248 │ 0 │
└───────────────┴───────────────┴──────────────────┘

You simply can't freeze these rows, because they're not vacuum_freeze_min_age
xids old.

With 16 and the default vacuum_freeze_strategy_threshold you'll get the same
(even though we wouldn't actually trigger an FPW).

With 16 and vacuum_freeze_strategy_threshold=0, you'll get:
┌───────────────┬───────────────┬──────────────────┐
│ relsize_pages │ all_vis_pages │ all_frozen_pages │
├───────────────┼───────────────┼──────────────────┤
│ 44248 │ 44248 │ 44248 │
└───────────────┴───────────────┴──────────────────┘

IOW, basically what you get with VACUUM FREEZE.

That's actually what I was complaining about. The commit message in a way is
right that
Settings
like vacuum_freeze_min_age still get applied in just the same way in
every VACUUM, independent of the strategy in use. The only mechanical
difference between eager and lazy freezing strategies is that only the
former applies its own additional criteria to trigger freezing pages.

but that's only true because page level freezing neutered
vacuum_freeze_min_age. Compared to <16, it's a *huge* change.

Yes, it's true that VACUUM still is less agressive than VACUUM FREEZE, even
disregarding cleanup locks, because it won't freeze if there's non-removable
rows on the page. But more often than not that's a pretty small difference.

> Once again I'll refer you to my Wiki page on this:
>
> https://wiki.postgresql.org/wiki/Freezing/skipping_strategies_patch:_motivating_examples#Patch_2
>
> The difference between this and VACUUM FREEZE is described here:
>
> "Note how we freeze most pages, but still leave a significant number
> unfrozen each time, despite using an eager approach to freezing
> (2981204 scanned - 2355230 frozen = 625974 pages scanned but left
> unfrozen). Again, this is because we don't freeze pages unless they're
> already eligible to be set all-visible.

The only reason there is a substantial difference is because of pgbench's
uniform access pattern. Most real-world applications don't have that.

Greetings,

Andres Freund