From ae4fdde1352fa6b2c9123e91435efafc78c370a0 Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <pg@bowt.ie>
Date: Thu, 23 Mar 2023 11:16:17 -0700
Subject: 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
---
 doc/src/sgml/monitoring.sgml | 28 +++++++++++++++++++++++-----
 1 file changed, 23 insertions(+), 5 deletions(-)

(limited to 'doc/src')

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 7ab4424bf13..21e6ce2841e 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4789,7 +4789,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
        <structfield>n_tup_ins</structfield> <type>bigint</type>
       </para>
       <para>
-       Number of rows inserted
+       Total number of rows inserted
       </para></entry>
      </row>
 
@@ -4798,7 +4798,10 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
        <structfield>n_tup_upd</structfield> <type>bigint</type>
       </para>
       <para>
-       Number of rows updated (includes <link linkend="storage-hot">HOT updated rows</link>)
+       Total number of rows updated.  (This includes row updates
+       counted in <structfield>n_tup_hot_upd</structfield> and
+       <structfield>n_tup_newpage_upd</structfield>, and remaining
+       non-<acronym>HOT</acronym> updates.)
       </para></entry>
      </row>
 
@@ -4807,7 +4810,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
        <structfield>n_tup_del</structfield> <type>bigint</type>
       </para>
       <para>
-       Number of rows deleted
+       Total number of rows deleted
       </para></entry>
      </row>
 
@@ -4816,8 +4819,23 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
        <structfield>n_tup_hot_upd</structfield> <type>bigint</type>
       </para>
       <para>
-       Number of rows HOT updated (i.e., with no separate index
-       update required)
+       Number of rows <link linkend="storage-hot">HOT updated</link>.
+       These are updates where no successor versions are required in
+       indexes.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>n_tup_newpage_upd</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of rows updated where the successor version goes onto a
+       <emphasis>new</emphasis> heap page, leaving behind an original
+       version with a
+       <link linkend="storage-tuple-layout"><structfield>t_ctid</structfield>
+        field</link> that points to a different heap page.  These are
+       always non-<acronym>HOT</acronym> updates.
       </para></entry>
      </row>
 
-- 
cgit v1.2.3