On 16.03.2026 02:27, Alena Rybakina wrote:
With pg_stat_force_next_flush() they seem stable enough without using
waiting functions.
I returned it back - I noticed that the test has become unstable again
and the same practice is used in stats.sql.
--
-----------
Best regards,
Alena Rybakina
From fcd68e8ddd702d8926d24b8cc0a2335550d58e50 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Mon, 16 Mar 2026 01:55:22 +0300
Subject: [PATCH] Track table VM stability.
Add visible_pages_vm_cleared and frozen_pages_vm_cleared 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 visible_pages_vm_cleared 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]>,
Andrey Borodin <[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 +++++++-
src/test/regress/expected/rules.out | 12 ++++--
src/test/regress/expected/vacuum.out | 45 ++++++++++++++++++++
src/test/regress/sql/vacuum.sql | 21 +++++++++
10 files changed, 154 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 9c5c6dc490f..0ec4ddbd6ee 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_vm_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 a
+ page of this table. The all-visible bit is cleared by backend
+ processes when they modify a heap page that was previously marked
+ all-visible, for example during an <command>INSERT</command>,
+ <command>UPDATE</command>, or <command>DELETE</command>.
+ A high rate of change in this counter means that index-only scans
+ on this table may frequently need to fall back to heap fetches,
+ and that vacuum must re-do visibility map work on those pages.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>frozen_pages_vm_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 a
+ page of this table. The all-frozen bit is cleared by backend
+ processes when they modify a heap page that was previously marked
+ all-frozen. A high value compared to the number of vacuum cycles
+ indicates that DML activity is frequently undoing the freezing work
+ performed by vacuum.
+ </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..11a98482743 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_vm_rev_all_visible(rel);
+ if (map[mapByte] & ((flags & VISIBILITYMAP_ALL_FROZEN) <<
mapOffset))
+ pgstat_count_vm_rev_all_frozen(rel);
+
map[mapByte] &= ~mask;
MarkBufferDirty(vmbuf);
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index 90d48bc9c80..9f8645e4fa7 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_vm_cleared(C.oid) AS
visible_pages_vm_cleared,
+ pg_stat_get_frozen_pages_vm_cleared(C.oid) AS
frozen_pages_vm_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..c8dc61ac341 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_vm_cleared +=
lstats->counts.visible_pages_vm_cleared;
+ tabentry->frozen_pages_vm_cleared +=
lstats->counts.frozen_pages_vm_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..b944851c240 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_vm_cleared */
+PG_STAT_GET_RELENTRY_INT64(visible_pages_vm_cleared)
+
+/* pg_stat_get_frozen_pages_vm_cleared */
+PG_STAT_GET_RELENTRY_INT64(frozen_pages_vm_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..14e4a3c81e1 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_vm_cleared', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_visible_pages_vm_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_vm_cleared', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_frozen_pages_vm_cleared' },
]
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 216b93492ba..70c1c8a41cf 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_vm_cleared;
+ PgStat_Counter frozen_pages_vm_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_vm_cleared;
+ PgStat_Counter frozen_pages_vm_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_vm_rev_all_visible(rel)
\
+ do {
\
+ if (pgstat_should_count_relation(rel))
\
+ (rel)->pgstat_info->counts.visible_pages_vm_cleared++;
\
+ } while (0)
+#define pgstat_count_vm_rev_all_frozen(rel)
\
+ do {
\
+ if (pgstat_should_count_relation(rel))
\
+ (rel)->pgstat_info->counts.frozen_pages_vm_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/regress/expected/rules.out
b/src/test/regress/expected/rules.out
index 71d7262049e..704806b770a 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_vm_cleared(c.oid) AS visible_pages_vm_cleared,
+ pg_stat_get_frozen_pages_vm_cleared(c.oid) AS frozen_pages_vm_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_vm_cleared,
+ frozen_pages_vm_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_vm_cleared,
+ frozen_pages_vm_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,
diff --git a/src/test/regress/expected/vacuum.out
b/src/test/regress/expected/vacuum.out
index d4696bc3325..5b2c44a8a1a 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -730,3 +730,48 @@ SELECT pg_column_toast_chunk_id(f1) = :'id_2_chunk' AS
same_chunk
(1 row)
DROP TABLE vac_rewrite_toast;
+-- Visibility map clear statistics: all_visible/all_frozen bits and vm_cleared
counters
+SET vacuum_freeze_min_age = 0;
+SET vacuum_freeze_table_age = 0;
+CREATE TABLE vacvm_clear_test (x int)
+ WITH (autovacuum_enabled = off, fillfactor = 70);
+INSERT INTO vacvm_clear_test SELECT x FROM generate_series(1, 5000) AS g(x);
+ANALYZE vacvm_clear_test;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+VACUUM vacvm_clear_test;
+SELECT relallvisible > 0 AS all_visible, relallfrozen > 0 AS all_frozen
+ FROM pg_class WHERE relname = 'vacvm_clear_test';
+ all_visible | all_frozen
+-------------+------------
+ t | t
+(1 row)
+
+SELECT visible_pages_vm_cleared = 0 AS visible_pages_vm_cleared,
frozen_pages_vm_cleared = 0 AS frozen_pages_vm_cleared
+ FROM pg_stat_all_tables WHERE relname = 'vacvm_clear_test';
+ visible_pages_vm_cleared | frozen_pages_vm_cleared
+--------------------------+-------------------------
+ t | t
+(1 row)
+
+UPDATE vacvm_clear_test SET x = x + 1001;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT visible_pages_vm_cleared > 0 AS visible_pages_vm_cleared,
frozen_pages_vm_cleared > 0 AS frozen_pages_vm_cleared
+ FROM pg_stat_all_tables WHERE relname = 'vacvm_clear_test';
+ visible_pages_vm_cleared | frozen_pages_vm_cleared
+--------------------------+-------------------------
+ t | t
+(1 row)
+
+DROP TABLE vacvm_clear_test;
+RESET vacuum_freeze_min_age;
+RESET vacuum_freeze_table_age;
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 247b8e23b23..2ffb6fe4c0d 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -525,3 +525,24 @@ SELECT id, pg_column_toast_chunk_id(f1) IS NULL AS
f1_chunk_null,
SELECT pg_column_toast_chunk_id(f1) = :'id_2_chunk' AS same_chunk
FROM vac_rewrite_toast WHERE id = 2;
DROP TABLE vac_rewrite_toast;
+
+-- Visibility map clear statistics: all_visible/all_frozen bits and vm_cleared
counters
+SET vacuum_freeze_min_age = 0;
+SET vacuum_freeze_table_age = 0;
+CREATE TABLE vacvm_clear_test (x int)
+ WITH (autovacuum_enabled = off, fillfactor = 70);
+INSERT INTO vacvm_clear_test SELECT x FROM generate_series(1, 5000) AS g(x);
+ANALYZE vacvm_clear_test;
+SELECT pg_stat_force_next_flush();
+VACUUM vacvm_clear_test;
+SELECT relallvisible > 0 AS all_visible, relallfrozen > 0 AS all_frozen
+ FROM pg_class WHERE relname = 'vacvm_clear_test';
+SELECT visible_pages_vm_cleared = 0 AS visible_pages_vm_cleared,
frozen_pages_vm_cleared = 0 AS frozen_pages_vm_cleared
+ FROM pg_stat_all_tables WHERE relname = 'vacvm_clear_test';
+UPDATE vacvm_clear_test SET x = x + 1001;
+SELECT pg_stat_force_next_flush();
+SELECT visible_pages_vm_cleared > 0 AS visible_pages_vm_cleared,
frozen_pages_vm_cleared > 0 AS frozen_pages_vm_cleared
+ FROM pg_stat_all_tables WHERE relname = 'vacvm_clear_test';
+DROP TABLE vacvm_clear_test;
+RESET vacuum_freeze_min_age;
+RESET vacuum_freeze_table_age;
--
2.39.5 (Apple Git-154)
