Re: Trigger more frequent autovacuums of heavy insert tables

Hi Melanie Plageman
Thank you for working on this ,Actually, there were two patches aimed at
optimizing vacuum-triggered processes, and one of them reached a consensus
and has been committed:https://commitfest.postgresql.org/52/5046/ ,
https://commitfest.postgresql.org/51/5395/, Maybe referring to the already
committed patch and setting a maximum value for vacuum_max_ins_threshold
would be more acceptable.

Thanks

On Thu, Feb 6, 2025 at 6:08 AM Melanie Plageman <melanieplageman(at)gmail(dot)com>
wrote:

> On Thu, Jan 16, 2025 at 5:50 PM Melanie Plageman
> <melanieplageman(at)gmail(dot)com> wrote:
> >
> > On Thu, Jan 16, 2025 at 4:43 PM Melanie Plageman
> > <melanieplageman(at)gmail(dot)com> wrote:
> > >
> > > On Fri, Oct 25, 2024 at 11:14 AM Melanie Plageman
> > > <melanieplageman(at)gmail(dot)com> wrote:
> > > >
> > > > I've done something similar to this in attached v2.
> > >
> > > This needed a rebase. See attached v4.
> >
> > Whoops -- docs didn't build. Attached v5.
>
> Outside of the positive performance impact of vacuuming pages before
> they go cold (detailed in my first email [1]), there is also a
> substantial positive effect with this patch for large tables with
> substantial cold regions: fewer anti-wraparound vacuums and more
> frequent normal/aggressive vacuums
>
> With the default vacuum settings, you often see an append-only table
> devolve to _only_ anti-wraparound vacuums after the first aggressive
> vacuum. I ran an insert-only workload for an hour (with 32 clients and
> synchronous commit off to maximize the amount of data inserted) with
> the default vacuum settings. On master, after the first aggressive
> vacuum, we do only anti-wraparound vacuums (and only two of these are
> triggered). With the patch, after the first aggressive vacuum, 10 more
> vacuums are triggered -- none of which are anti-wraparound vacuums.
>
> I attached a chart comparing the autovacuums triggered on master vs
> with the patch.
>
> Besides the performance benefit of spreading the freezing work over
> more normal vacuums (thereby disrupting foreground workloads less),
> anti-wraparound vacuums are not auto canceled by DDL -- making them
> more of a nuisance to users.
>
> [1]
> https://www.postgresql.org/message-id/CAAKRu_aj-P7YyBz_cPNwztz6ohP%2BvWis%3Diz3YcomkB3NpYA--w%40mail.gmail.com
>