public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sami Imseih <[email protected]>
To: Robert Treat <[email protected]>
Cc: Bharath Rupireddy <[email protected]>
Cc: [email protected]
Cc: pgsql-hackers <[email protected]>
Subject: Re: Add pg_stat_autovacuum_priority
Date: Mon, 30 Mar 2026 13:16:07 -0500
Message-ID: <CAA5RZ0uiBCiQDUfA5sbc50gybWx1YES3HfLKMd9gv661YpJyRg@mail.gmail.com> (raw)
In-Reply-To: <CABV9wwNBifXpOjxO9rGn1HHK=DG02qApVurWSHa+rDzPriK6pA@mail.gmail.com>
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>
> I think we are also in agreement here, although based on my
> experience, filtering out things like system and toast tables will be
> common, but I don't see that changing what you said above. On a
> similar note, +1 to your changes in v2.
Thanks!
v3 now includes the refactoring [1] suggestion brought up by Alvarro
[1] [https://www.postgresql.org/message-id/202603301508.up22nvhgnnoj%40alvherre.pgsql]
--
Sami
Attachments:
[application/octet-stream] v3-0002-Refactor-autovacuum-score-computation-into-comput.patch (4.4K, 2-v3-0002-Refactor-autovacuum-score-computation-into-comput.patch)
download | inline diff:
From 2940d2a1b497ea6791918a6646690779c9b01065 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Mon, 30 Mar 2026 17:23:09 +0000
Subject: [PATCH v3 2/3] Refactor autovacuum score computation into
compute_autovac_score
The pattern of fetching a relation's pgstat entry and
calling relation_needs_vacanalyze() is needed in multiple
places: table_recheck_autovac and a future view that will
emit a relation's autovacuum priority score.
Introduce compute_autovac_score() to consolidate this.
It accepts relopts and force_scores parameters, and
replaces recheck_relation_needs_vacanalyze() which did
the same thing with a narrower interface.
Discussion: https://postgr.es/m/CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw%40mail.gmail.com
---
src/backend/postmaster/autovacuum.c | 51 ++++++++++++++---------------
1 file changed, 24 insertions(+), 27 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index f6b213852e3..fbe670d375f 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -370,10 +370,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, bool force_scores,
+ bool *dovacuum, bool *doanalyze,
+ bool *wraparound, AutoVacuumScores *scores);
static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
Form_pg_class classForm,
PgStat_StatTabEntry *tabentry,
@@ -2834,6 +2835,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
bool wraparound;
AutoVacOpts *avopts;
bool free_avopts = false;
+ AutoVacuumScores scores;
/* fetch the relation's relcache entry */
classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
@@ -2859,9 +2861,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, false,
+ &dovacuum, &doanalyze, &wraparound, &scores);
/* OK, it needs something done */
if (doanalyze || dovacuum)
@@ -2971,34 +2974,28 @@ 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, bool force_scores,
+ 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,
- effective_multixact_freeze_max_age,
- false,
- dovacuum, doanalyze, wraparound,
- &scores);
+ relation_needs_vacanalyze(classForm->oid, relopts, classForm, tabentry,
+ effective_multixact_freeze_max_age, force_scores,
+ dovacuum, doanalyze, wraparound, scores);
/* Release tabentry to avoid leakage */
if (tabentry)
--
2.47.3
[application/octet-stream] v3-0001-Add-force_scores-option-to-relation_needs_vacanal.patch (7.1K, 3-v3-0001-Add-force_scores-option-to-relation_needs_vacanal.patch)
download | inline diff:
From 656fe90948206b7fd2b64a32006f701b85a84cae Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Fri, 27 Mar 2026 20:38:26 +0000
Subject: [PATCH v3 1/3] Add force_scores option to relation_needs_vacanalyze
A future commit will need the ability for relation_needs_vacanalyze()
to forcefully calculate an autovacuum priority score even if autovacuum
would otherwise abandon the computation of the score. This is needed for
tools that wish to introspect the score outside of the normal path of
do_autovacuum().
Discussion: https://postgr.es/m/CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw%40mail.gmail.com
---
src/backend/postmaster/autovacuum.c | 81 +++++++++++++++++------------
1 file changed, 48 insertions(+), 33 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index d695f1de4bd..f6b213852e3 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -378,6 +378,7 @@ static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
Form_pg_class classForm,
PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
+ bool force_scores,
bool *dovacuum, bool *doanalyze, bool *wraparound,
AutoVacuumScores *scores);
@@ -2075,6 +2076,7 @@ do_autovacuum(void)
/* Check if it needs vacuum or analyze */
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
+ false,
&dovacuum, &doanalyze, &wraparound,
&scores);
@@ -2175,6 +2177,7 @@ do_autovacuum(void)
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
+ false,
&dovacuum, &doanalyze, &wraparound,
&scores);
@@ -2993,6 +2996,7 @@ recheck_relation_needs_vacanalyze(Oid relid,
relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
effective_multixact_freeze_max_age,
+ false,
dovacuum, doanalyze, wraparound,
&scores);
@@ -3080,6 +3084,9 @@ 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.
+ *
+ * force_scores set to true forces the computation of a score. This is useful for
+ * tools that wish to inspect scores outside of the do_vacuum() path.
*/
static void
relation_needs_vacanalyze(Oid relid,
@@ -3087,6 +3094,7 @@ relation_needs_vacanalyze(Oid relid,
Form_pg_class classForm,
PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
+ bool force_scores,
/* output params below */
bool *dovacuum,
bool *doanalyze,
@@ -3252,18 +3260,21 @@ relation_needs_vacanalyze(Oid relid,
*dovacuum = true;
}
- /* User disabled it in pg_class.reloptions? (But ignore if at risk) */
- if (!av_enabled && !force_vacuum)
+ /*
+ * User disabled it in pg_class.reloptions? (But ignore if at risk or
+ * forced)
+ */
+ if (!force_scores && !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, and autovacuum is currently enabled
+ * (or scores are forced), 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 (tabentry && AutoVacuumingActive())
+ if (tabentry && (AutoVacuumingActive() || force_scores))
{
float4 pcnt_unfrozen = 1;
float4 reltuples = classForm->reltuples;
@@ -3304,32 +3315,33 @@ 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.
+ * Update the vacuum score and determine if this table needs vacuum
*/
+ scores->vac = (double) vactuples / Max(vacthresh, 1);
+ scores->vac *= autovacuum_vacuum_score_weight;
+ scores->max = Max(scores->max, scores->vac);
if (vactuples > vacthresh)
- {
- scores->vac = (double) vactuples / Max(vacthresh, 1);
- scores->vac *= autovacuum_vacuum_score_weight;
- scores->max = Max(scores->max, scores->vac);
*dovacuum = true;
- }
- if (vac_ins_base_thresh >= 0 && instuples > vacinsthresh)
+ /*
+ * Ditto for insert vacuum
+ */
+ if (vac_ins_base_thresh >= 0)
{
scores->vac_ins = (double) instuples / Max(vacinsthresh, 1);
scores->vac_ins *= autovacuum_vacuum_insert_score_weight;
scores->max = Max(scores->max, scores->vac_ins);
- *dovacuum = true;
}
+ if (vac_ins_base_thresh >= 0 && instuples > vacinsthresh)
+ *dovacuum = true;
/*
* Determine if this table needs analyze, and update the score if it
* does. Note that we don't analyze TOAST tables and pg_statistic.
*/
- if (anltuples > anlthresh &&
- relid != StatisticRelationId &&
- classForm->relkind != RELKIND_TOASTVALUE)
+ if ((anltuples > anlthresh &&
+ relid != StatisticRelationId &&
+ classForm->relkind != RELKIND_TOASTVALUE))
{
scores->anl = (double) anltuples / Max(anlthresh, 1);
scores->anl *= autovacuum_analyze_score_weight;
@@ -3337,19 +3349,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 (!force_scores)
+ {
+ if (vac_ins_base_thresh >= 0)
+ elog(DEBUG3, "%s: vac: %.0f (thresh %.0f, score %.2f), ins: %.0f (thresh %.0f, score %.2f), anl: %.0f (thresh %.0f, score %.2f), xid score: %.2f, mxid score: %.2f",
+ NameStr(classForm->relname),
+ vactuples, vacthresh, scores->vac,
+ instuples, vacinsthresh, scores->vac_ins,
+ anltuples, anlthresh, scores->anl,
+ scores->xid, scores->mxid);
+ else
+ elog(DEBUG3, "%s: vac: %.0f (thresh %.0f, score %.2f), ins: (disabled), anl: %.0f (thresh %.0f, score %.2f), xid score: %.2f, mxid score: %.2f",
+ NameStr(classForm->relname),
+ vactuples, vacthresh, scores->vac,
+ anltuples, anlthresh, scores->anl,
+ scores->xid, scores->mxid);
+ }
}
}
--
2.47.3
[application/octet-stream] v3-0003-Add-pg_stat_autovacuum_priority-view.patch (18.6K, 4-v3-0003-Add-pg_stat_autovacuum_priority-view.patch)
download | inline diff:
From 791b8580a78afca44c97fa9722503bbbfde53728 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Mon, 23 Mar 2026 17:03:59 +0000
Subject: [PATCH v3 3/3] 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 relation_needs_vacanalyze(). 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 | 84 ++++++++++++++
src/include/catalog/pg_proc.dat | 10 ++
src/test/regress/expected/rules.out | 15 +++
src/test/regress/expected/vacuum.out | 34 ++++++
src/test/regress/sql/vacuum.sql | 25 ++++
8 files changed, 361 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..8ace8d4a9b0 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>xid_score</structfield>,
+ and <structfield>mxid_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 autovacuum considers this table in need of vacuuming
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>needs_analyze</structfield> <type>boolean</type>
+ </para>
+ <para>
+ True if autovacuum considers this table in need of analyzing
+ </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>xid_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>mxid_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_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>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vacuum_ins_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..55e08e20d80 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.xid_score,
+ S.mxid_score,
+ S.vacuum_dead_score,
+ S.vacuum_ins_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 fbe670d375f..de3005c96e1 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"
@@ -3676,3 +3678,85 @@ 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 all eligible relations
+ * in the current database.
+ *
+ * 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,
+ true, &dovacuum, &doanalyze,
+ &wraparound, &scores);
+
+ if (avopts)
+ pfree(avopts);
+
+ values[0] = ObjectIdGetDatum(classForm->oid);
+ values[1] = BoolGetDatum(dovacuum);
+ values[2] = BoolGetDatum(doanalyze);
+ values[3] = BoolGetDatum(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..6268f8c6018 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,xid_score,mxid_score,vacuum_dead_score,vacuum_ins_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..d2d3c3dd048 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.xid_score,
+ s.mxid_score,
+ s.vacuum_dead_score,
+ s.vacuum_ins_score,
+ s.analyze_score
+ FROM ((pg_stat_get_autovacuum_priority() s(relid, needs_vacuum, needs_analyze, wraparound, score, xid_score, mxid_score, vacuum_dead_score, vacuum_ins_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..bbee7457796 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -730,3 +730,37 @@ 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. Only xid_score and mxid_score
+-- are not tested, as they need to consume enough XID's. The test is
+-- run with autovacuum disabled for test stability as well as ensuring
+-- that priority fields are still populated in this case. The test ensures
+-- that the computed score is within an expected range.
+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_dead_score > 0 AND vacuum_dead_score <= 2 AS vacuum_dead_score,
+ vacuum_ins_score > 0 AND vacuum_ins_score <= 2 AS vacuum_ins_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_dead_score | vacuum_ins_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..a3bb1cc1dc2 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -525,3 +525,28 @@ 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. Only xid_score and mxid_score
+-- are not tested, as they need to consume enough XID's. The test is
+-- run with autovacuum disabled for test stability as well as ensuring
+-- that priority fields are still populated in this case. The test ensures
+-- that the computed score is within an expected range.
+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_dead_score > 0 AND vacuum_dead_score <= 2 AS vacuum_dead_score,
+ vacuum_ins_score > 0 AND vacuum_ins_score <= 2 AS vacuum_ins_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]
Subject: Re: Add pg_stat_autovacuum_priority
In-Reply-To: <CAA5RZ0uiBCiQDUfA5sbc50gybWx1YES3HfLKMd9gv661YpJyRg@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