Feedback-ID: id4a34324:Fastmail
Date: Mon, 9 Jan 2023 17:22:15 -0800
From: Andres Freund <andres@anarazel.de>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Jeff Davis <pgsql@j-davis.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Decoupling antiwraparound autovacuum from special rules around
 auto cancellation
Message-ID: <20230110012215.fgmzfx6oabtj3pja@awork3.anarazel.de>
References: <e1ebf722a589a785214d189beb646f9b5a28912d.camel@j-davis.com>
 <CAH2-Wz=AfJKAwRq-PV-3WTU3mg-RRfnbMEdga0CBmwAXRmebcQ@mail.gmail.com>
 <CAH2-WzkXt3zjDc5e42tK-4j2bVRN=BJQjiCvS7tF6TpM=7k5UA@mail.gmail.com>
 <5308f8fdd85fea4cd3c3e5cab2e64e164c46c6d9.camel@j-davis.com>
 <CAH2-WzmL=q6tUrD6_EXi70yHq+gtKaFt6D73N=scJpwkwzD8iA@mail.gmail.com>
 <d9e07793e8ca6b3f1e76b6e0f683429dc6d29703.camel@j-davis.com>
 <CAH2-WzkBBtohCvL4GXwGFr0z3p8yFGUckws5-EOtEXY+xWq2aA@mail.gmail.com>
 <CAH2-Wz=hj-RCr6fOj_L3_0J1Ws8fOoxTQLmtM57gPc19beZz=Q@mail.gmail.com>
 <CAH2-WzkdbXfYQepdd6Ju7PA8u5MZxQ5K_TardbPTjS0144L+5Q@mail.gmail.com>
 <CAH2-Wz=dvf6AzA1SjCCbShbt5Cd5LSAJQ_9hcOhgnJw7D0v0FA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CAH2-Wz=dvf6AzA1SjCCbShbt5Cd5LSAJQ_9hcOhgnJw7D0v0FA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20230110012215.fgmzfx6oabtj3pja%40awork3.anarazel.de>
Precedence: bulk

Hi,

On 2023-01-08 17:49:20 -0800, Peter Geoghegan wrote:
> Teach autovacuum.c to launch "table age" autovacuums at the same point
> that it previously triggered antiwraparound autovacuums.  Antiwraparound
> autovacuums are retained, but are only used as a true option of last
> resort, when regular autovacuum has presumably tried and failed to
> advance relfrozenxid (likely because the auto-cancel behavior kept
> cancelling regular autovacuums triggered based on table age).

I've also seen the inverse, with recent versions of postgres: Autovacuum can
only ever make progress if it's an anti-wraparound vacuum, because it'll
always get cancelled otherwise.  I'm worried that substantially increasing the
time until an anti-wraparound autovacuum happens will lead to more users
running into out-of-xid shutdowns.

I don't think it's safe to just increase the time at which anti-wrap vacuums
happen to a hardcoded 1 billion.

I'm also doubtful that it's ok to just make all autovacuums on relations with
an age > 1 billion anti-wraparound ones. For people that use a large
autovacuum_freeze_max_age that will be a rude awakening.


I am all in favor for adding logic to trigger autovacuum based on the table
age, without needing to reach autovacuum_freeze_max_age. It never made sense
to me that we get to the "emergency mode" in entirely normal operation. But
I'm not in favor of just entirely reinterpreting existing GUCs and adding
important thresholds as hardcoded numbers.

Greetings,

Andres Freund