Add wal_fpi_bytes to pg_stat_wal and pg_stat_get_backend_wal()

This new counter, called "wal_fpi_bytes", tracks the total amount in
bytes of full page images (FPIs) generated in WAL.  This data becomes
available globally via pg_stat_wal, and for backend statistics via
pg_stat_get_backend_wal().

Previously, this information could only be retrieved with pg_waldump or
pg_walinspect, which may not be available depending on the environment,
and are expensive to execute.  It offers hints about how much FPIs
impact the WAL generated, which could be a large percentage for some
workloads, as well as the effects of wal_compression or page holes.

Bump catalog version.
Bump PGSTAT_FILE_FORMAT_ID, due to the addition of wal_fpi_bytes in
PgStat_WalCounters.

Author: Shinya Kato <shinya11.kato@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/CAOzEurQtZEAfg6P0kU3Wa-f9BWQOi0RzJEMPN56wNTOmJLmfaQ@mail.gmail.com

12 files changed, 46 insertions, 15 deletions

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index d5f0fb7ba7c..f3bf527d5b4 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3325,6 +3325,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
 
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>wal_fpi_bytes</structfield> <type>numeric</type>
+      </para>
+      <para>
+       Total amount of WAL full page images in bytes
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>wal_buffers_full</structfield> <type>bigint</type>
       </para>
       <para>
diff --git a/src/backend/access/transam/xloginsert.c b/src/backend/access/transam/xloginsert.c
index 496e0fa4ac6..b3abf386f80 100644
--- a/src/backend/access/transam/xloginsert.c
+++ b/src/backend/access/transam/xloginsert.c
@@ -33,12 +33,14 @@
 #include "access/xloginsert.h"
 #include "catalog/pg_control.h"
 #include "common/pg_lzcompress.h"
+#include "executor/instrument.h"
 #include "miscadmin.h"
 #include "pg_trace.h"
 #include "replication/origin.h"
 #include "storage/bufmgr.h"
 #include "storage/proc.h"
 #include "utils/memutils.h"
+#include "utils/pgstat_internal.h"
 
 /*
  * Guess the maximum buffer size required to store a compressed version of
@@ -796,6 +798,10 @@ XLogRecordAssemble(RmgrId rmid, uint8 info,
 			}
 
 			total_len += bimg.length;
+
+			/* Track the WAL full page images in bytes */
+			pgWalUsage.wal_fpi_bytes += bimg.length;
+			pgstat_report_fixed = true;
 		}
 
 		if (needs_data)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 823776c1498..dec8df4f8ee 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1221,6 +1221,7 @@ CREATE VIEW pg_stat_wal AS
         w.wal_records,
         w.wal_fpi,
         w.wal_bytes,
+        w.wal_fpi_bytes,
         w.wal_buffers_full,
         w.stats_reset
     FROM pg_stat_get_wal() w;
diff --git a/src/backend/executor/instrument.c b/src/backend/executor/instrument.c
index 56e635f4700..9e11c662a7c 100644
--- a/src/backend/executor/instrument.c
+++ b/src/backend/executor/instrument.c
@@ -280,6 +280,7 @@ WalUsageAdd(WalUsage *dst, WalUsage *add)
 	dst->wal_bytes += add->wal_bytes;
 	dst->wal_records += add->wal_records;
 	dst->wal_fpi += add->wal_fpi;
+	dst->wal_fpi_bytes += add->wal_fpi_bytes;
 	dst->wal_buffers_full += add->wal_buffers_full;
 }
 
@@ -289,5 +290,6 @@ WalUsageAccumDiff(WalUsage *dst, const WalUsage *add, const WalUsage *sub)
 	dst->wal_bytes += add->wal_bytes - sub->wal_bytes;
 	dst->wal_records += add->wal_records - sub->wal_records;
 	dst->wal_fpi += add->wal_fpi - sub->wal_fpi;
