public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sami Imseih <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: Add pg_stat_autovacuum_priority
Date: Fri, 27 Mar 2026 18:14:14 -0500
Message-ID: <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com> (raw)
Hi,
This is a quick follow-up to the commit d7965d65f which
introduced autovacuum prioritization based on a score that
is the Max of several components, such as vacuum
thresholds, xid age, etc.
It was also discussed in that thread [1] that we will need
a view to expose the priority scores, per table in a view.
This will allow a user to introspect what the autovacuum
launcher will prioritize next as well as verify tuning
efforts for autovacuum prioritization; the latter case
likely being rare.
So after spending time on this today, I am proposing a view
that returns a line for each relation with information
about if the table needs autovacuum/autoanalyze, as well as
scores of each component and the Max score. It looks like
the below:
```
postgres=# select * FROM pg_stat_autovacuum_priority;
-[ RECORD 1 ]-----+----------------------------
relid | 16410
schemaname | public
relname | av_priority_test
needs_vacuum | f
needs_analyze | f
wraparound | f
score | 0
xid_score | 0
mxid_score | 0
vacuum_dead_score | 0
vacuum_ins_score | 0
analyze_score | 0
```
The function essentially calls relation_needs_vacanalyze()
with some setup work, such as scanning the catalog with an
AccessShareLock, etc. and emits the result of this call.
To make this work 0001 introduces a small change to
relation_needs_vacanalyze() to take in a boolean to force
the calculation of the score (even if autovacuum is
disabled for the relation).
0002 introduces the view with documentation and testing in
vacuum.c (xid age and mxid age scores are not tested as
they require xid_wraparound to consume enough XIDs to
trigger a score, which will cost too much time for a
regression test).
Find the attached taking the first attempt at this view.
[1] [https://www.postgresql.org/message-id/CAApHDvqQN-B2sQov8nsfZOmx-VeJMauSf4kLa3A8LsK1tUyBNw%40mail.gma...]
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
[application/octet-stream] v1-0002-Add-pg_stat_autovacuum_priority-view.patch (19.5K, 2-v1-0002-Add-pg_stat_autovacuum_priority-view.patch)
download | inline diff:
From a5bc5aaf9b468d08a879983481199d4ea82f49d3 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Mon, 23 Mar 2026 17:03:59 +0000
Subject: [PATCH v1 2/2] Add pg_stat_autovacuum_priority view
d7965d65f introduced vacuum prioritization based on a score that
calculates the urgency based on a Max of several components, such as
vacuum thresholds, xid age, etc. These scores are used by autovacuum
to prioritize which tables to vacuum next.
The aforementioned commit includes documentation into how
prioritization works and several GUCs to tune priority.
This change introduces a view to show a line per relation with vacuum
or analyze eligibility, the score of each component and the Max score
across all components. Having this information exposed provides the
user with a way to introspect which tables are eligible for autovacuum
or autoanalyze as well as provide feedback on tuning of the GUCs.
The view simply calls relation_needs_vacanalyze() which provides the
scores and eligibility details. An earlier commit also introduced the
force_scores field to relation_needs_vacanalyze() so it can compute
scores regardless of the table's eligibility for autovacuum or
autoanalyze.
The view also emits the relid, namespace and relname, therefore 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
---
doc/src/sgml/maintenance.sgml | 6 +
doc/src/sgml/monitoring.sgml | 158 +++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 18 +++
src/backend/postmaster/autovacuum.c | 113 +++++++++++++++++++
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.dat | 9 ++
src/test/regress/expected/rules.out | 15 +++
src/test/regress/expected/vacuum.out | 30 +++++
src/test/regress/sql/vacuum.sql | 21 ++++
9 files changed, 371 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..39508c6e979 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
@@ -2856,6 +2865,155 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para>
</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.
+ </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 to prevent
+ transaction ID wraparound.
+ </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 to prevent
+ multixact ID wraparound.
+ </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, reflecting the need for an insert-triggered 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, reflecting the need for updated statistics.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+
<sect2 id="monitoring-pg-stat-io-view">
<title><structname>pg_stat_io</structname></title>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index e54018004db..41cda990135 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -795,6 +795,24 @@ 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;
+
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..ccb4118df51 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,110 @@ 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 each relation 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.
+ */
+#define NUM_AV_SCORE_COLS 10
+
+Datum
+pg_stat_get_autovacuum_priority(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ Relation classRel;
+ TableScanDesc relScan;
+ HeapTuple tuple;
+ TupleDesc pg_class_desc;
+ int effective_multixact_freeze_max_age;
+
+ 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/catversion.h b/src/include/catalog/catversion.h
index 4d9ccc5789c..bce64758823 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202603241
+#define CATALOG_VERSION_NO 202603231
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 0118e970dda..91d47617b0b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5667,6 +5667,15 @@
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' },
{ 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..0d7d1d87c0b 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -730,3 +730,33 @@ 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.
+CREATE TABLE vacuum_priority_test (id int)
+ WITH (autovacuum_analyze_threshold = 1,
+ autovacuum_vacuum_threshold = 1,
+ autovacuum_vacuum_insert_threshold = 1);
+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..7a50858eeea 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -525,3 +525,24 @@ 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.
+CREATE TABLE vacuum_priority_test (id int)
+ WITH (autovacuum_analyze_threshold = 1,
+ autovacuum_vacuum_threshold = 1,
+ autovacuum_vacuum_insert_threshold = 1);
+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] v1-0001-Add-force_scores-option-to-relation_needs_vacanal.patch (7.0K, 3-v1-0001-Add-force_scores-option-to-relation_needs_vacanal.patch)
download | inline diff:
From 85e99681cc7207b8e62a0ba8e604fde36bd5f6e4 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Fri, 27 Mar 2026 20:38:26 +0000
Subject: [PATCH v1 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]
Subject: Re: Add pg_stat_autovacuum_priority
In-Reply-To: <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@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