Re: vacuum freeze - possible improvements

Thanks Masahiko for the response.

"What is
the use case where users want to freeze fewer transactions, meaning
invoking anti-wraparound frequently?"

My overall focus here is anti wraparound vacuum on huge tables in emergency
situations (where we reached very close to 2B transactions or already in
outage window). In this situation we want to recover ASAP instead of having
many hours of outage.The Purpose of increasing "vacuum_freeze_min_age" to
high value is that anti wraparound vacuum will have to do less work because
we are asking less transactions/tuples to freeze (Of Course subsequent
vacuum has to do the remaining work).

"So the 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."

Yes another thing here is anti wraparound vacuum also cleans dead tuples
but i am not sure what we can do to avoid that.
There can be vacuum to only freeze the tulpes?

Thanks for sharing PG14 improvements, those are nice to have. But still the
anti wraparound vacuum will have to scan all the pages (from visibility
map) even if we are freezing fewer transactions because currently there is
no way to know what block/tuple contains which transaction id. If there is
a way then it would be easier to directly freeze those tuples quickly and
advance the relfrozenxid for the table.

On Tue, Apr 13, 2021 at 7:52 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
wrote:

> 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/
>