On 12/3/26 13:02, Andrei Lepikhov wrote:
On 9/3/26 16:46, Alena Rybakina wrote:
I discovered that my last patches were incorrectly formed. I updated
the correct version.
I see that v29-0001-* is a quite separate feature itself at the moment.
It makes sense to remove the commit message phrase for
vm_new_frozen_pages and vm_new_visible_pages, introduced in later patches.
This patch itself looks good to me.
Since this patch is almost ready for commit, I reviewed it carefully. I
noticed a documentation entry was missing, so I added it. Please see the
attachment.
While updating the patch file, I also made a few small adjustments,
including changing the parameter order in the struct and VIEW. The
commit message is also fixed.
In addition, it makes sense to discuss how these parameters are supposed
to be used. I see the following use cases:
1. Which tables have the most VM churn? - monitoring
rev_all_visible_pages normalised on the table size and its average tuple
width might expose the most suspicious tables (in terms of table
statistics).
2. DML Skew. Dividing rev_all_visible_pages by the number of tuple
updates/deletes, normalised by the average table and tuple sizes, might
indicate whether changes are localised within the table.
3. IndexOnlyScan effectiveness. Considering the speed of
rev_all_visible_pages change, normalised to the value of the
relallvisible statistic, we may detect tables where Index-Only Scan
might be inefficiently used.
Feel free to criticise it or add your own - I’m just a developer, not a
DBA. Also, I’m not sure what use cases there are for the
rev_all_frozen_pages parameter.
--
regards, Andrei Lepikhov,
pgEdge
From 96789144424e991aab44e7c8dfad9db4a2e368e1 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email protected]>
Date: Sat, 28 Feb 2026 18:30:12 +0300
Subject: [PATCH v30] 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 | 2 ++
src/backend/utils/activity/pgstat_relation.c | 2 ++
src/backend/utils/adt/pgstatfuncs.c | 6 ++++
src/include/catalog/pg_proc.dat | 12 +++++++-
src/include/pgstat.h | 17 ++++++++++-
src/test/regress/expected/rules.out | 6 ++++
8 files changed, 85 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index cc014564c97..8ce0d0dd2cb 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4249,6 +4249,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>rev_all_visible_pages</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>rev_all_frozen_pages</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..9ea7a068ef0 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] >> mapOffset & flags &
VISIBILITYMAP_ALL_VISIBLE)
+ pgstat_count_vm_rev_all_visible(rel);
+ if (map[mapByte] >> mapOffset & flags &
VISIBILITYMAP_ALL_FROZEN)
+ 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 339c016e510..1eaf79fdb4e 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -729,6 +729,8 @@ CREATE VIEW pg_stat_all_tables AS
pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze,
pg_stat_get_ins_since_vacuum(C.oid) AS n_ins_since_vacuum,
+ pg_stat_get_rev_all_visible_pages(C.oid) AS rev_all_visible_pages,
+ pg_stat_get_rev_all_frozen_pages(C.oid) AS rev_all_frozen_pages,
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_analyze_time(C.oid) as last_analyze,
diff --git a/src/backend/utils/activity/pgstat_relation.c
b/src/backend/utils/activity/pgstat_relation.c
index bc8c43b96aa..bb26e97898d 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->rev_all_visible_pages += lstats->counts.rev_all_visible_pages;
+ tabentry->rev_all_frozen_pages += lstats->counts.rev_all_frozen_pages;
/* 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..6d7c4cc1ed2 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_rev_all_frozen_pages */
+PG_STAT_GET_RELENTRY_INT64(rev_all_frozen_pages)
+
+/* pg_stat_get_rev_all_visible_pages */
+PG_STAT_GET_RELENTRY_INT64(rev_all_visible_pages)
+
#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..252eab079d6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12831,6 +12831,16 @@
prosrc => 'hashoid8' },
{ oid => '8281', descr => 'hash',
proname => 'hashoid8extended', prorettype => 'int8',
- proargtypes => 'oid8 int8', prosrc => 'hashoid8extended' },
+ proargtypes => 'oid8 int8', prosrc => 'hashoid8extended' },
+{ oid => '8002',
+ descr => 'statistics: number of times the all-visible pages in the
visibility map was removed for pages of table',
+ proname => 'pg_stat_get_rev_all_visible_pages', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_rev_all_visible_pages' },
+{ oid => '8003',
+ descr => 'statistics: number of times the all-frozen pages in the visibility
map was removed for pages of table',
+ proname => 'pg_stat_get_rev_all_frozen_pages', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_rev_all_frozen_pages' },
]
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 216b93492ba..849eea24f29 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 rev_all_visible_pages;
+ PgStat_Counter rev_all_frozen_pages;
} 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 rev_all_visible_pages;
+ PgStat_Counter rev_all_frozen_pages;
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.rev_all_visible_pages++;
\
+ } while (0)
+#define pgstat_count_vm_rev_all_frozen(rel)
\
+ do {
\
+ if (pgstat_should_count_relation(rel))
\
+ (rel)->pgstat_info->counts.rev_all_frozen_pages++;
\
+ } 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 f373ad704b6..4fb3167e99c 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1834,6 +1834,8 @@ pg_stat_all_tables| SELECT c.oid AS relid,
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze,
pg_stat_get_ins_since_vacuum(c.oid) AS n_ins_since_vacuum,
+ pg_stat_get_rev_all_visible_pages(c.oid) AS rev_all_visible_pages,
+ pg_stat_get_rev_all_frozen_pages(c.oid) AS rev_all_frozen_pages,
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_analyze_time(c.oid) AS last_analyze,
@@ -2285,6 +2287,8 @@ pg_stat_sys_tables| SELECT relid,
n_dead_tup,
n_mod_since_analyze,
n_ins_since_vacuum,
+ rev_all_visible_pages,
+ rev_all_frozen_pages,
last_vacuum,
last_autovacuum,
last_analyze,
@@ -2340,6 +2344,8 @@ pg_stat_user_tables| SELECT relid,
n_dead_tup,
n_mod_since_analyze,
n_ins_since_vacuum,
+ rev_all_visible_pages,
+ rev_all_frozen_pages,
last_vacuum,
last_autovacuum,
last_analyze,
--
2.53.0
