Re: Vacuum & pg_class.relallvisible - Mailing list pgsql-admin

Hiya,

Thanks for the reply.  I should have mentioned we're 9.5.19 so I don't believe
that behaviour would apply (even though it sounds absolutely like what I want).

However with fresh eyes this morning, I can't find any tables with
age(relfrozenxid)
greater than autovacuum_freeze_max_age; so I think I misinterpreted a number
somewhere and then ended up deeply confused.

Thanks,
Rob



On 17/10/2019, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> 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
>
>


-- 
Robert Emery
Infrastructure Director

01785 711633

<> Codeweavers

Phone:  0800 021 0888
Website:  codeweavers.net

Barn 4, Dunston Business Village, ST18 9AB. Registered in England and
Wales No. 04092394 VAT registration no. 974 9705 63

--