v7-0004-Add-pg_stat_autovacuum_priority-view.patch

From 45ceac183547ed8e4f090f2efd97326015483f31 Mon Sep 17 00:00:00 2001
From: Sami Imseih <samimseih@gmail.com>
Date: Mon, 23 Mar 2026 17:03:59 +0000
Subject: [PATCH v7 4/4] Add pg_stat_autovacuum_priority view

d7965d65f introduced autovacuum prioritization with
scoring. This change adds a view to expose those scores.

The view shows a row per relation indicating whether it
needs vacuum or analyze, the score of each component
and the Max score across all components. This provides
a way to introspect autovacuum priority and provide
feedback on tuning of the related GUCs.

The underlying function pg_stat_get_autovacuum_priority()
scans all relations in the current database and computes
scores using compute_autovac_score(). By default,
only superusers and roles with privileges of
pg_read_all_stats can execute the function, as
controlled by the function's ACL in pg_proc.

The view also emits the relid, namespace and relname,
so it can be joined with other views like
pg_stat_all_tables and pg_stat_progress_vacuum for
complementary vacuum details.

Tests added to vacuum.sql

Discussion: https://postgr.es/m/CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw%40mail.gmail.com
---
 doc/src/sgml/maintenance.sgml        |   6 +
 doc/src/sgml/monitoring.sgml         | 166 +++++++++++++++++++++++++++
 src/backend/catalog/system_views.sql |  21 ++++
 src/backend/postmaster/autovacuum.c  |  86 ++++++++++++++
 src/include/catalog/pg_proc.dat      |  10 ++
 src/test/regress/expected/rules.out  |  15 +++
 src/test/regress/expected/vacuum.out |  32 ++++++
 src/test/regress/sql/vacuum.sql      |  23 ++++
 8 files changed, 359 insertions(+)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 0d2a28207ed..2125774aff3 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -1164,6 +1164,12 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
      <literal>2.0</literal> effectively doubles the
      <emphasis>analyze</emphasis> component score.
     </para>
+
+    <para>
+     The <link linkend="monitoring-pg-stat-autovacuum-priority-view">
+     <structname>pg_stat_autovacuum_priority</structname></link> view can be
+     used to inspect each table's autovacuum need and priority score.
+    </para>
    </sect3>
   </sect2>
  </sect1>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index bb75ed1069b..791850bafe7 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -463,6 +463,15 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
       </entry>
      </row>
 
+     <row>
+      <entry><structname>pg_stat_autovacuum_priority</structname><indexterm><primary>pg_stat_autovacuum_priority</primary></indexterm></entry>
+      <entry>One row per relation in the current database, showing
+       a table's autovacuum need and priority. See
+       <link linkend="monitoring-pg-stat-autovacuum-priority-view">
+       <structname>pg_stat_autovacuum_priority</structname></link> for details.
+      </entry>
+     </row>
+
      <row>
       <entry><structname>pg_stat_bgwriter</structname><indexterm><primary>pg_stat_bgwriter</primary></indexterm></entry>
       <entry>One row only, showing statistics about the
@@ -5256,6 +5265,163 @@ description | Waiting for a newly initialized WAL file to reach durable storage
 
  </sect2>
 
+ <sect2 id="monitoring-pg-stat-autovacuum-priority-view">
+  <title><structname>pg_stat_autovacuum_priority</structname></title>
+
+  <indexterm>
+   <primary>pg_stat_autovacuum_priority</primary>
+  </indexterm>
+
+  <para>
+   The <structname>pg_stat_autovacuum_priority</structname> view contains
+   one row per relation in the current database, showing whether a table
+   needs autovacuum or autoanalyze and its priority. The
+   <structfield>score</structfield>, <structfield>freeze_score</structfield>,
+   and <structfield>multixact_freeze_score</structfield> values may be very large for
+   tables approaching wraparound, as these scores are scaled aggressively
+   once they surpass the failsafe age thresholds.
+  </para>
+
+  <table id="pg-stat-autovacuum-priority-view" xreflabel="pg_stat_autovacuum_priority">
+   <title><structname>pg_stat_autovacuum_priority</structname> View</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>relid</structfield> <type>oid</type>
+      </para>
+      <para>
+       OID of a table
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>schemaname</structfield> <type>name</type>
+      </para>
+      <para>
+       Name of the schema that this table is in
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>relname</structfield> <type>name</type>
+      </para>
+      <para>
+       Name of this table
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>needs_vacuum</structfield> <type>boolean</type>
+      </para>
+      <para>
+       True if the table exceeds the vacuum threshold
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>needs_analyze</structfield> <type>boolean</type>
+      </para>
+      <para>
+       True if the table exceeds the analyze threshold
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>wraparound</structfield> <type>boolean</type>
+      </para>
+      <para>
+       True if vacuuming is needed to prevent transaction ID or
+       multixact ID wraparound
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>score</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Priority score used by autovacuum to order which tables to
+       process first. Higher values indicate greater urgency. This is
+       the maximum of all component scores below.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>freeze_score</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Score component based on transaction ID age.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>multixact_freeze_score</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Score component based on multixact ID age.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>vacuum_score</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Score component based on the estimated number of dead tuples
+       needing removal by vacuum.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>vacuum_insert_score</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Score component based on the number of inserts since the last
+       vacuum.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>analyze_score</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Score component based on the number of modifications since the
+       last analyze.
+      </para></entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+
+  <para>
+   By default, the <structname>pg_stat_autovacuum_priority</structname> view can
+   be read only by superusers or roles with privileges of the
+   <literal>pg_read_all_stats</literal> role.
+  </para>
+
+ </sect2>
+
  <sect2 id="monitoring-stats-functions">
   <title>Statistics Functions</title>
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index e54018004db..30bbd55e330 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -795,6 +795,27 @@ CREATE VIEW pg_stat_xact_user_tables AS
     WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
           schemaname !~ '^pg_toast';
 
+CREATE VIEW pg_stat_autovacuum_priority AS
+    SELECT
+            S.relid,
+            N.nspname AS schemaname,
+            C.relname AS relname,
+            S.needs_vacuum,
+            S.needs_analyze,
+            S.wraparound,
+            S.score,
+            S.freeze_score,
+            S.multixact_freeze_score,
+            S.vacuum_score,
+            S.vacuum_insert_score,
+            S.analyze_score
+    FROM pg_stat_get_autovacuum_priority() S
+         JOIN pg_class C ON C.oid = S.relid
+         LEFT JOIN pg_namespace N ON N.oid = C.relnamespace;
+
+REVOKE ALL ON pg_stat_autovacuum_priority FROM PUBLIC;
+GRANT SELECT ON pg_stat_autovacuum_priority TO pg_read_all_stats;
+
 CREATE VIEW pg_statio_all_tables AS
     SELECT
             C.oid AS relid,
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 090b87fa41c..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"
 
 
@@ -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