On Wed, Apr 24, 2024 at 8:08 AM Frédéric Yhuel <frederic.yh...@dalibo.com> wrote: > > Hello, > > I would like to suggest a new parameter, autovacuum_max_threshold, which > would set an upper limit on the number of tuples to delete/update/insert > prior to vacuum/analyze.
Hi Frédéric, thanks for the proposal! You are tackling a very tough problem. I would also find it useful to know more about what led you to suggest this particular solution. I am very interested in user stories around difficulties with what tables are autovacuumed and when. Am I correct in thinking that one of the major goals here is for a very large table to be more likely to be vacuumed? > The idea would be to replace the following calculation : > > vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples; > > with this one : > > vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples / (1 > + vac_scale_factor * reltuples / autovacuum_max_threshold) > > (and the same for the others, vacinsthresh and anlthresh). My first thought when reviewing the GUC and how it is used is wondering if its description is a bit misleading. autovacuum_vacuum_threshold is the "minimum number of updated or deleted tuples needed to trigger a vacuum". That is, if this many tuples are modified, it *may* trigger a vacuum, but we also may skip vacuuming the table for other reasons or due to other factors. autovacuum_max_threshold's proposed definition is the upper limit/maximum number of tuples to insert/update/delete prior to vacuum/analyze. This implies that if that many tuples have been modified or inserted, the table will definitely be vacuumed -- which isn't true. Maybe that is okay, but I thought I would bring it up. > The attached (draft) patch further illustrates the idea. Thanks for including a patch! > My guess is that a similar proposal has already been submitted... and > rejected 🙂 If so, I'm very sorry for the useless noise. I rooted around in the hackers archive and couldn't find any threads on this specific proposal. I copied some other hackers I knew of who have worked on this problem and thought about it in the past, in case they know of some existing threads or prior work on this specific topic. - Melanie
