v26-0004-Add-system-view-tracking-IO-ops-per-backend-type.patch

From 6d28dbdd174df4da115735c67f2dc3f5ff51555b Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Mon, 4 Jul 2022 15:44:17 -0400
Subject: [PATCH v26 4/4] Add system view tracking IO ops per backend type

Add pg_stat_io, a system view which tracks the number of IOOp (allocs,
extends, fsyncs, reads, and writes) done through each IOPath (shared
buffers, local buffers, strategy buffers) by each type of backend (e.g.
client backend, checkpointer).

Some IOPaths are not used by some BackendTypes and will not be in the
view. For example, checkpointer does not use a BufferAccessStrategy
(currently), so there will be no row for the "strategy" IOPath for
checkpointer.

Some IOOps are invalid in combination with certain IOPaths. Those cells
will be NULL in the view. For example, local buffers are not fsync'd so
cells for all BackendTypes for IOPATH_STRATEGY and IOOP_FSYNC will be
NULL.

View stats are fetched from statistics incremented when a backend
performs an IO Operation and maintained by the cumulative statistics
subsystem.

Each row of the view is stats for a particular BackendType for a
particular IOPath (e.g. shared buffer accesses by checkpointer) and
each column in the view is the total number of IO Operations done (e.g.
writes).
So a cell in the view would be, for example, the number of shared
buffers written by checkpointer since the last stats reset.

Note that some of the cells in the view are redundant with fields in
pg_stat_bgwriter (e.g. buffers_backend), however these have been kept in
pg_stat_bgwriter for backwards compatibility. Deriving the redundant
pg_stat_bgwriter stats from the IO operations stats structures was also
problematic due to the separate reset targets for 'bgwriter' and
'io'.

Suggested by Andres Freund

Author: Melanie Plageman <melanieplageman@gmail.com>
Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>, Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/20200124195226.lth52iydq2n2uilq%40alap3.anarazel.de
---
 doc/src/sgml/monitoring.sgml         | 117 ++++++++++++++++-
 src/backend/catalog/system_views.sql |  12 ++
 src/backend/utils/adt/pgstatfuncs.c  | 110 ++++++++++++++++
 src/include/catalog/pg_proc.dat      |   9 ++
 src/test/regress/expected/rules.out  |   9 ++
 src/test/regress/expected/stats.out  | 190 +++++++++++++++++++++++++++
 src/test/regress/sql/stats.sql       |  91 +++++++++++++
 7 files changed, 537 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index f2066e5f0f..105e86d678 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -448,6 +448,15 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
      </entry>
      </row>
 
+     <row>
+      <entry><structname>pg_stat_io</structname><indexterm><primary>pg_stat_io</primary></indexterm></entry>
+      <entry>A row for each IO path for each backend type showing
+      statistics about backend IO operations. See
+       <link linkend="monitoring-pg-stat-io-view">
+       <structname>pg_stat_io</structname></link> for details.
+     </entry>
+     </row>
+
      <row>
       <entry><structname>pg_stat_wal</structname><indexterm><primary>pg_stat_wal</primary></indexterm></entry>
       <entry>One row only, showing statistics about WAL activity. See
@@ -3600,7 +3609,111 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
        <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
       </para>
       <para>
-       Time at which these statistics were last reset
+       Time at which these statistics were last reset.
+      </para></entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+
+ </sect2>
+
+ <sect2 id="monitoring-pg-stat-io-view">
+  <title><structname>pg_stat_io</structname></title>
+
+  <indexterm>
+   <primary>pg_stat_io</primary>
+  </indexterm>
+
+  <para>
+   The <structname>pg_stat_io</structname> view has a row for each backend
+   type for each possible IO path containing global data for the cluster for
+   that backend and IO path.
+  </para>
+
+  <table id="pg-stat-io-view" xreflabel="pg_stat_io">
+   <title><structname>pg_stat_io</structname> View</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>backend_type</structfield> <type>text</type>
+      </para>
+      <para>
+       Type of backend (e.g. background worker, autovacuum worker).
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>io_path</structfield> <type>text</type>
+      </para>
+      <para>
+       IO path taken (e.g. shared buffers, direct).
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>alloc</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of buffers allocated.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>extend</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of blocks extended.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>fsync</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of blocks fsynced.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>read</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of blocks read.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>write</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of blocks written.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
+      </para>
+      <para>
+       Time at which these statistics were last reset.
       </para></entry>
      </row>
     </tbody>
