On Tue, Jun 16, 2026 at 11:21:57AM -0500, Sami Imseih wrote: > I think this particular scenario is very clear to explain just like how > we explain the failsafe scenario. Also, the suggested docs in the view > link to the already existing detailed explanation of this behavior. > > More generally, I think anytime there is a drastic change in a score, > like jumping from a gradually ramping value around 1.x to suddenly > hundreds of millions, that's something worth calling out in the docs. > Users monitoring pg_stat_autovacuum_scores will notice that jump and want > to understand why it happened.
Okay. I fiddled with the patch a bit and came up with the attached. WDYT? -- nathan
>From c685699587198122de4dc340e502196b7c410384 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Wed, 17 Jun 2026 11:08:57 -0500 Subject: [PATCH v2 1/1] fix division-by-zero when calculating autovacuum mxid score --- doc/src/sgml/maintenance.sgml | 6 ++++-- src/backend/postmaster/autovacuum.c | 11 ++++++++--- 2 files changed, 12 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 96ea84b11f2..e341f165efd 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -1112,8 +1112,10 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu field as compared to <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>. Furthermore, this component increases greatly once the age surpasses - <xref linkend="guc-vacuum-multixact-failsafe-age"/>. The final value - for this component can be adjusted via + <xref linkend="guc-vacuum-multixact-failsafe-age"/> or when the number + of multixact member entries created exceeds approximately 2 billion + entries (see <xref linkend="vacuum-for-multixact-wraparound"/>). The + final value for this component can be adjusted via <xref linkend="guc-autovacuum-multixact-freeze-score-weight"/>. Note that increasing this parameter's value also lowers the age at which this component begins scaling aggressively, i.e., the scaling age is divided diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index a5a8db2ff88..e9aaf24c1be 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -3048,7 +3048,10 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, * One exception to the previous paragraph is for tables nearing wraparound, * i.e., those that have surpassed the effective failsafe ages. In that case, * the relfrozenxid/relminmxid-based score is scaled aggressively so that the - * table has a decent chance of sorting to the front of the list. + * table has a decent chance of sorting to the front of the list. Furthermore, + * the relminmxid-based score is scaled aggressively as + * effective_multixact_freeze_max_age is lowered due to high multixact member + * space usage. * * To adjust how strongly each component contributes to the score, the * following parameters can be adjusted from their default of 1.0 to anywhere @@ -3194,13 +3197,15 @@ relation_needs_vacanalyze(Oid relid, /* * To calculate the (M)XID age portion of the score, divide the age by its - * respective *_freeze_max_age parameter. + * respective *_freeze_max_age parameter. The multixact_freeze_max_age + * variable might be 0 here (i.e., a division-by-zero hazard), so in that + * case we use the mxid_age as the MXID score. */ xid_age = TransactionIdIsNormal(relfrozenxid) ? recentXid - relfrozenxid : 0; mxid_age = MultiXactIdIsValid(relminmxid) ? recentMulti - relminmxid : 0; scores->xid = (double) xid_age / freeze_max_age; - scores->mxid = (double) mxid_age / multixact_freeze_max_age; + scores->mxid = (double) mxid_age / Max(1, multixact_freeze_max_age); /* * To ensure tables are given increased priority once they begin -- 2.50.1 (Apple Git-155)
