Hi, On 2020-03-17 20:42:07 +0100, Laurenz Albe wrote: > > I think Andres was thinking this would maybe be an optimization independent > > of > > is_insert_only (?) > > I wasn't sure.
I'm not sure myself - but I'm doubtful that using a 0 min age by default will be ok. I was trying to say (in a later email) that I think it might be a good compromise to opportunistically freeze if we're dirtying the page anyway, but not optimize WAL emission etc. That's a pretty simple change, and it'd address a lot of the potential performance regressions, while still freezing for the "first" vacuum in insert only workloads. > Add "autovacuum_vacuum_insert_threshold" and > "autovacuum_vacuum_insert_scale_factor" GUC and reloption. > The default value for the threshold is 10000000. > The scale factor defaults to 0, which means that it is > effectively disabled, but it offers some flexibility > to tune the feature similar to other autovacuum knobs. I don't think a default scale factor of 0 is going to be ok. For large-ish tables this will basically cause permanent vacuums. And it'll sometimes trigger for tables that actually coped well so far. 10 million rows could be a few seconds, not more. I don't think that the argument that otherwise a table might not get vacuumed before autovacuum_freeze_max_age is convincing enough. a) if that's indeed the argument, we should increase the default autovacuum_freeze_max_age - now that there's insert triggered vacuums, the main argument against that from before isn't valid anymore. b) there's not really a good arguments for vacuuming more often than autovacuum_freeze_max_age for such tables. It'll not be not frequent enough to allow IOS for new data, and you're not preventing anti-wraparound vacuums from happening. Greetings, Andres Freund