public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nathan Bossart <[email protected]>
Subject: [PATCH v10 2/2] add pg_stat_autovacuum_scores system view
Date: Thu, 2 Apr 2026 15:47:19 -0500
---
doc/src/sgml/maintenance.sgml | 6 ++
doc/src/sgml/monitoring.sgml | 143 +++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 15 +++
src/backend/postmaster/autovacuum.c | 70 +++++++++++++
src/include/catalog/pg_proc.dat | 7 ++
src/test/regress/expected/rules.out | 12 +++
6 files changed, 253 insertions(+)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 0d2a28207ed..906aca2c228 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-scores-view">
+ <structname>pg_stat_autovacuum_scores</structname></link>
+ view shows the current scores of all tables in the current database.
+ </para>
</sect3>
</sect2>
</sect1>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 312374da5e0..b8b2967aa36 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -596,6 +596,16 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
user tables are shown.</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_autovacuum_scores</structname><indexterm><primary>pg_stat_autovacuum_scores</primary></indexterm></entry>
+ <entry>
+ One row for each table in the current database, showing the current
+ autovacuum scores for that specific table. See
+ <link linkend="monitoring-pg-stat-autovacuum-scores-view">
+ <structname>pg_stat_autovacuum_scores</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_all_indexes</structname><indexterm><primary>pg_stat_all_indexes</primary></indexterm></entry>
<entry>
@@ -4502,6 +4512,139 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</sect2>
+ <sect2 id="monitoring-pg-stat-autovacuum-scores-view">
+ <title><structname>pg_stat_autovacuum_scores</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_autovacuum_scores</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_autovacuum_scores</structname> view will contain one
+ row for each table in the current database (including TOAST tables), showing
+ the current autovacuum scores for that specific table. Tables deemed
+ eligible for processing are prioritized based on their
+ <structfield>score</structfield>, with higher scores indicating higher
+ priority. See <xref linkend="autovacuum-priority"/> for more information.
+ </para>
+
+ <table id="pg-stat-autovacuum-scores-view" xreflabel="pg_stat_autovacuum_scores">
+ <title><structname>pg_stat_autovacuum_scores</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 the 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 the 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 the table.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>score</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Maximum value of all component scores. This is the value that
+ autovacuum uses to sort the list of tables to process. When the
+ "weight" parameters are set to their default values of
+ <literal>1.0</literal>, scores greater than or equal to
+ <literal>1.0</literal> indicate the table will be processed (unless
+ autovacuum is disabled and neither <literal>xid_score</literal> nor
+ <literal>mxid_score</literal> are greater than or equal to
+ <literal>1.0</literal>).
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>xid_score</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Transaction ID age component score. Scores greater than or equal to
+ <xref linkend="guc-autovacuum-freeze-score-weight"/> indicate the table
+ will be vacuumed.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>mxid_score</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Multixact ID age component score. Scores greater than or equal to
+ <xref linkend="guc-autovacuum-multixact-freeze-score-weight"/> indicate
+ the table will be vacuumed.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vacuum_score</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Vacuum component score. Scores greater than or equal to
+ <xref linkend="guc-autovacuum-vacuum-score-weight"/> indicate the table
+ will be vacuumed (unless autovacuum is disabled).
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vacuum_insert_score</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Vacuum insert component score. Scores greater than or equal to
+ <xref linkend="guc-autovacuum-vacuum-insert-score-weight"/> indicate the
+ table will be vacuumed (unless autovacuum is disabled).
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>analyze_score</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Analyze component score. Scores greater than or equal to
+ <xref linkend="guc-autovacuum-analyze-score-weight"/> indicate the table
+ will be analyzed (unless autovacuum is disabled).
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect2>
+
<sect2 id="monitoring-pg-stat-all-indexes-view">
<title><structname>pg_stat_all_indexes</structname></title>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index eba25aa3e4d..5c2f2977965 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -795,6 +795,21 @@ 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_scores AS
+ SELECT
+ s.oid AS relid,
+ n.nspname AS schemaname,
+ c.relname AS relname,
+ s.score,
+ s.xid_score,
+ s.mxid_score,
+ s.vacuum_score,
+ s.vacuum_insert_score,
+ s.analyze_score
+ FROM pg_stat_get_autovacuum_scores() s
+ JOIN pg_class c on c.oid = s.oid
+ LEFT JOIN pg_namespace n ON n.oid = c.relnamespace;
+
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 1be1ba8a25f..c6c601dd3ad 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"
@@ -3623,3 +3625,71 @@ 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_scores
+ *
+ * Returns current autovacuum scores for all relevant tables in the current
+ * database.
+ */
+Datum
+pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
+{
+ int effective_multixact_freeze_max_age;
+ Relation rel;
+ TableScanDesc scan;
+ HeapTuple tup;
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ /* some prerequisite initialization */
+ effective_multixact_freeze_max_age = MultiXactMemberFreezeThreshold();
+ recentXid = ReadNextTransactionId();
+ recentMulti = ReadNextMultiXactId();
+
+ /* scan pg_class */
+ rel = table_open(RelationRelationId, AccessShareLock);
+ scan = table_beginscan_catalog(rel, 0, NULL);
+ while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ Form_pg_class form = (Form_pg_class) GETSTRUCT(tup);
+ AutoVacOpts *avopts;
+ bool dovacuum;
+ bool doanalyze;
+ bool wraparound;
+ AutoVacuumScores scores;
+ Datum vals[7];
+ bool nulls[7] = {false};
+
+ /* skip ineligible entries */
+ if (form->relkind != RELKIND_RELATION &&
+ form->relkind != RELKIND_MATVIEW &&
+ form->relkind != RELKIND_TOASTVALUE)
+ continue;
+ if (form->relpersistence == RELPERSISTENCE_TEMP)
+ continue;
+
+ avopts = extract_autovac_opts(tup, RelationGetDescr(rel));
+ relation_needs_vacanalyze(form->oid, avopts, form,
+ effective_multixact_freeze_max_age, 0,
+ &dovacuum, &doanalyze, &wraparound,
+ &scores);
+ if (avopts)
+ pfree(avopts);
+
+ vals[0] = ObjectIdGetDatum(form->oid);
+ vals[1] = Float8GetDatum(scores.max);
+ vals[2] = Float8GetDatum(scores.xid);
+ vals[3] = Float8GetDatum(scores.mxid);
+ vals[4] = Float8GetDatum(scores.vac);
+ vals[5] = Float8GetDatum(scores.vac_ins);
+ vals[6] = Float8GetDatum(scores.anl);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, vals, nulls);
+ }
+ table_endscan(scan);
+ table_close(rel, AccessShareLock);
+
+ return (Datum) 0;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index bd177aebfcb..0de3bb52eb2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5667,6 +5667,13 @@
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 => 'autovacuum scores',
+ proname => 'pg_stat_get_autovacuum_scores', prorows => '100', proretset => 't',
+ provolatile => 'v', proparallel => 'u', prorettype => 'record', proargtypes => '',
+ proallargtypes => '{oid,float8,float8,float8,float8,float8,float8}',
+ proargmodes => '{o,o,o,o,o,o,o}',
+ proargnames => '{oid,score,xid_score,mxid_score,vacuum_score,vacuum_insert_score,analyze_score}',
+ prosrc => 'pg_stat_get_autovacuum_scores' },
{ 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 81a73c426d2..b167e8d3cab 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1860,6 +1860,18 @@ 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_scores| SELECT s.oid AS relid,
+ n.nspname AS schemaname,
+ c.relname,
+ s.score,
+ s.xid_score,
+ s.mxid_score,
+ s.vacuum_score,
+ s.vacuum_insert_score,
+ s.analyze_score
+ FROM ((pg_stat_get_autovacuum_scores() s(oid, score, xid_score, mxid_score, vacuum_score, vacuum_insert_score, analyze_score)
+ JOIN pg_class c ON ((c.oid = s.oid)))
+ 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,
--
2.50.1 (Apple Git-155)
--Zo6zvSwSGWTStc7v--
view thread (4+ 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]
Subject: Re: [PATCH v10 2/2] add pg_stat_autovacuum_scores system view
In-Reply-To: <no-message-id-601671@localhost>
* 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