v1-0001-Add-temp-table-monitoring-columns-to-pg_stat_data.patch

From c314143f4eb8a4daf08be01bba3e313b4fcf138d Mon Sep 17 00:00:00 2001
From: Mohamed Ali <75553212+mmohali@users.noreply.github.com>
Date: Thu, 7 May 2026 12:33:37 -0700
Subject: [PATCH v1] Add temp table monitoring columns to pg_stat_database

Add visibility into temporary table usage and I/O at the database
level. Currently pg_stat_database tracks temp_files and temp_bytes
for temporary files created during query execution (sorts, hashes),
but provides no metrics for temporary tables created with CREATE
TEMP TABLE.

pg_stat_statements already tracks local_blks_* metrics per-query,
but there is no database-level aggregation. This patch fills that
gap by adding the following columns to pg_stat_database:

  temp_tables        - number of temporary tables created
  local_blks_hit     - local buffer cache hits
  local_blks_read    - local blocks read from disk
  local_blks_dirtied - local blocks dirtied
  local_blks_written - local blocks written to disk

Implementation:
- Track temp table creation in heap_create_with_catalog()
- Track I/O operations in bufmgr.c (reads, hits) and localbuf.c
  (writes, dirtied)
- Add counting functions in pgstat_database.c with proper guards
  for bootstrap and invalid database OID
- Add accessor functions using PG_STAT_GET_DBENTRY_INT64 macro
- Register 5 new system functions
- Update pg_stat_database view and documentation

This enables DBAs to monitor temp table disk I/O, calculate cache
hit ratios, and identify undersized temp_buffers at the database
level.
---
 doc/src/sgml/monitoring.sgml                 | 49 ++++++++++++
 src/backend/catalog/heap.c                   |  6 ++
 src/backend/catalog/system_views.sql         |  5 ++
 src/backend/storage/buffer/bufmgr.c          | 10 +++
 src/backend/storage/buffer/localbuf.c        |  7 ++
 src/backend/utils/activity/pgstat_database.c | 80 ++++++++++++++++++++
 src/backend/utils/adt/pgstatfuncs.c          | 15 ++++
 src/include/catalog/catversion.h             |  2 +-
 src/include/catalog/pg_proc.dat              | 25 ++++++
 src/include/pgstat.h                         | 10 +++
 src/test/regress/expected/rules.out          |  5 ++
 11 files changed, 213 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index cc014564c97..f4258ea487f 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3766,6 +3766,55 @@ 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>temp_tables</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of temporary tables created in this database.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>local_blks_hit</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of times temporary table blocks were found already in the
