Date: Tue, 28 Apr 2026 21:29:12 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Gurmokh <gurmokh@protonmail.com>
Cc: wenhui qiu <qiuwenhuifx@gmail.com>, David Rowley <dgrowleyml@gmail.com>,
	"pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Subject: Re: New vacuum config to avoid anti wraparound vacuums
Message-ID: <afFs-Mv4QjpOGB9m@nathan>
References: 
 <CeFiI3W4yXPznxyELYARQ5H6Cfy1HJIm0Tsn5CDVINyORFVwKfIMwc5EKHYlVzAjy5vgHgfA7X7lgfA3CnD0GchBvuanglpr4xd-IoyjPWY=@protonmail.com>
 <CAApHDvonBh3YPGzFjg79-9RPRcnWVrzZyLR1xr05WxdY=EqjSg@mail.gmail.com>
 <AkM6NiLDE4pBr-PL1M79iydoh3enMO-UruUI2Xd3n3xOysAjGbm-lYz34DrLZcKbtRehBYM8lHRY6zVYZZkqL8vcM55LEt8i0lKVGwWUX54=@protonmail.com>
 <CAApHDvoa0ZwBjFYRypxHaqdFYwwDd6rTr5GWY8LASXr3kWY8Dg@mail.gmail.com>
 <GLEV5fUtSc1YTeNMMmCbl3RsUJXzbclrg4tgYP_xh1db3F8ErUZ-lmLaVDNPBS0JeanYUCVKyanNRY-rEbo2Ep1XnDEQuJB8DuuwtqdFGdA=@protonmail.com>
 <CAGjGUAL3J5nfz4+xgOGMB1Os6MHK_B24-YSc6YF3HWGcHWQonA@mail.gmail.com>
 <8udBo1vgTX5lWBFmq4HKWGy94FOomFWWc_xr4Hc7jJ91NzZbdvWB_QqfVDF1jmSk-Eavuuu-y3aUZOJZPubVdUcpLHTNmf-0zDuMkzBArug=@protonmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <8udBo1vgTX5lWBFmq4HKWGy94FOomFWWc_xr4Hc7jJ91NzZbdvWB_QqfVDF1jmSk-Eavuuu-y3aUZOJZPubVdUcpLHTNmf-0zDuMkzBArug=@protonmail.com>
Archived-At: <https://www.postgresql.org/message-id/afFs-Mv4QjpOGB9m%40nathan>
Precedence: bulk

On Tue, Apr 28, 2026 at 09:21:35PM +0000, Gurmokh wrote:
> The config parameters in [1] autovacuum_vacuum_threshold,
> autovacuum_vacuum_insert_threshold, autovacuum_vacuum_scale_factor,
> autovacuum_vacuum_insert_scale_factor and autovacuum_vacuum_max_threshold
> rely on regular activity to trigger autovacuums. However it is entirely
> plausible that these can be configured with values that are not sensitive
> enough and a table breaches the autovacuum_freeze_max_age triggering an
> aggressive vacuum to prevent wraparound before any less aggressive
> vacuums can be triggered.
> 
> In my experience I have seen tables that have significant activity and
> still not meet the criteria to trigger an autovacuum and subsequently age
> out. I have seen production systems slow to a grind waiting for these to
> complete.
> 
> What I'm suggesting here is to have a configurable parameter that
> represents a value as a percentage of autovacuum_freeze_max_age that
> would enable a table to be autovacuumed before a vacuum to prevent
> wraparound is triggered if none of the above conditions are met.

So your new parameter is meant to trigger non-aggressive vacuums in hopes
that it might advance relfrozenxid and avoid an upcoming aggressive vacuum.
Do I have that right?

If so, I'm not sure that such a feature will make a tremendous amount of
difference.  Non-aggressive vacuums can only advance relfrozenxid (thus
preventing an imminent aggressive vacuum) if they don't skip any pages and
are able to obtain cleanup locks for the relevant buffers, but page
skipping and conditional locking seem like key features that would make a
non-aggressive autovacuum less disruptive.  I think there's a good chance
that even with your parameter, a preemptive non-aggressive vacuum would be
followed by an aggressive one shortly afterwards.

Perhaps there are other reasons prioritizing a non-aggressive autovacuum
would help, but it's hard to tell from the details you've shared thus far.
Would you mind elaborating on what you are seeing that is causing your
servers to "slow to a grind"?

-- 
nathan