public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sami Imseih <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: Bharath Rupireddy <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Greg Burd <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Robert Treat <[email protected]>
Cc: Jeremy Schneider <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: another autovacuum scheduling thread
Date: Mon, 23 Mar 2026 14:01:22 -0500
Message-ID: <CAA5RZ0tz55uoQfcvLwvRWRPM0ry6SGfZ=-enQ4F0kaUgfPh+Vg@mail.gmail.com> (raw)
In-Reply-To: <acFan6knPZljm1Ay@nathan>
References: <abrOO8GCgkhMXvcq@nathan>
<[email protected]>
<CAApHDvpnR5zKb7LSB0idj4RPsV+=Dhtk+c5H0cZ4evN2hFDwCQ@mail.gmail.com>
<[email protected]>
<abwbDTu6OO6Izp4o@nathan>
<CAA5RZ0s7k6yve1Bq1OY+aXU3ifc_fvOiYkpE2VxwrUcHr2AtTg@mail.gmail.com>
<CAApHDvpvkKYB6z-i1kiO9iePF0fz18-CaS9DZEWnSx1eO0RGFQ@mail.gmail.com>
<CAA5RZ0tPvWLVXhDMC3t3LhwTM-OnWHSvPo7tnZzOphR=HB4kgQ@mail.gmail.com>
<ab2uoj5yU8CVUJ_H@nathan>
<CALj2ACV0HboeYiT1Z-=rb509WL3N0H9LsWRPqc_xUMEM-TM85g@mail.gmail.com>
<acFan6knPZljm1Ay@nathan>
> > 4/ Is adding a reason (such as how each of these scores influenced the
> > autovacuum to pick this table) to vacuum progress reporting a good
> > idea? This helps answer some of the why and how questions when the
> > autovacuum is in progress.
>
> Yeah, adding that in addition to a system view, etc. could be nice. I'm a
> little hesitant to start making big additions to the patch at this point,
> but I can give it a whirl if folks think something like this should be
> added for v19.
Adding a system view will be nice. I am attaching a version I used in earlier
testing (cleaned up with docs), if we are inclined to get this in. I
think it will be
useful.
This follows the same setup as do_autovacuum(); scanning pg_class,
filtering relation kinds and temp tables, and computing
effective_multixact_freeze_max_age
are done in the SQL-callable function, while another wrapper
compute_autovac_score() handles
the per-relation setup (snapshotting recentXid/recentMulti, fetching
reloptions and the pgstat entry)
before calling relation_needs_vacanalyze(). The function holds an
AccessShareLock on pg_class for
the duration of the scan, so this should be relatively lightweight.
```
test=# select * from pg_stat_autovacuum_priority order by score desc ;
relid | schemaname | relname | dovacuum |
doanalyze | wraparound | score
-------+--------------------+-----------------------------+----------+-----------+------------+-----------------------
16400 | public | pgbench_accounts | t |
f | t | 1.055318563196673e+16
16404 | public | pgbench_branches | t |
t | t | 442.01666666666665
16396 | public | pgbench_tellers | t |
t | t | 172.97333333333333
16393 | public | pgbench_history | t |
t | t | 4.703261221642761
14227 | pg_toast | pg_toast_14224 | t |
f | t | 2.08555407
```
Note in the test above, I used xid_wraparound to calculate a score
with the failsafe POW()
adjustment. Notice that this is a very high score being emitted as
discussed earlier [1].
This is documented in v14 as "scaled aggressively so that the table
has a decent chance of
sorting to the top of the list."
Maybe the doc should say something like " scaled aggressively, which
can produce very large values, to ensure
the table sorts to the top of the list."
[1] [https://www.postgresql.org/message-id/CAA5RZ0vfhAnFBp4HrBQc%2BALaJMx6vCvMtnBi39ST_4nH9PZEjA%40mail.g...]
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
[application/octet-stream] v1-0001-Add-pg_stat_autovacuum_priority-view.patch (15.3K, 2-v1-0001-Add-pg_stat_autovacuum_priority-view.patch)
download | inline diff:
From 2e08f022fef177c586838d1adb3f14202ea48578 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Mon, 23 Mar 2026 17:03:59 +0000
Subject: [PATCH v1 1/1] Add pg_stat_autovacuum_priority view
Add a new system view that exposes the autovacuum
priority score for each relation in the current
database. This allows users to inspect each table's
autovacuum eligibility and priority.
The columns returned are: relid, schemaname, relname,
needs_vacuum, needs_analyze, wraparound, and score.
The view results are based on the output of
relation_needs_vacanalyze(), in which the same setup
as do_autovacuum() is performed before calling
relation_needs_vacanalyze(). pg_class is scanned with
an AccessShareLock, so it is relatively lightweight.
Unlike do_autovacuum(), we don't need to derive
pg_toast relationships to the relation in advance, and
we just treat TOAST tables as another relation coming
in from pg_class.
---
doc/src/sgml/maintenance.sgml | 6 ++
doc/src/sgml/monitoring.sgml | 108 ++++++++++++++++++++++
src/backend/catalog/system_views.sql | 13 +++
src/backend/postmaster/autovacuum.c | 132 +++++++++++++++++++++++++--
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.dat | 9 ++
src/test/regress/expected/rules.out | 10 ++
7 files changed, 270 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index b5a191c130b..1a262fa1244 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -1153,6 +1153,12 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
listed in the <literal>pg_class</literal> system catalog), set all of the
aforementioned "weight" parameters to <literal>0.0</literal>.
</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 eligibility and priority score.
+ </para>
</sect3>
</sect2>
</sect1>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 462019a972c..901dd704804 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 eligibility 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
@@ -2847,6 +2856,105 @@ 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 a table's
+ autovacuum eligibility and 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 the relation
+ </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 the relation
+ </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 relation 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 relation 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 relations to
+ process first. Higher values indicate greater urgency. Zero if
+ the relation does not currently need vacuuming or analyzing.
+ </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 f1ed7b58f13..f6ec7653204 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -795,6 +795,19 @@ 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
+ 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 b5c153a8835..8a73f167653 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"
@@ -372,6 +374,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, double *score);
static void avl_sigusr2_handler(SIGNAL_ARGS);
static bool av_worker_available(void);
static void check_av_worker_gucs(void);
@@ -2057,6 +2063,13 @@ do_autovacuum(void)
&dovacuum, &doanalyze, &wraparound,
&score);
+ elog(DEBUG3, "%s: dovacuum: %s, doanalyze: %s, wraparound: %s, score: %.3f",
+ NameStr(classForm->relname),
+ dovacuum ? "yes" : "no",
+ doanalyze ? "yes" : "no",
+ wraparound ? "yes" : "no",
+ score);
+
/* Relations that need work are added to tables_to_process */
if (dovacuum || doanalyze)
{
@@ -2157,6 +2170,12 @@ do_autovacuum(void)
&dovacuum, &doanalyze, &wraparound,
&score);
+ elog(DEBUG3, "%s: dovacuum: %s, wraparound: %s, score: %.3f",
+ NameStr(classForm->relname),
+ dovacuum ? "yes" : "no",
+ wraparound ? "yes" : "no",
+ score);
+
/* ignore analyze for toast tables */
if (dovacuum)
{
@@ -3312,15 +3331,6 @@ relation_needs_vacanalyze(Oid relid,
*score = Max(*score, anlthresh_score);
*doanalyze = true;
}
-
- if (vac_ins_base_thresh >= 0)
- elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: %.0f (threshold %.0f), anl: %.0f (threshold %.0f), score: %.3f",
- NameStr(classForm->relname),
- vactuples, vacthresh, instuples, vacinsthresh, anltuples, anlthresh, *score);
- else
- elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: (disabled), anl: %.0f (threshold %.0f), score: %.3f",
- NameStr(classForm->relname),
- vactuples, vacthresh, anltuples, anlthresh, *score);
}
}
@@ -3635,3 +3645,107 @@ 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, double *score)
+{
+ 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,
+ dovacuum, doanalyze, wraparound, score);
+
+ 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(): snapshotting
+ * recentXid/recentMulti, scanning pg_class, filtering relation kinds
+ * and temp tables, and computing effective_multixact_freeze_max_age
+ * are done here, while compute_autovac_score() handles the per-relation
+ * setup (fetching reloptions and the pgstat entry).
+ */
+#define NUM_AV_SCORE_COLS 5
+
+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();
+
+ /* Snapshot once before the scan, like do_autovacuum()'s caller. */
+ 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;
+ double score = 0.0;
+ 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, &score);
+
+ values[0] = ObjectIdGetDatum(classForm->oid);
+ values[1] = BoolGetDatum(dovacuum);
+ values[2] = BoolGetDatum(doanalyze);
+ values[3] = BoolGetDatum(wraparound);
+ values[4] = Float8GetDatum(score);
+
+ 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 420850293f8..bce64758823 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202603201
+#define CATALOG_VERSION_NO 202603231
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 84e7adde0e5..e52420898ce 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}',
+ proargmodes => '{o,o,o,o,o}',
+ proargnames => '{relid,needs_vacuum,needs_analyze,wraparound,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 32bea58db2c..257f21be004 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1860,6 +1860,16 @@ 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
+ FROM ((pg_stat_get_autovacuum_priority() s(relid, needs_vacuum, needs_analyze, wraparound, 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,
--
2.47.3
view thread (143+ 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], [email protected], [email protected], [email protected], [email protected]
Subject: Re: another autovacuum scheduling thread
In-Reply-To: <CAA5RZ0tz55uoQfcvLwvRWRPM0ry6SGfZ=-enQ4F0kaUgfPh+Vg@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