+       local buffer cache, so that a read was not necessary (this only
+       includes hits in the local buffer cache, whose size is controlled
+       by <xref linkend="guc-temp-buffers"/>, not the operating system's
+       file system cache).
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>local_blks_read</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of temporary table blocks read from disk in this database.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>local_blks_dirtied</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of temporary table blocks dirtied in this database.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>local_blks_written</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of temporary table blocks written to disk in this database.
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>deadlocks</structfield> <type>bigint</type>
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 5748aa9a1a9..39221dcd388 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -1520,6 +1520,12 @@ heap_create_with_catalog(const char *relname,
 	if (oncommit != ONCOMMIT_NOOP)
 		register_on_commit_action(relid, oncommit);
 
+	/*
+	 * Track temporary table statistics.
+	 */
+	if (relpersistence == RELPERSISTENCE_TEMP && relkind == RELKIND_RELATION)
+		pgstat_count_temp_table(MyDatabaseId);
+
 	/*
 	 * ok, the relation has been cataloged, so close our relations and return
 	 * the OID of the newly created relation.
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 339c016e510..20371822bf9 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1133,6 +1133,11 @@ CREATE VIEW pg_stat_database AS
             pg_stat_get_db_conflict_all(D.oid) AS conflicts,
             pg_stat_get_db_temp_files(D.oid) AS temp_files,
             pg_stat_get_db_temp_bytes(D.oid) AS temp_bytes,
+            pg_stat_get_db_temp_tables(D.oid) AS temp_tables,
+            pg_stat_get_db_local_blks_hit(D.oid) AS local_blks_hit,
+            pg_stat_get_db_local_blks_read(D.oid) AS local_blks_read,
+            pg_stat_get_db_local_blks_dirtied(D.oid) AS local_blks_dirtied,
+            pg_stat_get_db_local_blks_written(D.oid) AS local_blks_written,
             pg_stat_get_db_deadlocks(D.oid) AS deadlocks,
             pg_stat_get_db_checksum_failures(D.oid) AS checksum_failures,
             pg_stat_get_db_checksum_last_failure(D.oid) AS checksum_last_failure,
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 5f3d083e938..78c1db9c318 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -828,6 +828,7 @@ ReadRecentBuffer(RelFileLocator rlocator, ForkNumber forkNum, BlockNumber blockN
 			PinLocalBuffer(bufHdr, true);
 
 			pgBufferUsage.local_blks_hit++;
+			pgstat_count_local_blk_hit(MyDatabaseId);
 
 			return true;
 		}
@@ -1249,7 +1250,10 @@ PinBufferForBlock(Relation rel,
 	{
 		bufHdr = LocalBufferAlloc(smgr, forkNum, blockNum, foundPtr);
 		if (*foundPtr)
+		{
 			pgBufferUsage.local_blks_hit++;
+			pgstat_count_local_blk_hit(MyDatabaseId);
+		}
 	}
 	else
 	{
@@ -1990,7 +1994,10 @@ AsyncReadBuffers(ReadBuffersOperation *operation, int *nblocks_progress)
 										  true);
 
 		if (persistence == RELPERSISTENCE_TEMP)
+		{
 			pgBufferUsage.local_blks_hit += 1;
+			pgstat_count_local_blk_hit(MyDatabaseId);
+		}
 		else
 			pgBufferUsage.shared_blks_hit += 1;
 
@@ -2060,7 +2067,10 @@ AsyncReadBuffers(ReadBuffersOperation *operation, int *nblocks_progress)
 								io_start, 1, io_buffers_len * BLCKSZ);
 
 		if (persistence == RELPERSISTENCE_TEMP)
+		{
 			pgBufferUsage.local_blks_read += io_buffers_len;
+			pgstat_count_local_blk_read(MyDatabaseId, io_buffers_len);
+		}
 		else
 			pgBufferUsage.shared_blks_read += io_buffers_len;
 
diff --git a/src/backend/storage/buffer/localbuf.c b/src/backend/storage/buffer/localbuf.c
index 04a540379a2..643936e3cf4 100644
--- a/src/backend/storage/buffer/localbuf.c
+++ b/src/backend/storage/buffer/localbuf.c
@@ -218,6 +218,9 @@ FlushLocalBuffer(BufferDesc *bufHdr, SMgrRelation reln)
 	TerminateLocalBufferIO(bufHdr, true, 0, false);
 
 	pgBufferUsage.local_blks_written++;
+
+	/* Track at database level */
+	pgstat_count_local_blk_written(MyDatabaseId, 1);
 }
 
 static Buffer
@@ -479,6 +482,7 @@ ExtendBufferedRelLocal(BufferManagerRelation bmr,
 	*extended_by = extend_by;
 
 	pgBufferUsage.local_blks_written += extend_by;
+	pgstat_count_local_blk_written(MyDatabaseId, extend_by);
 
 	return first_block;
 }
@@ -509,7 +513,10 @@ MarkLocalBufferDirty(Buffer buffer)
 	buf_state = pg_atomic_read_u64(&bufHdr->state);
 
 	if (!(buf_state & BM_DIRTY))
+	{
 		pgBufferUsage.local_blks_dirtied++;
+		pgstat_count_local_blk_dirtied(MyDatabaseId);
+	}
 
 	buf_state |= BM_DIRTY;
 
diff --git a/src/backend/utils/activity/pgstat_database.c b/src/backend/utils/activity/pgstat_database.c
index 933dcb5cae5..c499459f3b7 100644
--- a/src/backend/utils/activity/pgstat_database.c
+++ b/src/backend/utils/activity/pgstat_database.c
@@ -143,6 +143,81 @@ pgstat_report_deadlock(void)
 	dbent->deadlocks++;
 }
 
