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 for them, especially when trying to avoid anti wraparound vacuums. Some tables are massive and needs scale factor settings in the fractions of percent, sometimes, with no guarantee that enough actions cross that threshold. Increasing the autovacuum_freeze_max_age to keep up with an growing number of transactions across an instance is just kicking the can down the road. Engineers run backfills that throw off any calculations you may use to work out these scale factors and using the same values across an instance is too simple while managing individually for tables can be too messy. Or you run 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 200million setting. 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 threshold settings. 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_3 tag. Happy Postgresing Gurmokh
0001-age-based-vacuum.patch
Description: Binary data
