public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sami Imseih <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: Bharath Rupireddy <[email protected]>
Cc: Robert Treat <[email protected]>
Cc: [email protected]
Cc: pgsql-hackers <[email protected]>
Subject: Re: Add pg_stat_autovacuum_priority
Date: Tue, 31 Mar 2026 21:46:16 -0500
Message-ID: <CAA5RZ0uVZLsX_k33QoWt8iRes73jyqs+EjypL9+1wW1v8NsYbg@mail.gmail.com> (raw)
In-Reply-To: <acwTxpz3Toxt0ty8@nathan>
References: <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com>
<CALj2ACXcmYsoJXTYtuGhkxNVXV5jtTNfL-vB+sQq-jE9__N5AA@mail.gmail.com>
<CAA5RZ0surzz41exF5QwecuFU8NqZVRR5aDnC6MObeEcsXhfu4Q@mail.gmail.com>
<CALj2ACX+SRgv2RO9Oo4Me-zzMMjHVg8rf-MqvMRbp9=1ioWbsg@mail.gmail.com>
<CABV9wwM3nRitsgUxeCF0ywbAaLZV5jWC-9tj6WKxkdmQkHRcWg@mail.gmail.com>
<CAA5RZ0vMp2B3UBUoqLVedy8G3u8_O8M11+Y5V7uZv3++CGYasg@mail.gmail.com>
<CABV9wwNBifXpOjxO9rGn1HHK=DG02qApVurWSHa+rDzPriK6pA@mail.gmail.com>
<CAA5RZ0uiBCiQDUfA5sbc50gybWx1YES3HfLKMd9gv661YpJyRg@mail.gmail.com>
<CALj2ACVnQRS=T8SY2zegfft=Nku1n6w5609JPdd9jqiM+G_gGg@mail.gmail.com>
<acwTxpz3Toxt0ty8@nathan>
> > 1/ + while ((classTup = heap_getnext(relScan, ForwardScanDirection)) != NULL)
> >
> > Missing check_for_interrupts call while scanning the pg_class system catalog.
>
> From a glance I don't see one in the scanning code in do_autovacuum(),
> either. I'm not sure we need to be worried about this.
Yes, I will leave it out. Even for catalogs with thousands of tables, I don't
foresee this being an issue.
> > + values[8] = Float8GetDatum(scores.vac_ins);
> > + values[9] = Float8GetDatum(scores.anl);
> >
> > Nit: It's a matter of taste. How about using something like below
> > instead of hardcoded column numbers? I expect this view to grow in the
> > future, so it helps to keep things simple.
> >
> > values[i++] = Float8GetDatum(scores.anl);
> > Assert(i == NUM_AV_SCORE_COLS);
>
> I don't think either way is substantially better.
I agree. using numbers is more readable IMO.
> > + <row>
> > + <entry role="catalog_table_entry"><para role="column_definition">
> > + <structfield>vacuum_dead_score</structfield> <type>double precision</type>
> > + </para>
> > + <para>
> > + Score component based on the estimated number of dead tuples
> > + needing removal by vacuum.
> > + </para></entry>
> > + </row>
> >
> > I think we should make sure the column names align with the names given to
> > the new parameters [0] and the new "Autovacuum Prioritization" section in
> > the docs [1].
> I will look into this in the next rev.
The field names now match the GUC names (without the _weight). We might as well
also make this name change in the AutoVacuumScore struct.
I attached v4 which includes 4 patches in the set to address earlier comments:
0001:
This modifies relation_needs_vacanalyze() to always compute the score so
the monitoring view can report the score regardless if autovacuum is enabled
on the table. AutoVacuumScores is renamed to AutoVacuumPriority as it
now also tracks needs_analyze, needs_vacuum and is_wraparound which
are different from the dovacuum, doanalyze and wraparound output parameters
which are acted on by autovacuum. This is a clean separation of reporting
the scores/needs for autovacuum/analyze and the eligibility for autovacuum
which is based on the state of autovacuum being enabled and set on the table
level.
0002:
relation_needs_vacanalyze() takes a new elevel argument to control logging.
Callers pass DEBUG3 for autovacuum or 0 to suppress logging.
0003:
Alvaro's earlier suggestion to factor out recheck_relation_needs_vacanalyze()
and introduce compute_autovac_score()
0004:
The view implementation with field names that better match the GUCs as suggested
by Nathan in an earlier comment.
--
Sami
Attachments:
[application/octet-stream] v4-0001-Always-compute-autovacuum-priority-scores.patch (17.1K, 2-v4-0001-Always-compute-autovacuum-priority-scores.patch)
download | inline diff:
From f12fd986764fc218cae670ddc4f6ba6ec1fdcc98 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
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
[application/octet-stream] v4-0002-Add-elevel-parameter-to-relation_needs_vacanalyze.patch (4.1K, 3-v4-0002-Add-elevel-parameter-to-relation_needs_vacanalyze.patch)
download | inline diff:
From 2e49700cc2ce24399a5f260c78ba907c519dffe7 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Tue, 31 Mar 2026 18:33:18 +0000
Subject: [PATCH v4 2/4] Add elevel parameter to relation_needs_vacanalyze
Allow callers to control the log level for debug output
by passing an elevel parameter. Passing 0 suppresses
logging. This prepares the function for use by a future
view that should not emit debug messages when queried.
Discussion: https://postgr.es/m/CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw%40mail.gmail.com
---
src/backend/postmaster/autovacuum.c | 22 +++++++++++++---------
1 file changed, 13 insertions(+), 9 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 2ca15aee4eb..72ad30f6ae7 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -383,7 +383,8 @@ static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
bool *dovacuum, bool *doanalyze, bool *wraparound,
- AutoVacuumPriority *priority);
+ AutoVacuumPriority *priority,
+ int elevel);
static void autovacuum_do_vac_analyze(autovac_table *tab,
BufferAccessStrategy bstrategy);
@@ -2080,7 +2081,7 @@ do_autovacuum(void)
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
&dovacuum, &doanalyze, &wraparound,
- &priority);
+ &priority, DEBUG3);
/* Relations that need work are added to tables_to_process */
if (dovacuum || doanalyze)
@@ -2180,7 +2181,7 @@ do_autovacuum(void)
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
&dovacuum, &doanalyze, &wraparound,
- &priority);
+ &priority, DEBUG3);
/* ignore analyze for toast tables */
if (dovacuum)
@@ -2998,7 +2999,7 @@ recheck_relation_needs_vacanalyze(Oid relid,
relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
effective_multixact_freeze_max_age,
dovacuum, doanalyze, wraparound,
- &priority);
+ &priority, DEBUG3);
/* Release tabentry to avoid leakage */
if (tabentry)
@@ -3087,6 +3088,8 @@ recheck_relation_needs_vacanalyze(Oid relid,
*
* Priority scores are always computed. dovacuum and doanalyze are only set when
* autovacuum is active and enabled for the relation.
+ *
+ * elevel controls the log level for debug output. Pass 0 to suppress logging.
*/
static void
relation_needs_vacanalyze(Oid relid,
@@ -3098,7 +3101,8 @@ relation_needs_vacanalyze(Oid relid,
bool *dovacuum,
bool *doanalyze,
bool *wraparound,
- AutoVacuumPriority *priority)
+ AutoVacuumPriority *priority,
+ int elevel)
{
bool force_vacuum;
bool av_enabled;
@@ -3345,15 +3349,15 @@ relation_needs_vacanalyze(Oid relid,
*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",
+ if (elevel > 0 && vac_ins_base_thresh >= 0)
+ 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, 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",
+ else if (elevel > 0)
+ 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, priority->vac,
anltuples, anlthresh, priority->anl,
--
2.47.3
[application/octet-stream] v4-0004-Add-pg_stat_autovacuum_priority-view.patch (18.5K, 4-v4-0004-Add-pg_stat_autovacuum_priority-view.patch)
download | inline diff:
From f53d03702f4882a9135176a3932a93dd668cfca1 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Mon, 23 Mar 2026 17:03:59 +0000
Subject: [PATCH v4 4/4] Add pg_stat_autovacuum_priority view
d7965d65f introduced autovacuum prioritization with
scoring. This change adds a view to expose those scores.
The view shows a row per relation indicating whether it
needs vacuum or analyze, the score of each component
and the Max score across all components. This provides
a way to introspect autovacuum priority and provide
feedback on tuning of the related GUCs.
The underlying function pg_stat_get_autovacuum_priority()
scans all relations in the current database and computes
scores using compute_autovac_score(). By default,
only superusers and roles with privileges of
pg_read_all_stats can execute the function, as
controlled by the function's ACL in pg_proc.
The view also emits the relid, namespace and relname,
so it can be joined with other views like
pg_stat_all_tables and pg_stat_progress_vacuum for
complementary vacuum details.
Tests added to vacuum.sql
Discussion: https://postgr.es/m/CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw%40mail.gmail.com
---
doc/src/sgml/maintenance.sgml | 6 +
doc/src/sgml/monitoring.sgml | 166 +++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 21 ++++
src/backend/postmaster/autovacuum.c | 86 ++++++++++++++
src/include/catalog/pg_proc.dat | 10 ++
src/test/regress/expected/rules.out | 15 +++
src/test/regress/expected/vacuum.out | 32 ++++++
src/test/regress/sql/vacuum.sql | 23 ++++
8 files changed, 359 insertions(+)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 0d2a28207ed..2125774aff3 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="monitoring-pg-stat-autovacuum-priority-view">
+ <structname>pg_stat_autovacuum_priority</structname></link> view can be
+ used to inspect each table's autovacuum need and priority score.
+ </para>
</sect3>
</sect2>
</sect1>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index bb75ed1069b..791850bafe7 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -463,6 +463,15 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_autovacuum_priority</structname><indexterm><primary>pg_stat_autovacuum_priority</primary></indexterm></entry>
+ <entry>One row per relation in the current database, showing
+ a table's autovacuum need and priority. See
+ <link linkend="monitoring-pg-stat-autovacuum-priority-view">
+ <structname>pg_stat_autovacuum_priority</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_bgwriter</structname><indexterm><primary>pg_stat_bgwriter</primary></indexterm></entry>
<entry>One row only, showing statistics about the
@@ -5256,6 +5265,163 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</sect2>
+ <sect2 id="monitoring-pg-stat-autovacuum-priority-view">
+ <title><structname>pg_stat_autovacuum_priority</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_autovacuum_priority</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_autovacuum_priority</structname> view contains
+ one row per relation in the current database, showing whether a table
+ needs autovacuum or autoanalyze and its priority. The
+ <structfield>score</structfield>, <structfield>freeze_score</structfield>,
+ and <structfield>multixact_freeze_score</structfield> values may be very large for
+ tables approaching wraparound, as these scores are scaled aggressively
+ once they surpass the failsafe age thresholds.
+ </para>
+
+ <table id="pg-stat-autovacuum-priority-view" xreflabel="pg_stat_autovacuum_priority">
+ <title><structname>pg_stat_autovacuum_priority</structname> View</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 a 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 this 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 this table
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>needs_vacuum</structfield> <type>boolean</type>
+ </para>
+ <para>
+ True if the table exceeds the vacuum threshold
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>needs_analyze</structfield> <type>boolean</type>
+ </para>
+ <para>
+ True if the table exceeds the analyze threshold
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>wraparound</structfield> <type>boolean</type>
+ </para>
+ <para>
+ True if vacuuming is needed to prevent transaction ID or
+ multixact ID wraparound
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>score</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Priority score used by autovacuum to order which tables to
+ process first. Higher values indicate greater urgency. This is
+ the maximum of all component scores below.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>freeze_score</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Score component based on transaction ID age.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>multixact_freeze_score</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Score component based on multixact ID age.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vacuum_score</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Score component based on the estimated number of dead tuples
+ needing removal by vacuum.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vacuum_insert_score</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Score component based on the number of inserts since the last
+ vacuum.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>analyze_score</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Score component based on the number of modifications since the
+ last analyze.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ By default, the <structname>pg_stat_autovacuum_priority</structname> view can
+ be read only by superusers or roles with privileges of the
+ <literal>pg_read_all_stats</literal> role.
+ </para>
+
+ </sect2>
+
<sect2 id="monitoring-stats-functions">
<title>Statistics Functions</title>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index e54018004db..30bbd55e330 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -795,6 +795,27 @@ CREATE VIEW pg_stat_xact_user_tables AS
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
schemaname !~ '^pg_toast';
+CREATE VIEW pg_stat_autovacuum_priority AS
+ SELECT
+ S.relid,
+ N.nspname AS schemaname,
+ C.relname AS relname,
+ S.needs_vacuum,
+ S.needs_analyze,
+ S.wraparound,
+ S.score,
+ S.freeze_score,
+ S.multixact_freeze_score,
+ S.vacuum_score,
+ S.vacuum_insert_score,
+ S.analyze_score
+ FROM pg_stat_get_autovacuum_priority() S
+ JOIN pg_class C ON C.oid = S.relid
+ LEFT JOIN pg_namespace N ON N.oid = C.relnamespace;
+
+REVOKE ALL ON pg_stat_autovacuum_priority FROM PUBLIC;
+GRANT SELECT ON pg_stat_autovacuum_priority TO pg_read_all_stats;
+
CREATE VIEW pg_statio_all_tables AS
SELECT
C.oid AS relid,
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 4b0d2526f5b..a469d2858ff 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"
@@ -3675,3 +3677,87 @@ 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_stat_get_autovacuum_priority
+ *
+ * Returns the autovacuum priority score for a relation as well as if the
+ * relation needs vacuum or analyze, and if the vacuum is a force vacuum
+ * due to wraparound.
+ *
+ * This follows the same setup as do_autovacuum(). Global state such
+ * as recentXid/recentMulti and effective_multixact_freeze_max_age is
+ * computed here, while compute_autovac_score() handles the per-relation
+ * score computation.
+ */
+Datum
+pg_stat_get_autovacuum_priority(PG_FUNCTION_ARGS)
+{
+#define NUM_AV_SCORE_COLS 10
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ Relation classRel;
+ TupleDesc pg_class_desc;
+ int effective_multixact_freeze_max_age;
+ TableScanDesc relScan;
+ HeapTuple classTup;
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ effective_multixact_freeze_max_age = MultiXactMemberFreezeThreshold();
+
+ recentXid = ReadNextTransactionId();
+ recentMulti = ReadNextMultiXactId();
+
+ classRel = table_open(RelationRelationId, AccessShareLock);
+ pg_class_desc = CreateTupleDescCopy(RelationGetDescr(classRel));
+
+ relScan = table_beginscan_catalog(classRel, 0, NULL);
+ while ((classTup = heap_getnext(relScan, ForwardScanDirection)) != NULL)
+ {
+ Form_pg_class classForm = (Form_pg_class) GETSTRUCT(classTup);
+ bool dovacuum;
+ bool doanalyze;
+ bool wraparound;
+ AutoVacuumPriority priority;
+ AutoVacOpts *avopts;
+ Datum values[NUM_AV_SCORE_COLS];
+ bool nulls[NUM_AV_SCORE_COLS] = {false};
+
+ if (classForm->relkind != RELKIND_RELATION &&
+ classForm->relkind != RELKIND_MATVIEW &&
+ classForm->relkind != RELKIND_TOASTVALUE)
+ continue;
+
+ if (classForm->relpersistence == RELPERSISTENCE_TEMP)
+ continue;
+
+ avopts = extract_autovac_opts(classTup, pg_class_desc);
+
+ compute_autovac_score(classTup, pg_class_desc,
+ effective_multixact_freeze_max_age, avopts,
+ 0, &dovacuum, &doanalyze,
+ &wraparound, &priority);
+
+ if (avopts)
+ pfree(avopts);
+
+ values[0] = ObjectIdGetDatum(classForm->oid);
+ values[1] = BoolGetDatum(priority.needs_vacuum);
+ values[2] = BoolGetDatum(priority.needs_analyze);
+ values[3] = BoolGetDatum(priority.is_wraparound);
+ values[4] = Float8GetDatum(priority.max);
+ values[5] = Float8GetDatum(priority.xid);
+ values[6] = Float8GetDatum(priority.mxid);
+ values[7] = Float8GetDatum(priority.vac);
+ values[8] = Float8GetDatum(priority.vac_ins);
+ values[9] = Float8GetDatum(priority.anl);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+ values, nulls);
+ }
+ table_endscan(relScan);
+
+ table_close(classRel, AccessShareLock);
+
+ return (Datum) 0;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 3579cec5744..6f5d199a506 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5667,6 +5667,16 @@
proname => 'pg_stat_get_total_autoanalyze_time', provolatile => 's',
proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
prosrc => 'pg_stat_get_total_autoanalyze_time' },
+{ oid => '8409',
+ descr => 'statistics: autovacuum priority scores for all relations',
+ proname => 'pg_stat_get_autovacuum_priority', prorows => '100',
+ proretset => 't', provolatile => 'v', proparallel => 'r',
+ prorettype => 'record', proargtypes => '',
+ proallargtypes => '{oid,bool,bool,bool,float8,float8,float8,float8,float8,float8}',
+ proargmodes => '{o,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{relid,needs_vacuum,needs_analyze,wraparound,score,freeze_score,multixact_freeze_score,vacuum_score,vacuum_insert_score,analyze_score}',
+ prosrc => 'pg_stat_get_autovacuum_priority',
+ proacl => '{POSTGRES=X,pg_read_all_stats=X}' },
{ oid => '1936', descr => 'statistics: currently active backend IDs',
proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't',
provolatile => 's', proparallel => 'r', prorettype => 'int4',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2b3cf6d8569..53bf9a46e4f 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1860,6 +1860,21 @@ pg_stat_archiver| SELECT archived_count,
last_failed_time,
stats_reset
FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset);
+pg_stat_autovacuum_priority| SELECT s.relid,
+ n.nspname AS schemaname,
+ c.relname,
+ s.needs_vacuum,
+ s.needs_analyze,
+ s.wraparound,
+ s.score,
+ s.freeze_score,
+ s.multixact_freeze_score,
+ s.vacuum_score,
+ s.vacuum_insert_score,
+ s.analyze_score
+ FROM ((pg_stat_get_autovacuum_priority() s(relid, needs_vacuum, needs_analyze, wraparound, score, freeze_score, multixact_freeze_score, vacuum_score, vacuum_insert_score, analyze_score)
+ JOIN pg_class c ON ((c.oid = s.relid)))
+ LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)));
pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_alloc() AS buffers_alloc,
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index d4696bc3325..9cbc2f6a14b 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -730,3 +730,35 @@ SELECT pg_column_toast_chunk_id(f1) = :'id_2_chunk' AS same_chunk
(1 row)
DROP TABLE vac_rewrite_toast;
+-- Test pg_stat_autovacuum_priority view. Scores are checked to be
+-- within an expected range. freeze_score and multixact_freeze_score are excluded
+-- as they require consuming enough XIDs to be meaningful.
+CREATE TABLE vacuum_priority_test (id int)
+ WITH (autovacuum_analyze_threshold = 1,
+ autovacuum_vacuum_threshold = 1,
+ autovacuum_vacuum_insert_threshold = 1,
+ autovacuum_enabled = off);
+INSERT INTO vacuum_priority_test SELECT 1;
+INSERT INTO vacuum_priority_test SELECT 2;
+DELETE FROM vacuum_priority_test WHERE id = 1;
+DELETE FROM vacuum_priority_test WHERE id = 2;
+-- force vacuum stats to be flushed
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT needs_vacuum, needs_analyze,
+ score > 0 AND score <= 4 AS score,
+ vacuum_score > 0 AND vacuum_score <= 2 AS vacuum_score,
+ vacuum_insert_score > 0 AND vacuum_insert_score <= 2 AS vacuum_insert_score,
+ analyze_score > 0 AND analyze_score <= 4 AS analyze_score
+ FROM pg_stat_autovacuum_priority
+ WHERE relname = 'vacuum_priority_test';
+ needs_vacuum | needs_analyze | score | vacuum_score | vacuum_insert_score | analyze_score
+--------------+---------------+-------+--------------+---------------------+---------------
+ t | t | t | t | t | t
+(1 row)
+
+DROP TABLE vacuum_priority_test;
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 247b8e23b23..556fe3127f4 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -525,3 +525,26 @@ SELECT id, pg_column_toast_chunk_id(f1) IS NULL AS f1_chunk_null,
SELECT pg_column_toast_chunk_id(f1) = :'id_2_chunk' AS same_chunk
FROM vac_rewrite_toast WHERE id = 2;
DROP TABLE vac_rewrite_toast;
+
+-- Test pg_stat_autovacuum_priority view. Scores are checked to be
+-- within an expected range. freeze_score and multixact_freeze_score are excluded
+-- as they require consuming enough XIDs to be meaningful.
+CREATE TABLE vacuum_priority_test (id int)
+ WITH (autovacuum_analyze_threshold = 1,
+ autovacuum_vacuum_threshold = 1,
+ autovacuum_vacuum_insert_threshold = 1,
+ autovacuum_enabled = off);
+INSERT INTO vacuum_priority_test SELECT 1;
+INSERT INTO vacuum_priority_test SELECT 2;
+DELETE FROM vacuum_priority_test WHERE id = 1;
+DELETE FROM vacuum_priority_test WHERE id = 2;
+-- force vacuum stats to be flushed
+SELECT pg_stat_force_next_flush();
+SELECT needs_vacuum, needs_analyze,
+ score > 0 AND score <= 4 AS score,
+ vacuum_score > 0 AND vacuum_score <= 2 AS vacuum_score,
+ vacuum_insert_score > 0 AND vacuum_insert_score <= 2 AS vacuum_insert_score,
+ analyze_score > 0 AND analyze_score <= 4 AS analyze_score
+ FROM pg_stat_autovacuum_priority
+ WHERE relname = 'vacuum_priority_test';
+DROP TABLE vacuum_priority_test;
--
2.47.3
[application/octet-stream] v4-0003-Refactor-autovacuum-score-computation-into-comput.patch (4.3K, 5-v4-0003-Refactor-autovacuum-score-computation-into-comput.patch)
download | inline diff:
From e36b8b1427757b1e09c32713b0b4912a0c806073 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Tue, 31 Mar 2026 18:43:25 +0000
Subject: [PATCH v4 3/4] Refactor autovacuum score computation into
compute_autovac_score
Autovacuum priority score will be computed for several
reasons: Autovacuum processing and a future monitoring
view to report the scores per table.
This consolidates this code into a new routine
compute_autovac_score() and also removes the need for
recheck_relation_needs_vacanalyze() which is doing the
same thing.
Discussion: https://postgr.es/m/CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw%40mail.gmail.com
---
src/backend/postmaster/autovacuum.c | 47 ++++++++++++++---------------
1 file changed, 23 insertions(+), 24 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 72ad30f6ae7..4b0d2526f5b 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -374,10 +374,11 @@ 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_autovac_score(HeapTuple tuple, TupleDesc pg_class_desc,
+ int effective_multixact_freeze_max_age,
+ AutoVacOpts *relopts, int elevel,
+ bool *dovacuum, bool *doanalyze,
+ bool *wraparound, AutoVacuumPriority *priority);
static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
Form_pg_class classForm,
PgStat_StatTabEntry *tabentry,
@@ -2836,6 +2837,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
bool wraparound;
AutoVacOpts *avopts;
bool free_avopts = false;
+ AutoVacuumPriority priority;
/* fetch the relation's relcache entry */
classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
@@ -2861,9 +2863,10 @@ 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_autovac_score(classTup, pg_class_desc,
+ effective_multixact_freeze_max_age,
+ avopts, DEBUG3,
+ &dovacuum, &doanalyze, &wraparound, &priority);
/* OK, it needs something done */
if (doanalyze || dovacuum)
@@ -2973,33 +2976,29 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
}
/*
- * recheck_relation_needs_vacanalyze
- *
- * Subroutine for table_recheck_autovac.
- *
- * Fetch the pgstat of a relation and recheck whether a relation
- * needs to be vacuumed or analyzed.
+ * compute_autovac_score
+ * Fetch the pgstat entry for a relation and call
+ * relation_needs_vacanalyze() to determine whether it needs
+ * vacuum or analyze and compute its priority scores.
*/
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_autovac_score(HeapTuple tuple, TupleDesc pg_class_desc,
+ int effective_multixact_freeze_max_age,
+ AutoVacOpts *relopts, int elevel,
+ bool *dovacuum, bool *doanalyze,
+ bool *wraparound, AutoVacuumPriority *priority)
{
+ Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
PgStat_StatTabEntry *tabentry;
- AutoVacuumPriority priority;
/* fetch the pgstat table entry */
tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
- relid);
+ classForm->oid);
- relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
+ relation_needs_vacanalyze(classForm->oid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
dovacuum, doanalyze, wraparound,
- &priority, DEBUG3);
+ priority, elevel);
/* Release tabentry to avoid leakage */
if (tabentry)
--
2.47.3
view thread (60+ messages) latest in thread
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Add pg_stat_autovacuum_priority
In-Reply-To: <CAA5RZ0uVZLsX_k33QoWt8iRes73jyqs+EjypL9+1wW1v8NsYbg@mail.gmail.com>
* 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