I recently got few DBs to maintain. I just come up about this "Preventing Transaction ID Wraparound Failures". I checked my DBs and got the numbers:
I did vacuumdb -a -v on this DB, and then got the numbers:
In my opinion it is not good enough? Should I worry?
If I understand correctly by PostgreSQL manual, should I use vacuum freeze to reset counters to zero? I know that this db steadly will grow, usualy on inserts, not so for deletes/updates.
Thank you.
The failure limit is 2,000,000,000, which is ten times more than the autovacuum_freeze_max_age cutoff of 200,000,000. So there is nothing to worry about there, you are very far from failure and it will automatically kick in in plenty of time.
But once an anti-wraparound vacuum does kick in, it might cause inconvenience due to the table lock. So you might want to do freezing (like with -F flag to vacuumdb) at a time of your choosing to preempt it from happening automatically at some time out of your control.
No, VACUUM will not freeze all rows, so it won't set datfrozenxid to the current transaction ID.
VACUUM won't freeze any row that is less than vacuum_freeze_min_age transactions old (default 50 million).
Rows that have remain unmodified for a while have good odds of staying around, so it is worth freezing them. New rows will often get deleted or updated soon, in which case freezing them (which causes a write!) would be a loss.
VACUUM will skip all blocks that are “all-visible”, so rows on those pages won't be frozen.
VACUUM will skip pages that it cannot get a lock on because they are pinned.
The second and third point don't apply to an anti-wraparound (“aggressive”) autovacuum run; such a process really wants to visit all pages that are not “all-frozen”.
Everything is fine with your database. It is good for performance if rows don't get frozen too early.
