From f12fd986764fc218cae670ddc4f6ba6ec1fdcc98 Mon Sep 17 00:00:00 2001 From: Sami Imseih Date: Tue, 31 Mar 2026 18:25:30 +0000 Subject: [PATCH v4 1/4] Always compute autovacuum priority scores Previously, XID/MXID age scores were only computed when a table was at wraparound risk, and threshold-based scores were only computed when autovacuum was globally active. This meant the scores were unavailable for tables not yet at risk or with autovacuum disabled. A future patch to monitor the scores and the need for vacuum/analyze must be able to compute these values regardless of autovacuum state on the table. Therefore, separate the need for vacuum and analyze and track them in AutoVacuumScore so they can be used for this purpose rather than the output dovacuum, doanalyze and wraparound parameters that are acted upon by autovacuum. AutoVacuumScores is also renamed to AutoVacuumPriority as it no longer just tracks scores. Discussion: https://postgr.es/m/CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw%40mail.gmail.com --- src/backend/postmaster/autovacuum.c | 216 ++++++++++++++-------------- src/tools/pgindent/typedefs.list | 2 +- 2 files changed, 113 insertions(+), 105 deletions(-) diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 6694f485216..2ca15aee4eb 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -318,8 +318,9 @@ static MemoryContext DatabaseListCxt = NULL; /* * This struct is used by relation_needs_vacanalyze() to return the table's - * score (i.e., the maximum of the component scores) as well as the component - * scores themselves. + * autovacuum priority, including the overall score (i.e., the maximum of the + * component scores), the component scores themselves, and whether the table + * needs vacuum, analyze, or is at risk of wraparound. */ typedef struct { @@ -329,7 +330,10 @@ typedef struct double vac; /* vacuum component */ double vac_ins; /* vacuum insert component */ double anl; /* analyze component */ -} AutoVacuumScores; + bool needs_vacuum; /* threshold exceeded for vacuum */ + bool needs_analyze; /* threshold exceeded for analyze */ + bool is_wraparound; /* at risk of XID/MXID wraparound */ +} AutoVacuumPriority; /* * This struct is used to track and sort the list of tables to process. @@ -379,7 +383,7 @@ static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts, PgStat_StatTabEntry *tabentry, int effective_multixact_freeze_max_age, bool *dovacuum, bool *doanalyze, bool *wraparound, - AutoVacuumScores *scores); + AutoVacuumPriority *priority); static void autovacuum_do_vac_analyze(autovac_table *tab, BufferAccessStrategy bstrategy); @@ -2034,7 +2038,7 @@ do_autovacuum(void) bool dovacuum; bool doanalyze; bool wraparound; - AutoVacuumScores scores; + AutoVacuumPriority priority; if (classForm->relkind != RELKIND_RELATION && classForm->relkind != RELKIND_MATVIEW) @@ -2076,7 +2080,7 @@ do_autovacuum(void) relation_needs_vacanalyze(relid, relopts, classForm, tabentry, effective_multixact_freeze_max_age, &dovacuum, &doanalyze, &wraparound, - &scores); + &priority); /* Relations that need work are added to tables_to_process */ if (dovacuum || doanalyze) @@ -2084,7 +2088,7 @@ do_autovacuum(void) TableToProcess *table = palloc_object(TableToProcess); table->oid = relid; - table->score = scores.max; + table->score = priority.max; tables_to_process = lappend(tables_to_process, table); } @@ -2142,7 +2146,7 @@ do_autovacuum(void) bool dovacuum; bool doanalyze; bool wraparound; - AutoVacuumScores scores; + AutoVacuumPriority priority; /* * We cannot safely process other backends' temp tables, so skip 'em. @@ -2176,7 +2180,7 @@ do_autovacuum(void) relation_needs_vacanalyze(relid, relopts, classForm, tabentry, effective_multixact_freeze_max_age, &dovacuum, &doanalyze, &wraparound, - &scores); + &priority); /* ignore analyze for toast tables */ if (dovacuum) @@ -2184,7 +2188,7 @@ do_autovacuum(void) TableToProcess *table = palloc_object(TableToProcess); table->oid = relid; - table->score = scores.max; + table->score = priority.max; tables_to_process = lappend(tables_to_process, table); } @@ -2985,7 +2989,7 @@ recheck_relation_needs_vacanalyze(Oid relid, bool *wraparound) { PgStat_StatTabEntry *tabentry; - AutoVacuumScores scores; + AutoVacuumPriority priority; /* fetch the pgstat table entry */ tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared, @@ -2994,7 +2998,7 @@ recheck_relation_needs_vacanalyze(Oid relid, relation_needs_vacanalyze(relid, avopts, classForm, tabentry, effective_multixact_freeze_max_age, dovacuum, doanalyze, wraparound, - &scores); + &priority); /* Release tabentry to avoid leakage */ if (tabentry) @@ -3030,10 +3034,10 @@ recheck_relation_needs_vacanalyze(Oid relid, * transactions back, and if its relminmxid is more than * multixact_freeze_max_age multixacts back. * - * A table whose autovacuum_enabled option is false is - * automatically skipped (unless we have to vacuum it due to freeze_max_age). - * Thus autovacuum can be disabled for specific tables. Also, when the cumulative - * stats system does not have data about a table, it will be skipped. + * A table whose autovacuum_enabled option is false is automatically skipped + * by autovacuum (unless we have to vacuum it due to freeze_max_age), + * but scores are still computed. Also, when the cumulative stats system does + * not have data about a table, threshold-based scores will be zero. * * A table whose vac_base_thresh value is < 0 takes the base value from the * autovacuum_vacuum_threshold GUC variable. Similarly, a vac_scale_factor @@ -3061,10 +3065,10 @@ recheck_relation_needs_vacanalyze(Oid relid, * compared to the vacuum threshold, and the number of inserted/updated/deleted * tuples compared to the analyze threshold. * - * 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 relfrozen/relminmxid-based score is scaled aggressively so that the - * table has a decent chance of sorting to the front of the list. + * Furthermore, for tables nearing wraparound, i.e., those that have surpassed + * the effective failsafe ages, the relfrozen/relminmxid-based score is scaled + * aggressively so that the table has a decent chance of sorting to the front + * of the list. * * To adjust how strongly each component contributes to the score, the * following parameters can be adjusted from their default of 1.0 to anywhere @@ -3077,9 +3081,12 @@ recheck_relation_needs_vacanalyze(Oid relid, * autovacuum_vacuum_insert_score_weight * autovacuum_analyze_score_weight * - * The autovacuum table score is returned in scores->max. The component scores - * are also returned in the "scores" argument via the other members of the - * AutoVacuumScores struct. + * The autovacuum table score is returned in priority->max. The component scores + * are also returned in the "priority" argument via the other members of the + * AutoVacuumPriority struct. + * + * Priority scores are always computed. dovacuum and doanalyze are only set when + * autovacuum is active and enabled for the relation. */ static void relation_needs_vacanalyze(Oid relid, @@ -3091,7 +3098,7 @@ relation_needs_vacanalyze(Oid relid, bool *dovacuum, bool *doanalyze, bool *wraparound, - AutoVacuumScores *scores) + AutoVacuumPriority *priority) { bool force_vacuum; bool av_enabled; @@ -3122,11 +3129,15 @@ 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; Assert(classForm != NULL); Assert(OidIsValid(relid)); - memset(scores, 0, sizeof(AutoVacuumScores)); + memset(priority, 0, sizeof(AutoVacuumPriority)); *dovacuum = false; *doanalyze = false; @@ -3196,74 +3207,64 @@ 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; - /* - * 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; + priority->xid = (double) xid_age / freeze_max_age; + priority->mxid = (double) mxid_age / multixact_freeze_max_age; - scores->xid = (double) xid_age / freeze_max_age; - scores->mxid = (double) mxid_age / multixact_freeze_max_age; + /* + * 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); - /* - * 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 (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; - 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; + if (xid_age >= effective_xid_failsafe_age) + priority->xid = pow(priority->xid, Max(1.0, (double) xid_age / 100000000)); + if (mxid_age >= effective_mxid_failsafe_age) + priority->mxid = pow(priority->mxid, Max(1.0, (double) mxid_age / 100000000)); - 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)); + priority->xid *= autovacuum_freeze_score_weight; + priority->mxid *= autovacuum_multixact_freeze_score_weight; - scores->xid *= autovacuum_freeze_score_weight; - scores->mxid *= autovacuum_multixact_freeze_score_weight; + priority->max = Max(priority->xid, priority->mxid); - scores->max = Max(scores->xid, scores->mxid); + if (force_vacuum) + { *dovacuum = true; + priority->is_wraparound = priority->needs_vacuum = true; } - /* User disabled it in pg_class.reloptions? (But ignore if at risk) */ - if (!av_enabled && !force_vacuum) - return; - /* - * If we found stats for the table, and autovacuum is currently enabled, - * make a threshold-based decision whether to vacuum and/or analyze. If - * autovacuum is currently disabled, we must be here for anti-wraparound - * vacuuming only, so don't vacuum (or analyze) anything that's not being - * forced. + * If we found stats for the table, make a threshold-based decision + * whether to vacuum and/or analyze, and compute the corresponding + * priority. dovacuum/doanalyze are only set when autovacuum is active and + * enabled for this table. */ - if (tabentry && AutoVacuumingActive()) + if (tabentry) { float4 pcnt_unfrozen = 1; float4 reltuples = classForm->reltuples; @@ -3304,52 +3305,59 @@ relation_needs_vacanalyze(Oid relid, anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples; /* - * Determine if this table needs vacuum, and update the score if it - * does. + * Determine if this table needs vacuum, and if it does, update the + * score and mark the table as needing vacuum. */ 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; + priority->vac = (double) vactuples / Max(vacthresh, 1); + priority->vac *= autovacuum_vacuum_score_weight; + priority->max = Max(priority->max, priority->vac); + priority->needs_vacuum = true; + if (av_enabled && AutoVacuumingActive()) + *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; + priority->vac_ins = (double) instuples / Max(vacinsthresh, 1); + priority->vac_ins *= autovacuum_vacuum_insert_score_weight; + priority->max = Max(priority->max, priority->vac_ins); + priority->needs_vacuum = true; + if (av_enabled && AutoVacuumingActive()) + *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. + * Determine if this table needs analyze, and if it does, update the + * score and mark the table as needing analyze. 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; + priority->anl = (double) anltuples / Max(anlthresh, 1); + priority->anl *= autovacuum_analyze_score_weight; + priority->max = Max(priority->max, priority->anl); + priority->needs_analyze = true; + if (av_enabled && AutoVacuumingActive()) + *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); + vactuples, vacthresh, priority->vac, + instuples, vacinsthresh, priority->vac_ins, + anltuples, anlthresh, priority->anl, + priority->xid, priority->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); + vactuples, vacthresh, priority->vac, + anltuples, anlthresh, priority->anl, + priority->xid, priority->mxid); } } diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 8e9c06547d6..de874c62cd3 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -187,7 +187,7 @@ AuthToken AutoPrewarmReadStreamData AutoPrewarmSharedState AutoVacOpts -AutoVacuumScores +AutoVacuumPriority AutoVacuumShmemStruct AutoVacuumWorkItem AutoVacuumWorkItemType -- 2.47.3