From f53d03702f4882a9135176a3932a93dd668cfca1 Mon Sep 17 00:00:00 2001 From: Sami Imseih Date: Mon, 23 Mar 2026 17:03:59 +0000 Subject: [PATCH v4 4/4] 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 compute_autovac_score(). 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://postgr.es/m/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 | 86 ++++++++++++++ src/include/catalog/pg_proc.dat | 10 ++ src/test/regress/expected/rules.out | 15 +++ src/test/regress/expected/vacuum.out | 32 ++++++ src/test/regress/sql/vacuum.sql | 23 ++++ 8 files changed, 359 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 2.0 effectively doubles the analyze component score. + + + The + pg_stat_autovacuum_priority view can be + used to inspect each table's autovacuum need and priority score. + diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index bb75ed1069b..791850bafe7 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 + + pg_stat_autovacuum_prioritypg_stat_autovacuum_priority + One row per relation in the current database, showing + a table's autovacuum need and priority. See + + pg_stat_autovacuum_priority for details. + + + pg_stat_bgwriterpg_stat_bgwriter One row only, showing statistics about the @@ -5256,6 +5265,163 @@ description | Waiting for a newly initialized WAL file to reach durable storage + + <structname>pg_stat_autovacuum_priority</structname> + + + pg_stat_autovacuum_priority + + + + The pg_stat_autovacuum_priority view contains + one row per relation in the current database, showing whether a table + needs autovacuum or autoanalyze and its priority. The + score, freeze_score, + and multixact_freeze_score values may be very large for + tables approaching wraparound, as these scores are scaled aggressively + once they surpass the failsafe age thresholds. + + + + <structname>pg_stat_autovacuum_priority</structname> View + + + + + Column Type + + + Description + + + + + + + + relid oid + + + OID of a table + + + + + + schemaname name + + + Name of the schema that this table is in + + + + + + relname name + + + Name of this table + + + + + + needs_vacuum boolean + + + True if the table exceeds the vacuum threshold + + + + + + needs_analyze boolean + + + True if the table exceeds the analyze threshold + + + + + + wraparound boolean + + + True if vacuuming is needed to prevent transaction ID or + multixact ID wraparound + + + + + + score double precision + + + 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. + + + + + + freeze_score double precision + + + Score component based on transaction ID age. + + + + + + multixact_freeze_score double precision + + + Score component based on multixact ID age. + + + + + + vacuum_score double precision + + + Score component based on the estimated number of dead tuples + needing removal by vacuum. + + + + + + vacuum_insert_score double precision + + + Score component based on the number of inserts since the last + vacuum. + + + + + + analyze_score double precision + + + Score component based on the number of modifications since the + last analyze. + + + + +
+ + + By default, the pg_stat_autovacuum_priority view can + be read only by superusers or roles with privileges of the + pg_read_all_stats role. + + + + Statistics Functions diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index e54018004db..30bbd55e330 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.freeze_score, + S.multixact_freeze_score, + S.vacuum_score, + S.vacuum_insert_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 4b0d2526f5b..a469d2858ff 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" @@ -3675,3 +3677,87 @@ 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_priority + * + * Returns the autovacuum priority score for a relation as well as if the + * relation needs vacuum or analyze, and if the vacuum is a force vacuum + * due to wraparound. + * + * 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 classTup; + + 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 ((classTup = heap_getnext(relScan, ForwardScanDirection)) != NULL) + { + Form_pg_class classForm = (Form_pg_class) GETSTRUCT(classTup); + bool dovacuum; + bool doanalyze; + bool wraparound; + AutoVacuumPriority priority; + AutoVacOpts *avopts; + 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; + + avopts = extract_autovac_opts(classTup, pg_class_desc); + + compute_autovac_score(classTup, pg_class_desc, + effective_multixact_freeze_max_age, avopts, + 0, &dovacuum, &doanalyze, + &wraparound, &priority); + + if (avopts) + pfree(avopts); + + values[0] = ObjectIdGetDatum(classForm->oid); + values[1] = BoolGetDatum(priority.needs_vacuum); + values[2] = BoolGetDatum(priority.needs_analyze); + values[3] = BoolGetDatum(priority.is_wraparound); + values[4] = Float8GetDatum(priority.max); + values[5] = Float8GetDatum(priority.xid); + values[6] = Float8GetDatum(priority.mxid); + values[7] = Float8GetDatum(priority.vac); + values[8] = Float8GetDatum(priority.vac_ins); + values[9] = Float8GetDatum(priority.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..6f5d199a506 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,freeze_score,multixact_freeze_score,vacuum_score,vacuum_insert_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..53bf9a46e4f 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.freeze_score, + s.multixact_freeze_score, + s.vacuum_score, + s.vacuum_insert_score, + s.analyze_score + FROM ((pg_stat_get_autovacuum_priority() s(relid, needs_vacuum, needs_analyze, wraparound, score, freeze_score, multixact_freeze_score, vacuum_score, vacuum_insert_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..9cbc2f6a14b 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -730,3 +730,35 @@ 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. Scores are checked to be +-- within an expected range. freeze_score and multixact_freeze_score are excluded +-- as they require consuming enough XIDs to be meaningful. +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 AND score <= 4 AS score, + vacuum_score > 0 AND vacuum_score <= 2 AS vacuum_score, + vacuum_insert_score > 0 AND vacuum_insert_score <= 2 AS vacuum_insert_score, + analyze_score > 0 AND analyze_score <= 4 AS analyze_score + FROM pg_stat_autovacuum_priority + WHERE relname = 'vacuum_priority_test'; + needs_vacuum | needs_analyze | score | vacuum_score | vacuum_insert_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..556fe3127f4 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -525,3 +525,26 @@ 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. Scores are checked to be +-- within an expected range. freeze_score and multixact_freeze_score are excluded +-- as they require consuming enough XIDs to be meaningful. +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 AND score <= 4 AS score, + vacuum_score > 0 AND vacuum_score <= 2 AS vacuum_score, + vacuum_insert_score > 0 AND vacuum_insert_score <= 2 AS vacuum_insert_score, + analyze_score > 0 AND analyze_score <= 4 AS analyze_score + FROM pg_stat_autovacuum_priority + WHERE relname = 'vacuum_priority_test'; +DROP TABLE vacuum_priority_test; -- 2.47.3