Postgresql documentation on vacuum states that
Frozen row versions are treated as if the inserting XID were FrozenTransactionId, so that they will appear to be “in the past” to all normal transactions regardless of wraparound issues, and so such row versions will be valid until deleted, no matter how long that is.
and further states that
Tables whose relfrozenxid value is more than autovacuum_freeze_max_age transactions old are always vacuumed
The first excerpt for me means that a table where every record on the table was 'frozen' and there are no further changes to the table NO further vacuum is needed for transaction ID wraparound prevention purposes.
But the second excerpt says the opposite and says that even for tables with everything frozen and no changes a periodic vacuum is needed for wraparound prevention reasons?
Which of the two is true?
I did a small test and it seems the second is true but I do not want to believe it:
create t (c int);
select age(c.relfrozenxid) from pg_class c where relname = 't';
-- 6
vacuum freeze t;
select age(c.relfrozenxid) from pg_class c where relname = 't';
-- 2
select age(c.relfrozenxid) from pg_class c where relname = 't';
-- 68
vacuum freeze t;
select age(c.relfrozenxid) from pg_class c where relname = 't';
-- 7
select age(c.relfrozenxid) from pg_class c where relname = 't';
-- 15
To me this seems to be a bug in postgres that static tables over and over again will have an autovacuum (to prevent wraparound) done to them unless you have some kind of script that periodically does a vacuum freeze on them?
There is no bug, and yes, totally frozen tables will still receive a regular anti-wraparound autovacuum run. However, since all blocks in the table are all-frozen, that autovacuum run actually does nothing except update pg_class.relfrozenxid.