On Thu, Mar 05, 2020 at 03:27:31PM +0100, Laurenz Albe wrote: > On Thu, 2020-03-05 at 19:40 +1300, David Rowley wrote: > > 1. I'd go for 2 new GUCs and reloptions. > > autovacuum_vacuum_insert_scale_factor and these should work exactly > > I disagree about the scale_factor (and have not added it to the > updated version of the patch). If we have a scale_factor, then the > time between successive autovacuum runs would increase as the table > gets bigger, which defeats the purpose of reducing the impact of each > autovacuum run.
I would vote to include scale factor. You're right that a nonzero scale factor would cause vacuum to run with geometrically decreasing frequency. The same thing currently happens with autoanalyze as a table grows in size. I found that our monthly-partitioned tables were being analyzed too infrequently towards the end of the month. (At the beginning of the month, 10% is 2.4 hours worth of timeseries data, but at the end of the month 10% is 3 days, which was an issue when querying the previous day may have rowcount estimates near zero.) If someone wanted to avoid that, they'd set scale_factor=0. I think this patch should parallel what's already in place, and we can add documention for the behavior if need be. Possibly scale_factor should default to zero, which I think might make sense since insert-only tables seem to be the main target of this patch. > +++ b/doc/src/sgml/maintenance.sgml > + <para> > + Tables that have received more than > + <xref linkend="guc-autovacuum-vacuum-insert-threshold"/> > + inserts since they were last vacuumed and are not eligible for vacuuming > + based on the above criteria will be vacuumed to reduce the impact of a > future > + anti-wraparound vacuum run. > + Such a vacuum will aggressively freeze tuples, and it will not clean up > dead > + index tuples. "BUT will not clean .." > +++ b/src/backend/postmaster/autovacuum.c > + /* > + * If the number of inserted tuples exceeds the limit I would say "exceeds the threshold" Thanks for working on this; we would use this feature on our insert-only tables. -- Justin
