Date: Wed, 22 Oct 2025 13:40:11 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>,
	Jeremy Schneider <schneider@ardentperf.com>,
	Sami Imseih <samimseih@gmail.com>, pgsql-hackers@postgresql.org
Subject: Re: another autovacuum scheduling thread
Message-ID: <aPklC5V61VcTu7IP@nathan>
References: 
 <CAApHDvpMEs3rewULyJZ=huT8ZM1C1PboV-G7K7jfGtJSgdx-fA@mail.gmail.com>
 <20251008182520.6e05a8b8@ardentperf.com>
 <20251008184740.328d45de@ardentperf.com>
 <CAApHDvq76BBseUh2cG0=m=8r-j6HF_jrQt16Eszgsxp3bciGQw@mail.gmail.com>
 <aOffPCBoQLG5dGd8@nathan>
 <aOlC4aDoQcgW8ZpC@nathan>
 <CA+TgmoYC4ShRp8vcyrBjkefSBdFfY1fnUgCvjocN-iq55G-7bA@mail.gmail.com>
 <CAApHDvqrd=SHVUytdRj55OWnLH98Rvtzqam5zq2f4XKRZa7t9Q@mail.gmail.com>
 <aPea8dG8_lFwTsyo@nathan>
 <CAApHDvocam8_cxqO=LiSifgp0B3rs1=VWRVQiwAwz_DvOMgfVw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAApHDvocam8_cxqO=LiSifgp0B3rs1=VWRVQiwAwz_DvOMgfVw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aPklC5V61VcTu7IP%40nathan>
Precedence: bulk

On Wed, Oct 22, 2025 at 09:07:33AM +1300, David Rowley wrote:
> However, just thinking of non-standard setting... I do wonder if it'll
> be aggressive enough if someone did something like raise the
> *freeze_max_age to 1 billion (it's certainly common that people raise
> this). With a 1.6 billion vacuum_failsafe_age, a table at
> freeze_max_age only scores in at 110. I guess there's no reason we
> couldn't keep your calc and then scale the score further once over
> vacuum_failsafe_age to ensure those are the highest priority. There is
> a danger that if a table scores too low when age(relfrozenxid) >
> vacuum_failsafe_age that autovacuum dawdles along handling bloated
> tables while oblivious to the nearing armageddon.

That's a good point.  I wonder if we should try to make the wraparound
score independent of the *_freeze_max_age parameters (once the table age
surpasses said parameters).  Else, different settings will greatly impact
how aggressively tables are prioritized the closer they are to wraparound.
Even if autovacuum_freeze_max_age is set to 200M, it's not critically
important for autovacuum to pick up tables right away as soon as their age
reaches 200M.  But if the parameter is set to 2B, we _do_ want autovacuum
to prioritize tables right away once their age reaches 2B.

> Is it worth writing a comment explaining the philosophy behind the
> scoring system to make it easier for people to understand that it aims
> to standardise the priority of vacuums and unify the various trigger
> thresholds into a single number to determine which tables are most
> important to vacuum and/or analyze first?

Yes, I think so.
 
> Thanks for working on this.

I appreciate the discussion.

-- 
nathan