+/*
+ * Report creation of a temporary table.
+ */
+void
+pgstat_count_temp_table(Oid dboid)
+{
+	PgStat_StatDBEntry *dbent;
+
+	if (!pgstat_track_counts)
+		return;
+
+	dbent = pgstat_prep_database_pending(dboid);
+	dbent->temp_tables++;
+}
+
+/*
+ * Report local buffer (temp table) write operations.
+ */
+void
+pgstat_count_local_blk_written(Oid dboid, int64 count)
+{
+	PgStat_StatDBEntry *dbent;
+
+	if (!pgstat_track_counts || !OidIsValid(dboid) || !IsUnderPostmaster)
+		return;
+
+	dbent = pgstat_prep_database_pending(dboid);
+	dbent->local_blks_written += count;
+}
+
+/*
+ * Report local buffer (temp table) read operations.
+ */
+void
+pgstat_count_local_blk_read(Oid dboid, int64 count)
+{
+	PgStat_StatDBEntry *dbent;
+
+	if (!pgstat_track_counts || !OidIsValid(dboid) || !IsUnderPostmaster)
+		return;
+
+	dbent = pgstat_prep_database_pending(dboid);
+	dbent->local_blks_read += count;
+}
+
+/*
+ * Report local buffer (temp table) cache hit.
+ */
+void
+pgstat_count_local_blk_hit(Oid dboid)
+{
+	PgStat_StatDBEntry *dbent;
+
+	if (!pgstat_track_counts || !OidIsValid(dboid) || !IsUnderPostmaster)
+		return;
+
+	dbent = pgstat_prep_database_pending(dboid);
+	dbent->local_blks_hit++;
+}
+
+/*
+ * Report local buffer (temp table) dirtied.
+ */
+void
+pgstat_count_local_blk_dirtied(Oid dboid)
+{
+	PgStat_StatDBEntry *dbent;
+
+	if (!pgstat_track_counts || !OidIsValid(dboid) || !IsUnderPostmaster)
+		return;
+
+	dbent = pgstat_prep_database_pending(dboid);
+	dbent->local_blks_dirtied++;
+}
+
 /*
  * Allow this backend to later report checksum failures for dboid, even if in
  * a critical section at the time of the report.
@@ -472,6 +547,11 @@ pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
 
 	PGSTAT_ACCUM_DBCOUNT(temp_bytes);
 	PGSTAT_ACCUM_DBCOUNT(temp_files);
+	PGSTAT_ACCUM_DBCOUNT(temp_tables);
+	PGSTAT_ACCUM_DBCOUNT(local_blks_hit);
+	PGSTAT_ACCUM_DBCOUNT(local_blks_read);
+	PGSTAT_ACCUM_DBCOUNT(local_blks_dirtied);
+	PGSTAT_ACCUM_DBCOUNT(local_blks_written);
 	PGSTAT_ACCUM_DBCOUNT(deadlocks);
 
 	/* checksum failures are reported immediately */
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 5ac022274a7..a35be46e217 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1110,6 +1110,21 @@ PG_STAT_GET_DBENTRY_INT64(temp_bytes)
 /* pg_stat_get_db_temp_files */
 PG_STAT_GET_DBENTRY_INT64(temp_files)
 
+/* pg_stat_get_db_temp_tables */
+PG_STAT_GET_DBENTRY_INT64(temp_tables)
+
+/* pg_stat_get_db_local_blks_hit */
+PG_STAT_GET_DBENTRY_INT64(local_blks_hit)
+
+/* pg_stat_get_db_local_blks_read */
+PG_STAT_GET_DBENTRY_INT64(local_blks_read)
+
+/* pg_stat_get_db_local_blks_dirtied */
+PG_STAT_GET_DBENTRY_INT64(local_blks_dirtied)
+
+/* pg_stat_get_db_local_blks_written */
+PG_STAT_GET_DBENTRY_INT64(local_blks_written)
+
 /* pg_stat_get_db_tuples_deleted */
 PG_STAT_GET_DBENTRY_INT64(tuples_deleted)
 
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 90f46b03502..59bebd8b079 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202603101
+#define CATALOG_VERSION_NO	202603106
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 361e2cfffeb..e609d90d99b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5898,6 +5898,31 @@
   proname => 'pg_stat_get_db_temp_bytes', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_db_temp_bytes' },
