public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nathan Bossart <[email protected]>
To: Gurmokh <[email protected]>
Cc: wenhui qiu <[email protected]>
Cc: David Rowley <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: New vacuum config to avoid anti wraparound vacuums
Date: Tue, 28 Apr 2026 21:29:12 -0500
Message-ID: <afFs-Mv4QjpOGB9m@nathan> (raw)
In-Reply-To: <8udBo1vgTX5lWBFmq4HKWGy94FOomFWWc_xr4Hc7jJ91NzZbdvWB_QqfVDF1jmSk-Eavuuu-y3aUZOJZPubVdUcpLHTNmf-0zDuMkzBArug=@protonmail.com>
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>
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
view thread (9+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: New vacuum config to avoid anti wraparound vacuums
In-Reply-To: <afFs-Mv4QjpOGB9m@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox