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 13:45:59 -0500
Message-ID: <CAA5RZ0tOJjUuJidUQYAeoUD4VGjOSjuB7ox4dtLAsyoCZqSN+Q@mail.gmail.com> (raw)
In-Reply-To: <CAA5RZ0vFtDKAOZALeLp3vLyhWcLntaqS-Oh36Xxgx5_8sYeSKQ@mail.gmail.com>
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>
<CAA5RZ0vFtDKAOZALeLp3vLyhWcLntaqS-Oh36Xxgx5_8sYeSKQ@mail.gmail.com>
v6 attached addresses a silly bug with v5-0001 that Nathan pointed out
offline. v5 was not computing the scores for vacuum,vacuum insert
and analyze thresholds for the !wraparound case.
--
Sami
Attachments:
[application/octet-stream] v6-0003-Refactor-autovacuum-score-computation-into-comput.patch (4.2K, 2-v6-0003-Refactor-autovacuum-score-computation-into-comput.patch)
download | inline diff:
From f337ea61a6ce51af4fd999a2f4e823007d0f2ee3 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Tue, 31 Mar 2026 18:43:25 +0000
Subject: [PATCH v6 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 f6e8722a17a..6b2a7e41e53 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] v6-0002-Add-elevel-parameter-to-relation_needs_vacanalyze.patch (4.5K, 3-v6-0002-Add-elevel-parameter-to-relation_needs_vacanalyze.patch)
download | inline diff:
From eed371d2a5d5c7991045a210f5a7314526416f8f Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Tue, 31 Mar 2026 18:33:18 +0000
Subject: [PATCH v6 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 f409a5d9028..f6e8722a17a 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;
@@ -3352,19 +3356,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] v6-0004-Add-pg_stat_autovacuum_priority-view.patch (18.8K, 4-v6-0004-Add-pg_stat_autovacuum_priority-view.patch)
download | inline diff:
From 329406e3dbe91811cc41ea1988fd49f96814e274 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Mon, 23 Mar 2026 17:03:59 +0000
Subject: [PATCH v6 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 6b2a7e41e53..a02057cf1c6 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)
@@ -3685,3 +3687,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
[application/octet-stream] v6-0001-Always-compute-autovacuum-priority-scores.patch (10.3K, 5-v6-0001-Always-compute-autovacuum-priority-scores.patch)
download | inline diff:
From 616e02c0e9a2cc5c098387d120751ac8adfa20b1 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Tue, 31 Mar 2026 18:25:30 +0000
Subject: [PATCH v6 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 | 159 +++++++++++++++-------------
1 file changed, 87 insertions(+), 72 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 6694f485216..f409a5d9028 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,37 +3306,50 @@ 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 requires
+ * vacuuming.
*/
+ 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;
+ scores->needs_vacuum = true;
+ if (av_enabled && AutoVacuumingActive())
+ *dovacuum = true;
}
- if (vac_ins_base_thresh >= 0 && instuples > vacinsthresh)
+ /*
+ * Ditto for vacuum insert score when it is applicable.
+ */
+ 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 (instuples > vacinsthresh)
+ {
+ 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.
+ * Update the analyze scores and determine if this table requires
+ * analyze. Note that we don't analyze TOAST tables and pg_statistic.
*/
+ scores->anl = (double) anltuples / Max(anlthresh, 1);
+ scores->anl *= autovacuum_analyze_score_weight;
+ scores->max = Max(scores->max, scores->anl);
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;
+ scores->needs_analyze = true;
+ if (av_enabled && AutoVacuumingActive())
+ *doanalyze = true;
}
if (vac_ins_base_thresh >= 0)
--
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: <CAA5RZ0tOJjUuJidUQYAeoUD4VGjOSjuB7ox4dtLAsyoCZqSN+Q@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