+	dst->wal_fpi_bytes += add->wal_fpi_bytes - sub->wal_fpi_bytes;
 	dst->wal_buffers_full += add->wal_buffers_full - sub->wal_buffers_full;
 }
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index a864ae8e6a6..199ba2cc17a 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -252,6 +252,7 @@ pgstat_flush_backend_entry_wal(PgStat_EntryRef *entry_ref)
 	WALSTAT_ACC(wal_records, wal_usage_diff);
 	WALSTAT_ACC(wal_fpi, wal_usage_diff);
 	WALSTAT_ACC(wal_bytes, wal_usage_diff);
+	WALSTAT_ACC(wal_fpi_bytes, wal_usage_diff);
 #undef WALSTAT_ACC
 
 	/*
diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c
index 0d04480d2f6..d4edb8b5733 100644
--- a/src/backend/utils/activity/pgstat_wal.c
+++ b/src/backend/utils/activity/pgstat_wal.c
@@ -121,6 +121,7 @@ pgstat_wal_flush_cb(bool nowait)
 	WALSTAT_ACC(wal_records, wal_usage_diff);
 	WALSTAT_ACC(wal_fpi, wal_usage_diff);
 	WALSTAT_ACC(wal_bytes, wal_usage_diff);
+	WALSTAT_ACC(wal_fpi_bytes, wal_usage_diff);
 	WALSTAT_ACC(wal_buffers_full, wal_usage_diff);
 #undef WALSTAT_ACC
 
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 1fe33df2756..a710508979e 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1637,7 +1637,7 @@ static Datum
 pg_stat_wal_build_tuple(PgStat_WalCounters wal_counters,
 						TimestampTz stat_reset_timestamp)
 {
-#define PG_STAT_WAL_COLS	5
+#define PG_STAT_WAL_COLS	6
 	TupleDesc	tupdesc;
 	Datum		values[PG_STAT_WAL_COLS] = {0};
 	bool		nulls[PG_STAT_WAL_COLS] = {0};
@@ -1651,9 +1651,11 @@ pg_stat_wal_build_tuple(PgStat_WalCounters wal_counters,
 					   INT8OID, -1, 0);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 3, "wal_bytes",
 					   NUMERICOID, -1, 0);
-	TupleDescInitEntry(tupdesc, (AttrNumber) 4, "wal_buffers_full",
+	TupleDescInitEntry(tupdesc, (AttrNumber) 4, "wal_fpi_bytes",
+					   NUMERICOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 5, "wal_buffers_full",
 					   INT8OID, -1, 0);
-	TupleDescInitEntry(tupdesc, (AttrNumber) 5, "stats_reset",
+	TupleDescInitEntry(tupdesc, (AttrNumber) 6, "stats_reset",
 					   TIMESTAMPTZOID, -1, 0);
 
 	BlessTupleDesc(tupdesc);
@@ -1669,12 +1671,18 @@ pg_stat_wal_build_tuple(PgStat_WalCounters wal_counters,
 									ObjectIdGetDatum(0),
 									Int32GetDatum(-1));
 
-	values[3] = Int64GetDatum(wal_counters.wal_buffers_full);
+	snprintf(buf, sizeof buf, UINT64_FORMAT, wal_counters.wal_fpi_bytes);
+	values[3] = DirectFunctionCall3(numeric_in,
+									CStringGetDatum(buf),
+									ObjectIdGetDatum(0),
+									Int32GetDatum(-1));
+
+	values[4] = Int64GetDatum(wal_counters.wal_buffers_full);
 
 	if (stat_reset_timestamp != 0)
-		values[4] = TimestampTzGetDatum(stat_reset_timestamp);
+		values[5] = TimestampTzGetDatum(stat_reset_timestamp);
 	else
-		nulls[4] = true;
+		nulls[5] = true;
 
 	/* Returns the record as Datum */
 	PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 1b0b16a343f..18e95179ab6 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202510221
