Date: Tue, 21 Oct 2025 09:38:41 -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: <aPea8dG8_lFwTsyo@nathan>
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>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="qlOG+R55HnQikzN+"
Content-Disposition: inline
In-Reply-To: 
 <CAApHDvqrd=SHVUytdRj55OWnLH98Rvtzqam5zq2f4XKRZa7t9Q@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aPea8dG8_lFwTsyo%40nathan>
Precedence: bulk


--qlOG+R55HnQikzN+
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Sun, Oct 12, 2025 at 07:27:10PM +1300, David Rowley wrote:
> On Sat, 11 Oct 2025 at 07:43, Robert Haas <robertmhaas@gmail.com> wrote:
>> I think this is a reasonable starting point, although I'm surprised
>> that you chose to combine the sub-scores using + rather than Max.
> 
> Adding up the component scores doesn't make sense to me either. That
> means you could have 0.5 for inserted tuples, 0.5 for dead tuples and,
> say 0.1 for analyze threshold, which all add up to 1.1, but neither
> component score is high enough for auto-vacuum to have to do anything
> yet. With Max(), we'd clearly see that there's nothing to do since the
> overall score isn't >= 1.0.

In v3, I switched to Max().

> Maybe the score calculation could change when the relevant age() goes
> above vacuum_failsafe_age / vacuum_multixact_failsafe_age and start
> scaling it very aggressively beyond that. There's plenty to debate,
> but at a first cut, maybe something like the following (coded in SQL
> for ease of result viewing):
> 
> select xidage as "age(relfrozenxid)",case xidage::float8 <
> current_setting('vacuum_failsafe_age')::float8 when true then xidage /
> current_setting('autovacuum_freeze_max_age')::float8 else power(xidage
> / current_setting('autovacuum_freeze_max_age')::float8,xidage::float8
> / 100_000_000) end xid_age_score from
> generate_series(0,2_000_000_000,100_000_000) xidage;
> 
> which gives 1e+20 for age of 2 billion. It would take quite an
> unreasonable amount of bloat to score higher than that.
> 
> I guess someone might argue that we should start taking it more
> seriously before the table's relfrozenxid age gets to
> vacuum_failsafe_age. Maybe that's true. I just don't know what. In any
> case, if a table's age gets that old, then something's probably not
> configured very well and needs attention. I did think maybe we could
> keep the addressing of auto-vacuum being configured to run too slowly
> as a separate thread.

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.

-- 
nathan

--qlOG+R55HnQikzN+
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename=v3-0001-autovacuum-scheduling-improvements.patch