vacuum freeze - possible improvements

From: Virender Singla <virender(dot)cse(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: vacuum freeze - possible improvements
Date: 2021-04-12 08:19:04
Message-ID: CAM6Zo8y-fuCu+3zf42=JcMtN5xU5hQqbYXAii-QKU8U4oAmVJw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

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.

For Ex - A Binary Tree structure where it gets all the rows corresponding
to a table including transaction ids. So whenever we say free all tuples
having transaction id greater than x and less than y. Yes that makes extra
overhead on data load and lots of other things to consider.

Thanks,
Virender

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Julien Rouhaud 2021-04-12 08:43:24 Re: Problems around compute_query_id
Previous Message Christoph Berg 2021-04-12 08:09:39 Re: pgsql: Move tablespace path re-creation from the makefiles to pg_regres