+#define CATALOG_VERSION_NO	202510281
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index eecb43ec6f0..9121a382f76 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6029,16 +6029,16 @@
 { oid => '1136', descr => 'statistics: information about WAL activity',
   proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's',
   proparallel => 'r', prorettype => 'record', proargtypes => '',
-  proallargtypes => '{int8,int8,numeric,int8,timestamptz}',
-  proargmodes => '{o,o,o,o,o}',
-  proargnames => '{wal_records,wal_fpi,wal_bytes,wal_buffers_full,stats_reset}',
+  proallargtypes => '{int8,int8,numeric,numeric,int8,timestamptz}',
+  proargmodes => '{o,o,o,o,o,o}',
+  proargnames => '{wal_records,wal_fpi,wal_bytes,wal_fpi_bytes,wal_buffers_full,stats_reset}',
   prosrc => 'pg_stat_get_wal' },
 { oid => '6313', descr => 'statistics: backend WAL activity',
   proname => 'pg_stat_get_backend_wal', provolatile => 'v', proparallel => 'r',
   prorettype => 'record', proargtypes => 'int4',
-  proallargtypes => '{int4,int8,int8,numeric,int8,timestamptz}',
-  proargmodes => '{i,o,o,o,o,o}',
-  proargnames => '{backend_pid,wal_records,wal_fpi,wal_bytes,wal_buffers_full,stats_reset}',
+  proallargtypes => '{int4,int8,int8,numeric,numeric,int8,timestamptz}',
+  proargmodes => '{i,o,o,o,o,o,o}',
+  proargnames => '{backend_pid,wal_records,wal_fpi,wal_bytes,wal_fpi_bytes,wal_buffers_full,stats_reset}',
   prosrc => 'pg_stat_get_backend_wal' },
 { oid => '6248', descr => 'statistics: information about WAL prefetching',
   proname => 'pg_stat_get_recovery_prefetch', prorows => '1', proretset => 't',
diff --git a/src/include/executor/instrument.h b/src/include/executor/instrument.h
index 03653ab6c6c..ffe470f2b84 100644
--- a/src/include/executor/instrument.h
+++ b/src/include/executor/instrument.h
@@ -53,6 +53,7 @@ typedef struct WalUsage
 	int64		wal_records;	/* # of WAL records produced */
 	int64		wal_fpi;		/* # of WAL full page images produced */
 	uint64		wal_bytes;		/* size of WAL records produced */
+	uint64		wal_fpi_bytes;	/* size of WAL full page images produced */
 	int64		wal_buffers_full;	/* # of times the WAL buffers became full */
 } WalUsage;
 
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index bc8077cbae6..7ae503e71a2 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -212,7 +212,7 @@ typedef struct PgStat_TableXactStatus
  * ------------------------------------------------------------
  */
 
-#define PGSTAT_FILE_FORMAT_ID	0x01A5BCB9
+#define PGSTAT_FILE_FORMAT_ID	0x01A5BCBA
 
 typedef struct PgStat_ArchiverStats
 {
@@ -473,6 +473,7 @@ typedef struct PgStat_WalCounters
 	PgStat_Counter wal_records;
 	PgStat_Counter wal_fpi;
 	uint64		wal_bytes;
+	uint64		wal_fpi_bytes;
 	PgStat_Counter wal_buffers_full;
 } PgStat_WalCounters;
 
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 16753b2e4c0..77e25ca029e 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2306,9 +2306,10 @@ pg_stat_user_tables| SELECT relid,
 pg_stat_wal| SELECT wal_records,
     wal_fpi,
     wal_bytes,
+    wal_fpi_bytes,
     wal_buffers_full,
     stats_reset
-   FROM pg_stat_get_wal() w(wal_records, wal_fpi, wal_bytes, wal_buffers_full, stats_reset);
+   FROM pg_stat_get_wal() w(wal_records, wal_fpi, wal_bytes, wal_fpi_bytes, wal_buffers_full, stats_reset);
 pg_stat_wal_receiver| SELECT pid,
     status,
     receive_start_lsn,