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
>From 2b2942e38db24d84aeb17a9075d4173b2b797fa9 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> 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
