Hi! Thank you for your attention to this patch!
On 16.03.2026 11:34, Андрей Зубков wrote:
Really it was "revocations", but I'm agree with Andrey that naming
isn't clear. *_vm_cleared looks better, but talking about naming here
"vm" meaning is not clear. I think it will be understood as visibility
map, but it is "mark" really. Maybe "*_pages_marks_cleared" will be
better?
Also a macro in pgstat.h:733 and pgstat.h:738 still holds "_rev_".
Good catch, fixed.
I think the docs description needs a little correction:
- visible_pages_vm_cleared. I think listing of possible DML operations
is not needed here, also it seems a high rate of this counter has no
direct relation to the index only scans because we can have very
agressive vacuum on a table that will do the opposite. It will hold
few pages without visibility marks constantly but with the cost
of high visible_pages_vm_cleared rate. My proposition follows:
Number of times the all-visible bit in the
<link linkend="storage-vm">visibility map</link> was cleared for a
pages of this table. The all-visible bit of a heap page is cleared
every time backend process modifies a page previously marked
all-visible by vacuum. Vacuum process must process page once again
on the next run. A high rate of change of this counter means that
vacuum should re-do its work on this table.
- frozen_pages_vm_cleared:
Number of times the all-frozen bit in the
<link linkend="storage-vm">visibility map</link> was cleared for a
pages of this table. The all-frozen bit of a heap page is cleared
every time backend process modifies a page previously marked
all-frozen by vacuum. Vacuum process must process page once again on
the next freeze run on this table.
I agree, this description is clearer. Fixed.
-----------
Best regards,
Alena Rybakina
From bd7cbd4450512aaf640156e977faa28e5095d33a Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Mon, 16 Mar 2026 14:55:45 +0300
Subject: [PATCH] Track table VM stability.
Add rev_all_visible_pages and rev_all_frozen_pages counters to
pg_stat_all_tables tracking the number of times the all-visible and
all-frozen bits are cleared in the visibility map. These bits are cleared by
backend processes during regular DML operations. Hence, the counters are placed
in table statistic entry.
A high rev_all_visible_pages rate relative to DML volume indicates
that modifications are scattered across previously-clean pages rather
than concentrated on already-dirty ones, causing index-only scans to
fall back to heap fetches. A high rev_all_frozen_pages rate indicates
that vacuum's freezing work is being frequently undone by concurrent
DML.
Authors: Alena Rybakina <[email protected]>,
Andrei Lepikhov <[email protected]>,
Andrei Zubkov <[email protected]>
Reviewed-by: Dilip Kumar <[email protected]>,
Masahiko Sawada <[email protected]>,
Ilia Evdokimov <[email protected]>,
Jian He <[email protected]>,
Kirill Reshke <[email protected]>,
Alexander Korotkov <[email protected]>,
Jim Nasby <[email protected]>,
Sami Imseih <[email protected]>,
Karina Litskevich <[email protected]>
---
doc/src/sgml/monitoring.sgml | 32 ++++++++
src/backend/access/heap/visibilitymap.c | 10 +++
src/backend/catalog/system_views.sql | 4 +-
src/backend/utils/activity/pgstat_relation.c | 2 +
src/backend/utils/adt/pgstatfuncs.c | 6 ++
src/include/catalog/pg_proc.dat | 10 +++
src/include/pgstat.h | 17 ++++-
.../expected/vacuum-extending-freeze.out | 50 +++++++++++++
src/test/isolation/isolation_schedule | 1 +
.../specs/vacuum-extending-freeze.spec | 73 +++++++++++++++++++
src/test/regress/expected/rules.out | 12 ++-
11 files changed, 212 insertions(+), 5 deletions(-)
create mode 100644 src/test/isolation/expected/vacuum-extending-freeze.out
create mode 100644 src/test/isolation/specs/vacuum-extending-freeze.spec
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 9c5c6dc490f..0b27558686e 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4258,6 +4258,38 @@ description | Waiting for a newly initialized WAL file
to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>visible_pages_cleared</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times the all-visible bit in the
+ <link linkend="storage-vm">visibility map</link> was cleared for
+ pages of this table. The all-visible bit of a heap page is
+ cleared whenever a backend process modifies a page that was
+ previously marked all-visible by <command>VACUUM</command>. The
+ page must then be processed again by <command>VACUUM</command> on a
+ subsequent run. A high rate of change in this counter means that
+ <command>VACUUM</command> has to repeatedly re-process pages of this
+ table.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>frozen_pages_cleared</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times the all-frozen bit in the
+ <link linkend="storage-vm">visibility map</link> was cleared for
+ pages of this table. The all-frozen bit of a heap page is cleared
+ whenever a backend process modifies a page that was previously
+ marked all-frozen by <command>VACUUM</command>. The page must then
+ be processed again by <command>VACUUM</command> on the next freeze
+ run for this table.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>last_vacuum</structfield> <type>timestamp with time
zone</type>
diff --git a/src/backend/access/heap/visibilitymap.c
b/src/backend/access/heap/visibilitymap.c
index e21b96281a6..7b3ab6244d0 100644
--- a/src/backend/access/heap/visibilitymap.c
+++ b/src/backend/access/heap/visibilitymap.c
@@ -92,6 +92,7 @@
#include "access/xloginsert.h"
#include "access/xlogutils.h"
#include "miscadmin.h"
+#include "pgstat.h"
#include "port/pg_bitutils.h"
#include "storage/bufmgr.h"
#include "storage/smgr.h"
@@ -163,6 +164,15 @@ visibilitymap_clear(Relation rel, BlockNumber heapBlk,
Buffer vmbuf, uint8 flags
if (map[mapByte] & mask)
{
+ /*
+ * Track how often all-visible or all-frozen bits are cleared
in the
+ * visibility map.
+ */
+ if (map[mapByte] & ((flags & VISIBILITYMAP_ALL_VISIBLE) <<
mapOffset))
+ pgstat_count_visible_pages_cleared(rel);
+ if (map[mapByte] & ((flags & VISIBILITYMAP_ALL_FROZEN) <<
mapOffset))
+ pgstat_count_frozen_pages_cleared(rel);
+
map[mapByte] &= ~mask;
MarkBufferDirty(vmbuf);
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index 90d48bc9c80..9ff013ac797 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -741,7 +741,9 @@ CREATE VIEW pg_stat_all_tables AS
pg_stat_get_total_autovacuum_time(C.oid) AS total_autovacuum_time,
pg_stat_get_total_analyze_time(C.oid) AS total_analyze_time,
pg_stat_get_total_autoanalyze_time(C.oid) AS
total_autoanalyze_time,
- pg_stat_get_stat_reset_time(C.oid) AS stats_reset
+ pg_stat_get_stat_reset_time(C.oid) AS stats_reset,
+ pg_stat_get_visible_pages_cleared(C.oid) AS visible_pages_cleared,
+ pg_stat_get_frozen_pages_cleared(C.oid) AS frozen_pages_cleared
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
diff --git a/src/backend/utils/activity/pgstat_relation.c
b/src/backend/utils/activity/pgstat_relation.c
index bc8c43b96aa..78936aca82e 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -879,6 +879,8 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool
nowait)
tabentry->blocks_fetched += lstats->counts.blocks_fetched;
tabentry->blocks_hit += lstats->counts.blocks_hit;
+ tabentry->visible_pages_cleared += lstats->counts.visible_pages_cleared;
+ tabentry->frozen_pages_cleared += lstats->counts.frozen_pages_cleared;
/* Clamp live_tuples in case of negative delta_live_tuples */
tabentry->live_tuples = Max(tabentry->live_tuples, 0);
diff --git a/src/backend/utils/adt/pgstatfuncs.c
b/src/backend/utils/adt/pgstatfuncs.c
index 5ac022274a7..d50b7233c0e 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -107,6 +107,12 @@ PG_STAT_GET_RELENTRY_INT64(tuples_updated)
/* pg_stat_get_vacuum_count */
PG_STAT_GET_RELENTRY_INT64(vacuum_count)
+/* pg_stat_get_visible_pages_cleared */
+PG_STAT_GET_RELENTRY_INT64(visible_pages_cleared)
+
+/* pg_stat_get_frozen_pages_cleared */
+PG_STAT_GET_RELENTRY_INT64(frozen_pages_cleared)
+
#define PG_STAT_GET_RELENTRY_FLOAT8(stat)
\
Datum
\
CppConcat(pg_stat_get_,stat)(PG_FUNCTION_ARGS)
\
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 361e2cfffeb..b52e463e63f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12833,4 +12833,14 @@
proname => 'hashoid8extended', prorettype => 'int8',
proargtypes => 'oid8 int8', prosrc => 'hashoid8extended' },
+{ oid => '8002',
+ descr => 'statistics: number of times the all-visible pages in the
visibility map were cleared for pages of this table',
+ proname => 'pg_stat_get_visible_pages_cleared', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_visible_pages_cleared' },
+{ oid => '8003',
+ descr => 'statistics: number of times the all-frozen pages in the visibility
map were cleared for pages of this table',
+ proname => 'pg_stat_get_frozen_pages_cleared', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_frozen_pages_cleared' },
]
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 216b93492ba..8116d0959de 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -159,6 +159,8 @@ typedef struct PgStat_TableCounts
PgStat_Counter blocks_fetched;
PgStat_Counter blocks_hit;
+ PgStat_Counter visible_pages_cleared;
+ PgStat_Counter frozen_pages_cleared;
} PgStat_TableCounts;
/* ----------
@@ -217,7 +219,7 @@ typedef struct PgStat_TableXactStatus
* ------------------------------------------------------------
*/
-#define PGSTAT_FILE_FORMAT_ID 0x01A5BCBB
+#define PGSTAT_FILE_FORMAT_ID 0x01A5BCBC
typedef struct PgStat_ArchiverStats
{
@@ -450,6 +452,8 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter blocks_fetched;
PgStat_Counter blocks_hit;
+ PgStat_Counter visible_pages_cleared;
+ PgStat_Counter frozen_pages_cleared;
TimestampTz last_vacuum_time; /* user initiated vacuum */
PgStat_Counter vacuum_count;
@@ -725,6 +729,17 @@ extern void pgstat_report_analyze(Relation rel,
if (pgstat_should_count_relation(rel))
\
(rel)->pgstat_info->counts.blocks_hit++;
\
} while (0)
+/* count revocations of all-visible and all-frozen bits in visibility map */
+#define pgstat_count_visible_pages_cleared(rel)
\
+ do {
\
+ if (pgstat_should_count_relation(rel))
\
+ (rel)->pgstat_info->counts.visible_pages_cleared++;
\
+ } while (0)
+#define pgstat_count_frozen_pages_cleared(rel)
\
+ do {
\
+ if (pgstat_should_count_relation(rel))
\
+ (rel)->pgstat_info->counts.frozen_pages_cleared++;
\
+ } while (0)
extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n);
extern void pgstat_count_heap_update(Relation rel, bool hot, bool newpage);
diff --git a/src/test/isolation/expected/vacuum-extending-freeze.out
b/src/test/isolation/expected/vacuum-extending-freeze.out
new file mode 100644
index 00000000000..58b51570e5e
--- /dev/null
+++ b/src/test/isolation/expected/vacuum-extending-freeze.out
@@ -0,0 +1,50 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1_initial_vacuum s2_vacuum s1_get_set_vm_flags_stats
s1_update_table s1_get_cleared_vm_flags_stats
+pg_stat_force_next_flush
+------------------------
+
+(1 row)
+
+step s1_initial_vacuum:
+ SELECT pg_stat_force_next_flush();
+
+pg_stat_force_next_flush
+------------------------
+
+(1 row)
+
+step s2_vacuum:
+ VACUUM vestat;
+
+step s1_get_set_vm_flags_stats:
+ SELECT relallfrozen > 0 AS relallfrozen_pos,
+ relallvisible > 0 AS relallvisible_pos
+ FROM pg_class c
+ WHERE c.relname = 'vestat';
+
+relallfrozen_pos|relallvisible_pos
+----------------+-----------------
+t |t
+(1 row)
+
+step s1_update_table:
+ UPDATE vestat SET x = x + 1001;
+ SELECT pg_stat_force_next_flush();
+
+pg_stat_force_next_flush
+------------------------
+
+(1 row)
+
+step s1_get_cleared_vm_flags_stats:
+ SELECT visible_pages_cleared > 0 AS visible_pages_cleared,
+ frozen_pages_cleared > 0 AS frozen_pages_cleared
+ FROM pg_stat_all_tables
+ WHERE relname = 'vestat';
+
+visible_pages_cleared|frozen_pages_cleared
+---------------------+--------------------
+t |t
+(1 row)
+
diff --git a/src/test/isolation/isolation_schedule
b/src/test/isolation/isolation_schedule
index 4e466580cd4..81e68f85d88 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -124,3 +124,4 @@ test: serializable-parallel-2
test: serializable-parallel-3
test: matview-write-skew
test: lock-nowait
+test: vacuum-extending-freeze
diff --git a/src/test/isolation/specs/vacuum-extending-freeze.spec
b/src/test/isolation/specs/vacuum-extending-freeze.spec
new file mode 100644
index 00000000000..b8f8c177595
--- /dev/null
+++ b/src/test/isolation/specs/vacuum-extending-freeze.spec
@@ -0,0 +1,73 @@
+# In short, this test validates the correctness and stability of cumulative
+# vacuum statistics accounting around freezing, visibility, and revision
+# tracking across VACUUM and backend operations.
+
+setup
+{
+ CREATE TABLE vestat (x int)
+ WITH (autovacuum_enabled = off, fillfactor = 70);
+
+ INSERT INTO vestat
+ SELECT i FROM generate_series(1, 5000) AS g(i);
+
+ ANALYZE vestat;
+
+ -- Ensure stats are flushed before starting the scenario
+ SELECT pg_stat_force_next_flush();
+}
+
+teardown
+{
+ DROP TABLE IF EXISTS vestat;
+ RESET vacuum_freeze_min_age;
+ RESET vacuum_freeze_table_age;
+
+}
+
+session s1
+
+step s1_initial_vacuum
+{
+ SELECT pg_stat_force_next_flush();
+}
+
+step s1_get_set_vm_flags_stats
+{
+ SELECT relallfrozen > 0 AS relallfrozen_pos,
+ relallvisible > 0 AS relallvisible_pos
+ FROM pg_class c
+ WHERE c.relname = 'vestat';
+}
+
+step s1_get_cleared_vm_flags_stats
+{
+ SELECT visible_pages_cleared > 0 AS visible_pages_cleared,
+ frozen_pages_cleared > 0 AS frozen_pages_cleared
+ FROM pg_stat_all_tables
+ WHERE relname = 'vestat';
+}
+
+session s2
+setup
+{
+ -- Configure aggressive freezing vacuum behavior
+ SET vacuum_freeze_min_age = 0;
+ SET vacuum_freeze_table_age = 0;
+}
+step s2_vacuum
+{
+ VACUUM vestat;
+}
+
+step s1_update_table
+{
+ UPDATE vestat SET x = x + 1001;
+ SELECT pg_stat_force_next_flush();
+}
+
+permutation
+ s1_initial_vacuum
+ s2_vacuum
+ s1_get_set_vm_flags_stats
+ s1_update_table
+ s1_get_cleared_vm_flags_stats
diff --git a/src/test/regress/expected/rules.out
b/src/test/regress/expected/rules.out
index 71d7262049e..b36b551d877 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1846,7 +1846,9 @@ pg_stat_all_tables| SELECT c.oid AS relid,
pg_stat_get_total_autovacuum_time(c.oid) AS total_autovacuum_time,
pg_stat_get_total_analyze_time(c.oid) AS total_analyze_time,
pg_stat_get_total_autoanalyze_time(c.oid) AS total_autoanalyze_time,
- pg_stat_get_stat_reset_time(c.oid) AS stats_reset
+ pg_stat_get_stat_reset_time(c.oid) AS stats_reset,
+ pg_stat_get_visible_pages_cleared(c.oid) AS visible_pages_cleared,
+ pg_stat_get_frozen_pages_cleared(c.oid) AS frozen_pages_cleared
FROM ((pg_class c
LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
@@ -2298,7 +2300,9 @@ pg_stat_sys_tables| SELECT relid,
total_autovacuum_time,
total_analyze_time,
total_autoanalyze_time,
- stats_reset
+ stats_reset,
+ visible_pages_cleared,
+ frozen_pages_cleared
FROM pg_stat_all_tables
WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name,
'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
pg_stat_user_functions| SELECT p.oid AS funcid,
@@ -2353,7 +2357,9 @@ pg_stat_user_tables| SELECT relid,
total_autovacuum_time,
total_analyze_time,
total_autoanalyze_time,
- stats_reset
+ stats_reset,
+ visible_pages_cleared,
+ frozen_pages_cleared
FROM pg_stat_all_tables
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name,
'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
pg_stat_wal| SELECT wal_records,
--
2.39.5 (Apple Git-154)
