On Thu, 12 Mar 2020 at 14:38, Laurenz Albe <laurenz.a...@cybertec.at> wrote: > > On Thu, 2020-03-12 at 17:47 +1300, David Rowley wrote: > > I'm starting to think that we should set the scale_factor to something > > like 0.3 and the threshold to 50. Is anyone strongly against that? Or > > Laurenz, are you really set on the 10 million threshold? > > These values are almost the same as "autovacuum_vacuum_scale_factor" > and "autovacuum_vacuum_threshold", so you actually agree with Masahiko > with the exception that you want it tunable separately. > > I don't like the high scale factor. > > If your insert-only table was last vacuumed when it had 500 million rows, > the next autovacuum will freeze 150 million tuples, which is a lot. > The impact will be less than that of an anti-wraparound vacuum because > it is not as persistent, but if our 150 million tuple autovacuum backs > down because it hits a lock or gets killed by the DBA, that is also not > good, since it will just come again. > And the bigger the vacuum run is, the more likely it is to meet an obstacle. > > So I think that large insert-only tables should be vacuumed more often > than that. If the number of tuples that have to be frozen is small, > the vacuum run will be short and is less likely to cause problems. > That is why I chose a scale factor of 0 here.
The reason why you want to add new GUC parameters is to use different default values for insert-update table case and insert-only table case? I think I understand the pros and cons of adding separate parameters, but I still cannot understand use cases where we cannot handle without separate parameters. Regards, -- Masahiko Sawada http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
