Count updates that move row to a new page.

Add pgstat counter to track row updates that result in the successor
version going to a new heap page, leaving behind an original version
whose t_ctid points to the new version.  The current count is shown by
the n_tup_newpage_upd column of each of the pg_stat_*_tables views.

The new n_tup_newpage_upd column complements the existing n_tup_hot_upd
and n_tup_upd columns.  Tables that have high n_tup_newpage_upd values
(relative to n_tup_upd) are good candidates for tuning heap fillfactor.

Corey Huinker, with small tweaks by me.

Author: Corey Huinker <corey.huinker@gmail.com>
Reviewed-By: Peter Geoghegan <pg@bowt.ie>
Reviewed-By: Andres Freund <andres@anarazel.de>
Discussion: https://postgr.es/m/CADkLM=ded21M9iZ36hHm-vj2rE2d=zcKpUQMds__Xm2pxLfHKA@mail.gmail.com

3 files changed, 16 insertions, 4 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index e94528a7c7..69270c313f 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202303181
+#define CATALOG_VERSION_NO	202303231
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5cf87aeb2c..7c358cff16 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5360,6 +5360,11 @@
   proname => 'pg_stat_get_tuples_hot_updated', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_tuples_hot_updated' },
+{ oid => '8614',
+  descr => 'statistics: number of tuples updated onto a new page',
+  proname => 'pg_stat_get_tuples_newpage_updated', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_tuples_newpage_updated' },
 { oid => '2878', descr => 'statistics: number of live tuples',
   proname => 'pg_stat_get_live_tuples', provolatile => 's', proparallel => 'r',
   prorettype => 'int8', proargtypes => 'oid',
@@ -5823,6 +5828,11 @@
   proname => 'pg_stat_get_xact_tuples_hot_updated', provolatile => 'v',
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_xact_tuples_hot_updated' },
+{ oid => '8615',
+  descr => 'statistics: number of tuples updated onto a new page in current transaction',
+  proname => 'pg_stat_get_xact_tuples_newpage_updated', provolatile => 'v',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_xact_tuples_newpage_updated' },
 { oid => '3044',
   descr => 'statistics: number of blocks fetched in current transaction',
   proname => 'pg_stat_get_xact_blocks_fetched', provolatile => 'v',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 1e418b682b..6dd14004be 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -151,8 +151,8 @@ typedef struct PgStat_BackendSubEntry
  * the index AM, while tuples_fetched is the number of tuples successfully
  * fetched by heap_fetch under the control of simple indexscans for this index.
  *
- * tuples_inserted/updated/deleted/hot_updated count attempted actions,
- * regardless of whether the transaction committed.  delta_live_tuples,
+ * tuples_inserted/updated/deleted/hot_updated/newpage_updated count attempted
+ * actions, regardless of whether the transaction committed.  delta_live_tuples,
  * delta_dead_tuples, and changed_tuples are set depending on commit or abort.
  * Note that delta_live_tuples and delta_dead_tuples can be negative!
  * ----------
@@ -168,6 +168,7 @@ typedef struct PgStat_TableCounts
 	PgStat_Counter t_tuples_updated;
 	PgStat_Counter t_tuples_deleted;
 	PgStat_Counter t_tuples_hot_updated;
+	PgStat_Counter t_tuples_newpage_updated;
 	bool		t_truncdropped;
 
 	PgStat_Counter t_delta_live_tuples;
@@ -401,6 +402,7 @@ typedef struct PgStat_StatTabEntry
 	PgStat_Counter tuples_updated;
 	PgStat_Counter tuples_deleted;
 	PgStat_Counter tuples_hot_updated;
+	PgStat_Counter tuples_newpage_updated;
 
 	PgStat_Counter live_tuples;
 	PgStat_Counter dead_tuples;
@@ -616,7 +618,7 @@ extern void pgstat_report_analyze(Relation rel,
 	} while (0)
 
 extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n);
-extern void pgstat_count_heap_update(Relation rel, bool hot);
+extern void pgstat_count_heap_update(Relation rel, bool hot, bool newpage);
 extern void pgstat_count_heap_delete(Relation rel);
 extern void pgstat_count_truncate(Relation rel);
 extern void pgstat_update_heap_dead_tuples(Relation rel, int delta);