Hi,
I have a fairly large database (several TBs) where auto vacuum is enabled. My focus is to keep the datfrozenxid reasonably low and avoid manual vacuum.
Why? The age of datfrozenxid needs to be kept below 2 billion, but you should already be staying way below that, without needing to do anything. What benefit do you think you will accrue from keeping it even more lower?
> ...So, eventually I am afraid I have to vacuum the tables manually
Autovacuum to prevent wrap around will automatically kick in at autovacuum_freeze_max_age (if nothing makes it happen sooner). There is no need to do a manual vacuum to accomplish that, so nothing needs to be done to avoid it.
which has its
own problems like creating massive WALs in a short time etc. I would like to avoid manual vacuuming for this reason.
While manual vacuums are unthrottled by default, you can change vacuum_cost_delay to be the same as autovacuum_vacuum_cost_delay so they will be throttled in the same way as autovac is. So if you were to have a good reason to do regular manual vacuums (which I don't think you do), this would not be much of a counterargument. (There are other counterarguments which are better, like autocancelling upon lock conflicts, or just not wanting to write your own scheduling code when autovacuum already exists.)
SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname
FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname
FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS foo
ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC;
Following are the settings I have. I am wondering if there is a way autovacuum can keep the above “datfrozenxid” low and not keep increasing. Thank you ahead time for reading my post.
If you want autovacuum_freeze_max_age to be lower just make it lower. You are basically saying you want it to behave as if it were lower, but without actually making it lower. You don't want the ratio to be much more than 100%, but expecting it to never even get close to 100% doesn't make any sense. On a busy system, it will likely approach the value you told it to, that is what the setting is for.
Cheers,
Jeff
