agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Nathan Bossart <nathan@postgresql.org>
Subject: [PATCH v1 1/1] Fix backwards scaling of autovacuum freeze scores.
Date: Mon, 31 Aug 2026 10:09:37 -0500
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--
view thread (205+ messages) latest in thread
Message-ID: <no-message-id-1719638@localhost>
Permalink: ../no-message-id-1719638@localhost/
Also on: postgresql.org/message-id/no-message-id-1719638@localhost
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: pgsql-hackers@postgresql.org
Cc: nathan@postgresql.org
Subject: Re: [PATCH v1 1/1] Fix backwards scaling of autovacuum freeze scores.
In-Reply-To: <no-message-id-1719638@localhost>
* 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