Re: Trigger more frequent autovacuums of heavy insert tables

From: wenhui qiu <qiuwenhuifx(at)gmail(dot)com>
To: Melanie Plageman <melanieplageman(at)gmail(dot)com>
Cc: Greg Sabino Mullane <htamfids(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, Peter Geoghegan <pg(at)bowt(dot)ie>, David Rowley <dgrowley(at)gmail(dot)com>
Subject: Re: Trigger more frequent autovacuums of heavy insert tables
Date: 2025-02-07 06:40:22
Message-ID: CAGjGUALD-zus_GW7Ls43_Gm_+Cgq6wiDN6c1d7XhwX0eH94tKA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi
> We could add autovacuum_vacuum_insert_max_threshold, but with an
> insert-only workload, we can expect that the cold data is being
> frozen. By calculating the threshold based on unfrozen data, we are
> effectively capping the threshold for inserted data without adding
> another guc. If any of that data is being unfrozen via updates or
> deletes, then the autovacuum_vacuum_max_threshold would apply.

> Perhaps I'm missing a case where calculating the insert threshold on
> unfrozen data would not act as a cap, in which case I could get on
> board with a guc.
Actually ,I like your solution. Even I think this formula could use that
pcnt_unfrozen parameter
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples *
pcnt_unfrozen;

Thanks

On Thu, Feb 6, 2025 at 11:42 PM Melanie Plageman <melanieplageman(at)gmail(dot)com>
wrote:

> Attached v6 is rebased over 306dc520b9dfd60
>
> On Wed, Feb 5, 2025 at 8:54 PM wenhui qiu <qiuwenhuifx(at)gmail(dot)com> wrote:
> >
> > 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.
>
> We could add autovacuum_vacuum_insert_max_threshold, but with an
> insert-only workload, we can expect that the cold data is being
> frozen. By calculating the threshold based on unfrozen data, we are
> effectively capping the threshold for inserted data without adding
> another guc. If any of that data is being unfrozen via updates or
> deletes, then the autovacuum_vacuum_max_threshold would apply.
>
> Perhaps I'm missing a case where calculating the insert threshold on
> unfrozen data would not act as a cap, in which case I could get on
> board with a guc.
>
>
> - Melanie
>

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2025-02-07 06:47:02 Re: Fix for a crash caused by triggers in cross-partition updates
Previous Message Peter Smith 2025-02-07 06:34:45 DOCS - inactive_since field readability