All we need is an easy explanation of the problem, so here it is.
We have a table where we store events. This is an append-only table with 4 billion rows/events in it. We never delete nor update the rows in this table, hence we never have
dead_rows in it.
However to my understanding, AUTOVACUUM never runs on write-only tables because they don’t have dead rows in them hence they never exceed the
How would transaction id wraparound be prevented in such a case?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
autovacuum_multixact_freeze_max_age, which defaults to 200 millions.
Once a table has an unfrozen live row that is more than
autovacuum_multixact_freeze_max_age transactions old, autovacuum will launch an worker that performs an anti-wraparound vacuum on the table and freezes rows older than
Note that from PostgreSQL v13 on, autovacuum is not only triggered by dead tuples, but also by the number of rows inserted (based on
autovacuum_vacuum_insert_scale_factor). That allows freezing rows earlier, and reduces the impact of an anti-wraparound vacuum. See my blog post for details.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