Why is a Read-only Table Gets Autovacuumed "to prevent wraparound"

From: Fred Habash <fmhabash(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Why is a Read-only Table Gets Autovacuumed "to prevent wraparound"
Date: 2023-01-16 12:48:24
Message-ID: CADpeV5xCiU_arY4gS6dtQqL2-7trLeLpn3GF_+k3dJ3dhb5JbA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

This is a puzzle I have not been able to crack yet.

We have a single-page table with 28 rows that is purely read-only. There
isn't a way in postgres to make a table RO, but I say this with confidence
because pg_stat_user_tables has always showed 0 updates/deletes/inserts.

Furthermore, the schema app developers know, for certain, this table does
not get changed at all.

We installed scripts that run every few minutes that do a 'select *' and
over a period of days, we have not seen a change.

We disabled autovacuum on this table '{autovacuum_enabled=false}'. But,
despite the fact that this table is read-only (by design) and autovac id is
disabled, it got autovac'd twice in less than 10 days and on both
occasions, pg_stat_activity showed the worker with 'to prevent wraparound'.
This explains why autovac did not honor the disabled status.

But why is this table autovac'd at all?

I have a hypothesis, but I need it validated and may be indicate if it is
scientifically plausible. It goes like this ...

1. Application initiates a T1 transaction
2. App. reads multiple tables to get product metadata and this small table
is one of them.
3. At some point, app. locks a row on one of the tables (not the small
one).
4. Client app. keeps session 'idle in transaction' while it refreshes a
webpage to render the data.
4. Once the client app verifies the web app has rendered the data
correctly, it comes back to the database to finish the transaction.

So, even if the small table is never changed, it is part of a transaction
to be queried. Will this use-case cause the table to qualify for an
aggressive autovac to prevent wraparound.

If not, why else is a table with zero DML changes ever gets autovac'd?

----------------------------------------
Thank you

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2023-01-16 13:11:58 Re: Why is a Read-only Table Gets Autovacuumed "to prevent wraparound"
Previous Message Harmen 2023-01-16 08:55:23 Re: row estimate for partial index