+{ oid => '9950',
+  descr => 'statistics: number of temporary tables created',
+  proname => 'pg_stat_get_db_temp_tables', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_temp_tables' },
+{ oid => '9951',
+  descr => 'statistics: temp table blocks written to disk',
+  proname => 'pg_stat_get_db_local_blks_written', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_local_blks_written' },
+{ oid => '9952',
+  descr => 'statistics: temp table blocks read from disk',
+  proname => 'pg_stat_get_db_local_blks_read', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_local_blks_read' },
+{ oid => '9953',
+  descr => 'statistics: temp table cache hits',
+  proname => 'pg_stat_get_db_local_blks_hit', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_local_blks_hit' },
+{ oid => '9954',
+  descr => 'statistics: temp table blocks dirtied',
+  proname => 'pg_stat_get_db_local_blks_dirtied', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_local_blks_dirtied' },
 { oid => '2844', descr => 'statistics: block read time, in milliseconds',
   proname => 'pg_stat_get_db_blk_read_time', provolatile => 's',
   proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 216b93492ba..a09b127a9bd 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -365,6 +365,11 @@ typedef struct PgStat_StatDBEntry
 	PgStat_Counter conflict_startup_deadlock;
 	PgStat_Counter temp_files;
 	PgStat_Counter temp_bytes;
+	PgStat_Counter temp_tables;	/* # of temp tables created */
+	PgStat_Counter local_blks_hit;	/* temp table cache hits */
+	PgStat_Counter local_blks_read;	/* temp table blocks read */
+	PgStat_Counter local_blks_dirtied;	/* temp table blocks dirtied */
+	PgStat_Counter local_blks_written;	/* temp table blocks written */
 	PgStat_Counter deadlocks;
 	PgStat_Counter checksum_failures;
 	TimestampTz last_checksum_failure;
@@ -625,6 +630,11 @@ extern void pgstat_drop_database(Oid databaseid);
 extern void pgstat_report_autovac(Oid dboid);
 extern void pgstat_report_recovery_conflict(int reason);
 extern void pgstat_report_deadlock(void);
+extern void pgstat_count_temp_table(Oid dboid);
+extern void pgstat_count_local_blk_hit(Oid dboid);
+extern void pgstat_count_local_blk_read(Oid dboid, int64 count);
+extern void pgstat_count_local_blk_dirtied(Oid dboid);
+extern void pgstat_count_local_blk_written(Oid dboid, int64 count);
 extern void pgstat_prepare_report_checksum_failure(Oid dboid);
 extern void pgstat_report_checksum_failures_in_db(Oid dboid, int failurecount);
 extern void pgstat_report_connect(Oid dboid);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index f373ad704b6..d1b78a199ec 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1893,6 +1893,11 @@ pg_stat_database| SELECT oid AS datid,
     pg_stat_get_db_conflict_all(oid) AS conflicts,
     pg_stat_get_db_temp_files(oid) AS temp_files,
     pg_stat_get_db_temp_bytes(oid) AS temp_bytes,
+    pg_stat_get_db_temp_tables(oid) AS temp_tables,
+    pg_stat_get_db_local_blks_hit(oid) AS local_blks_hit,
+    pg_stat_get_db_local_blks_read(oid) AS local_blks_read,
+    pg_stat_get_db_local_blks_dirtied(oid) AS local_blks_dirtied,
+    pg_stat_get_db_local_blks_written(oid) AS local_blks_written,
     pg_stat_get_db_deadlocks(oid) AS deadlocks,
     pg_stat_get_db_checksum_failures(oid) AS checksum_failures,
     pg_stat_get_db_checksum_last_failure(oid) AS checksum_last_failure,
-- 
2.50.1 (Apple Git-155)