0003-Extended-vacuum-statistics-missed-dead-tuples-and-pa.patch

From 3abb75ddc121acec0970c7f469417f0ef637dfa2 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <alenka.rybakina@gmail.com>
Date: Tue, 16 Jun 2026 10:41:24 +0300
Subject: [PATCH 3/8] Extended vacuum statistics: missed dead tuples and pages
 for tables

Expose the counters for dead tuples that VACUUM could not remove because it
failed to acquire a cleanup lock on their heap page, with documentation and
regression coverage:

  missed_dead_tuples  dead tuples skipped because their page could not be
                      cleanup-locked
  missed_dead_pages   heap pages that contained such skipped dead tuples
---
 doc/src/sgml/system-views.sgml                | 16 +++++++
 src/backend/access/heap/vacuumlazy.c          |  2 +
 src/backend/catalog/system_views.sql          |  4 +-
 src/backend/utils/activity/pgstat_vacuum.c    |  2 +
 src/backend/utils/adt/pgstatfuncs.c           |  4 +-
 src/include/catalog/pg_proc.dat               |  6 +--
 src/include/pgstat.h                          |  4 ++
 .../vacuum-extending-in-repetable-read.out    | 46 +++++++++++++++----
 .../vacuum-extending-in-repetable-read.spec   | 28 +++++++++--
 src/test/regress/expected/rules.out           |  6 ++-
 src/test/regress/expected/vacuum_stats.out    | 12 +++++
 src/test/regress/sql/vacuum_stats.sql         |  8 ++++
 12 files changed, 116 insertions(+), 22 deletions(-)

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index bdcd8d9f47..b96c653929 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -5876,6 +5876,22 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
        Number of dead tuples that are still visible to some transaction and therefore could not yet be removed.
       </para></entry>
      </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>missed_dead_pages</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of heap pages containing dead tuples that the vacuum could not remove because it failed to acquire a cleanup lock.
+      </para></entry>
+     </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>missed_dead_tuples</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of dead tuples that the vacuum could not remove because it failed to acquire a cleanup lock on their page.
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 3c403e86f0..7abc83dbfd 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -631,6 +631,8 @@ accumulate_heap_vacuum_statistics(LVRelState *vacrel,
 	extVacStats->common.tuples_deleted = vacrel->tuples_deleted;
 	extVacStats->table.tuples_frozen = vacrel->tuples_frozen;
 	extVacStats->table.recently_dead_tuples = vacrel->recently_dead_tuples;
+	extVacStats->table.missed_dead_pages = vacrel->missed_dead_pages;
+	extVacStats->table.missed_dead_tuples = vacrel->missed_dead_tuples;
 }
 
 /*
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 01487f1665..6f697ab390 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1570,7 +1570,9 @@ CREATE VIEW pg_stat_vacuum_tables AS
         S.pages_removed AS pages_removed,
         S.tuples_deleted AS tuples_deleted,
         S.tuples_frozen AS tuples_frozen,
-        S.recently_dead_tuples AS recently_dead_tuples
+        S.recently_dead_tuples AS recently_dead_tuples,
+        S.missed_dead_pages AS missed_dead_pages,
+        S.missed_dead_tuples AS missed_dead_tuples
 
     FROM pg_class C JOIN
             pg_namespace N ON N.oid = C.relnamespace,
diff --git a/src/backend/utils/activity/pgstat_vacuum.c b/src/backend/utils/activity/pgstat_vacuum.c
index 5a625132dd..8cc505ea86 100644
--- a/src/backend/utils/activity/pgstat_vacuum.c
+++ b/src/backend/utils/activity/pgstat_vacuum.c
@@ -54,6 +54,8 @@ pgstat_accumulate_extvac_stats_relations(PgStat_VacuumRelationCounts *dst,
 		ACCUMULATE_SUBFIELD(table, pages_removed);
 		ACCUMULATE_SUBFIELD(table, tuples_frozen);
 		ACCUMULATE_SUBFIELD(table, recently_dead_tuples);
+		ACCUMULATE_SUBFIELD(table, missed_dead_pages);
+		ACCUMULATE_SUBFIELD(table, missed_dead_tuples);
 	}
 	else if (dst->type == PGSTAT_EXTVAC_INDEX)
 	{
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index d6927d945d..742f4974d5 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2374,7 +2374,7 @@ pg_stat_have_stats(PG_FUNCTION_ARGS)
 Datum
 pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_VACUUM_TABLES_STATS_COLS 6
+#define PG_STAT_GET_VACUUM_TABLES_STATS_COLS 8
 
 	Oid			relid = PG_GETARG_OID(0);
 	PgStat_VacuumRelationCounts *extvacuum;
@@ -2405,6 +2405,8 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
 	values[i++] = Int64GetDatum(extvacuum->common.tuples_deleted);
 	values[i++] = Int64GetDatum(extvacuum->table.tuples_frozen);
 	values[i++] = Int64GetDatum(extvacuum->table.recently_dead_tuples);
+	values[i++] = Int64GetDatum(extvacuum->table.missed_dead_pages);
+	values[i++] = Int64GetDatum(extvacuum->table.missed_dead_tuples);
 
 	Assert(i == PG_STAT_GET_VACUUM_TABLES_STATS_COLS);
 
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6bc3bd909b..6d683413a4 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12643,9 +12643,9 @@
   proname => 'pg_stat_get_vacuum_tables', prorows => 1000, provolatile => 's', prorettype => 'record', proisstrict => 'f',
   proretset => 't',
   proargtypes => 'oid',
-  proallargtypes => '{oid,oid,int8,int8,int8,int8,int8}',
-  proargmodes => '{i,o,o,o,o,o,o}',
-  proargnames => '{reloid,relid,pages_scanned,pages_removed,tuples_deleted,tuples_frozen,recently_dead_tuples}',
+  proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8}',
+  proargmodes => '{i,o,o,o,o,o,o,o,o}',
+  proargnames => '{reloid,relid,pages_scanned,pages_removed,tuples_deleted,tuples_frozen,recently_dead_tuples,missed_dead_pages,missed_dead_tuples}',
   prosrc => 'pg_stat_get_vacuum_tables' }
 
 # oid8 related functions
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 404e7d0297..bdcf758441 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -218,6 +218,10 @@ typedef struct PgStat_VacuumRelationCounts
 										 * VM) */
 			int64		pages_removed;	/* heap pages removed by vacuum
 										 * "truncation" */
+			int64		missed_dead_pages;	/* pages with missed dead tuples */
+			int64		missed_dead_tuples; /* tuples not pruned by vacuum due
+											 * to failure to get a cleanup
+											 * lock */
 		}			table;
 		struct
 		{
diff --git a/src/test/isolation/expected/vacuum-extending-in-repetable-read.out b/src/test/isolation/expected/vacuum-extending-in-repetable-read.out
index a9d977c520..3eb038b9ad 100644
--- a/src/test/isolation/expected/vacuum-extending-in-repetable-read.out
+++ b/src/test/isolation/expected/vacuum-extending-in-repetable-read.out
@@ -1,6 +1,6 @@
-Parsed test spec with 2 sessions
+Parsed test spec with 3 sessions
 
-starting permutation: s1_begin_repeatable_read s2_delete s2_vacuum s2_print_vacuum_stats_table s1_commit s2_vacuum s2_print_vacuum_stats_table
+starting permutation: s1_begin_repeatable_read s2_delete s2_vacuum s2_print_vacuum_stats_table s1_commit pinholder_cursor s2_vacuum s2_print_vacuum_stats_table pinholder_commit s2_vacuum s2_print_vacuum_stats_table
 step s1_begin_repeatable_read: 
   BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   SELECT count(*) FROM test_vacuum_stat_isolation;
@@ -14,25 +14,51 @@ step s2_delete: DELETE FROM test_vacuum_stat_isolation WHERE id <= 10;
 step s2_vacuum: VACUUM test_vacuum_stat_isolation;
 step s2_print_vacuum_stats_table: 
     SELECT
-    vt.relname, vt.tuples_deleted, vt.recently_dead_tuples
+    vt.relname, vt.tuples_deleted, vt.recently_dead_tuples,
+    vt.missed_dead_tuples, vt.missed_dead_pages
     FROM pg_stat_vacuum_tables vt, pg_class c
     WHERE vt.relname = 'test_vacuum_stat_isolation' AND vt.relid = c.oid;
 
-relname                   |tuples_deleted|recently_dead_tuples
---------------------------+--------------+--------------------
-test_vacuum_stat_isolation|             0|                  10
+relname                   |tuples_deleted|recently_dead_tuples|missed_dead_tuples|missed_dead_pages
+--------------------------+--------------+--------------------+------------------+-----------------
+test_vacuum_stat_isolation|             0|                  10|                 0|                0
 (1 row)
 
 step s1_commit: COMMIT;
+step pinholder_cursor: 
+  BEGIN;
+  DECLARE c CURSOR FOR SELECT 1 AS dummy FROM test_vacuum_stat_isolation;
+  FETCH NEXT FROM c;
+
+dummy
+-----
+    1
+(1 row)
+
+step s2_vacuum: VACUUM test_vacuum_stat_isolation;
+step s2_print_vacuum_stats_table: 
+    SELECT
+    vt.relname, vt.tuples_deleted, vt.recently_dead_tuples,
+    vt.missed_dead_tuples, vt.missed_dead_pages
+    FROM pg_stat_vacuum_tables vt, pg_class c
+    WHERE vt.relname = 'test_vacuum_stat_isolation' AND vt.relid = c.oid;
+
+relname                   |tuples_deleted|recently_dead_tuples|missed_dead_tuples|missed_dead_pages
+--------------------------+--------------+--------------------+------------------+-----------------
+test_vacuum_stat_isolation|             0|                  10|                10|                1
+(1 row)
+
+step pinholder_commit: COMMIT;
 step s2_vacuum: VACUUM test_vacuum_stat_isolation;
 step s2_print_vacuum_stats_table: 
     SELECT
-    vt.relname, vt.tuples_deleted, vt.recently_dead_tuples
+    vt.relname, vt.tuples_deleted, vt.recently_dead_tuples,
+    vt.missed_dead_tuples, vt.missed_dead_pages
     FROM pg_stat_vacuum_tables vt, pg_class c
     WHERE vt.relname = 'test_vacuum_stat_isolation' AND vt.relid = c.oid;
 
-relname                   |tuples_deleted|recently_dead_tuples
---------------------------+--------------+--------------------
-test_vacuum_stat_isolation|            10|                  10
+relname                   |tuples_deleted|recently_dead_tuples|missed_dead_tuples|missed_dead_pages
+--------------------------+--------------+--------------------+------------------+-----------------
+test_vacuum_stat_isolation|            10|                  10|                10|                1
 (1 row)
 
diff --git a/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec b/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
index 50b79715f3..334846193d 100644
--- a/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
+++ b/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
@@ -1,9 +1,11 @@
-# Test for recently_dead_tuples in pg_stat_vacuum_tables.
+# Test for recently_dead_tuples and missed_dead_tuples/missed_dead_pages in
+# pg_stat_vacuum_tables.
 #
 # A tuple deleted while an older snapshot can still see it is counted as
-# recently_dead_tuples, because VACUUM is not allowed to remove it yet; once
-# the older snapshot is gone, the next VACUUM removes it and counts it in
-# tuples_deleted instead.
+# recently_dead_tuples, because VACUUM is not allowed to remove it yet.  Once
+# the tuple becomes removable but VACUUM cannot acquire a cleanup lock on its
+# heap page (because another session pins the page), it is counted instead as
+# missed_dead_tuples, and its heap page as missed_dead_pages.
 
 setup
 {
@@ -30,6 +32,17 @@ step s1_begin_repeatable_read
 }
 step s1_commit			{ COMMIT; }
 
+# Holds a pin on the table's single heap page, so VACUUM cannot get a
+# cleanup lock on it:
+session pinholder
+step pinholder_cursor
+{
+  BEGIN;
+  DECLARE c CURSOR FOR SELECT 1 AS dummy FROM test_vacuum_stat_isolation;
+  FETCH NEXT FROM c;
+}
+step pinholder_commit	{ COMMIT; }
+
 # Performs the DML, the vacuums and prints the collected statistics:
 session s2
 setup		{ SET track_vacuum_statistics TO 'on'; }
@@ -38,7 +51,8 @@ step s2_vacuum			{ VACUUM test_vacuum_stat_isolation; }
 step s2_print_vacuum_stats_table
 {
     SELECT
-    vt.relname, vt.tuples_deleted, vt.recently_dead_tuples
+    vt.relname, vt.tuples_deleted, vt.recently_dead_tuples,
+    vt.missed_dead_tuples, vt.missed_dead_pages
     FROM pg_stat_vacuum_tables vt, pg_class c
     WHERE vt.relname = 'test_vacuum_stat_isolation' AND vt.relid = c.oid;
 }
@@ -49,5 +63,9 @@ permutation
     s2_vacuum
     s2_print_vacuum_stats_table
     s1_commit
+    pinholder_cursor
+    s2_vacuum
+    s2_print_vacuum_stats_table
+    pinholder_commit
     s2_vacuum
     s2_print_vacuum_stats_table
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 40c6aca96e..a2b0472a2d 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2441,10 +2441,12 @@ pg_stat_vacuum_tables| SELECT n.nspname AS schemaname,
     s.pages_removed,
     s.tuples_deleted,
     s.tuples_frozen,
-    s.recently_dead_tuples
+    s.recently_dead_tuples,
+    s.missed_dead_pages,
+    s.missed_dead_tuples
    FROM (pg_class c
      JOIN pg_namespace n ON ((n.oid = c.relnamespace))),
-    LATERAL pg_stat_get_vacuum_tables(c.oid) s(relid, pages_scanned, pages_removed, tuples_deleted, tuples_frozen, recently_dead_tuples)
+    LATERAL pg_stat_get_vacuum_tables(c.oid) s(relid, pages_scanned, pages_removed, tuples_deleted, tuples_frozen, recently_dead_tuples, missed_dead_pages, missed_dead_tuples)
   WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
 pg_stat_wal| SELECT wal_records,
     wal_fpi,
diff --git a/src/test/regress/expected/vacuum_stats.out b/src/test/regress/expected/vacuum_stats.out
index c3079e3379..f342f71c57 100644
--- a/src/test/regress/expected/vacuum_stats.out
+++ b/src/test/regress/expected/vacuum_stats.out
@@ -54,6 +54,18 @@ SELECT tuples_frozen > 0 AS tuples_frozen
 (1 row)
 
 DROP TABLE vacstat_freeze;
+-- dead tuples/pages that could not be removed because the page was pinned by
+-- another backend (cleanup lock not acquired); none here, since this VACUUM
+-- runs without concurrent activity.  The non-zero path is covered by the
+-- vacuum-extending-in-repetable-read isolation test.
+SELECT missed_dead_pages = 0 AS missed_dead_pages,
+       missed_dead_tuples = 0 AS missed_dead_tuples
+  FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_t';
+ missed_dead_pages | missed_dead_tuples 
+-------------------+--------------------
+ t                 | t
+(1 row)
+
 -- per-index view: the primary key index is processed by the same VACUUM.
 -- No btree leaf empties out (interleaved deletions), so pages_deleted = 0,
 -- while every index entry for a removed heap tuple is deleted.
diff --git a/src/test/regress/sql/vacuum_stats.sql b/src/test/regress/sql/vacuum_stats.sql
index 04696ed824..b6f0f55af0 100644
--- a/src/test/regress/sql/vacuum_stats.sql
+++ b/src/test/regress/sql/vacuum_stats.sql
@@ -44,6 +44,14 @@ SELECT tuples_frozen > 0 AS tuples_frozen
   FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_freeze';
 DROP TABLE vacstat_freeze;
 
+-- dead tuples/pages that could not be removed because the page was pinned by
+-- another backend (cleanup lock not acquired); none here, since this VACUUM
+-- runs without concurrent activity.  The non-zero path is covered by the
+-- vacuum-extending-in-repetable-read isolation test.
+SELECT missed_dead_pages = 0 AS missed_dead_pages,
+       missed_dead_tuples = 0 AS missed_dead_tuples
+  FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_t';
+
 -- per-index view: the primary key index is processed by the same VACUUM.
 -- No btree leaf empties out (interleaved deletions), so pages_deleted = 0,
 -- while every index entry for a removed heap tuple is deleted.
-- 
2.39.5 (Apple Git-154)