Your following statement made me understand this parameter better… Thank you!
“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.”
At this point I am trying to comprehend https://www.postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
I understand default for autovacuum_freeze_max_age is 200 million. I currently have this setting at 1.5 billion. I am planning on lowering this to 1 billion in the next server restart and expect the ratio to get close to 100%. With 1 billion setting, if I base my ratio with max allowed 2 billion, the ratio will be 50%. That makes me feel better because the autovacuum will attempt to prevent wraparound when it gets close to 1 billion setting. If my understanding is wrong and if I start seeing dreaded warning such as “HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.” then I will have another billion leg room of transactions to set autovacuum_freeze_max_age to while I work on manual vacuuming. But only disadvantage is I have to restart.
This is that I currently have. Please comment when you are able to. Thanks again!
select name, setting, unit, context, source::char(8) from pg_settings where name like '%vacuum%';
name | setting | unit | context | source
autovacuum | on | | sighup | configur
autovacuum_analyze_scale_factor | 0.05 | | sighup | configur
autovacuum_analyze_threshold | 0 | | sighup | configur
autovacuum_freeze_max_age | 1500000000 | | postmaster | configur
autovacuum_max_workers | 5 | | postmaster | configur
autovacuum_multixact_freeze_max_age | 400000000 | | postmaster | default
autovacuum_naptime | 60 | s | sighup | configur
autovacuum_vacuum_cost_delay | -1 | ms | sighup | configur
autovacuum_vacuum_cost_limit | -1 | | sighup | configur
autovacuum_vacuum_insert_scale_factor | 0.1 | | sighup | configur
autovacuum_vacuum_insert_threshold | 0 | | sighup | configur
autovacuum_vacuum_scale_factor | 0.1 | | sighup | configur
autovacuum_vacuum_threshold | 0 | | sighup | configur
autovacuum_work_mem | -1 | kB | sighup | default
log_autovacuum_min_duration | 0 | ms | sighup | configur
vacuum_cost_delay | 2 | ms | user | configur
vacuum_cost_limit | 200 | | user | configur
vacuum_cost_page_dirty | 20 | | user | default
vacuum_cost_page_hit | 1 | | user | default
vacuum_cost_page_miss | 2 | | user | default
vacuum_defer_cleanup_age | 0 | | sighup | default
vacuum_failsafe_age | 1600000000 | | user | default
vacuum_freeze_min_age | 50000000 | | user | default
vacuum_freeze_table_age | 150000000 | | user | default
vacuum_multixact_failsafe_age | 1600000000 | | user | default
vacuum_multixact_freeze_min_age | 5000000 | | user | default
vacuum_multixact_freeze_table_age | 150000000 | | user | default
On Fri, May 19, 2023 at 11:36 AM Murthy Nunna <mnunna@xxxxxxxx> wrote:
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.
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.)
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.