v2-0001-Add-mode-and-reason-columns-to-pg_stat_progress_v.patch
application/octet-stream
Filename: v2-0001-Add-mode-and-reason-columns-to-pg_stat_progress_v.patch
Type: application/octet-stream
Part: 0
From e7f9e07f8a69b3675d85cc71472508ced047f80c Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 24 Oct 2025 13:36:33 +0900
Subject: [PATCH v2] Add mode and reason columns to pg_stat_progress_vacuum
This commit enhances the pg_stat_progress_vacuum view by adding mode and
reason columns, specify the vacuum's behavior mode and the reason it was
initiated.
The mode can be one of the following:
- normal
- aggressive
- failsafe
The reason can be one of the following:
- manual
- autovacuum
- anti-wraparound
These allow administrators to better understand the context and urgency
of vacuum operations.
Author: Shinya Kato <shinya11.kato@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-by: Nathan Bossart <nathandbossart@gmail.com>
Reviewed-by: Robert Treat <rob@xzilla.net>
Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com>
Reviewed-by: Sami Imseih <samimseih@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discusssion: https://postgr.es/m/CAOzEurQcOY-OBL_ouEVfEaFqe_md3vB5pXjR_m6L71Dcp1JKCQ@mail.gmail.com
---
doc/src/sgml/monitoring.sgml | 67 ++++++++++++++++++++++++++++
src/backend/access/heap/vacuumlazy.c | 24 ++++++++--
src/backend/catalog/system_views.sql | 10 ++++-
src/include/commands/progress.h | 12 +++++
src/test/regress/expected/rules.out | 14 +++++-
5 files changed, 121 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index d5f0fb7ba7c..e22773a26f1 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -6667,6 +6667,73 @@ FROM pg_stat_get_backend_idset() AS backendid;
stale.
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>mode</structfield> <type>text</type>
+ </para>
+ <para>
+ The mode of the current vacuum operation. Possible values are:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>normal</literal>: A standard vacuum operation that is not
+ required to be aggressive and has not entered failsafe mode.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>aggressive</literal>: A vacuum that must scan the entire
+ table because <xref linkend="guc-vacuum-freeze-table-age"/> or
+ <xref linkend="guc-vacuum-freeze-min-age"/> (or the corresponding
+ per-table storage parameters) required it, or because page skipping
+ was disabled via <command>VACUUM (DISABLE_PAGE_SKIPPING)</command>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>failsafe</literal>: A vacuum operation that entered failsafe
+ mode when the system was at risk of transaction ID or multixact ID
+ wraparound (see <xref linkend="guc-vacuum-failsafe-age"/> and
+ <xref linkend="guc-vacuum-multixact-failsafe-age"/>).
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>reason</structfield> <type>text</type>
+ </para>
+ <para>
+ The reason why the current vacuum started. Possible values are:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>manual</literal>: Initiated by an explicit
+ <command>VACUUM</command> command.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>autovacuum</literal>: Launched by autovacuum based on
+ <xref linkend="guc-autovacuum-vacuum-threshold"/> or
+ <xref linkend="guc-autovacuum-vacuum-insert-threshold"/>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>anti-wraparound</literal>: Launched by autovacuum to avoid
+ transaction ID or multixact ID wraparound (see
+ <xref linkend="vacuum-for-wraparound"/> as well as
+ <xref linkend="guc-autovacuum-freeze-max-age"/> and
+ <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>).
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index d2b031fdd06..71d3b55e42e 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -664,6 +664,16 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
pgstat_progress_start_command(PROGRESS_COMMAND_VACUUM,
RelationGetRelid(rel));
+ pgstat_progress_update_param(PROGRESS_VACUUM_MODE,
+ PROGRESS_VACUUM_MODE_NORMAL);
+ if (AmAutoVacuumWorkerProcess())
+ pgstat_progress_update_param(PROGRESS_VACUUM_REASON,
+ params.is_wraparound
+ ? PROGRESS_VACUUM_REASON_ANTI_WRAPAROUND
+ : PROGRESS_VACUUM_REASON_AUTOVACUUM);
+ else
+ pgstat_progress_update_param(PROGRESS_VACUUM_REASON,
+ PROGRESS_VACUUM_REASON_MANUAL);
/*
* Setup error traceback support for ereport() first. The idea is to set
@@ -787,6 +797,9 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
* to increase the number of dead tuples it can prune away.)
*/
vacrel->aggressive = vacuum_get_cutoffs(rel, params, &vacrel->cutoffs);
+ if (vacrel->aggressive)
+ pgstat_progress_update_param(PROGRESS_VACUUM_MODE,
+ PROGRESS_VACUUM_MODE_AGGRESSIVE);
vacrel->rel_pages = orig_rel_pages = RelationGetNumberOfBlocks(rel);
vacrel->vistest = GlobalVisTestFor(rel);
@@ -808,6 +821,8 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
* visibility map (even those set all-frozen)
*/
vacrel->aggressive = true;
+ pgstat_progress_update_param(PROGRESS_VACUUM_MODE,
+ PROGRESS_VACUUM_MODE_AGGRESSIVE);
skipwithvm = false;
}
@@ -2995,9 +3010,10 @@ lazy_check_wraparound_failsafe(LVRelState *vacrel)
{
const int progress_index[] = {
PROGRESS_VACUUM_INDEXES_TOTAL,
- PROGRESS_VACUUM_INDEXES_PROCESSED
+ PROGRESS_VACUUM_INDEXES_PROCESSED,
+ PROGRESS_VACUUM_MODE
};
- int64 progress_val[2] = {0, 0};
+ int64 progress_val[3] = {0, 0, PROGRESS_VACUUM_MODE_FAILSAFE};
VacuumFailsafeActive = true;
@@ -3013,8 +3029,8 @@ lazy_check_wraparound_failsafe(LVRelState *vacrel)
vacrel->do_index_cleanup = false;
vacrel->do_rel_truncate = false;
- /* Reset the progress counters */
- pgstat_progress_update_multi_param(2, progress_index, progress_val);
+ /* Reset the progress counters and the mode */
+ pgstat_progress_update_multi_param(3, progress_index, progress_val);
ereport(WARNING,
(errmsg("bypassing nonessential maintenance of table \"%s.%s.%s\" as a failsafe after %d index scans",
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 823776c1498..3fffd7db3b9 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1264,7 +1264,15 @@ CREATE VIEW pg_stat_progress_vacuum AS
S.param6 AS max_dead_tuple_bytes, S.param7 AS dead_tuple_bytes,
S.param8 AS num_dead_item_ids, S.param9 AS indexes_total,
S.param10 AS indexes_processed,
- S.param11 / 1000000::double precision AS delay_time
+ S.param11 / 1000000::double precision AS delay_time,
+ CASE S.param12 WHEN 1 THEN 'normal'
+ WHEN 2 THEN 'aggressive'
+ WHEN 3 THEN 'failsafe'
+ ELSE NULL END AS mode,
+ CASE S.param13 WHEN 1 THEN 'manual'
+ WHEN 2 THEN 'autovacuum'
+ WHEN 3 THEN 'anti-wraparound'
+ ELSE NULL END AS reason
FROM pg_stat_get_progress_info('VACUUM') AS S
LEFT JOIN pg_database D ON S.datid = D.oid;
diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h
index 1cde4bd9bcf..bfbe4713488 100644
--- a/src/include/commands/progress.h
+++ b/src/include/commands/progress.h
@@ -29,6 +29,8 @@
#define PROGRESS_VACUUM_INDEXES_TOTAL 8
#define PROGRESS_VACUUM_INDEXES_PROCESSED 9
#define PROGRESS_VACUUM_DELAY_TIME 10
+#define PROGRESS_VACUUM_MODE 11
+#define PROGRESS_VACUUM_REASON 12
/* Phases of vacuum (as advertised via PROGRESS_VACUUM_PHASE) */
#define PROGRESS_VACUUM_PHASE_SCAN_HEAP 1
@@ -38,6 +40,16 @@
#define PROGRESS_VACUUM_PHASE_TRUNCATE 5
#define PROGRESS_VACUUM_PHASE_FINAL_CLEANUP 6
+/* Modes of vacuum (as advertised via PROGRESS_VACUUM_MODE) */
+#define PROGRESS_VACUUM_MODE_NORMAL 1
+#define PROGRESS_VACUUM_MODE_AGGRESSIVE 2
+#define PROGRESS_VACUUM_MODE_FAILSAFE 3
+
+/* Reasons for vacuum (as advertised via PROGRESS_VACUUM_REASON) */
+#define PROGRESS_VACUUM_REASON_MANUAL 1
+#define PROGRESS_VACUUM_REASON_AUTOVACUUM 2
+#define PROGRESS_VACUUM_REASON_ANTI_WRAPAROUND 3
+
/* Progress parameters for analyze */
#define PROGRESS_ANALYZE_PHASE 0
#define PROGRESS_ANALYZE_BLOCKS_TOTAL 1
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 16753b2e4c0..3d513823a58 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2104,7 +2104,19 @@ pg_stat_progress_vacuum| SELECT s.pid,
s.param8 AS num_dead_item_ids,
s.param9 AS indexes_total,
s.param10 AS indexes_processed,
- ((s.param11)::double precision / (1000000)::double precision) AS delay_time
+ ((s.param11)::double precision / (1000000)::double precision) AS delay_time,
+ CASE s.param12
+ WHEN 1 THEN 'normal'::text
+ WHEN 2 THEN 'aggressive'::text
+ WHEN 3 THEN 'failsafe'::text
+ ELSE NULL::text
+ END AS mode,
+ CASE s.param13
+ WHEN 1 THEN 'manual'::text
+ WHEN 2 THEN 'autovacuum'::text
+ WHEN 3 THEN 'anti-wraparound'::text
+ ELSE NULL::text
+ END AS reason
FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
LEFT JOIN pg_database d ON ((s.datid = d.oid)));
pg_stat_recovery_prefetch| SELECT stats_reset,
--
2.47.3