I've got a Postgres 11 database. The database has logical dumps (REPLICA using streaming replication). Am I wright in thinking that if I've got tables in which information is inserted, but not modified, then usual autovacuum will not be triggered, because there will not be dead tuples. And for sure in this tables will be autovacuum freeze which is triggered by age(relfrozenxid) > current_setting('vacuum_freeze_table_age'). But why then sometimes autovacuum to prevent wraparound is on? Autovacuum cannot freeze in time? Maybe cannot get special lock? What are the reasons? And one more question. What is a difference between autovacuum freeze (which was triggered by vacuum_freeze_table_age) and autovacuum to prevent wraparound? I know that it is started even if vacuum is off. But whats more? It has special lock on tables? Or maybe it does more then previous vacuum?
CodePudding user response：
If you upgrade to PostgreSQL v13 or better, autovacuum will also be triggered by inserts, which would make the anti-wraparound autovacuum run lighter once it comes around.
Anti-wraparound autovacuum is triggered if a table has an unfrozen row older than vacuum_freeze_table_age. That's the same as what you call "autovacuum freeze": rows are frozen to avoid problems with the reuse of transaction IDs. Those are the autovacuum runs "to prevent wraparound" that you see. That is completely normal and nothing to worry about.
The difference between this so-called "aggressive" autovacuum and a normal one is
it visits all pages that are not already all-frozen, potentially waiting for page locks to go away
it won't back down if it blocks a high-level lock, such as taken by
CREATE INDEX,TRUNCATE,ALTER TABLEetc.
Other than that, everything is as usual; INSERT, UPDATE and DELETE statements on the table are not blocked.