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 10:16:50 -0500
Message-ID: <CAA5RZ0vMp2B3UBUoqLVedy8G3u8_O8M11+Y5V7uZv3++CGYasg@mail.gmail.com> (raw)
In-Reply-To: <CABV9wwM3nRitsgUxeCF0ywbAaLZV5jWC-9tj6WKxkdmQkHRcWg@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>
> On Sun, Mar 29, 2026 at 10:09 PM Bharath Rupireddy
> <[email protected]> wrote:
> > On Sat, Mar 28, 2026 at 10:54 AM Sami Imseih <[email protected]> wrote:
> > >
> > > > 4. Is the view intended to be exposed to PUBLIC without any ACL restrictions?
> > >
> > > > 2/ Do we need to revoke permissions on pg_stat_get_autovacuum_priority
> > > > for all and grant them to pg_monitor or similar? Especially since this
> > > > function loops over all the relations in a database, we may not want
> > > > everyone to be able to do this.
> > >
> > > I think you're correct there. While the data is not sensitive, it
> > > should have more controlled usage. It's only taking an AccessShareLock,
> > > but you would not want anyone to be able to run this since it's
> > > doing real computation. I think requiring pg_read_all_stats
> > > is a good idea. Will do.
> >
> > +1 for pg_read_all_stats.
> >
>
> Is there a gap here where someone may have been granted MAINTAIN on a
> relation but they do not have pg_read_all_stats?
Yes, that is possible. MAINTAIN is a per-object privilege granted on a relation,
whereas pg_read_all_stats is a global role membership. They operate at
different levels.
I don't think one needs to have MAINTAIN permissions on the table to see the
autovacuum score. DBA Monitoring users are usually separate from the DBA
operational users.
I think pg_read_all_stats is the right permission here and it should
be implemented
similar to how pg_get_shmem_allocations is done where the default permissions
are pg_read_all_stats. pg_monitor inherits pg_read_all_stats so any
user with this
privilege will be able to access this view. A DBA is free to also add
privileges to
to other users if they wish.
This is unlike other pg_stat_* views that have tuple level permission
checks ( i.e.
pg_stat_activity), but in those cases the permissions are needed to
hide sensitive data.
This is not the case here.
> > IMHO, we can have pg_stat_get_relation_autovacuum_priority defined as
> > a C function to give the autovacuum scoring as of the given moment for
> > the given table. It's easy for one to write a function to get scoring
> > for all the relations in a database. This keeps things simple yet
> > useful.
> >
>
> I don't have a strong opinion on the above, but I do suspect that the
> most common way people will interact with this is by querying against
> the view with a WHERE clause, so optimizing for that case seems
> important.
Yeah, after sleeping on it I actually think the most common case will likely be
ORDER BY score DESC LIMIT ... because you usually want to see where your
table priority is relative to everything else in the database.
For the rare case where someone wants to look up an individual table, the caller
can just use a WHERE clause. So, we should just always do the full pg_class
scan. I don't see why we need to complicate the c-function more than this.
Attached v2 implements it as above.
A few other things in v2:
1/ I set autovacuum_enabled = OFF in the tests. This will make
sure the test is both stable and will also test that the score is
returned even in
the case where autovacuum is disabled.
2/ Moved pg_stat_autovacuum_priority to the end of the docs in
"Monitoring Database Activity".
3/ Also added a mention of the extremely high score values when failsafe
is triggered [1]
[1] https://www.postgresql.org/message-id/abGP87A3JPIXDG2I%40nathan
--
Sami
Attachments:
[application/octet-stream] v2-0002-Add-pg_stat_autovacuum_priority-view.patch (19.7K, 2-v2-0002-Add-pg_stat_autovacuum_priority-view.patch)
download | inline diff:
From b2c317a24e35d41587797c7dd60ba7cadecca541 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Mon, 23 Mar 2026 17:03:59 +0000
Subject: [PATCH v2 2/2] 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://www.postgresql.org/message-id/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 | 112 ++++++++++++++++++
src/include/catalog/pg_proc.dat | 10 ++
src/test/regress/expected/rules.out | 15 +++
src/test/regress/expected/vacuum.out | 33 ++++++
src/test/regress/sql/vacuum.sql | 24 ++++
8 files changed, 387 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 f6b213852e3..44be986e5ac 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"
@@ -390,6 +392,10 @@ static void perform_work_item(AutoVacuumWorkItem *workitem);
static void autovac_report_activity(autovac_table *tab);
static void autovac_report_workitem(AutoVacuumWorkItem *workitem,
const char *nspname, const char *relname);
+static void compute_autovac_score(HeapTuple tuple, TupleDesc pg_class_desc,
+ int effective_multixact_freeze_max_age,
+ bool *dovacuum, bool *doanalyze,
+ bool *wraparound, AutoVacuumScores *scores);
static void avl_sigusr2_handler(SIGNAL_ARGS);
static bool av_worker_available(void);
static void check_av_worker_gucs(void);
@@ -3679,3 +3685,109 @@ 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)));
}
+
+/*
+ * compute_autovac_score
+ * Wrapper around relation_needs_vacanalyze() that handles the
+ * per-relation setup similar to do_autovacuum() before calling
+ * relation_needs_vacanalyze().
+ */
+static void
+compute_autovac_score(HeapTuple tuple, TupleDesc pg_class_desc,
+ int effective_multixact_freeze_max_age,
+ bool *dovacuum, bool *doanalyze,
+ bool *wraparound, AutoVacuumScores *scores)
+{
+ Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
+ AutoVacOpts *relopts;
+ PgStat_StatTabEntry *tabentry;
+
+ relopts = extract_autovac_opts(tuple, pg_class_desc);
+
+ tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
+ classForm->oid);
+
+ relation_needs_vacanalyze(classForm->oid, relopts, classForm, tabentry,
+ effective_multixact_freeze_max_age, true,
+ dovacuum, doanalyze, wraparound, scores);
+
+ if (relopts)
+ pfree(relopts);
+ if (tabentry)
+ pfree(tabentry);
+}
+
+/*
+ * 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 tuple;
+
+ 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 ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
+ {
+ Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
+ bool dovacuum;
+ bool doanalyze;
+ bool wraparound;
+ AutoVacuumScores scores;
+ 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;
+
+ compute_autovac_score(tuple, pg_class_desc,
+ effective_multixact_freeze_max_age,
+ &dovacuum, &doanalyze, &wraparound, &scores);
+
+ 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..e8c1fc8d152 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -730,3 +730,36 @@ 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.
+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 AS score,
+ vacuum_dead_score > 0 AS vacuum_dead_score,
+ vacuum_ins_score > 0 AS vacuum_ins_score,
+ analyze_score > 0 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..ba373fa31c6 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -525,3 +525,27 @@ 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.
+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 AS score,
+ vacuum_dead_score > 0 AS vacuum_dead_score,
+ vacuum_ins_score > 0 AS vacuum_ins_score,
+ analyze_score > 0 AS analyze_score
+ FROM pg_stat_autovacuum_priority
+ WHERE relname = 'vacuum_priority_test';
+DROP TABLE vacuum_priority_test;
\ No newline at end of file
--
2.47.3
[application/octet-stream] v2-0001-Add-force_scores-option-to-relation_needs_vacanal.patch (7.0K, 3-v2-0001-Add-force_scores-option-to-relation_needs_vacanal.patch)
download | inline diff:
From 9e09eae2dd17fb8b88f9b444d61aaab791c57c16 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Fri, 27 Mar 2026 20:38:26 +0000
Subject: [PATCH v2 1/2] 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().
---
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
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: <CAA5RZ0vMp2B3UBUoqLVedy8G3u8_O8M11+Y5V7uZv3++CGYasg@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