Hi, I would like to propose adding new fields to pg_stat_all_tables to track tables skipped during autovacuum and autoanalyze.
Currently, when autovacuum or autoanalyze is skipped because a lock cannot be acquired, this information is only emitted in log messages. However, it would be more useful if users could access this information via a system view, alongside related fields such as last_autovacuum, on a per-table basis. The attached patch add the following fields to pg_stat_all_tables: - last_skipped_autovacuum - last_skipped_autoanalyze - skipped_autovacuum_count - skipped_autoanalyze_count Are there any concerns about exposing this in pg_stat_all_tables, or suggestions for a better approach? Regards, Yugo Nagata -- Yugo Nagata <[email protected]>
>From e09abee64334a061716d5e8cb698e18a7db16ade Mon Sep 17 00:00:00 2001 From: Yugo Nagata <[email protected]> Date: Tue, 24 Mar 2026 13:09:00 +0900 Subject: [PATCH] Track skipped tables during autovacuum and autoanalyze --- src/backend/catalog/system_views.sql | 4 ++ src/backend/commands/vacuum.c | 10 +++ src/backend/utils/activity/pgstat_relation.c | 66 ++++++++++++++++++++ src/backend/utils/adt/pgstatfuncs.c | 12 ++++ src/include/catalog/pg_proc.dat | 16 +++++ src/include/pgstat.h | 8 +++ src/test/regress/expected/rules.out | 12 ++++ 7 files changed, 128 insertions(+) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index f1ed7b58f13..914ba762b68 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -737,12 +737,16 @@ CREATE VIEW pg_stat_all_tables AS pg_stat_get_ins_since_vacuum(C.oid) AS n_ins_since_vacuum, pg_stat_get_last_vacuum_time(C.oid) as last_vacuum, pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum, + pg_stat_get_last_skipped_autovacuum_time(C.oid) as last_skipped_autovacuum, pg_stat_get_last_analyze_time(C.oid) as last_analyze, pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze, + pg_stat_get_last_skipped_autoanalyze_time(C.oid) as last_skipped_autoanalyze, pg_stat_get_vacuum_count(C.oid) AS vacuum_count, pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count, + pg_stat_get_skipped_autovacuum_count(C.oid) AS skipped_autovacuum_count, pg_stat_get_analyze_count(C.oid) AS analyze_count, pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count, + pg_stat_get_skipped_autoanalyze_count(C.oid) AS skipped_autoanalyze_count, pg_stat_get_total_vacuum_time(C.oid) AS total_vacuum_time, pg_stat_get_total_autovacuum_time(C.oid) AS total_autovacuum_time, pg_stat_get_total_analyze_time(C.oid) AS total_analyze_time, diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index bce3a2daa24..5a0b5db39ad 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -829,10 +829,15 @@ vacuum_open_relation(Oid relid, RangeVar *relation, bits32 options, if ((options & VACOPT_VACUUM) != 0) { if (!rel_lock) + { ereport(elevel, (errcode(ERRCODE_LOCK_NOT_AVAILABLE), errmsg("skipping vacuum of \"%s\" --- lock not available", relation->relname))); + + if (AmAutoVacuumWorkerProcess()) + pgstat_report_skipped_vacuum(relid); + } else ereport(elevel, (errcode(ERRCODE_UNDEFINED_TABLE), @@ -850,10 +855,15 @@ vacuum_open_relation(Oid relid, RangeVar *relation, bits32 options, if ((options & VACOPT_ANALYZE) != 0) { if (!rel_lock) + { ereport(elevel, (errcode(ERRCODE_LOCK_NOT_AVAILABLE), errmsg("skipping analyze of \"%s\" --- lock not available", relation->relname))); + + if (AmAutoVacuumWorkerProcess()) + pgstat_report_skipped_analyze(relid); + } else ereport(elevel, (errcode(ERRCODE_UNDEFINED_TABLE), diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index bc8c43b96aa..1329f7d9bf1 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -271,6 +271,72 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples, (void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO); } +/* + * Report that the table was skipped during autovacuum. + */ +void +pgstat_report_skipped_vacuum(Oid relid) +{ + PgStat_EntryRef *entry_ref; + PgStatShared_Relation *shtabentry; + PgStat_StatTabEntry *tabentry; + TimestampTz ts; + + Assert(AmAutoVacuumWorkerProcess()); + + if (!pgstat_track_counts) + return; + + /* Store the data in the table's hash table entry. */ + ts = GetCurrentTimestamp(); + + /* block acquiring lock for the same reason as pgstat_report_autovac() */ + entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION, MyDatabaseId, + relid, false); + + shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats; + tabentry = &shtabentry->stats; + + + tabentry->last_skipped_autovacuum_time = ts; + tabentry->skipped_autovacuum_count++; + + pgstat_unlock_entry(entry_ref); +} + +/* + * Report that the table was skipped during autoanalyze. + */ +void +pgstat_report_skipped_analyze(Oid relid) +{ + PgStat_EntryRef *entry_ref; + PgStatShared_Relation *shtabentry; + PgStat_StatTabEntry *tabentry; + TimestampTz ts; + + Assert(AmAutoVacuumWorkerProcess()); + + if (!pgstat_track_counts) + return; + + /* Store the data in the table's hash table entry. */ + ts = GetCurrentTimestamp(); + + /* block acquiring lock for the same reason as pgstat_report_autovac() */ + entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION, MyDatabaseId, + relid, false); + + shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats; + tabentry = &shtabentry->stats; + + + tabentry->last_skipped_autoanalyze_time = ts; + tabentry->skipped_autoanalyze_count++; + + pgstat_unlock_entry(entry_ref); +} + /* * Report that the table was just analyzed and flush IO statistics. * diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 5f907335990..ae8e8fd90f6 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -84,6 +84,12 @@ PG_STAT_GET_RELENTRY_INT64(mod_since_analyze) /* pg_stat_get_numscans */ PG_STAT_GET_RELENTRY_INT64(numscans) +/* pg_stat_get_skipped_autoanalyze_count */ +PG_STAT_GET_RELENTRY_INT64(skipped_autoanalyze_count) + +/* pg_stat_get_skipped_autovacuum_count */ +PG_STAT_GET_RELENTRY_INT64(skipped_autovacuum_count) + /* pg_stat_get_tuples_deleted */ PG_STAT_GET_RELENTRY_INT64(tuples_deleted) @@ -170,6 +176,12 @@ PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_vacuum_time) /* pg_stat_get_lastscan */ PG_STAT_GET_RELENTRY_TIMESTAMPTZ(lastscan) +/* pg_stat_get_last_skipped_autoanalyze_time */ +PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_skipped_autoanalyze_time) + +/* pg_stat_get_last_skipped_autovacuum_time */ +PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_skipped_autovacuum_time) + /* pg_stat_get_stat_reset_time */ PG_STAT_GET_RELENTRY_TIMESTAMPTZ(stat_reset_time) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 84e7adde0e5..3ca1d6762f1 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5667,6 +5667,22 @@ proname => 'pg_stat_get_total_autoanalyze_time', provolatile => 's', proparallel => 'r', prorettype => 'float8', proargtypes => 'oid', prosrc => 'pg_stat_get_total_autoanalyze_time' }, +{ oid => '8142', descr => 'statistics: last skipped auto analyze time for a table', + proname => 'pg_stat_get_last_skipped_autoanalyze_time', provolatile => 's', + proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid', + prosrc => 'pg_stat_get_last_skipped_autoanalyze_time' }, +{ oid => '8143', descr => 'statistics: last skipped auto vacuum time for a table', + proname => 'pg_stat_get_last_skipped_autovacuum_time', provolatile => 's', + proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid', + prosrc => 'pg_stat_get_last_skipped_autovacuum_time' }, +{ oid => '8144', descr => 'statistics: number of skipped auto analyzes for a table', + proname => 'pg_stat_get_skipped_autoanalyze_count', provolatile => 's', + proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', + prosrc => 'pg_stat_get_skipped_autoanalyze_count' }, +{ oid => '8145', descr => 'statistics: number of skipped auto vacuum for a table', + proname => 'pg_stat_get_skipped_autovacuum_count', provolatile => 's', + proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', + prosrc => 'pg_stat_get_skipped_autovacuum_count' }, { 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/include/pgstat.h b/src/include/pgstat.h index 216b93492ba..bd6be54dfb3 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -460,6 +460,11 @@ typedef struct PgStat_StatTabEntry TimestampTz last_autoanalyze_time; /* autovacuum initiated */ PgStat_Counter autoanalyze_count; + TimestampTz last_skipped_autovacuum_time; /* autovacuum initiated */ + PgStat_Counter skipped_autovacuum_count; + TimestampTz last_skipped_autoanalyze_time; /* autovacuum initiated */ + PgStat_Counter skipped_autoanalyze_count; + PgStat_Counter total_vacuum_time; /* times in milliseconds */ PgStat_Counter total_autovacuum_time; PgStat_Counter total_analyze_time; @@ -679,6 +684,9 @@ extern void pgstat_report_analyze(Relation rel, PgStat_Counter livetuples, PgStat_Counter deadtuples, bool resetcounter, TimestampTz starttime); +extern void pgstat_report_skipped_vacuum(Oid relid); +extern void pgstat_report_skipped_analyze(Oid relid); + /* * If stats are enabled, but pending data hasn't been prepared yet, call * pgstat_assoc_relation() to do so. See its comment for why this is done diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 32bea58db2c..1ca129ad2a4 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1836,12 +1836,16 @@ pg_stat_all_tables| SELECT c.oid AS relid, pg_stat_get_ins_since_vacuum(c.oid) AS n_ins_since_vacuum, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, + pg_stat_get_last_skipped_autovacuum_time(c.oid) AS last_skipped_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, + pg_stat_get_last_skipped_autoanalyze_time(c.oid) AS last_skipped_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, + pg_stat_get_skipped_autovacuum_count(c.oid) AS skipped_autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count, + pg_stat_get_skipped_autoanalyze_count(c.oid) AS skipped_autoanalyze_count, pg_stat_get_total_vacuum_time(c.oid) AS total_vacuum_time, pg_stat_get_total_autovacuum_time(c.oid) AS total_autovacuum_time, pg_stat_get_total_analyze_time(c.oid) AS total_analyze_time, @@ -2288,12 +2292,16 @@ pg_stat_sys_tables| SELECT relid, n_ins_since_vacuum, last_vacuum, last_autovacuum, + last_skipped_autovacuum, last_analyze, last_autoanalyze, + last_skipped_autoanalyze, vacuum_count, autovacuum_count, + skipped_autovacuum_count, analyze_count, autoanalyze_count, + skipped_autoanalyze_count, total_vacuum_time, total_autovacuum_time, total_analyze_time, @@ -2343,12 +2351,16 @@ pg_stat_user_tables| SELECT relid, n_ins_since_vacuum, last_vacuum, last_autovacuum, + last_skipped_autovacuum, last_analyze, last_autoanalyze, + last_skipped_autoanalyze, vacuum_count, autovacuum_count, + skipped_autovacuum_count, analyze_count, autoanalyze_count, + skipped_autoanalyze_count, total_vacuum_time, total_autovacuum_time, total_analyze_time, -- 2.43.0
