Date: Tue, 24 Mar 2026 15:11:33 +0900
From: Yugo Nagata <nagata@sraoss.co.jp>
To: Pgsql Hackers <pgsql-hackers@postgresql.org>
Subject: Track skipped tables during autovacuum and autoanalyze
Message-Id: <20260324151133.7940a5c1f2ebd594d54da481@sraoss.co.jp>
Content-Type: multipart/mixed;
 boundary="Multipart=_Tue__24_Mar_2026_15_11_33_+0900_PyndxOyIILJh_XYK"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 2
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?us-ascii?Q?YvBSTHjTKIWbrel4KerZs8+eJ13tSxlnXzD4DyzQa+Ugr9BiZLhqw8EEWDkA?=
 =?us-ascii?Q?tlmO5B3/y1njkwItsIsNLeAcQ1ImpE+7KSPtPjMq/dbn7jxH1bxCT4HmjiyR?=
 =?us-ascii?Q?NqfiHUrt2iNMyKvR2usWDwAvSJapgZYw/VzQ1I1CwVGc9wJroHLf8NGxoLE2?=
 =?us-ascii?Q?vUgmRAsF4jCwJQHpIgfaJywI9Tx+RKpv/8q8D2EEqDeTvsx41JYLjxjOCK1S?=
 =?us-ascii?Q?ogb1kesUtGua7cjCBa1tCDum3U1LwwgQiTed3Tgl0hPXqq9/Maw8pEagbHy1?=
 =?us-ascii?Q?/uhUvtyOzdfpZtDNHUdqhIjNMSQt42nXhnKo9VwStlFXAIpF3L5P0DdqsrD6?=
 =?us-ascii?Q?ZFdQN/PbaDjc4ZUASp2bzg3nYAezEq6fefWgGhYFNpi7RNtfUlk7BNq7AGue?=
 =?us-ascii?Q?YwETAznAQyj1R7LM7sbsapQozI9GOD/wO2SFdwp+TYK1qS+W2RVvigaOrArT?=
 =?us-ascii?Q?J+Dq3wKw2OduLOzYd4OFIThO7ruh7GT9XDZReGPWG2L+1iOmnrZ1SAmy42rh?=
 =?us-ascii?Q?NcuGYQyyKdcaaZyj0IXOdIegFadKtJ96D+7yKGVlUKRBee36bevk98malFr1?=
 =?us-ascii?Q?C8tK4nwbWyfKvDGEshqduKP0yRX8JSLuDs6ybzE1lJ6XwxC41cjk42haN/D7?=
 =?us-ascii?Q?OCMCM5cjlDVsRg+KDExdO/BthEg1X/J/Xol+tnepwiUUXLISj+ySlBIViiI1?=
 =?us-ascii?Q?XDQmKzX2H4vsdt8/FSXKd7Ovbt1Kk3Joa5OxrWgBnZzWhQ/E7hloOZjGtkIe?=
 =?us-ascii?Q?O+4jgMdOQHy2smZSUyI8kOVTTHujNONr/sZaueeroNraS4gbOH+KUpwrcZWc?=
 =?us-ascii?Q?oLaA/hJp3poOn2EWS5UNDm1dTmlfxlWN4GpBEg+E9RbdSXnxilD9zmEfKc21?=
 =?us-ascii?Q?T73vZGHmX/fmSFiJZXFSRkVqdMNhejg/py8gXJMM7whUtAEUgw0EQDkFKZdp?=
 =?us-ascii?Q?yqlIYp/lzr3C50KGa1rZOKsBc4Lyy6wuGwDqOwpZh5LO5Hk4HLT6ijLtdu6b?=
 =?us-ascii?Q?fLuinvpjyv28HHJ2SiV6gahZrCsD5J32WlUpBfeV64CjMxondeErI1KO1PZV?=
 =?us-ascii?Q?w1i1fLka365xupfooNgsBmdGleCJFf5p9+x9YeaAT85n0rSCF07L42hdWHF9?=
 =?us-ascii?Q?wfjYI3k9nDiK0WruI+SWAf2sqs2GjlBkME+K9xyaiN/taqom+q7G1SqXSkRm?=
 =?us-ascii?Q?vDwQ44ynSparvEBMhOG3H/iO2fLngFr4bBUkrcXqmq60T+m2I0h47LMg7xVK?=
 =?us-ascii?Q?GTUJVoD3kF0+j6To1SBqDHw/u6L4X9fRj6xd9kfd5Yf7BnLOdYOdudlC5j5a?=
 =?us-ascii?Q?dAb12tO/3yMuHoq9xjfMyn/aj14doPwqMXHCSOt/nPi4+YdBSQoWBX6+FKKD?=
 =?us-ascii?Q?6y7pEzUhMCAQldPp+1tpuf6vEUqiu8/P9gS/YdaOQ3NWAAxRG3G/Mhf4LUM9?=
 =?us-ascii?Q?7a35RDxBmCmpVr4460D7SnonudDZg3Wet8cfl/LtRkZQZ4Qpxs0T+u+J9fvl?=
 =?us-ascii?Q?raS3fVe7e0bNoEKRH477AL0qUv9ssvoG+GUc6A5M8qBtnBzZkzeGqGjshLsS?=
 =?us-ascii?Q?2Wg5q9mnlJfE5hP3SYV7cTZ9GCnYYC9n3yPbOs2X0wkMbBY0P2HrJ3pWKvAV?=
 =?us-ascii?Q?VZ4aMC7gxC5gXh/H1wb492Tq5nOikarQAKlQcFIr7jQKu0SYiJ1mUMG65F7c?=
 =?us-ascii?Q?8IysIqxbIfzsbcNCB5jl7OZEITrMuiDVBgstS7ZvXLHsRrfYAAAwlMTYDEYZ?=
 =?us-ascii?Q?nQw+SYAKuqi0SxV1Y3wd2VT/r0Oy0zmmERE+BWD+zltyzph1fUhDhlw7ie6R?=
X-MS-Exchange-AntiSpam-MessageData-1: Aa4MyXo49dzNfA==
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 42492769-e44e-4622-0b15-08de896c331b
X-MS-Exchange-CrossTenant-AuthSource: OSZP286MB2160.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 24 Mar 2026 06:11:34.1727
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 75db6c32-352e-4063-ae79-cafb8623f6db
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: 
 sI3S2YzAVqLC1VcMbBkgidSR55achXePeuSTxf0CLQy6MxqwdIyOv69JSHxbR3cCFMuxwSXMad1/d5/093GUoA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TY7P286MB6186
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/20260324151133.7940a5c1f2ebd594d54da481%40sraoss.co.jp>
Precedence: bulk

--Multipart=_Tue__24_Mar_2026_15_11_33_+0900_PyndxOyIILJh_XYK
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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 <nagata@sraoss.co.jp>

--Multipart=_Tue__24_Mar_2026_15_11_33_+0900_PyndxOyIILJh_XYK
Content-Type: text/x-diff;
 name="0001-Track-skipped-tables-during-autovacuum-and-autoanaly.patch"
Content-Disposition: attachment;
 filename="0001-Track-skipped-tables-during-autovacuum-and-autoanaly.patch"
Content-Transfer-Encoding: 7bit

From e09abee64334a061716d5e8cb698e18a7db16ade Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nagata@sraoss.co.jp>
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


--Multipart=_Tue__24_Mar_2026_15_11_33_+0900_PyndxOyIILJh_XYK--