0003-Extended-vacuum-statistics-recently-dead-and-missed-.patch

From ffb8a24d9772ac5bc408962750280f9d3886990f 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/9] Extended vacuum statistics: recently-dead and missed dead
 tuples for tables

Expose the counters for dead tuples that VACUUM could not remove, with
documentation and regression coverage:

  recently_dead_tuples  dead tuples still visible to some transaction and
                        therefore not yet removable
  missed_dead_tuples    dead tuples skipped because their heap page could not
                        be cleanup-locked
  missed_dead_pages     heap pages that contained such skipped dead tuples

These non-zero paths require concurrent activity that an ordinary regression
test cannot create deterministically, so they are covered by a dedicated TAP
test, src/test/modules/test_misc/t/014_vacuum_stats.pl: a held REPEATABLE READ
snapshot keeps recently deleted tuples visible (recently_dead_tuples), and a
concurrently pinned heap page prevents cleanup (missed_dead_tuples /
missed_dead_pages).
---
 doc/src/sgml/system-views.sgml                | 24 ++++++
 src/backend/access/heap/vacuumlazy.c          |  3 +
 src/backend/catalog/system_views.sql          |  5 +-
 src/backend/utils/activity/pgstat_vacuum.c    |  3 +
 src/backend/utils/adt/pgstatfuncs.c           |  5 +-
 src/include/catalog/pg_proc.dat               |  6 +-
 src/include/pgstat.h                          |  7 ++
 src/test/modules/test_misc/meson.build        |  1 +
 .../modules/test_misc/t/014_vacuum_stats.pl   | 84 +++++++++++++++++++
 src/test/regress/expected/rules.out           |  7 +-
 src/test/regress/expected/vacuum_stats.out    | 15 ++++
 src/test/regress/sql/vacuum_stats.sql         | 11 +++
 12 files changed, 164 insertions(+), 7 deletions(-)
 create mode 100644 src/test/modules/test_misc/t/014_vacuum_stats.pl

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 19576018df..b96c653929 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -5868,6 +5868,30 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
        Number of tuples frozen by the vacuum.
       </para></entry>
      </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>recently_dead_tuples</structfield> <type>bigint</type>
