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.
On Thu, Jan 16, 2025 at 5:50 PM Melanie Plageman <melanieplageman@gmail.com> wrote: > > On Thu, Jan 16, 2025 at 4:43 PM Melanie Plageman > <melanieplageman@gmail.com> wrote: > > > > On Fri, Oct 25, 2024 at 11:14 AM Melanie Plageman > > <melanieplageman@gmail.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.
