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: Wed, 1 Apr 2026 11:58:44 -0500
Message-ID: <CAA5RZ0vFtDKAOZALeLp3vLyhWcLntaqS-Oh36Xxgx5_8sYeSKQ@mail.gmail.com> (raw)
In-Reply-To: <ac00otrpVnWzZ_i4@nathan>
References: <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>
<CAA5RZ0uVZLsX_k33QoWt8iRes73jyqs+EjypL9+1wW1v8NsYbg@mail.gmail.com>
<ac00otrpVnWzZ_i4@nathan>
> 0001:
>
> - AutoVacuumScores *scores);
> + AutoVacuumPriority *priority);
>
> IMHO we need to minimize these kinds of extraneous changes in this patch
> set. AutoVacuumScores still seems accurate enough, even when the struct
> contains some extra bool members.
>
> - * 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.
>
> I don't think we need to update this comment.
>
> - * 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.
>
> Or this one.
Fixed both.
> + * Priority scores are always computed. dovacuum and doanalyze are only set when
> + * autovacuum is active and enabled for the relation.
>
> I think we should more explicitly state that while scores->needs_vacuum and
> friends are always set regardless of whether autovacuum is enabled, the
> return parameters dovacuum, etc., are not.
I made this more explicit by saying " All fields in AutoVacuumScores
are always computed
regardless of autovacuum settings...." I think that is clear enough.
> Or perhaps we should return
> whether autovacuum is enabled in the struct and consolidate the return
> parameters and the struct members. WDYT?
dovacuum, doanalyze will be unused by callers like the sql function, and putting
them in the struct could make this cleaner, but I don't think it's
worth it to blur
the purpose of the struct. I rather keep it just for score computation purposes.
> 0002:
>
> Seems fine.
I found a bug in my v4 that I fixed.
+ if (elevel > 0 && vac_ins_base_thresh >= 0)
is wrong. It should be:
if (elevel > 0)
{
if (vac_ins_base_thresh >= 0)
> 0004:
>
> + 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;
>
> What do you think about ordering by score so this view automatically shows
> the tables most in need of vacuuming/analyzing first?
I thought about that initially, but we don't really have an example where the
data is ordered in a stats view ( or any other catalog view ), and I prefer not
to impose that on the user automatically.
See v5.
--
Sami
Attachments:
[application/octet-stream] v5-0001-Always-compute-autovacuum-priority-scores.patch (10.0K, 2-v5-0001-Always-compute-autovacuum-priority-scores.patch)
download | inline diff:
From c4f01cac83e2e265ea018c625bd4cb1b69634f79 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Tue, 31 Mar 2026 18:25:30 +0000
Subject: [PATCH v5 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 AutoVacuumScores so they can be
used for this purpose rather than the output
dovacuum, doanalyze and wraparound parameters that
are acted upon by autovacuum.
Discussion: https://postgr.es/m/CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw%40mail.gmail.com
---
src/backend/postmaster/autovacuum.c | 139 +++++++++++++++-------------
1 file changed, 74 insertions(+), 65 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 6694f485216..013b49eb69c 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,6 +330,9 @@ typedef struct
double vac; /* vacuum component */
double vac_ins; /* vacuum insert component */
double anl; /* analyze component */
+ bool needs_vacuum; /* threshold exceeded for vacuum */
+ bool needs_analyze; /* threshold exceeded for analyze */
+ bool is_wraparound; /* at risk of XID/MXID wraparound */
} AutoVacuumScores;
/*
@@ -3080,6 +3084,10 @@ recheck_relation_needs_vacanalyze(Oid relid,
* 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.
+ *
+ * All fields in AutoVacuumScores are always computed regardless of autovacuum
+ * settings. The dovacuum and doanalyze output parameters are only set when
+ * autovacuum is globally active and enabled for the relation.
*/
static void
relation_needs_vacanalyze(Oid relid,
@@ -3122,6 +3130,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;
Assert(classForm != NULL);
Assert(OidIsValid(relid));
@@ -3196,74 +3208,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;
+ scores->xid = (double) xid_age / freeze_max_age;
+ scores->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)
+ 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 (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->xid *= autovacuum_freeze_score_weight;
+ scores->mxid *= autovacuum_multixact_freeze_score_weight;
- scores->xid *= autovacuum_freeze_score_weight;
- scores->mxid *= autovacuum_multixact_freeze_score_weight;
+ scores->max = Max(scores->xid, scores->mxid);
- scores->max = Max(scores->xid, scores->mxid);
+ if (force_vacuum)
+ {
*dovacuum = true;
+ scores->is_wraparound = scores->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 scores.
+ * 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,15 +3306,17 @@ 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;
+ scores->needs_vacuum = true;
+ if (av_enabled && AutoVacuumingActive())
+ *dovacuum = true;
}
if (vac_ins_base_thresh >= 0 && instuples > vacinsthresh)
@@ -3320,12 +3324,15 @@ 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;
+ scores->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 &&
@@ -3334,7 +3341,9 @@ 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;
+ scores->needs_analyze = true;
+ if (av_enabled && AutoVacuumingActive())
+ *doanalyze = true;
}
if (vac_ins_base_thresh >= 0)
--
2.47.3
[application/octet-stream] v5-0002-Add-elevel-parameter-to-relation_needs_vacanalyze.patch (4.5K, 3-v5-0002-Add-elevel-parameter-to-relation_needs_vacanalyze.patch)
download | inline diff:
From d6f25c71e89fb6499f1cbd4d4fdb25c51a415004 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Tue, 31 Mar 2026 18:33:18 +0000
Subject: [PATCH v5 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 | 43 +++++++++++++++++------------
1 file changed, 25 insertions(+), 18 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 013b49eb69c..ff6ffd4beb1 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,
- AutoVacuumScores *scores);
+ AutoVacuumScores *scores,
+ 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,
- &scores);
+ &scores, 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,
- &scores);
+ &scores, 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,
- &scores);
+ &scores, DEBUG3);
/* Release tabentry to avoid leakage */
if (tabentry)
@@ -3088,6 +3089,8 @@ recheck_relation_needs_vacanalyze(Oid relid,
* All fields in AutoVacuumScores are always computed regardless of autovacuum
* settings. The dovacuum and doanalyze output parameters are only set when
* autovacuum is globally 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,
@@ -3099,7 +3102,8 @@ relation_needs_vacanalyze(Oid relid,
bool *dovacuum,
bool *doanalyze,
bool *wraparound,
- AutoVacuumScores *scores)
+ AutoVacuumScores *scores,
+ int elevel)
{
bool force_vacuum;
bool av_enabled;
@@ -3346,19 +3350,22 @@ 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",
- 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);
+ if (elevel > 0)
+ {
+ if (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, scores->vac,
+ instuples, vacinsthresh, scores->vac_ins,
+ anltuples, anlthresh, scores->anl,
+ scores->xid, scores->mxid);
+ else
+ 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,
+ scores->xid, scores->mxid);
+ }
}
}
--
2.47.3
[application/octet-stream] v5-0003-Refactor-autovacuum-score-computation-into-comput.patch (4.2K, 4-v5-0003-Refactor-autovacuum-score-computation-into-comput.patch)
download | inline diff:
From 9b60707b4125630b1869ed5316c6d607db4f8509 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Tue, 31 Mar 2026 18:43:25 +0000
Subject: [PATCH v5 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 ff6ffd4beb1..a20eb39bf50 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, AutoVacuumScores *scores);
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;
+ AutoVacuumScores scores;
/* 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, AutoVacuumScores *scores)
{
+ Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
PgStat_StatTabEntry *tabentry;
- AutoVacuumScores scores;
/* 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,
- &scores, DEBUG3);
+ scores, elevel);
/* Release tabentry to avoid leakage */
if (tabentry)
--
2.47.3
[application/octet-stream] v5-0004-Add-pg_stat_autovacuum_priority-view.patch (18.8K, 5-v5-0004-Add-pg_stat_autovacuum_priority-view.patch)
download | inline diff:
From 98654f40b343a9219ea8f62fb9a490de7e738d82 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Mon, 23 Mar 2026 17:03:59 +0000
Subject: [PATCH v5 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 | 88 +++++++++++++-
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, 360 insertions(+), 1 deletion(-)
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 a20eb39bf50..959905b3b38 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"
@@ -2866,7 +2868,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
compute_autovac_score(classTup, pg_class_desc,
effective_multixact_freeze_max_age,
avopts, DEBUG3,
- &dovacuum, &doanalyze, &wraparound, &priority);
+ &dovacuum, &doanalyze, &wraparound, &scores);
/* OK, it needs something done */
if (doanalyze || dovacuum)
@@ -3679,3 +3681,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;
+ AutoVacuumScores scores;
+ 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, &scores);
+
+ if (avopts)
+ pfree(avopts);
+
+ values[0] = ObjectIdGetDatum(classForm->oid);
+ values[1] = BoolGetDatum(scores.needs_vacuum);
+ values[2] = BoolGetDatum(scores.needs_analyze);
+ values[3] = BoolGetDatum(scores.is_wraparound);
+ values[4] = Float8GetDatum(scores.max);
+ values[5] = Float8GetDatum(scores.xid);
+ values[6] = Float8GetDatum(scores.mxid);
+ values[7] = Float8GetDatum(scores.vac);
+ values[8] = Float8GetDatum(scores.vac_ins);
+ values[9] = Float8GetDatum(scores.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
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: <CAA5RZ0vFtDKAOZALeLp3vLyhWcLntaqS-Oh36Xxgx5_8sYeSKQ@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