pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Nathan Bossart <nathandbossart@gmail.com>
To: Sami Imseih <samimseih@gmail.com>
Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Cc: Robert Treat <rob@xzilla.net>
Cc: satyanarlapuram@gmail.com, pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Add pg_stat_autovacuum_priority
Date: Sat, 4 Apr 2026 11:33:09 -0500
Message-ID: <adE9RbcA7lhH_w2d@nathan> (raw )
In-Reply-To: <CAA5RZ0vxby2osMMaCuZ=680tmt583cF9n4rOzTGdsiS-1PJknA@mail.gmail.com >
References: <CAA5RZ0vFtDKAOZALeLp3vLyhWcLntaqS-Oh36Xxgx5_8sYeSKQ@mail.gmail.com >
<CAA5RZ0tOJjUuJidUQYAeoUD4VGjOSjuB7ox4dtLAsyoCZqSN+Q@mail.gmail.com >
<ac2ODZSOjWNzueHZ@nathan >
<CAA5RZ0uVg0L3vVvkOiHaY9ZJ+K9ZcsyQH0TVQ5rXSd-ZEeVFNg@mail.gmail.com >
<ac_vLKVgs4RYK1P-@nathan >
<CAA5RZ0sCRjH3xkHFdSXnKysdMZXFyaS_094+K-O_rr4Fkmwc=Q@mail.gmail.com >
<adB6FQeoUst6gHA0@nathan >
<CAA5RZ0t6bCR6Pasd_eWqXvyUmrNDWmHUy1yN_Po-c4+b9BtBqQ@mail.gmail.com >
<adEhRzu4YM7ztLXr@nathan >
<CAA5RZ0vxby2osMMaCuZ=680tmt583cF9n4rOzTGdsiS-1PJknA@mail.gmail.com >
On Sat, Apr 04, 2026 at 10:32:07AM -0500, Sami Imseih wrote:
>> On Sat, Apr 04, 2026 at 08:25:26AM -0500, Sami Imseih wrote:
>> > "Scores greater than or equal to <literal>1.0</literal>" in the comments
>> > of each field are misleading. This conflates scoring with vacuum/analyze
>> > eligibility and it's possible with a autovacuum_*_weight < 1.0 to trigger an
>> > autovacuum/analyze.
>>
>> Ah, that's unfortunate. I think it'd be good to give folks some idea of
>> what autovacuum will actually process. I wonder if we could adjust the
>> documentation accordingly.
>
> That's why I thought having the bool fields made sense in the earlier
> versions of the view. Since autovacuum is dealing with 2 concepts:
>
> eligibility: is av enabled and is the table meeting thresholds
> score: The priority of how the eligible tables will be processed.
>
> So, while this could be explained in docs, I think it's better we report
> these fields.
I understand your position, but I still worry about potential confusion if
when dovacuum and needs_vacuum differ. And I don't know how much we really
ought to be tailoring this stuff to clusters where autovacuum is disabled
or where the scores are being adjusted. Also, I think we ought to go into
more detail in the documentation, anyway. So my instinct was to do
something more like the attached.
If we did report booleans, I would probably argue for just reporting
dovacuum and doanalyze and calling out the criteria for why they may be
false even when it looks like the table needs processing.
--
nathan
From dee70b5a39310e6ef6cf68ba810249e29dd9375c Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 2 Apr 2026 14:17:11 -0500
Subject: [PATCH v10 1/2] refactor autovacuum subroutine in preparation for
system view
---
src/backend/postmaster/autovacuum.c | 73 ++++++-----------------------
1 file changed, 15 insertions(+), 58 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 8400e6722cc..1be1ba8a25f 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -370,13 +370,8 @@ static void FreeWorkerInfo(int code, Datum arg);
static autovac_table *table_recheck_autovac(Oid relid, HTAB *table_toast_map,
TupleDesc pg_class_desc,
int effective_multixact_freeze_max_age);
-static void recheck_relation_needs_vacanalyze(Oid relid, AutoVacOpts *avopts,
- Form_pg_class classForm,
- int effective_multixact_freeze_max_age,
- bool *dovacuum, bool *doanalyze, bool *wraparound);
static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
Form_pg_class classForm,
- PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
int elevel,
bool *dovacuum, bool *doanalyze, bool *wraparound,
@@ -2029,7 +2024,6 @@ do_autovacuum(void)
while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
{
Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
- PgStat_StatTabEntry *tabentry;
AutoVacOpts *relopts;
Oid relid;
bool dovacuum;
@@ -2070,11 +2064,9 @@ do_autovacuum(void)
/* Fetch reloptions and the pgstat entry for this table */
relopts = extract_autovac_opts(tuple, pg_class_desc);
- tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
- relid);
/* Check if it needs vacuum or analyze */
- relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
+ relation_needs_vacanalyze(relid, relopts, classForm,
effective_multixact_freeze_max_age,
DEBUG3,
&dovacuum, &doanalyze, &wraparound,
@@ -2121,8 +2113,6 @@ do_autovacuum(void)
/* Release stuff to avoid per-relation leakage */
if (relopts)
pfree(relopts);
- if (tabentry)
- pfree(tabentry);
}
table_endscan(relScan);
@@ -2137,7 +2127,6 @@ do_autovacuum(void)
while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
{
Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
- PgStat_StatTabEntry *tabentry;
Oid relid;
AutoVacOpts *relopts;
bool free_relopts = false;
@@ -2171,11 +2160,7 @@ do_autovacuum(void)
relopts = &hentry->ar_reloptions;
}
- /* Fetch the pgstat entry for this table */
- tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
- relid);
-
- relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
+ relation_needs_vacanalyze(relid, relopts, classForm,
effective_multixact_freeze_max_age,
DEBUG3,
&dovacuum, &doanalyze, &wraparound,
@@ -2194,8 +2179,6 @@ do_autovacuum(void)
/* Release stuff to avoid leakage */
if (free_relopts)
pfree(relopts);
- if (tabentry)
- pfree(tabentry);
}
table_endscan(relScan);
@@ -2834,6 +2817,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
bool wraparound;
AutoVacOpts *avopts;
bool free_avopts = false;
+ AutoVacuumScores scores;
/* fetch the relation's relcache entry */
classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
@@ -2859,9 +2843,11 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
avopts = &hentry->ar_reloptions;
}
- recheck_relation_needs_vacanalyze(relid, avopts, classForm,
- effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ relation_needs_vacanalyze(relid, avopts, classForm,
+ effective_multixact_freeze_max_age,
+ DEBUG3,
+ &dovacuum, &doanalyze, &wraparound,
+ &scores);
/* OK, it needs something done */
if (doanalyze || dovacuum)
@@ -2970,41 +2956,6 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
return tab;
}
-/*
- * recheck_relation_needs_vacanalyze
- *
- * Subroutine for table_recheck_autovac.
- *
- * Fetch the pgstat of a relation and recheck whether a relation
- * needs to be vacuumed or analyzed.
- */
-static void
-recheck_relation_needs_vacanalyze(Oid relid,
- AutoVacOpts *avopts,
- Form_pg_class classForm,
- int effective_multixact_freeze_max_age,
- bool *dovacuum,
- bool *doanalyze,
- bool *wraparound)
-{
- PgStat_StatTabEntry *tabentry;
- AutoVacuumScores scores;
-
- /* fetch the pgstat table entry */
- tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
- relid);
-
- relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
- effective_multixact_freeze_max_age,
- DEBUG3,
- dovacuum, doanalyze, wraparound,
- &scores);
-
- /* Release tabentry to avoid leakage */
- if (tabentry)
- pfree(tabentry);
-}
-
/*
* relation_needs_vacanalyze
*
@@ -3089,7 +3040,6 @@ static void
relation_needs_vacanalyze(Oid relid,
AutoVacOpts *relopts,
Form_pg_class classForm,
- PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
int elevel,
/* output params below */
@@ -3098,6 +3048,7 @@ relation_needs_vacanalyze(Oid relid,
bool *wraparound,
AutoVacuumScores *scores)
{
+ PgStat_StatTabEntry *tabentry;
bool force_vacuum;
bool av_enabled;
@@ -3265,6 +3216,8 @@ relation_needs_vacanalyze(Oid relid,
* vacuuming only, so don't vacuum (or analyze) anything that's not being
* forced.
*/
+ tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
+ relid);
if (!tabentry)
return;
@@ -3353,6 +3306,10 @@ relation_needs_vacanalyze(Oid relid,
vactuples, vacthresh, scores->vac,
anltuples, anlthresh, scores->anl,
scores->xid, scores->mxid);
+
+ /* Release tabentry to avoid leakage */
+ if (tabentry)
+ pfree(tabentry);
}
/*
--
2.50.1 (Apple Git-155)
From 61869b9e41180b54c8499f1725ef32199736ab57 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 2 Apr 2026 15:47:19 -0500
Subject: [PATCH v10 2/2] add pg_stat_autovacuum_scores system view
---
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)
Attachments:
[text/plain] v10-0001-refactor-autovacuum-subroutine-in-preparation-fo.patch (6.1K, ../adE9RbcA7lhH_w2d@nathan/2-v10-0001-refactor-autovacuum-subroutine-in-preparation-fo.patch)
download | inline diff:
From dee70b5a39310e6ef6cf68ba810249e29dd9375c Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 2 Apr 2026 14:17:11 -0500
Subject: [PATCH v10 1/2] refactor autovacuum subroutine in preparation for
system view
---
src/backend/postmaster/autovacuum.c | 73 ++++++-----------------------
1 file changed, 15 insertions(+), 58 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 8400e6722cc..1be1ba8a25f 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -370,13 +370,8 @@ static void FreeWorkerInfo(int code, Datum arg);
static autovac_table *table_recheck_autovac(Oid relid, HTAB *table_toast_map,
TupleDesc pg_class_desc,
int effective_multixact_freeze_max_age);
-static void recheck_relation_needs_vacanalyze(Oid relid, AutoVacOpts *avopts,
- Form_pg_class classForm,
- int effective_multixact_freeze_max_age,
- bool *dovacuum, bool *doanalyze, bool *wraparound);
static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
Form_pg_class classForm,
- PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
int elevel,
bool *dovacuum, bool *doanalyze, bool *wraparound,
@@ -2029,7 +2024,6 @@ do_autovacuum(void)
while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
{
Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
- PgStat_StatTabEntry *tabentry;
AutoVacOpts *relopts;
Oid relid;
bool dovacuum;
@@ -2070,11 +2064,9 @@ do_autovacuum(void)
/* Fetch reloptions and the pgstat entry for this table */
relopts = extract_autovac_opts(tuple, pg_class_desc);
- tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
- relid);
/* Check if it needs vacuum or analyze */
- relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
+ relation_needs_vacanalyze(relid, relopts, classForm,
effective_multixact_freeze_max_age,
DEBUG3,
&dovacuum, &doanalyze, &wraparound,
@@ -2121,8 +2113,6 @@ do_autovacuum(void)
/* Release stuff to avoid per-relation leakage */
if (relopts)
pfree(relopts);
- if (tabentry)
- pfree(tabentry);
}
table_endscan(relScan);
@@ -2137,7 +2127,6 @@ do_autovacuum(void)
while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
{
Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
- PgStat_StatTabEntry *tabentry;
Oid relid;
AutoVacOpts *relopts;
bool free_relopts = false;
@@ -2171,11 +2160,7 @@ do_autovacuum(void)
relopts = &hentry->ar_reloptions;
}
- /* Fetch the pgstat entry for this table */
- tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
- relid);
-
- relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
+ relation_needs_vacanalyze(relid, relopts, classForm,
effective_multixact_freeze_max_age,
DEBUG3,
&dovacuum, &doanalyze, &wraparound,
@@ -2194,8 +2179,6 @@ do_autovacuum(void)
/* Release stuff to avoid leakage */
if (free_relopts)
pfree(relopts);
- if (tabentry)
- pfree(tabentry);
}
table_endscan(relScan);
@@ -2834,6 +2817,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
bool wraparound;
AutoVacOpts *avopts;
bool free_avopts = false;
+ AutoVacuumScores scores;
/* fetch the relation's relcache entry */
classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
@@ -2859,9 +2843,11 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
avopts = &hentry->ar_reloptions;
}
- recheck_relation_needs_vacanalyze(relid, avopts, classForm,
- effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ relation_needs_vacanalyze(relid, avopts, classForm,
+ effective_multixact_freeze_max_age,
+ DEBUG3,
+ &dovacuum, &doanalyze, &wraparound,
+ &scores);
/* OK, it needs something done */
if (doanalyze || dovacuum)
@@ -2970,41 +2956,6 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
return tab;
}
-/*
- * recheck_relation_needs_vacanalyze
- *
- * Subroutine for table_recheck_autovac.
- *
- * Fetch the pgstat of a relation and recheck whether a relation
- * needs to be vacuumed or analyzed.
- */
-static void
-recheck_relation_needs_vacanalyze(Oid relid,
- AutoVacOpts *avopts,
- Form_pg_class classForm,
- int effective_multixact_freeze_max_age,
- bool *dovacuum,
- bool *doanalyze,
- bool *wraparound)
-{
- PgStat_StatTabEntry *tabentry;
- AutoVacuumScores scores;
-
- /* fetch the pgstat table entry */
- tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
- relid);
-
- relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
- effective_multixact_freeze_max_age,
- DEBUG3,
- dovacuum, doanalyze, wraparound,
- &scores);
-
- /* Release tabentry to avoid leakage */
- if (tabentry)
- pfree(tabentry);
-}
-
/*
* relation_needs_vacanalyze
*
@@ -3089,7 +3040,6 @@ static void
relation_needs_vacanalyze(Oid relid,
AutoVacOpts *relopts,
Form_pg_class classForm,
- PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
int elevel,
/* output params below */
@@ -3098,6 +3048,7 @@ relation_needs_vacanalyze(Oid relid,
bool *wraparound,
AutoVacuumScores *scores)
{
+ PgStat_StatTabEntry *tabentry;
bool force_vacuum;
bool av_enabled;
@@ -3265,6 +3216,8 @@ relation_needs_vacanalyze(Oid relid,
* vacuuming only, so don't vacuum (or analyze) anything that's not being
* forced.
*/
+ tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
+ relid);
if (!tabentry)
return;
@@ -3353,6 +3306,10 @@ relation_needs_vacanalyze(Oid relid,
vactuples, vacthresh, scores->vac,
anltuples, anlthresh, scores->anl,
scores->xid, scores->mxid);
+
+ /* Release tabentry to avoid leakage */
+ if (tabentry)
+ pfree(tabentry);
}
/*
--
2.50.1 (Apple Git-155)
[text/plain] v10-0002-add-pg_stat_autovacuum_scores-system-view.patch (12.9K, ../adE9RbcA7lhH_w2d@nathan/3-v10-0002-add-pg_stat_autovacuum_scores-system-view.patch)
download | inline diff:
From 61869b9e41180b54c8499f1725ef32199736ab57 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 2 Apr 2026 15:47:19 -0500
Subject: [PATCH v10 2/2] add pg_stat_autovacuum_scores system view
---
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)
view thread (61+ messages) latest in thread
Message-ID: <adE9RbcA7lhH_w2d@nathan>
Permalink: ../adE9RbcA7lhH_w2d@nathan/
Also on: postgresql.org/message-id/adE9RbcA7lhH_w2d@nathan
copy link · copy postgr.es · copy git am
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: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, samimseih@gmail.com, bharath.rupireddyforpostgres@gmail.com, rob@xzilla.net
Subject: Re: Add pg_stat_autovacuum_priority
In-Reply-To: <adE9RbcA7lhH_w2d@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox