On Wed, Apr 01, 2026 at 06:04:36PM -0500, Sami Imseih wrote: > Thanks! here is v7
Alright, I've been preparing these for commit. Most changes are cosmetic, but there are a couple of bigger ones I should note: * I added a prerequisite patch for relation_needs_vacanalyze() that saves a level of indentation on a chunk of code. This simplifies 0001 (now 0002) a bit. * I noticed that if autovacuum decides to force a vacuum for anti-wraparound purposes, it might also decide to analyze the table even if autovacuum is disabled for it. AFAICT this is accidental, but since it's behaved this way since commit 48188e1621 (2006) [0], I am slightly worried that this bug may have become a feature. In 0002, I separated this edge case in the code and added a comment, and I intend to start a new thread about removing it. * I removed the booleans in the view in favor of just noting that scores >= 1.0 means the table needs processing. IMHO trying to distinguish needs_vacuum from do_vacuum is just going to confuse folks more than anything, and IIUC this information is redundant with "score >= 1.0", anyway. * I renamed the view to pg_autovacuum_scores. While some of the information in the view depends on cumulative statistics, not all of it does, and what does is quite heavily modified from the original stats. So, I didn't think the pg_stat_* prefix was appropriate, although I can see how reasonable people might disagree. * I considered whether to make the backing function per-table and ultimately decided against it. The initialization logic is a bit expensive, and I assume most folks will be interested in the full picture of the current database. Maybe we could add a per-table function down the road, but I don't see any strong need for that for now. I'm planning to commit 0001-0004 this afternoon, assuming cfbot is happy. I'm hoping to commit 0005 on Monday or Tuesday. Please take a look at v8 if you have time. [0] https://postgr.es/m/23710.1162661716%40sss.pgh.pa.us -- nathan
>From b23277d416f005c64864104457bcd4d61f695c37 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Thu, 2 Apr 2026 13:18:49 -0500 Subject: [PATCH v8 1/5] refactor relation_needs_vacanalyze() --- src/backend/postmaster/autovacuum.c | 153 ++++++++++++++-------------- 1 file changed, 76 insertions(+), 77 deletions(-) diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 6694f485216..590e4c8e44c 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -3123,6 +3123,11 @@ relation_needs_vacanalyze(Oid relid, MultiXactId relminmxid; MultiXactId multiForceLimit; + float4 pcnt_unfrozen = 1; + float4 reltuples = classForm->reltuples; + int32 relpages = classForm->relpages; + int32 relallfrozen = classForm->relallfrozen; + Assert(classForm != NULL); Assert(OidIsValid(relid)); @@ -3263,94 +3268,88 @@ relation_needs_vacanalyze(Oid relid, * vacuuming only, so don't vacuum (or analyze) anything that's not being * forced. */ - if (tabentry && AutoVacuumingActive()) - { - float4 pcnt_unfrozen = 1; - float4 reltuples = classForm->reltuples; - int32 relpages = classForm->relpages; - int32 relallfrozen = classForm->relallfrozen; + if (!tabentry || !AutoVacuumingActive()) + return; - vactuples = tabentry->dead_tuples; - instuples = tabentry->ins_since_vacuum; - anltuples = tabentry->mod_since_analyze; + vactuples = tabentry->dead_tuples; + instuples = tabentry->ins_since_vacuum; + anltuples = tabentry->mod_since_analyze; - /* If the table hasn't yet been vacuumed, take reltuples as zero */ - if (reltuples < 0) - reltuples = 0; + /* If the table hasn't yet been vacuumed, take reltuples as zero */ + if (reltuples < 0) + reltuples = 0; + /* + * If we have data for relallfrozen, calculate the unfrozen percentage of + * the table to modify insert scale factor. This helps us decide whether + * or not to vacuum an insert-heavy table based on the number of inserts + * to the more "active" part of the table. + */ + if (relpages > 0 && relallfrozen > 0) + { /* - * If we have data for relallfrozen, calculate the unfrozen percentage - * of the table to modify insert scale factor. This helps us decide - * whether or not to vacuum an insert-heavy table based on the number - * of inserts to the more "active" part of the table. + * It could be the stats were updated manually and relallfrozen > + * relpages. Clamp relallfrozen to relpages to avoid nonsensical + * calculations. */ - if (relpages > 0 && relallfrozen > 0) - { - /* - * It could be the stats were updated manually and relallfrozen > - * relpages. Clamp relallfrozen to relpages to avoid nonsensical - * calculations. - */ - relallfrozen = Min(relallfrozen, relpages); - pcnt_unfrozen = 1 - ((float4) relallfrozen / relpages); - } + relallfrozen = Min(relallfrozen, relpages); + pcnt_unfrozen = 1 - ((float4) relallfrozen / relpages); + } - vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples; - if (vac_max_thresh >= 0 && vacthresh > (float4) vac_max_thresh) - vacthresh = (float4) vac_max_thresh; + vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples; + if (vac_max_thresh >= 0 && vacthresh > (float4) vac_max_thresh) + vacthresh = (float4) vac_max_thresh; - vacinsthresh = (float4) vac_ins_base_thresh + - vac_ins_scale_factor * reltuples * pcnt_unfrozen; - anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples; + vacinsthresh = (float4) vac_ins_base_thresh + + vac_ins_scale_factor * reltuples * pcnt_unfrozen; + anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples; - /* - * Determine if this table needs vacuum, and update the score if it - * does. - */ - if (vactuples > vacthresh) - { - scores->vac = (double) vactuples / Max(vacthresh, 1); - scores->vac *= autovacuum_vacuum_score_weight; - scores->max = Max(scores->max, scores->vac); - *dovacuum = true; - } - - if (vac_ins_base_thresh >= 0 && instuples > vacinsthresh) - { - scores->vac_ins = (double) instuples / Max(vacinsthresh, 1); - scores->vac_ins *= autovacuum_vacuum_insert_score_weight; - scores->max = Max(scores->max, scores->vac_ins); - *dovacuum = true; - } + /* + * Determine if this table needs vacuum, and update the score if it does. + */ + if (vactuples > vacthresh) + { + scores->vac = (double) vactuples / Max(vacthresh, 1); + scores->vac *= autovacuum_vacuum_score_weight; + scores->max = Max(scores->max, scores->vac); + *dovacuum = true; + } - /* - * Determine if this table needs analyze, and update the score if it - * does. Note that we don't analyze TOAST tables and pg_statistic. - */ - if (anltuples > anlthresh && - relid != StatisticRelationId && - classForm->relkind != RELKIND_TOASTVALUE) - { - scores->anl = (double) anltuples / Max(anlthresh, 1); - scores->anl *= autovacuum_analyze_score_weight; - scores->max = Max(scores->max, scores->anl); - *doanalyze = true; - } + if (vac_ins_base_thresh >= 0 && instuples > vacinsthresh) + { + scores->vac_ins = (double) instuples / Max(vacinsthresh, 1); + scores->vac_ins *= autovacuum_vacuum_insert_score_weight; + scores->max = Max(scores->max, scores->vac_ins); + *dovacuum = true; + } - if (vac_ins_base_thresh >= 0) - elog(DEBUG3, "%s: vac: %.0f (thresh %.0f, score %.2f), ins: %.0f (thresh %.0f, score %.2f), anl: %.0f (thresh %.0f, score %.2f), xid score: %.2f, mxid score: %.2f", - NameStr(classForm->relname), - vactuples, vacthresh, scores->vac, - instuples, vacinsthresh, scores->vac_ins, - anltuples, anlthresh, scores->anl, - scores->xid, scores->mxid); - else - elog(DEBUG3, "%s: vac: %.0f (thresh %.0f, score %.2f), ins: (disabled), anl: %.0f (thresh %.0f, score %.2f), xid score: %.2f, mxid score: %.2f", - NameStr(classForm->relname), - vactuples, vacthresh, scores->vac, - anltuples, anlthresh, scores->anl, - scores->xid, scores->mxid); + /* + * Determine if this table needs analyze, and update the score if it does. + * Note that we don't analyze TOAST tables and pg_statistic. + */ + if (anltuples > anlthresh && + relid != StatisticRelationId && + classForm->relkind != RELKIND_TOASTVALUE) + { + scores->anl = (double) anltuples / Max(anlthresh, 1); + scores->anl *= autovacuum_analyze_score_weight; + scores->max = Max(scores->max, scores->anl); + *doanalyze = true; } + + if (vac_ins_base_thresh >= 0) + elog(DEBUG3, "%s: vac: %.0f (thresh %.0f, score %.2f), ins: %.0f (thresh %.0f, score %.2f), anl: %.0f (thresh %.0f, score %.2f), xid score: %.2f, mxid score: %.2f", + NameStr(classForm->relname), + vactuples, vacthresh, scores->vac, + instuples, vacinsthresh, scores->vac_ins, + anltuples, anlthresh, scores->anl, + scores->xid, scores->mxid); + else + elog(DEBUG3, "%s: vac: %.0f (thresh %.0f, score %.2f), ins: (disabled), anl: %.0f (thresh %.0f, score %.2f), xid score: %.2f, mxid score: %.2f", + NameStr(classForm->relname), + vactuples, vacthresh, scores->vac, + anltuples, anlthresh, scores->anl, + scores->xid, scores->mxid); } /* -- 2.50.1 (Apple Git-155)
>From 2821299896b82eb6ef919616577570ffcd1ef31c Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Thu, 2 Apr 2026 13:45:48 -0500 Subject: [PATCH v8 2/5] always compute autovacuum scores --- src/backend/postmaster/autovacuum.c | 114 ++++++++++++++-------------- 1 file changed, 57 insertions(+), 57 deletions(-) diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 590e4c8e44c..817025ce616 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -3122,6 +3122,10 @@ relation_needs_vacanalyze(Oid relid, TransactionId relfrozenxid; MultiXactId relminmxid; MultiXactId multiForceLimit; + uint32 xid_age; + uint32 mxid_age; + int effective_xid_failsafe_age; + int effective_mxid_failsafe_age; float4 pcnt_unfrozen = 1; float4 reltuples = classForm->reltuples; @@ -3181,6 +3185,7 @@ relation_needs_vacanalyze(Oid relid, : effective_multixact_freeze_max_age; av_enabled = (relopts ? relopts->enabled : true); + av_enabled &= AutoVacuumingActive(); relfrozenxid = classForm->relfrozenxid; relminmxid = classForm->relminmxid; @@ -3201,65 +3206,51 @@ relation_needs_vacanalyze(Oid relid, } *wraparound = force_vacuum; - /* Update the score. */ - if (force_vacuum) - { - uint32 xid_age; - uint32 mxid_age; - int effective_xid_failsafe_age; - int effective_mxid_failsafe_age; - - /* - * To calculate the (M)XID age portion of the score, divide the age by - * its respective *_freeze_max_age parameter. - */ - 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; + /* + * To calculate the (M)XID age portion of the score, divide the age by its + * respective *_freeze_max_age parameter. + */ + xid_age = TransactionIdIsNormal(relfrozenxid) ? recentXid - relfrozenxid : 0; + mxid_age = MultiXactIdIsValid(relminmxid) ? recentMulti - relminmxid : 0; - /* - * To ensure tables are given increased priority once they begin - * approaching wraparound, we scale the score aggressively if the ages - * surpass vacuum_failsafe_age or vacuum_multixact_failsafe_age. - * - * As in vacuum_xid_failsafe_check(), the effective failsafe age is no - * less than 105% the value of the respective *_freeze_max_age - * parameter. Note that per-table settings could result in a low - * score even if the table surpasses the failsafe settings. However, - * this is a strange enough corner case that we don't bother trying to - * handle it. - * - * We further adjust the effective failsafe ages with the weight - * parameters so that increasing them lowers the ages at which we - * begin scaling aggressively. - */ - effective_xid_failsafe_age = Max(vacuum_failsafe_age, - autovacuum_freeze_max_age * 1.05); - effective_mxid_failsafe_age = Max(vacuum_multixact_failsafe_age, - autovacuum_multixact_freeze_max_age * 1.05); + scores->xid = (double) xid_age / freeze_max_age; + scores->mxid = (double) mxid_age / multixact_freeze_max_age; - if (autovacuum_freeze_score_weight > 1.0) - effective_xid_failsafe_age /= autovacuum_freeze_score_weight; - if (autovacuum_multixact_freeze_score_weight > 1.0) - effective_mxid_failsafe_age /= autovacuum_multixact_freeze_score_weight; + /* + * To ensure tables are given increased priority once they begin + * approaching wraparound, we scale the score aggressively if the ages + * surpass vacuum_failsafe_age or vacuum_multixact_failsafe_age. + * + * As in vacuum_xid_failsafe_check(), the effective failsafe age is no + * less than 105% the value of the respective *_freeze_max_age parameter. + * Note that per-table settings could result in a low score even if the + * table surpasses the failsafe settings. However, this is a strange + * enough corner case that we don't bother trying to handle it. + * + * We further adjust the effective failsafe ages with the weight + * parameters so that increasing them lowers the ages at which we begin + * scaling aggressively. + */ + effective_xid_failsafe_age = Max(vacuum_failsafe_age, + autovacuum_freeze_max_age * 1.05); + effective_mxid_failsafe_age = Max(vacuum_multixact_failsafe_age, + autovacuum_multixact_freeze_max_age * 1.05); - if (xid_age >= effective_xid_failsafe_age) - scores->xid = pow(scores->xid, Max(1.0, (double) xid_age / 100000000)); - if (mxid_age >= effective_mxid_failsafe_age) - scores->mxid = pow(scores->mxid, Max(1.0, (double) mxid_age / 100000000)); + if (autovacuum_freeze_score_weight > 1.0) + effective_xid_failsafe_age /= autovacuum_freeze_score_weight; + if (autovacuum_multixact_freeze_score_weight > 1.0) + effective_mxid_failsafe_age /= autovacuum_multixact_freeze_score_weight; - scores->xid *= autovacuum_freeze_score_weight; - scores->mxid *= autovacuum_multixact_freeze_score_weight; + if (xid_age >= effective_xid_failsafe_age) + scores->xid = pow(scores->xid, Max(1.0, (double) xid_age / 100000000)); + if (mxid_age >= effective_mxid_failsafe_age) + scores->mxid = pow(scores->mxid, Max(1.0, (double) mxid_age / 100000000)); - scores->max = Max(scores->xid, scores->mxid); - *dovacuum = true; - } + scores->xid *= autovacuum_freeze_score_weight; + scores->mxid *= autovacuum_multixact_freeze_score_weight; - /* User disabled it in pg_class.reloptions? (But ignore if at risk) */ - if (!av_enabled && !force_vacuum) - return; + scores->max = Max(scores->xid, scores->mxid); + *dovacuum |= force_vacuum; /* * If we found stats for the table, and autovacuum is currently enabled, @@ -3268,7 +3259,7 @@ relation_needs_vacanalyze(Oid relid, * vacuuming only, so don't vacuum (or analyze) anything that's not being * forced. */ - if (!tabentry || !AutoVacuumingActive()) + if (!tabentry) return; vactuples = tabentry->dead_tuples; @@ -3312,7 +3303,7 @@ relation_needs_vacanalyze(Oid relid, scores->vac = (double) vactuples / Max(vacthresh, 1); scores->vac *= autovacuum_vacuum_score_weight; scores->max = Max(scores->max, scores->vac); - *dovacuum = true; + *dovacuum |= av_enabled; } if (vac_ins_base_thresh >= 0 && instuples > vacinsthresh) @@ -3320,7 +3311,7 @@ relation_needs_vacanalyze(Oid relid, scores->vac_ins = (double) instuples / Max(vacinsthresh, 1); scores->vac_ins *= autovacuum_vacuum_insert_score_weight; scores->max = Max(scores->max, scores->vac_ins); - *dovacuum = true; + *dovacuum |= av_enabled; } /* @@ -3334,7 +3325,16 @@ relation_needs_vacanalyze(Oid relid, scores->anl = (double) anltuples / Max(anlthresh, 1); scores->anl *= autovacuum_analyze_score_weight; scores->max = Max(scores->max, scores->anl); - *doanalyze = true; + *doanalyze |= av_enabled; + + /* + * For historical reasons, we analyze even when autovacuum is disabled + * for the table if at risk of wraparound. It's not clear if this is + * necessary or intentional, but it has been this way for nearly 20 + * years, so it seems best to avoid changing it without further + * discussion. + */ + *doanalyze |= (force_vacuum && AutoVacuumingActive()); } if (vac_ins_base_thresh >= 0) -- 2.50.1 (Apple Git-155)
>From cce8784ffa874c6ace88b7cfdac86f2fe2e2f365 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Thu, 2 Apr 2026 13:52:21 -0500 Subject: [PATCH v8 3/5] add elevel parameter to relation_needs_vacanalyze() --- src/backend/postmaster/autovacuum.c | 9 +++++++-- 1 file changed, 7 insertions(+), 2 deletions(-) diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 817025ce616..12c205e57ea 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -378,6 +378,7 @@ static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts, Form_pg_class classForm, PgStat_StatTabEntry *tabentry, int effective_multixact_freeze_max_age, + int elevel, bool *dovacuum, bool *doanalyze, bool *wraparound, AutoVacuumScores *scores); @@ -2075,6 +2076,7 @@ do_autovacuum(void) /* Check if it needs vacuum or analyze */ relation_needs_vacanalyze(relid, relopts, classForm, tabentry, effective_multixact_freeze_max_age, + DEBUG3, &dovacuum, &doanalyze, &wraparound, &scores); @@ -2175,6 +2177,7 @@ do_autovacuum(void) relation_needs_vacanalyze(relid, relopts, classForm, tabentry, effective_multixact_freeze_max_age, + DEBUG3, &dovacuum, &doanalyze, &wraparound, &scores); @@ -2993,6 +2996,7 @@ recheck_relation_needs_vacanalyze(Oid relid, relation_needs_vacanalyze(relid, avopts, classForm, tabentry, effective_multixact_freeze_max_age, + DEBUG3, dovacuum, doanalyze, wraparound, &scores); @@ -3087,6 +3091,7 @@ relation_needs_vacanalyze(Oid relid, Form_pg_class classForm, PgStat_StatTabEntry *tabentry, int effective_multixact_freeze_max_age, + int elevel, /* output params below */ bool *dovacuum, bool *doanalyze, @@ -3338,14 +3343,14 @@ relation_needs_vacanalyze(Oid relid, } if (vac_ins_base_thresh >= 0) - elog(DEBUG3, "%s: vac: %.0f (thresh %.0f, score %.2f), ins: %.0f (thresh %.0f, score %.2f), anl: %.0f (thresh %.0f, score %.2f), xid score: %.2f, mxid score: %.2f", + elog(elevel, "%s: vac: %.0f (thresh %.0f, score %.2f), ins: %.0f (thresh %.0f, score %.2f), anl: %.0f (thresh %.0f, score %.2f), xid score: %.2f, mxid score: %.2f", NameStr(classForm->relname), vactuples, vacthresh, scores->vac, instuples, vacinsthresh, scores->vac_ins, anltuples, anlthresh, scores->anl, scores->xid, scores->mxid); else - elog(DEBUG3, "%s: vac: %.0f (thresh %.0f, score %.2f), ins: (disabled), anl: %.0f (thresh %.0f, score %.2f), xid score: %.2f, mxid score: %.2f", + elog(elevel, "%s: vac: %.0f (thresh %.0f, score %.2f), ins: (disabled), anl: %.0f (thresh %.0f, score %.2f), xid score: %.2f, mxid score: %.2f", NameStr(classForm->relname), vactuples, vacthresh, scores->vac, anltuples, anlthresh, scores->anl, -- 2.50.1 (Apple Git-155)
>From f64384464e33f76e908b01bd38815e706f7c5f18 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Thu, 2 Apr 2026 14:17:11 -0500 Subject: [PATCH v8 4/5] refactor autovacuum subroutine in preparation for system view --- src/backend/postmaster/autovacuum.c | 44 ++++++++++++++++------------- 1 file changed, 24 insertions(+), 20 deletions(-) diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 12c205e57ea..9ccd1861328 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -370,10 +370,12 @@ static void FreeWorkerInfo(int code, Datum arg); static autovac_table *table_recheck_autovac(Oid relid, HTAB *table_toast_map, TupleDesc pg_class_desc, int effective_multixact_freeze_max_age); -static void recheck_relation_needs_vacanalyze(Oid relid, AutoVacOpts *avopts, - Form_pg_class classForm, - int effective_multixact_freeze_max_age, - bool *dovacuum, bool *doanalyze, bool *wraparound); +static void compute_autovacuum_scores(Oid relid, AutoVacOpts *avopts, + Form_pg_class classForm, + int effective_multixact_freeze_max_age, + int elevel, + bool *dovacuum, bool *doanalyze, bool *wraparound, + AutoVacuumScores *scores); static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts, Form_pg_class classForm, PgStat_StatTabEntry *tabentry, @@ -2834,6 +2836,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, bool wraparound; AutoVacOpts *avopts; bool free_avopts = false; + AutoVacuumScores scores; /* fetch the relation's relcache entry */ classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid)); @@ -2859,9 +2862,11 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, avopts = &hentry->ar_reloptions; } - recheck_relation_needs_vacanalyze(relid, avopts, classForm, - effective_multixact_freeze_max_age, - &dovacuum, &doanalyze, &wraparound); + compute_autovacuum_scores(relid, avopts, classForm, + effective_multixact_freeze_max_age, + DEBUG3, + &dovacuum, &doanalyze, &wraparound, + &scores); /* OK, it needs something done */ if (doanalyze || dovacuum) @@ -2971,24 +2976,23 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, } /* - * recheck_relation_needs_vacanalyze - * - * Subroutine for table_recheck_autovac. + * compute_autovacuum_scores * * Fetch the pgstat of a relation and recheck whether a relation * needs to be vacuumed or analyzed. */ static void -recheck_relation_needs_vacanalyze(Oid relid, - AutoVacOpts *avopts, - Form_pg_class classForm, - int effective_multixact_freeze_max_age, - bool *dovacuum, - bool *doanalyze, - bool *wraparound) +compute_autovacuum_scores(Oid relid, + AutoVacOpts *avopts, + Form_pg_class classForm, + int effective_multixact_freeze_max_age, + int elevel, + bool *dovacuum, + bool *doanalyze, + bool *wraparound, + AutoVacuumScores *scores) { PgStat_StatTabEntry *tabentry; - AutoVacuumScores scores; /* fetch the pgstat table entry */ tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared, @@ -2996,9 +3000,9 @@ recheck_relation_needs_vacanalyze(Oid relid, relation_needs_vacanalyze(relid, avopts, classForm, tabentry, effective_multixact_freeze_max_age, - DEBUG3, + elevel, dovacuum, doanalyze, wraparound, - &scores); + scores); /* Release tabentry to avoid leakage */ if (tabentry) -- 2.50.1 (Apple Git-155)
>From 1ea7b5ff82183e6e874d24b0034173bcd91a7a2f Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Thu, 2 Apr 2026 15:47:19 -0500 Subject: [PATCH v8 5/5] add pg_stat_autovacuum_scores system view --- doc/src/sgml/maintenance.sgml | 6 ++ doc/src/sgml/system-views.sgml | 137 +++++++++++++++++++++++++++ src/backend/catalog/system_views.sql | 17 ++++ src/backend/postmaster/autovacuum.c | 70 ++++++++++++++ src/include/catalog/pg_proc.dat | 9 ++ src/test/regress/expected/rules.out | 12 +++ 6 files changed, 251 insertions(+) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 0d2a28207ed..6f500a66ae9 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -1164,6 +1164,12 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu <literal>2.0</literal> effectively doubles the <emphasis>analyze</emphasis> component score. </para> + + <para> + The + <link linkend="view-pg-autovacuum-scores"><structname>pg_autovacuum_scores</structname></link> + view shows the current scores of all tables in the current database. + </para> </sect3> </sect2> </sect1> diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index 9ee1a2bfc6a..ffe7152028d 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -56,6 +56,11 @@ <entry>in-use asynchronous IO handles</entry> </row> + <row> + <entry><link linkend="view-pg-autovacuum-scores"><structname>pg_autovacuum_scores</structname></link></entry> + <entry>autovacuum scores</entry> + </row> + <row> <entry><link linkend="view-pg-available-extensions"><structname>pg_available_extensions</structname></link></entry> <entry>available extensions</entry> @@ -540,6 +545,138 @@ </para> </sect1> + <sect1 id="view-pg-autovacuum-scores"> + <title><structname>pg_autovacuum_scores</structname></title> + + <indexterm zone="view-pg-autovacuum-scores"> + <primary>pg_autovacuum_scores</primary> + </indexterm> + + <para> + The <structname>pg_stat_autovacuum_scores</structname> view will contain one + row for each table in the current database, showing the current autovacuum + scores for that table. See <xref linkend="autovacuum-priority"/> for more + information. + </para> + + <table> + <title><structname>pg_autovacuum_scores</structname> Columns</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>relid</structfield> <type>oid</type> + </para> + <para> + Oid of the table. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>schemaname</structfield> <type>name</type> + </para> + <para> + Name of the schema that the table is in. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>relname</structfield> <type>name</type> + </para> + <para> + Name of the table. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>score</structfield> <type>double precision</type> + </para> + <para> + Maximum value of all component scores. This is the value that + autovacuum uses to sort the list of tables to process. Scores greater + than or equal to <literal>1.0</literal> indicate the table needs + processing. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>xid_score</structfield> <type>double precision</type> + </para> + <para> + Transaction ID age component score. Scores greater than or equal to + <literal>1.0</literal> indicate the table needs vacuuming. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>mxid_score</structfield> <type>double precision</type> + </para> + <para> + Multixact ID age component score. Scores greater than or equal to + <literal>1.0</literal> indicate the table needs vacuuming. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>vacuum_score</structfield> <type>double precision</type> + </para> + <para> + Vacuum component score. Scores greater than or equal to + <literal>1.0</literal> indicate the table needs vacuuming. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>vacuum_insert_score</structfield> <type>double precision</type> + </para> + <para> + Vacuum insert component score. Scores greater than or equal to + <literal>1.0</literal> indicate the table needs vacuuming. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>analyze_score</structfield> <type>double precision</type> + </para> + <para> + Analyze component score. Scores greater than or equal to + <literal>1.0</literal> indicate the table needs analyzing. + </para></entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + The <structname>pg_autovacuum_scores</structname> view is read-only. + </para> + + <para> + By default, the <structname>pg_autovacuum_scores</structname> view can be + read only by superusers or roles with privileges of the + <literal>pg_read_all_stats</literal> role. + </para> + </sect1> + <sect1 id="view-pg-available-extensions"> <title><structname>pg_available_extensions</structname></title> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index e54018004db..ef2448eff23 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1516,3 +1516,20 @@ CREATE VIEW pg_aios AS SELECT * FROM pg_get_aios(); REVOKE ALL ON pg_aios FROM PUBLIC; GRANT SELECT ON pg_aios TO pg_read_all_stats; + +CREATE VIEW pg_autovacuum_scores AS + SELECT + s.oid AS relid, + n.nspname AS schemaname, + c.relname AS relname, + s.score, + s.xid_score, + s.mxid_score, + s.vacuum_score, + s.vacuum_insert_score, + s.analyze_score + FROM pg_get_autovacuum_scores() s + JOIN pg_class c on c.oid = s.oid + LEFT JOIN pg_namespace n ON n.oid = c.relnamespace; +REVOKE ALL ON pg_autovacuum_scores FROM PUBLIC; +GRANT SELECT ON pg_autovacuum_scores TO pg_read_all_stats; diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 9ccd1861328..891bd82cfb0 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -80,6 +80,7 @@ #include "catalog/pg_namespace.h" #include "commands/vacuum.h" #include "common/int.h" +#include "funcapi.h" #include "lib/ilist.h" #include "libpq/pqsignal.h" #include "miscadmin.h" @@ -111,6 +112,7 @@ #include "utils/syscache.h" #include "utils/timeout.h" #include "utils/timestamp.h" +#include "utils/tuplestore.h" #include "utils/wait_event.h" @@ -3672,3 +3674,71 @@ check_av_worker_gucs(void) errdetail("The server will only start up to \"autovacuum_worker_slots\" (%d) autovacuum workers at a given time.", autovacuum_worker_slots))); } + +/* + * pg_get_autovacuum_scores + * + * Returns current autovacuum scores for all relevant tables in the current + * database. + */ +Datum +pg_get_autovacuum_scores(PG_FUNCTION_ARGS) +{ + int effective_multixact_freeze_max_age; + Relation rel; + TableScanDesc scan; + HeapTuple tup; + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + + InitMaterializedSRF(fcinfo, 0); + + /* some prerequisite initialization */ + effective_multixact_freeze_max_age = MultiXactMemberFreezeThreshold(); + recentXid = ReadNextTransactionId(); + recentMulti = ReadNextMultiXactId(); + + /* scan pg_class */ + rel = table_open(RelationRelationId, AccessShareLock); + scan = table_beginscan_catalog(rel, 0, NULL); + while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL) + { + Form_pg_class form = (Form_pg_class) GETSTRUCT(tup); + AutoVacOpts *avopts; + bool dovacuum; + bool doanalyze; + bool wraparound; + AutoVacuumScores scores; + Datum vals[7]; + bool nulls[7] = {false}; + + /* skip ineligible entries */ + if (form->relkind != RELKIND_RELATION && + form->relkind != RELKIND_MATVIEW && + form->relkind != RELKIND_TOASTVALUE) + continue; + if (form->relpersistence == RELPERSISTENCE_TEMP) + continue; + + avopts = extract_autovac_opts(tup, RelationGetDescr(rel)); + compute_autovacuum_scores(form->oid, avopts, form, + effective_multixact_freeze_max_age, 0, + &dovacuum, &doanalyze, &wraparound, + &scores); + if (avopts) + pfree(avopts); + + vals[0] = ObjectIdGetDatum(form->oid); + vals[1] = Float8GetDatum(scores.max); + vals[2] = Float8GetDatum(scores.xid); + vals[3] = Float8GetDatum(scores.mxid); + vals[4] = Float8GetDatum(scores.vac); + vals[5] = Float8GetDatum(scores.vac_ins); + vals[6] = Float8GetDatum(scores.anl); + + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, vals, nulls); + } + table_endscan(scan); + table_close(rel, AccessShareLock); + + return (Datum) 0; +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index acf16254b21..78ac2adb1ba 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -6245,6 +6245,15 @@ prosrc => 'pg_stat_reset_subscription_stats', proacl => '{POSTGRES=X}' }, +{ oid => '8409', descr => 'autovacuum scores', + proname => 'pg_get_autovacuum_scores', prorows => '100', proretset => 't', + provolatile => 'v', proparallel => 'u', prorettype => 'record', proargtypes => '', + proallargtypes => '{oid,float8,float8,float8,float8,float8,float8}', + proargmodes => '{o,o,o,o,o,o,o}', + proargnames => '{oid,score,xid_score,mxid_score,vacuum_score,vacuum_insert_score,analyze_score}', + prosrc => 'pg_get_autovacuum_scores', + proacl => '{POSTGRES=X,pg_read_all_stats=X}' }, + { oid => '3163', descr => 'current trigger depth', proname => 'pg_trigger_depth', provolatile => 's', proparallel => 'r', prorettype => 'int4', proargtypes => '', prosrc => 'pg_trigger_depth' }, diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 2b3cf6d8569..047dc90134f 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1302,6 +1302,18 @@ pg_aios| SELECT pid, f_localmem, f_buffered FROM pg_get_aios() pg_get_aios(pid, io_id, io_generation, state, operation, off, length, target, handle_data_len, raw_result, result, target_desc, f_sync, f_localmem, f_buffered); +pg_autovacuum_scores| SELECT s.oid AS relid, + n.nspname AS schemaname, + c.relname, + s.score, + s.xid_score, + s.mxid_score, + s.vacuum_score, + s.vacuum_insert_score, + s.analyze_score + FROM ((pg_get_autovacuum_scores() s(oid, score, xid_score, mxid_score, vacuum_score, vacuum_insert_score, analyze_score) + JOIN pg_class c ON ((c.oid = s.oid))) + LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))); pg_available_extension_versions| SELECT e.name, e.version, (x.extname IS NOT NULL) AS installed, -- 2.50.1 (Apple Git-155)