+      </para>
+      <para>
+       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 ffcfa3d16a..7abc83dbfd 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -630,6 +630,9 @@ accumulate_heap_vacuum_statistics(LVRelState *vacrel,
 	extVacStats->table.pages_removed = vacrel->removed_pages;
 	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 04ca38e51c..6f697ab390 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1569,7 +1569,10 @@ CREATE VIEW pg_stat_vacuum_tables AS
         S.pages_scanned AS pages_scanned,
         S.pages_removed AS pages_removed,
         S.tuples_deleted AS tuples_deleted,
-        S.tuples_frozen AS tuples_frozen
+        S.tuples_frozen AS tuples_frozen,
+        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 ac6baee516..644a732592 100644
--- a/src/backend/utils/activity/pgstat_vacuum.c
+++ b/src/backend/utils/activity/pgstat_vacuum.c
@@ -53,6 +53,9 @@ pgstat_accumulate_extvac_stats_relations(PgStat_VacuumRelationCounts *dst,
 		ACCUMULATE_SUBFIELD(table, pages_scanned);
 		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 e72f76180e..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 5
+#define PG_STAT_GET_VACUUM_TABLES_STATS_COLS 8
 
 	Oid			relid = PG_GETARG_OID(0);
 	PgStat_VacuumRelationCounts *extvacuum;
@@ -2404,6 +2404,9 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
 	values[i++] = Int64GetDatum(extvacuum->table.pages_removed);
 	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 9616a01466..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}',
-  proargmodes => '{i,o,o,o,o,o}',
-  proargnames => '{reloid,relid,pages_scanned,pages_removed,tuples_deleted,tuples_frozen}',
+  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 568624de5b..bdcf758441 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -211,10 +211,17 @@ typedef struct PgStat_VacuumRelationCounts
 		struct
 		{
 			int64		tuples_frozen;	/* tuples frozen up by vacuum */
+			int64		recently_dead_tuples;	/* deleted tuples that are
+												 * still visible to some
+												 * transaction */
 			int64		pages_scanned;	/* heap pages examined (not skipped by
 										 * 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/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 969e90b396..267145163a 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -22,6 +22,7 @@ tests += {
       't/011_lock_stats.pl',
       't/012_ddlutils.pl',
       't/013_temp_obj_multisession.pl',
+      't/014_vacuum_stats.pl',
     ],
     # The injection points are cluster-wide, so disable installcheck
     'runningcheck': false,
diff --git a/src/test/modules/test_misc/t/014_vacuum_stats.pl b/src/test/modules/test_misc/t/014_vacuum_stats.pl
new file mode 100644
index 0000000000..9b64b0996c
--- /dev/null
+++ b/src/test/modules/test_misc/t/014_vacuum_stats.pl
@@ -0,0 +1,84 @@
+# Copyright (c) 2024-2026, PostgreSQL Global Development Group
+
+# Test the recently_dead_tuples and missed_dead_tuples/missed_dead_pages
+# counters of the extended vacuum statistics view pg_stat_vacuum_tables.
+#
+# These counters depend on inter-session visibility and on VACUUM's ability to
+# acquire a cleanup lock, so they cannot be exercised by an ordinary
+# single-session regression test.  A dedicated TAP cluster gives us full
+# control over the removal horizon (no concurrent backends hold it back), which
+# makes the outcome deterministic -- unlike an isolation test running against a
+# shared regression cluster.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->append_conf(
+	'postgresql.conf', qq[
+autovacuum = off
+track_vacuum_statistics = on
+]);
+$node->start;
+
+# A small table that fits on a single heap page, so the deleted tuples and the
+# page pinned by the cursor below are the same page.
+$node->safe_psql(
+	'postgres', qq[
+CREATE TABLE vacstat_iso (id int, ival int) WITH (autovacuum_enabled = off);
+INSERT INTO vacstat_iso SELECT i, i FROM generate_series(1, 50) i;
+]);
+
+# Helper: fetch the four interesting counters for the table.
+my $stats_query = qq[
+SELECT tuples_deleted, recently_dead_tuples, missed_dead_tuples, missed_dead_pages
+  FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_iso'];
+
+# This session first holds an old snapshot (so the deleted tuples stay
+# recently dead), and later pins the heap page (so VACUUM cannot get a cleanup
+# lock and the now-removable tuples are missed instead).
+my $holder = $node->background_psql('postgres', on_error_stop => 1);
+
+# 1. Hold a repeatable-read snapshot that can still see the soon-to-be-deleted
+#    tuples, preventing VACUUM from removing them.
+$holder->query_safe(
+	'BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;'
+	  . ' SELECT count(*) FROM vacstat_iso;');
+
+# 2. Delete ten tuples and vacuum.  They are dead but not yet removable, so
+#    they are counted as recently dead and not removed.
+$node->safe_psql('postgres', 'DELETE FROM vacstat_iso WHERE id <= 10;');
+$node->safe_psql('postgres', 'VACUUM vacstat_iso;');
+is( $node->safe_psql('postgres', $stats_query),
+	"0|10|0|0",
+	'recently_dead_tuples counted while an old snapshot is held');
+
+# 3. Release the old snapshot, then pin the table's single heap page with a
+#    cursor so VACUUM cannot acquire a cleanup lock on it.
+$holder->query_safe('COMMIT;');
+$holder->query_safe(
+	'BEGIN; DECLARE c CURSOR FOR SELECT * FROM vacstat_iso;'
+	  . ' FETCH NEXT FROM c;');
+
+# 4. The deleted tuples are now removable, but the page is pinned, so a plain
+#    VACUUM skips it without a cleanup lock and counts the tuples as missed.
+#    (Counters accumulate, so recently_dead_tuples stays at 10.)
+$node->safe_psql('postgres', 'VACUUM vacstat_iso;');
+is( $node->safe_psql('postgres', $stats_query),
+	"0|10|10|1",
+	'missed_dead_tuples/missed_dead_pages counted while the page is pinned');
+
+# 5. Release the pin and vacuum once more; the tuples are finally removed.
+$holder->query_safe('COMMIT;');
+$node->safe_psql('postgres', 'VACUUM vacstat_iso;');
+is( $node->safe_psql('postgres', $stats_query),
+	"10|10|10|1",
+	'dead tuples removed once the pin is released');
+
+$holder->quit;
+$node->stop;
+done_testing();
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 8f779a92ff..a2b0472a2d 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2440,10 +2440,13 @@ pg_stat_vacuum_tables| SELECT n.nspname AS schemaname,
     s.pages_scanned,
     s.pages_removed,
     s.tuples_deleted,
-    s.tuples_frozen
+    s.tuples_frozen,
+    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)
+    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 cafaa9bdd9..cb11d6381c 100644
--- a/src/test/regress/expected/vacuum_stats.out
+++ b/src/test/regress/expected/vacuum_stats.out
@@ -69,6 +69,21 @@ SELECT tuples_frozen > 0 AS tuples_frozen
 (1 row)
 
 DROP TABLE vacstat_freeze;
+-- dead tuples that survived this vacuum: recently_dead_tuples are still visible
+-- to some transaction, while missed_dead_pages/missed_dead_tuples could not be
+-- removed because the page was pinned by another backend (cleanup lock not
+-- acquired).  None occur here, since this VACUUM runs without concurrent
+-- activity (all = 0).  The non-zero paths are covered by the
+-- vacuum-extending-in-repetable-read isolation test.
+SELECT recently_dead_tuples = 0 AS recently_dead_tuples,
+       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';
+ recently_dead_tuples | missed_dead_pages | missed_dead_tuples 
+----------------------+-------------------+--------------------
+ t                    | 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 6061f433a3..75a5a0052b 100644
--- a/src/test/regress/sql/vacuum_stats.sql
+++ b/src/test/regress/sql/vacuum_stats.sql
@@ -44,6 +44,17 @@ SELECT tuples_frozen > 0 AS tuples_frozen
   FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_freeze';
 DROP TABLE vacstat_freeze;
 
+-- dead tuples that survived this vacuum: recently_dead_tuples are still visible
+-- to some transaction, while missed_dead_pages/missed_dead_tuples could not be
+-- removed because the page was pinned by another backend (cleanup lock not
+-- acquired).  None occur here, since this VACUUM runs without concurrent
+-- activity (all = 0).  The non-zero paths are covered by the
+-- vacuum-extending-in-repetable-read isolation test.
+SELECT recently_dead_tuples = 0 AS recently_dead_tuples,
+       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)