public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nathan Bossart <[email protected]>
Subject: [PATCH v8 5/5] 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/system-views.sgml | 137 +++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 17 ++++
src/backend/postmaster/autovacuum.c | 70 ++++++++++++++
src/include/catalog/pg_proc.dat | 9 ++
src/test/regress/expected/rules.out | 12 +++
6 files changed, 251 insertions(+)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 0d2a28207ed..6f500a66ae9 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="view-pg-autovacuum-scores"><structname>pg_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/system-views.sgml b/doc/src/sgml/system-views.sgml
index 9ee1a2bfc6a..ffe7152028d 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -56,6 +56,11 @@
<entry>in-use asynchronous IO handles</entry>
</row>
+ <row>
+ <entry><link linkend="view-pg-autovacuum-scores"><structname>pg_autovacuum_scores</structname></link></entry>
+ <entry>autovacuum scores</entry>
+ </row>
+
<row>
<entry><link linkend="view-pg-available-extensions"><structname>pg_available_extensions</structname></link></entry>
<entry>available extensions</entry>
@@ -540,6 +545,138 @@
</para>
</sect1>
+ <sect1 id="view-pg-autovacuum-scores">
+ <title><structname>pg_autovacuum_scores</structname></title>
+
+ <indexterm zone="view-pg-autovacuum-scores">
+ <primary>pg_autovacuum_scores</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_autovacuum_scores</structname> view will contain one
+ row for each table in the current database, showing the current autovacuum
+ scores for that table. See <xref linkend="autovacuum-priority"/> for more
+ information.
+ </para>
+
+ <table>
+ <title><structname>pg_autovacuum_scores</structname> Columns</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. Scores greater
+ than or equal to <literal>1.0</literal> indicate the table needs
+ processing.
+ </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
+ <literal>1.0</literal> indicate the table needs vacuuming.
+ </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
+ <literal>1.0</literal> indicate the table needs vacuuming.
+ </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
+ <literal>1.0</literal> indicate the table needs vacuuming.
+ </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
+ <literal>1.0</literal> indicate the table needs vacuuming.
+ </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
+ <literal>1.0</literal> indicate the table needs analyzing.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <structname>pg_autovacuum_scores</structname> view is read-only.
+ </para>
+
+ <para>
+ By default, the <structname>pg_autovacuum_scores</structname> view can be
+ read only by superusers or roles with privileges of the
+ <literal>pg_read_all_stats</literal> role.
+ </para>
+ </sect1>
+
<sect1 id="view-pg-available-extensions">
<title><structname>pg_available_extensions</structname></title>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index e54018004db..ef2448eff23 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1516,3 +1516,20 @@ CREATE VIEW pg_aios AS
SELECT * FROM pg_get_aios();
REVOKE ALL ON pg_aios FROM PUBLIC;
GRANT SELECT ON pg_aios TO pg_read_all_stats;
+
+CREATE VIEW pg_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_get_autovacuum_scores() s
+ JOIN pg_class c on c.oid = s.oid
+ LEFT JOIN pg_namespace n ON n.oid = c.relnamespace;
+REVOKE ALL ON pg_autovacuum_scores FROM PUBLIC;
+GRANT SELECT ON pg_autovacuum_scores TO pg_read_all_stats;
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 9ccd1861328..891bd82cfb0 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"
@@ -3672,3 +3674,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_get_autovacuum_scores
+ *
+ * Returns current autovacuum scores for all relevant tables in the current
+ * database.
+ */
+Datum
+pg_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));
+ compute_autovacuum_scores(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 acf16254b21..78ac2adb1ba 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6245,6 +6245,15 @@
prosrc => 'pg_stat_reset_subscription_stats',
proacl => '{POSTGRES=X}' },
+{ oid => '8409', descr => 'autovacuum scores',
+ proname => 'pg_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_get_autovacuum_scores',
+ proacl => '{POSTGRES=X,pg_read_all_stats=X}' },
+
{ oid => '3163', descr => 'current trigger depth',
proname => 'pg_trigger_depth', provolatile => 's', proparallel => 'r',
prorettype => 'int4', proargtypes => '', prosrc => 'pg_trigger_depth' },
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2b3cf6d8569..047dc90134f 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1302,6 +1302,18 @@ pg_aios| SELECT pid,
f_localmem,
f_buffered
FROM pg_get_aios() pg_get_aios(pid, io_id, io_generation, state, operation, off, length, target, handle_data_len, raw_result, result, target_desc, f_sync, f_localmem, f_buffered);
+pg_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_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_available_extension_versions| SELECT e.name,
e.version,
(x.extname IS NOT NULL) AS installed,
--
2.50.1 (Apple Git-155)
--/vKBiHkEvphli8Mi--
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 v8 5/5] add pg_stat_autovacuum_scores system view
In-Reply-To: <no-message-id-601576@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