Re: New strategies for freezing, advancing relfrozenxid early

On Tue, Dec 13, 2022 at 8:00 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>
> On Mon, Dec 12, 2022 at 3:47 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > But the heuristic also seems off to me. What if you have lots of
partitions
> > in an append-only range-partitioned table? That would tend to use the
> > lazy freezing strategy (because each partition is small), but that's
> > not what you want. I understand heuristics aren't perfect, but it feels
> > like we could do something better.
>
> It is at least vastly superior to vacuum_freeze_min_age in cases like
> this. Not that that's hard -- vacuum_freeze_min_age just doesn't ever
> trigger freezing in any autovacuum given a table like pgbench_history
> (barring during aggressive mode), due to how it interacts with the
> visibility map. So we're practically guaranteed to do literally all
> freezing for an append-only table in an aggressive mode VACUUM.
>
> Worst of all, that happens on a timeline that has nothing to do with
> the physical characteristics of the table itself (like the number of
> unfrozen heap pages or something).

If the number of unfrozen heap pages is the thing we care about, perhaps
that, and not the total size of the table, should be the parameter that
drives freezing strategy?

> That said, I agree that the system-level picture of debt (the system
> level view of the number of unfrozen heap pages) is relevant, and that
> it isn't directly considered by the patch. I think that that can be
> treated as work for a future release. In fact, I think that there is a
> great deal that we could teach autovacuum.c about the system level
> view of things -- this is only one.

It seems an easier path to considering system-level of debt (as measured by
unfrozen heap pages) would be to start with considering table-level debt
measured the same way.

--
John Naylor
EDB: http://www.enterprisedb.com