Re: vacuum freeze - possible improvements - Mailing list pgsql-hackers

On Tue, Apr 13, 2021 at 1:51 PM Virender Singla <virender.cse@gmail.com> wrote:
>
> 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
ofoutage.The Purpose of increasing "vacuum_freeze_min_age" to high value is that anti wraparound vacuum will have to do
lesswork because we are asking less transactions/tuples to freeze (Of Course subsequent vacuum has to do the remaining
work).

I think I understood your proposal. For example, if we insert 500GB
tuples during the first 1 billion transactions and then insert more
500GB tuples into another 500GB blocks during the next 1 billion
transactions, vacuum freeze scans 1TB whereas we scans only 500GB that
are modified by the first insertions if we’re able to freeze directly
tuples that are older than the cut-off. Is that right?

>
> "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?

I think it's a good idea to skip all work except for freezing tuples
in emergency cases. Thanks to vacuum_failsafe_age we can avoid index
vacuuming, index cleanup, and heap vacuuming.

>
> Thanks for sharing PG14 improvements, those are nice to have. But still the anti wraparound vacuum will have to scan
allthe pages (from visibility map) even if we are freezing fewer transactions because currently there is no way to know
whatblock/tuple contains which transaction id. 

Yes, that feature is to speed up vacuum by dynamically disabling both
cost-based delay and some cleanup work whereas your idea is to do that
by speeding up heap scan.

> If there is a way then it would be easier to directly freeze those tuples quickly and advance the relfrozenxid for
thetable. 

Maybe we can track the oldest xid per page in a map like visiblity map
or integrate it with visibility map. We need to freeze only pages that
are all-visible and whose oldest xid is older than the cut-off xid. I
think we need to track both xid and multi xid.

Regards,


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