MIME-Version: 1.0
References: 
 <CAApHDvo3maZsrZVq=Bur=Z6Gtse4asSEgHU0HzBhhcTfM-AfeA@mail.gmail.com>
 <20251008172727.3befd129@ardentperf.com>
 <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>
In-Reply-To: <aPea8dG8_lFwTsyo@nathan>
From: David Rowley <dgrowleyml@gmail.com>
Date: Wed, 22 Oct 2025 09:07:33 +1300
Message-ID: 
 <CAApHDvocam8_cxqO=LiSifgp0B3rs1=VWRVQiwAwz_DvOMgfVw@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>,
 Jeremy Schneider <schneider@ardentperf.com>,
	Sami Imseih <samimseih@gmail.com>, pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvocam8_cxqO%3DLiSifgp0B3rs1%3DVWRVQiwAwz_DvOMgfVw%40mail.gmail.com>
Precedence: bulk

On Wed, 22 Oct 2025 at 03:38, Nathan Bossart <nathandbossart@gmail.com> wrote:
> I did something similar to this in v3, although I used the *_freeze_max_age
> parameters as the point to start scaling aggressively, and I simply raised
> the score to the power of 10.
>
> I've yet to do any real testing with this stuff.

I've not tested it or compiled it, but the patch looks good. I did
think that the freeze vacuum isn't that big a deal if it's just over
the *freeze_max_age and thought it should become aggressive very
quickly at the failsafe age, but that leaves a much smaller window of
time to do the freezing if autovacuum has been busy with other higher
priority tables.  Your scaling is much more gentle and comes out (with
standard settings) with a score of 1 billion for a table at the
failsafe age, and about 1 million at half the failsafe age. That seems
reasonable as it's hard to imagine a table having a 1 billion bloat
score.

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.

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?

Thanks for working on this.

David