Message-ID: <ef9dc7e4f6591f1025e6cbe5bb62fc20d8cee305.camel@cybertec.at>
Subject: Re: Vacuum & pg_class.relallvisible
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Rob Emery <re-pgsql@codeweavers.net>, pgsql-admin@postgresql.org
Date: Thu, 17 Oct 2019 18:41:12 +0200
In-Reply-To: 
 <CAPCETpuY2ooOjzhWzSAaQq_gCO8oK-W3emBZr3yA_5EdGYEUtA@mail.gmail.com>
References: 
	<CAPCETpuY2ooOjzhWzSAaQq_gCO8oK-W3emBZr3yA_5EdGYEUtA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.32.4 (3.32.4-1.fc30) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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