@@ -5360,6 +5473,8 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
         the <structname>pg_stat_bgwriter</structname>
         view, <literal>archiver</literal> to reset all the counters shown in
         the <structname>pg_stat_archiver</structname> view,
+        <literal>io</literal> to reset all the counters shown in the
+        <structname>pg_stat_io</structname> view,
         <literal>wal</literal> to reset all the counters shown in the
         <structname>pg_stat_wal</structname> view or
         <literal>recovery_prefetch</literal> to reset all the counters shown
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index fedaed533b..1fe3b07daa 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1115,6 +1115,18 @@ CREATE VIEW pg_stat_bgwriter AS
         pg_stat_get_buf_alloc() AS buffers_alloc,
         pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
 
+CREATE VIEW pg_stat_io AS
+SELECT
+       b.backend_type,
+       b.io_path,
+       b.alloc,
+       b.extend,
+       b.fsync,
+       b.read,
+       b.write,
+       b.stats_reset
+FROM pg_stat_get_io() b;
+
 CREATE VIEW pg_stat_wal AS
     SELECT
         w.wal_records,
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 6259cc4f4c..1c905d5413 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1739,6 +1739,116 @@ pg_stat_get_buf_alloc(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->buf_alloc);
 }
 
+/*
+* When adding a new column to the pg_stat_io view, add a new enum
+* value here above IO_NUM_COLUMNS.
+*/
+enum
+{
+	IO_COLUMN_BACKEND_TYPE,
+	IO_COLUMN_IO_PATH,
+	IO_COLUMN_ALLOCS,
+	IO_COLUMN_EXTENDS,
+	IO_COLUMN_FSYNCS,
+	IO_COLUMN_READS,
+	IO_COLUMN_WRITES,
+	IO_COLUMN_RESET_TIME,
+	IO_NUM_COLUMNS,
+};
+
+Datum
+pg_stat_get_io(PG_FUNCTION_ARGS)
+{
+	PgStat_BackendIOPathOps *io_stats;
+	ReturnSetInfo *rsinfo;
+	Datum reset_time;
+
+	SetSingleFuncCall(fcinfo, 0);
+	rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+	io_stats = pgstat_fetch_backend_io_path_ops();
+
+	/*
+	* Currently it is not permitted to reset IO operation stats for individual
+	* IO Paths or individual BackendTypes. All IO Operation statistics are
+	* reset together. As such, it is easiest to reuse the first reset timestamp
+	* available.
+	*/
+	reset_time = TimestampTzGetDatum(io_stats->stat_reset_timestamp);
+
+	for (int i = 0; i < BACKEND_NUM_TYPES; i++)
+	{
+		bool uses_local;
+		bool uses_strategy;
+		Datum backend_type_desc = CStringGetTextDatum(GetBackendTypeDesc(i));
+		PgStat_IOPathOps *io_path_ops = &io_stats->stats[i];
+
+
+	 /*
+		* IO Operation statistics are not collected for all BackendTypes.
+		* For those BackendTypes without IO Operation stats, skip representing them
+		* in the view altogether.
+		*
+		* The following BackendTypes do not participate in the cumulative stats
+		* subsystem or do not do IO operations worth reporting statistics on:
+		* - Startup process because it does not have relation OIDs
+		* - Syslogger because it is not connected to shared memory
+		* - Archiver because most relevant archiving IO is delegated to a
+		*   specialized command or module
+		* - WAL Receiver and WAL Writer IO is not tracked in pg_stat_io for now
+		*/
+		if (i == B_INVALID || i == B_ARCHIVER || i == B_LOGGER || i == B_STARTUP ||
+				i == B_WAL_RECEIVER || i == B_WAL_WRITER)
+			continue;
+
+		/*
+		 * Not all BackendTypes will use a BufferAccessStrategy. Omit those rows
+		 * from the view.
+		 */
+		uses_strategy = i == B_AUTOVAC_WORKER || i == B_BACKEND || i ==
+			B_STANDALONE_BACKEND || i == B_BG_WORKER;
+
+		uses_local = i == B_BACKEND || i == B_WAL_SENDER;
+
+		for (int j = 0; j < IOPATH_NUM_TYPES; j++)
+		{
+			PgStat_IOOpCounters *counters = &io_path_ops->data[j];
+			Datum values[IO_NUM_COLUMNS];
+			bool nulls[IO_NUM_COLUMNS];
+
+			if (j == IOPATH_STRATEGY && !uses_strategy)
+				continue;
+
+			if (j == IOPATH_LOCAL && !uses_local)
+				continue;
+
+			memset(values, 0, sizeof(values));
+			memset(nulls, 0, sizeof(nulls));
+
+			values[IO_COLUMN_BACKEND_TYPE] = backend_type_desc;
+			values[IO_COLUMN_IO_PATH] = CStringGetTextDatum(pgstat_io_path_desc(j));
+			values[IO_COLUMN_RESET_TIME] = TimestampTzGetDatum(reset_time);
+			values[IO_COLUMN_ALLOCS] = Int64GetDatum(counters->allocs);
+			values[IO_COLUMN_EXTENDS] = Int64GetDatum(counters->extends);
+			values[IO_COLUMN_FSYNCS] = Int64GetDatum(counters->fsyncs);
+			values[IO_COLUMN_READS] = Int64GetDatum(counters->reads);
+			values[IO_COLUMN_WRITES] = Int64GetDatum(counters->writes);
+
+		 /*
+			* Temporary tables using local buffers are not logged and thus do not
+			* require fsync'ing. Set this cell to NULL to differentiate between an
+			* invalid combination and 0 observed IO Operations.
+			*/
+			if (j == IOPATH_LOCAL)
+				nulls[IO_COLUMN_FSYNCS] = true;
+
+			tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+		}
+	}
+
+	return (Datum) 0;
+}
+
 /*
  * Returns statistics of WAL activity
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 2e41f4d9e8..bec3c93991 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5646,6 +5646,15 @@
   proname => 'pg_stat_get_buf_alloc', provolatile => 's', proparallel => 'r',
   prorettype => 'int8', proargtypes => '', prosrc => 'pg_stat_get_buf_alloc' },
 
+{ oid => '8459', descr => 'statistics: counts of all IO operations done through all IO paths by each type of backend.',
+  proname => 'pg_stat_get_io', provolatile => 's', proisstrict => 'f',
+  prorows => '52', proretset => 't',
+  proparallel => 'r', prorettype => 'record', proargtypes => '',
+  proallargtypes => '{text,text,int8,int8,int8,int8,int8,timestamptz}',
+  proargmodes => '{o,o,o,o,o,o,o,o}',
+  proargnames => '{backend_type,io_path,alloc,extend,fsync,read,write,stats_reset}',
+  prosrc => 'pg_stat_get_io' },
+
 { oid => '1136', descr => 'statistics: information about WAL activity',
   proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's',
   proparallel => 'r', prorettype => 'record', proargtypes => '',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 7ec3d2688f..2b269e005e 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1873,6 +1873,15 @@ pg_stat_gssapi| SELECT s.pid,
     s.gss_enc AS encrypted
    FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, leader_pid, query_id)
   WHERE (s.client_port IS NOT NULL);
+pg_stat_io| SELECT b.backend_type,
+    b.io_path,
+    b.alloc,
+    b.extend,
+    b.fsync,
+    b.read,
+    b.write,
+    b.stats_reset
+   FROM pg_stat_get_io() b(backend_type, io_path, alloc, extend, fsync, read, write, stats_reset);
 pg_stat_progress_analyze| SELECT s.pid,
     s.datid,
     d.datname,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 5b0ebf090f..7cdcadacd6 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -554,4 +554,194 @@ SELECT pg_stat_get_live_tuples(:drop_stats_test_subxact_oid);
 
 DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
 DROP TABLE prevstats;
+-- Test that allocs, extends, reads, and writes to Shared Buffers and fsyncs
+-- done to ensure durability of Shared Buffers are tracked in pg_stat_io.
+SELECT sum(alloc) AS io_sum_shared_allocs_before FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(extend) AS io_sum_shared_extends_before FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(fsync) AS io_sum_shared_fsyncs_before FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(read) AS io_sum_shared_reads_before FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(write) AS io_sum_shared_writes_before FROM pg_stat_io WHERE io_path = 'Shared' \gset
+-- Create a regular table and insert some data to generate IOPATH_SHARED allocs and extends.
+CREATE TABLE test_io_shared(a int);
+INSERT INTO test_io_shared SELECT i FROM generate_series(1,100)i;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+-- After a checkpoint, there should be some additional IOPATH_SHARED writes and fsyncs.
+CHECKPOINT;
+SELECT sum(alloc) AS io_sum_shared_allocs_after FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(extend) AS io_sum_shared_extends_after FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(write) AS io_sum_shared_writes_after FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(fsync) AS io_sum_shared_fsyncs_after FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT :io_sum_shared_allocs_after > :io_sum_shared_allocs_before;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT :io_sum_shared_extends_after > :io_sum_shared_extends_before;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT current_setting('fsync') = 'off' OR :io_sum_shared_fsyncs_after > :io_sum_shared_fsyncs_before;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT :io_sum_shared_writes_after > :io_sum_shared_writes_before;
+ ?column? 
+----------
+ t
+(1 row)
+
+-- Change the tablespace so that the table is rewritten directly, then SELECT
+-- from it to cause it to be read back into Shared Buffers.
+SET allow_in_place_tablespaces = true;
+CREATE TABLESPACE test_io_shared_stats_tblspc LOCATION '';
+ALTER TABLE test_io_shared SET TABLESPACE test_io_shared_stats_tblspc;
+SELECT COUNT(*) FROM test_io_shared;
+ count 
+-------
+   100
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+SELECT sum(read) AS io_sum_shared_reads_after FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT :io_sum_shared_reads_after > :io_sum_shared_reads_before;
+ ?column? 
+----------
+ t
+(1 row)
+
+DROP TABLE test_io_shared;
+DROP TABLESPACE test_io_shared_stats_tblspc;
+-- Test that allocs, extends, reads, and writes of temporary tables are tracked
+-- in pg_stat_io.
+CREATE TEMPORARY TABLE test_io_local(a int, b TEXT);
+SELECT sum(alloc) AS io_sum_local_allocs_before FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT sum(extend) AS io_sum_local_extends_before FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT sum(read) AS io_sum_local_reads_before FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT sum(write) AS io_sum_local_writes_before FROM pg_stat_io WHERE io_path = 'Local' \gset
+-- Insert enough values that we need to reuse and write out dirty local
+-- buffers.
+INSERT INTO test_io_local SELECT generate_series(1, 80000) as id,
+'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
+-- Read in evicted buffers.
+SELECT COUNT(*) FROM test_io_local;
+ count 
+-------
+ 80000
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+SELECT sum(alloc) AS io_sum_local_allocs_after FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT sum(extend) AS io_sum_local_extends_after FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT sum(read) AS io_sum_local_reads_after FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT sum(write) AS io_sum_local_writes_after FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT :io_sum_local_allocs_after > :io_sum_local_allocs_before;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT :io_sum_local_extends_after > :io_sum_local_extends_before;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT :io_sum_local_reads_after > :io_sum_local_reads_before;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT :io_sum_local_writes_after > :io_sum_local_writes_before;
+ ?column? 
+----------
+ t
+(1 row)
+
+-- Test that, when using a Strategy, reusing buffers from the Strategy ring
+-- count as "Strategy" allocs in pg_stat_io. Also test that Strategy reads are
+-- counted as such.
+SELECT sum(alloc) AS io_sum_strategy_allocs_before FROM pg_stat_io WHERE io_path = 'Strategy' \gset
+SELECT sum(read) AS io_sum_strategy_reads_before FROM pg_stat_io WHERE io_path = 'Strategy' \gset
+CREATE TABLE test_io_strategy(a INT, b INT);
+ALTER TABLE test_io_strategy SET (autovacuum_enabled = 'false');
+INSERT INTO test_io_strategy SELECT i, i from generate_series(1, 8000)i;
+-- Ensure that the next VACUUM will need to perform IO by rewriting the table
+-- first with VACUUM (FULL).
+VACUUM (FULL) test_io_strategy;
+VACUUM (PARALLEL 0) test_io_strategy;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+SELECT sum(alloc) AS io_sum_strategy_allocs_after FROM pg_stat_io WHERE io_path = 'Strategy' \gset
+SELECT sum(read) AS io_sum_strategy_reads_after FROM pg_stat_io WHERE io_path = 'Strategy' \gset
+SELECT :io_sum_strategy_allocs_after > :io_sum_strategy_allocs_before;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT :io_sum_strategy_reads_after > :io_sum_strategy_reads_before;
+ ?column? 
+----------
+ t
+(1 row)
+
+DROP TABLE test_io_strategy;
+-- Test that, when using a Strategy, if creating a relation, Strategy extends
+-- are counted in pg_stat_io.
+-- A CTAS uses a Bulkwrite strategy.
+SELECT sum(extend) AS io_sum_strategy_extends_before FROM pg_stat_io WHERE io_path = 'Strategy' \gset
+CREATE TABLE test_io_strategy_extend AS SELECT i FROM generate_series(1,100)i;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+SELECT sum(extend) AS io_sum_strategy_extends_after FROM pg_stat_io WHERE io_path = 'Strategy' \gset
+SELECT :io_sum_strategy_extends_after > :io_sum_strategy_extends_before;
+ ?column? 
+----------
+ t
+(1 row)
+
+DROP TABLE test_io_strategy_extend;
+SELECT sum(alloc) + sum(extend) + sum(fsync) + sum(read) + sum(write) AS io_stats_pre_reset FROM pg_stat_io \gset
+SELECT pg_stat_reset_shared('io');
+ pg_stat_reset_shared 
+----------------------
+ 
+(1 row)
+
+SELECT sum(alloc) + sum(extend) + sum(fsync) + sum(read) + sum(write) AS io_stats_post_reset FROM pg_stat_io \gset
+SELECT :io_stats_post_reset < :io_stats_pre_reset;
+ ?column? 
+----------
+ t
+(1 row)
+
 -- End of Stats Test
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 3f3cf8fb56..4d8932feba 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -285,4 +285,95 @@ SELECT pg_stat_get_live_tuples(:drop_stats_test_subxact_oid);
 
 DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
 DROP TABLE prevstats;
+
+-- Test that allocs, extends, reads, and writes to Shared Buffers and fsyncs
+-- done to ensure durability of Shared Buffers are tracked in pg_stat_io.
+SELECT sum(alloc) AS io_sum_shared_allocs_before FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(extend) AS io_sum_shared_extends_before FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(fsync) AS io_sum_shared_fsyncs_before FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(read) AS io_sum_shared_reads_before FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(write) AS io_sum_shared_writes_before FROM pg_stat_io WHERE io_path = 'Shared' \gset
+-- Create a regular table and insert some data to generate IOPATH_SHARED allocs and extends.
+CREATE TABLE test_io_shared(a int);
+INSERT INTO test_io_shared SELECT i FROM generate_series(1,100)i;
+SELECT pg_stat_force_next_flush();
+-- After a checkpoint, there should be some additional IOPATH_SHARED writes and fsyncs.
+CHECKPOINT;
+SELECT sum(alloc) AS io_sum_shared_allocs_after FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(extend) AS io_sum_shared_extends_after FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(write) AS io_sum_shared_writes_after FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT sum(fsync) AS io_sum_shared_fsyncs_after FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT :io_sum_shared_allocs_after > :io_sum_shared_allocs_before;
+SELECT :io_sum_shared_extends_after > :io_sum_shared_extends_before;
+SELECT current_setting('fsync') = 'off' OR :io_sum_shared_fsyncs_after > :io_sum_shared_fsyncs_before;
+SELECT :io_sum_shared_writes_after > :io_sum_shared_writes_before;
+-- Change the tablespace so that the table is rewritten directly, then SELECT
+-- from it to cause it to be read back into Shared Buffers.
+SET allow_in_place_tablespaces = true;
+CREATE TABLESPACE test_io_shared_stats_tblspc LOCATION '';
+ALTER TABLE test_io_shared SET TABLESPACE test_io_shared_stats_tblspc;
+SELECT COUNT(*) FROM test_io_shared;
+SELECT pg_stat_force_next_flush();
+SELECT sum(read) AS io_sum_shared_reads_after FROM pg_stat_io WHERE io_path = 'Shared' \gset
+SELECT :io_sum_shared_reads_after > :io_sum_shared_reads_before;
+DROP TABLE test_io_shared;
+DROP TABLESPACE test_io_shared_stats_tblspc;
+
+-- Test that allocs, extends, reads, and writes of temporary tables are tracked
+-- in pg_stat_io.
+CREATE TEMPORARY TABLE test_io_local(a int, b TEXT);
+SELECT sum(alloc) AS io_sum_local_allocs_before FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT sum(extend) AS io_sum_local_extends_before FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT sum(read) AS io_sum_local_reads_before FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT sum(write) AS io_sum_local_writes_before FROM pg_stat_io WHERE io_path = 'Local' \gset
+-- Insert enough values that we need to reuse and write out dirty local
+-- buffers.
+INSERT INTO test_io_local SELECT generate_series(1, 80000) as id,
+'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
+-- Read in evicted buffers.
+SELECT COUNT(*) FROM test_io_local;
+SELECT pg_stat_force_next_flush();
+SELECT sum(alloc) AS io_sum_local_allocs_after FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT sum(extend) AS io_sum_local_extends_after FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT sum(read) AS io_sum_local_reads_after FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT sum(write) AS io_sum_local_writes_after FROM pg_stat_io WHERE io_path = 'Local' \gset
+SELECT :io_sum_local_allocs_after > :io_sum_local_allocs_before;
+SELECT :io_sum_local_extends_after > :io_sum_local_extends_before;
+SELECT :io_sum_local_reads_after > :io_sum_local_reads_before;
+SELECT :io_sum_local_writes_after > :io_sum_local_writes_before;
+
+-- Test that, when using a Strategy, reusing buffers from the Strategy ring
+-- count as "Strategy" allocs in pg_stat_io. Also test that Strategy reads are
+-- counted as such.
+SELECT sum(alloc) AS io_sum_strategy_allocs_before FROM pg_stat_io WHERE io_path = 'Strategy' \gset
+SELECT sum(read) AS io_sum_strategy_reads_before FROM pg_stat_io WHERE io_path = 'Strategy' \gset
+CREATE TABLE test_io_strategy(a INT, b INT);
+ALTER TABLE test_io_strategy SET (autovacuum_enabled = 'false');
+INSERT INTO test_io_strategy SELECT i, i from generate_series(1, 8000)i;
+-- Ensure that the next VACUUM will need to perform IO by rewriting the table
+-- first with VACUUM (FULL).
+VACUUM (FULL) test_io_strategy;
+VACUUM (PARALLEL 0) test_io_strategy;
+SELECT pg_stat_force_next_flush();
+SELECT sum(alloc) AS io_sum_strategy_allocs_after FROM pg_stat_io WHERE io_path = 'Strategy' \gset
+SELECT sum(read) AS io_sum_strategy_reads_after FROM pg_stat_io WHERE io_path = 'Strategy' \gset
+SELECT :io_sum_strategy_allocs_after > :io_sum_strategy_allocs_before;
+SELECT :io_sum_strategy_reads_after > :io_sum_strategy_reads_before;
+DROP TABLE test_io_strategy;
+
+-- Test that, when using a Strategy, if creating a relation, Strategy extends
+-- are counted in pg_stat_io.
+-- A CTAS uses a Bulkwrite strategy.
+SELECT sum(extend) AS io_sum_strategy_extends_before FROM pg_stat_io WHERE io_path = 'Strategy' \gset
+CREATE TABLE test_io_strategy_extend AS SELECT i FROM generate_series(1,100)i;
+SELECT pg_stat_force_next_flush();
+SELECT sum(extend) AS io_sum_strategy_extends_after FROM pg_stat_io WHERE io_path = 'Strategy' \gset
+SELECT :io_sum_strategy_extends_after > :io_sum_strategy_extends_before;
+DROP TABLE test_io_strategy_extend;
+
+SELECT sum(alloc) + sum(extend) + sum(fsync) + sum(read) + sum(write) AS io_stats_pre_reset FROM pg_stat_io \gset
+SELECT pg_stat_reset_shared('io');
+SELECT sum(alloc) + sum(extend) + sum(fsync) + sum(read) + sum(write) AS io_stats_post_reset FROM pg_stat_io \gset
+SELECT :io_stats_post_reset < :io_stats_pre_reset;
+
 -- End of Stats Test
-- 
2.34.1