Date: Mon, 31 Aug 2026 10:23:26 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Sami Imseih <samimseih@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>,
	Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
	Jim Nasby <jnasby@upgrade.com>, Greg Burd <greg@burd.me>,
	Robert Haas <robertmhaas@gmail.com>, Robert Treat <rob@xzilla.net>,
	Jeremy Schneider <schneider@ardentperf.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: another autovacuum scheduling thread
Message-ID: <apWcbq91BAfhmsKU@nathan>
References: <acLGoktU2lp4SIlU@nathan>
 <CALj2ACUTuHZugAgbDk-NNPY_CPbw+c4T=HkRvvqq-FKqnVb24A@mail.gmail.com>
 <acRROBvlokDLQZlh@nathan>
 <CAApHDvrD9YZ8RBgb6NDtzyay1PsFReLvWHVqUCMFw1=qxM1N1g@mail.gmail.com>
 <acRdY_cxDvff5zW4@nathan>
 <CAApHDvqTDRbSVoGCv=1Y_TsAiggfEyGzH2QE+Hp57xwcBtfAOQ@mail.gmail.com>
 <acVjtJA2F68vdYTB@nathan>
 <CAApHDvrUQPZcjQK7+PAcXoXc664AizDiBY4thBh1kqEHUouPPQ@mail.gmail.com>
 <acafxccEv5ZcU0Ym@nathan>
 <CAA5RZ0vvDsnLEw9DW6SOmy8EdwRirPdYi2saM_3fgLY_96JdUg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="vj2G5GTUtDziqSI7"
Content-Disposition: inline
In-Reply-To: 
 <CAA5RZ0vvDsnLEw9DW6SOmy8EdwRirPdYi2saM_3fgLY_96JdUg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/apWcbq91BAfhmsKU%40nathan>
Precedence: bulk

--vj2G5GTUtDziqSI7
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Claude noticed a corner case that needs handling.  In short, if the freeze
score weights are high enough, the pow() calls can have the opposite of the
intended effect, since the freeze scores may be less than 1.  Patch
attached.

-- 
nathan

--vj2G5GTUtDziqSI7
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename=v1-0001-Fix-backwards-scaling-of-autovacuum-freeze-scores.patch

From 2b2942e38db24d84aeb17a9075d4173b2b797fa9 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 31 Aug 2026 10:09:37 -0500
Subject: [PATCH v1 1/1] Fix backwards scaling of autovacuum freeze scores.

Presently, once a table's age passes the effective failsafe age,
relation_needs_vacanalyze() raises the ratio of that age to the
freeze-max-age to a power of at least 1.0, so that the table sorts
towards the front of the list.  That amplifies the score only
while the ratio is above 1.0.  Below it, exponentiation shrinks
the score, and shrinks it further as the age grows, so a table can
cross into the failsafe range and see its score fall, and keep
falling as it ages.  Ratios below 1.0 are reachable because the
effective failsafe age is divided by
autovacuum_freeze_score_weight, which can leave it under the
freeze-max-age.  With autovacuum_freeze_max_age at 2000000000 and
the weight at 10.0, tables aged 220M, 400M, and 736M score 0.078,
0.016, and 0.0064, which is exactly backwards, and all three sit
below what they would score with no scaling at all.

To fix, only scale a score that the exponentiation will actually
amplify.  Raising the weight still starts the scaling earlier,
just not so early that it does the opposite of what it is for.
The default configuration is unaffected, since there scaling
begins at an age of 1.6 billion, where the ratio is already 8.0.

Oversight in commit d7965d65fc.

Backpatch-through: 19
---
 src/backend/postmaster/autovacuum.c | 10 ++++++++--
 1 file changed, 8 insertions(+), 2 deletions(-)

diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 393e81d53e9..13d330913be 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -3210,9 +3210,15 @@ relation_needs_vacanalyze(Oid relid,
 	if (autovacuum_multixact_freeze_score_weight > 1.0)
 		effective_mxid_failsafe_age /= autovacuum_multixact_freeze_score_weight;
 
-	if (xid_age >= effective_xid_failsafe_age)
+	/*
+	 * Note that raising a score below 1.0 to a power greater than 1.0 shrinks
+	 * it, and shrinks it further as the age grows, so scaling such a score
+	 * would leave an older table sorting behind a younger one.  Only scale
+	 * scores that the exponentiation will actually amplify.
+	 */
+	if (xid_age >= effective_xid_failsafe_age && scores->xid > 1.0)
 		scores->xid = pow(scores->xid, Max(1.0, (double) xid_age / 100000000));
-	if (mxid_age >= effective_mxid_failsafe_age)
+	if (mxid_age >= effective_mxid_failsafe_age && scores->mxid > 1.0)
 		scores->mxid = pow(scores->mxid, Max(1.0, (double) mxid_age / 100000000));
 
 	scores->xid *= autovacuum_freeze_score_weight;
-- 
2.55.0


--vj2G5GTUtDziqSI7--