public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v8 5/5] add pg_stat_autovacuum_scores system view 4+ messages / 1 participants [nested] [flat]
* [PATCH v8 5/5] add pg_stat_autovacuum_scores system view @ 2026-04-02 20:47 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Nathan Bossart @ 2026-04-02 20:47 UTC (permalink / raw) --- doc/src/sgml/maintenance.sgml | 6 ++ doc/src/sgml/system-views.sgml | 137 +++++++++++++++++++++++++++ src/backend/catalog/system_views.sql | 17 ++++ src/backend/postmaster/autovacuum.c | 70 ++++++++++++++ src/include/catalog/pg_proc.dat | 9 ++ src/test/regress/expected/rules.out | 12 +++ 6 files changed, 251 insertions(+) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 0d2a28207ed..6f500a66ae9 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="view-pg-autovacuum-scores"><structname>pg_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/system-views.sgml b/doc/src/sgml/system-views.sgml index 9ee1a2bfc6a..ffe7152028d 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -56,6 +56,11 @@ <entry>in-use asynchronous IO handles</entry> </row> + <row> + <entry><link linkend="view-pg-autovacuum-scores"><structname>pg_autovacuum_scores</structname></link></entry> + <entry>autovacuum scores</entry> + </row> + <row> <entry><link linkend="view-pg-available-extensions"><structname>pg_available_extensions</structname></link></entry> <entry>available extensions</entry> @@ -540,6 +545,138 @@ </para> </sect1> + <sect1 id="view-pg-autovacuum-scores"> + <title><structname>pg_autovacuum_scores</structname></title> + + <indexterm zone="view-pg-autovacuum-scores"> + <primary>pg_autovacuum_scores</primary> + </indexterm> + + <para> + The <structname>pg_stat_autovacuum_scores</structname> view will contain one + row for each table in the current database, showing the current autovacuum + scores for that table. See <xref linkend="autovacuum-priority"/> for more + information. + </para> + + <table> + <title><structname>pg_autovacuum_scores</structname> Columns</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. Scores greater + than or equal to <literal>1.0</literal> indicate the table needs + processing. + </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 + <literal>1.0</literal> indicate the table needs vacuuming. + </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 + <literal>1.0</literal> indicate the table needs vacuuming. + </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 + <literal>1.0</literal> indicate the table needs vacuuming. + </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 + <literal>1.0</literal> indicate the table needs vacuuming. + </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 + <literal>1.0</literal> indicate the table needs analyzing. + </para></entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + The <structname>pg_autovacuum_scores</structname> view is read-only. + </para> + + <para> + By default, the <structname>pg_autovacuum_scores</structname> view can be + read only by superusers or roles with privileges of the + <literal>pg_read_all_stats</literal> role. + </para> + </sect1> + <sect1 id="view-pg-available-extensions"> <title><structname>pg_available_extensions</structname></title> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index e54018004db..ef2448eff23 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1516,3 +1516,20 @@ CREATE VIEW pg_aios AS SELECT * FROM pg_get_aios(); REVOKE ALL ON pg_aios FROM PUBLIC; GRANT SELECT ON pg_aios TO pg_read_all_stats; + +CREATE VIEW pg_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_get_autovacuum_scores() s + JOIN pg_class c on c.oid = s.oid + LEFT JOIN pg_namespace n ON n.oid = c.relnamespace; +REVOKE ALL ON pg_autovacuum_scores FROM PUBLIC; +GRANT SELECT ON pg_autovacuum_scores TO pg_read_all_stats; diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 9ccd1861328..891bd82cfb0 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" @@ -3672,3 +3674,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_get_autovacuum_scores + * + * Returns current autovacuum scores for all relevant tables in the current + * database. + */ +Datum +pg_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)); + compute_autovacuum_scores(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 acf16254b21..78ac2adb1ba 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -6245,6 +6245,15 @@ prosrc => 'pg_stat_reset_subscription_stats', proacl => '{POSTGRES=X}' }, +{ oid => '8409', descr => 'autovacuum scores', + proname => 'pg_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_get_autovacuum_scores', + proacl => '{POSTGRES=X,pg_read_all_stats=X}' }, + { oid => '3163', descr => 'current trigger depth', proname => 'pg_trigger_depth', provolatile => 's', proparallel => 'r', prorettype => 'int4', proargtypes => '', prosrc => 'pg_trigger_depth' }, diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 2b3cf6d8569..047dc90134f 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1302,6 +1302,18 @@ pg_aios| SELECT pid, f_localmem, f_buffered FROM pg_get_aios() pg_get_aios(pid, io_id, io_generation, state, operation, off, length, target, handle_data_len, raw_result, result, target_desc, f_sync, f_localmem, f_buffered); +pg_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_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_available_extension_versions| SELECT e.name, e.version, (x.extname IS NOT NULL) AS installed, -- 2.50.1 (Apple Git-155) --/vKBiHkEvphli8Mi-- ^ permalink raw reply [nested|flat] 4+ messages in thread
* [PATCH v9 2/2] add pg_stat_autovacuum_scores system view @ 2026-04-02 20:47 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Nathan Bossart @ 2026-04-02 20:47 UTC (permalink / raw) --- doc/src/sgml/maintenance.sgml | 6 ++ doc/src/sgml/monitoring.sgml | 137 +++++++++++++++++++++++++++ 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, 247 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..34e3051c400 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,133 @@ 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. 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. 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 + <literal>1.0</literal> 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 + <literal>1.0</literal> 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 + <literal>1.0</literal> 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 + <literal>1.0</literal> 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 + <literal>1.0</literal> 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) --VsQ7gtq8Z1iRN55c-- ^ permalink raw reply [nested|flat] 4+ messages in thread
* [PATCH v10 2/2] add pg_stat_autovacuum_scores system view @ 2026-04-02 20:47 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Nathan Bossart @ 2026-04-02 20:47 UTC (permalink / raw) --- 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) --Zo6zvSwSGWTStc7v-- ^ permalink raw reply [nested|flat] 4+ messages in thread
* [PATCH v12 2/3] add pg_stat_autovacuum_scores system view @ 2026-04-02 20:47 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Nathan Bossart @ 2026-04-02 20:47 UTC (permalink / raw) --- 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) --wLga2yfBuT3a3SD4 Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=v12-0003-add-booleans-to-view.patch ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-04-02 20:47 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-04-02 20:47 [PATCH v9 2/2] add pg_stat_autovacuum_scores system view Nathan Bossart <[email protected]> 2026-04-02 20:47 [PATCH v10 2/2] add pg_stat_autovacuum_scores system view Nathan Bossart <[email protected]> 2026-04-02 20:47 [PATCH v12 2/3] add pg_stat_autovacuum_scores system view Nathan Bossart <[email protected]> 2026-04-02 20:47 [PATCH v8 5/5] add pg_stat_autovacuum_scores system view Nathan Bossart <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox