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

On 1/16/23 07:11, Laurenz Albe wrote:

On Mon, 2023-01-16 at 07:48 -0500, Fred Habash wrote:

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?

For every table PostgreSQL stores the oldest transaction ID in an unfrozen tuple
in "pg_class.relfrozenxid".  Once that is more than "autovacuum_freeze_max_age",
the table gets autovacuumed.  If the table is already all-frozen, that is a short
operation and will just advance "pg_class.relfrozenxid".

So OP should VACUUM FREEZE the table.