Re: vacuum freeze - possible improvements

exactly my point, want to scan only 500GB data instead of 1TB. That can be
handy for vacuum freeze at a dangerous stage (reaching towards 2B).

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

Yes I thought of that (keep track of olderst xid per page instead of per
tuple), only thing here is every time there is some modification on the
page, that oldest xid needs to be recalculated for respective page. Still
that makes sense with kind of BRIN type structure to keep the xid per page.
With Binary Tree Index structure, new transaction/tuple will fit right
side (as that would be news transaction until 2B) and then other side leaf
blocks can be removed with every vacuum freeze.

On Tue, Apr 13, 2021 at 6:02 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
wrote:

> On Tue, Apr 13, 2021 at 1:51 PM Virender Singla <virender(dot)cse(at)gmail(dot)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 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).
>
> 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 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.
>
> 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 the table.
>
> 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/
>