Re: vacuum freeze - possible improvements

On Mon, Apr 12, 2021 at 5:38 PM Virender Singla <virender(dot)cse(at)gmail(dot)com> wrote:
>
> Hi Postgres Community,
>
> Regarding anti wraparound vacuums (to freeze tuples), I see it has to scan all the pages which are not frozen-all (looking at visibility map). That means even if we want to freeze less transactions only (For ex - by increasing parameter vacuum_freeze_min_age to 1B), still it will scan all the pages in the visibility map and a time taking process.

If vacuum_freeze_min_age is 1 billion, autovacuum_freeze_max_age is 2
billion (vacuum_freeze_min_age is limited to the half of
autovacuum_freeze_max_age). So vacuum freeze will still have to
process tuples that are inserted/modified during consuming 1 billion
transactions. It seems to me that it’s not fewer transactions. What is
the use case where users want to freeze fewer transactions, meaning
invoking anti-wraparound frequently?

>
> Can there be any improvement on this process so VACUUM knows the tuple/pages of those transactions which need to freeze up.
>
> Benefit of such an improvement is that if we are reaching transaction id close to 2B (and downtime), that time we can quickly recover the database with vacuuming freeze only a few millions rows with quick lookup rather than going all the pages from visibility map.

Apart from this idea, in terms of speeding up vacuum,
vacuum_failsafe_age parameter, introduced to PG14[1], would also be
helpful. When the failsafe is triggered, cost-based delay is no longer
be applied, and index vacuuming is bypassed in order to finish vacuum
work and advance relfrozenxid as quickly as possible.

Regards

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1e55e7d1755cefbb44982fbacc7da461fa8684e6

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/