From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Rob Emery <re-pgsql@codeweavers.net>
To: pgsql-admin@postgresql.org
Subject: Re: Vacuum & pg_class.relallvisible
Date: Thu, 17 Oct 2019 18:41:12 +0200
Message-ID: <ef9dc7e4f6591f1025e6cbe5bb62fc20d8cee305.camel@cybertec.at> (raw)
In-Reply-To: <CAPCETpuY2ooOjzhWzSAaQq_gCO8oK-W3emBZr3yA_5EdGYEUtA@mail.gmail.com>
References: <CAPCETpuY2ooOjzhWzSAaQq_gCO8oK-W3emBZr3yA_5EdGYEUtA@mail.gmail.com>
On Thu, 2019-10-17 at 17:32 +0100, Rob Emery wrote:
> I've been attempting to figure out if the autovacuum/vacuum process will use
> pgclass.relallvisible when vacuuming a table to know if it's able to
> skip freezing at all.
>
> Basically we have tables that this query:
> ```
> SELECT c.oid::regclass as table_name,
> greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
> FROM pg_class c
> LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
> WHERE c.relkind IN ('r', 'm')
> ORDER BY age DESC
> ```
>
> returns the age as greater than 'autovacuum_freeze_max_age' which was making
> us believe that autovacuum wasn't running.
>
> When we looked into the actual rows with:
>
> ```
> SELECT t_infomask::bit(16) as bits, t_infomask::bit(16) & (x'0100' |
> x'0200') as isFrozen FROM heap_page_items(get_raw_page('tablename',
> 0))
> ```
> we could see that it looks like all the rows in the table are frozen;
> so it would never need a vacuum!
>
> I don't understand how the autovacuum knows that it can skip that
> table without looking at all the rows, which is the process of
> vacuuming that table!
>
> Much appreciated if someone can clean up my understanding.
PostgreSQL 9.6 had this new feature:
Avoid re-vacuuming pages containing only frozen tuples (Masahiko Sawada, Robert Haas, Andres Freund)
Formerly, anti-wraparound vacuum had to visit every page of a table, even pages where there
was nothing to do. Now, pages containing only already-frozen tuples are identified in the table's
visibility map, and can be skipped by vacuum even when doing transaction wraparound prevention.
This should greatly reduce the cost of maintaining large tables containing mostly-unchanging data.
So, to the best of my knowledge (I didn't read the code),
autovacuum should still launch an anti-wraparound worker, but that will
look at the visibility map, determine it has nothing to do and just update
"relfrozenxid".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, re-pgsql@codeweavers.net
Subject: Re: Vacuum & pg_class.relallvisible
In-Reply-To: <ef9dc7e4f6591f1025e6cbe5bb62fc20d8cee305.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox