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

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".

Yours,
Laurenz Albe

On 16/01/2023 13:48 CET Fred Habash <fmhabash@gmail.com> 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.

Only way to ensure that is to have database users other than the table owners
or superusers connect from your app. Then you can GRANT the absolute necessary
privileges like SELECT for read-only access.

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?

Wraparound protection is always performed even if autovacuum is disabled:
https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

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?

--
Erik

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.

--
Born in Arizona, moved to Babylonia.

On 1/16/23 14:18, Ron wrote:

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.

Hm, did OP say there was an actual problem as is?  Or just a "puzzle" -
now explained - and no action is necessary?

On 1/16/23 15:46, Rob Sargent wrote:

On 1/16/23 14:18, Ron wrote:

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.

Hm, did OP say there was an actual problem as is?  Or just a "puzzle" -
now explained - and no action is necessary?

"Should" as in "it's a good idea", not "it's important but not vital".

--
Born in Arizona, moved to Babylonia.