Hello hackers.
Hope you are all keeping well.
I have an idea for managing vacuums. When managing vacuums it can sometimes be a struggle to manage the config settings
forthem, especially when trying to avoid anti wraparound vacuums. Some tables are massive and needs scale factor
settingsin the fractions of percent, sometimes, with no guarantee that enough actions cross that threshold. Increasing
theautovacuum_freeze_max_age to keep up with an growing number of transactions across an instance is just kicking the
candown the road. Engineers run backfills that throw off any calculations you may use to work out these scale factors
andusing the same values across an instance is too simple while managing individually for tables can be too messy. Or
yourun a cron type job so you don't have to think about it at all.
So I thought a nice way to avoid anti wraparound vacuums is to use the age of the table as the trigger.
We could add, yet another, vacuum configuration. Eg autovacuum_age_scale_factor as a percentage of
autovacuum_freeze_max_age.
For example, set to 0.8 a 'standard' vacuum would be triggered when the table reached 160million with a default
200millionsetting.
As a noddy example.
create table a ( a int) ;
create table b ( a int) ;
alter table a set ( autovacuum_freeze_max_age=100000, autovacuum_age_scale_factor=0.1) // 10% of max age
Then run some activity table b keeping a inactive to increase its age, but not trigger a vacuum using scale factor or
thresholdsettings.
When the table reaches ~10000 age it will trigger a pre-emptive vacuum to prevent wraparound vacuum occurring.
The log entry for the event would appear like:
[56957] LOG: automatic vacuum (age-based proactive) of table "postgres.public.atable": index scans: 0
I thought I would put the idea out there. I've attached a patch file if anyone wants to try it. Its built against the
REL_18_3tag.
Happy Postgresing
Gurmokh
