Hi,
On Wed, Aug 27, 2025 at 01:57:13PM +0000, Bertrand Drouvot wrote:
> Hi,
>
> On Tue, Aug 26, 2025 at 04:55:09PM -0500, Sami Imseih wrote:
> > I worry that a single view will grow very wide, and we will have to
> > eventually
> > split it. So we may as well start thinking about having multiple views
> > in advance.
>
> I gave it more thoughts and I now think that multiple views is better. We
> could start with pg_stat_backend_relations here.
>
PFA v3, using pg_stat_backend_relation instead of pg_stat_backend.
In passing, adding PGDLLIMPORT for backend_has_relstats and PendingBackendStats
that were missing in v2.
As compared to v1, only 0001 and 0002 are shared. Let's discuss those first
before
moving foward with 0003+.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
>From ceeb61fffdccfb0f7b06dbe0767b77aa2191c082 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Mon, 4 Aug 2025 08:14:02 +0000
Subject: [PATCH v3 1/2] Adding per backend relation statistics tracking
This commit introduces per backend relation stats tracking and adds a
new PgStat_BackendRelPending struct to store the pending statistics. To begin with,
this commit adds a new counter numscans to record the number of sequential
scans initiated on tables.
This commit relies on the existing per backend statistics machinery that has been
added in 9aea73fc61d.
---
src/backend/utils/activity/pgstat_backend.c | 47 +++++++++++++++++++-
src/backend/utils/activity/pgstat_relation.c | 7 +++
src/include/pgstat.h | 17 +++++++
src/include/utils/pgstat_internal.h | 3 +-
src/tools/pgindent/typedefs.list | 1 +
5 files changed, 73 insertions(+), 2 deletions(-)
73.1% src/backend/utils/activity/
7.6% src/include/utils/
17.9% src/include/
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index 8714a85e2d9..0644e999e93 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -36,7 +36,7 @@
* reported within critical sections so we use static memory in order to avoid
* memory allocation.
*/
-static PgStat_BackendPending PendingBackendStats;
+PgStat_BackendPending PendingBackendStats;
static bool backend_has_iostats = false;
/*
@@ -47,6 +47,11 @@ static bool backend_has_iostats = false;
*/
static WalUsage prevBackendWalUsage;
+/*
+ * For backend relations's related statistics.
+ */
+bool backend_has_relstats = false;
+
/*
* Utility routines to report I/O stats for backends, kept here to avoid
* exposing PendingBackendStats to the outside world.
@@ -259,6 +264,39 @@ pgstat_flush_backend_entry_wal(PgStat_EntryRef *entry_ref)
prevBackendWalUsage = pgWalUsage;
}
+/*
+ * Flush out locally pending backend relations's related statistics. Locking is
+ * managed by the caller.
+ */
+static void
+pgstat_flush_backend_entry_rel(PgStat_EntryRef *entry_ref)
+{
+ PgStatShared_Backend *shbackendent;
+
+ /*
+ * This function can be called even if nothing at all has happened for
+ * relations's related statistics. In this case, avoid unnecessarily
+ * modifying the stats entry.
+ */
+ if (!backend_has_relstats)
+ return;
+
+ shbackendent = (PgStatShared_Backend *) entry_ref->shared_stats;
+
+#define BACKENDREL_ACC(stat) \
+ (shbackendent->stats.stat += PendingBackendStats.pending_backendrel.stat)
+
+ BACKENDREL_ACC(heap_scan);
+#undef BACKENDREL_ACC
+
+ /*
+ * Clear out the statistics buffer, so it can be re-used.
+ */
+ MemSet(&PendingBackendStats.pending_backendrel, 0, sizeof(PgStat_BackendRelPending));
+
+ backend_has_relstats = false;
+}
+
/*
* Flush out locally pending backend statistics
*
@@ -283,6 +321,10 @@ pgstat_flush_backend(bool nowait, bits32 flags)
pgstat_backend_wal_have_pending())
has_pending_data = true;
+ /* Some relations related data pending? */
+ if ((flags & PGSTAT_BACKEND_FLUSH_REL) && backend_has_relstats)
+ has_pending_data = true;
+
if (!has_pending_data)
return false;
@@ -298,6 +340,9 @@ pgstat_flush_backend(bool nowait, bits32 flags)
if (flags & PGSTAT_BACKEND_FLUSH_WAL)
pgstat_flush_backend_entry_wal(entry_ref);
+ if (flags & PGSTAT_BACKEND_FLUSH_REL)
+ pgstat_flush_backend_entry_rel(entry_ref);
+
pgstat_unlock_entry(entry_ref);
return false;
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 69df741cbf6..f2318bddb41 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -898,6 +898,13 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
dbentry->blocks_fetched += lstats->counts.blocks_fetched;
dbentry->blocks_hit += lstats->counts.blocks_hit;
+ /* Do the same for backend stats */
+ if (lstats->relation && lstats->relation->rd_rel->relkind == RELKIND_RELATION)
+ PendingBackendStats.pending_backendrel.heap_scan += lstats->counts.numscans;
+
+ backend_has_relstats = true;
+ pgstat_report_fixed = true;
+
return true;
}
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 202bd2d5ace..7d1787d7968 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -490,8 +490,14 @@ typedef struct PgStat_Backend
TimestampTz stat_reset_timestamp;
PgStat_BktypeIO io_stats;
PgStat_WalCounters wal_counters;
+ PgStat_Counter heap_scan;
} PgStat_Backend;
+typedef struct PgStat_BackendRelPending
+{
+ PgStat_Counter heap_scan;
+} PgStat_BackendRelPending;
+
/* ---------
* PgStat_BackendPending Non-flushed backend stats.
* ---------
@@ -502,6 +508,11 @@ typedef struct PgStat_BackendPending
* Backend statistics store the same amount of IO data as PGSTAT_KIND_IO.
*/
PgStat_PendingIO pending_io;
+
+ /*
+ * Backend statistics related to relations.
+ */
+ PgStat_BackendRelPending pending_backendrel;
} PgStat_BackendPending;
/*
@@ -800,6 +811,12 @@ extern PGDLLIMPORT bool pgstat_track_counts;
extern PGDLLIMPORT int pgstat_track_functions;
extern PGDLLIMPORT int pgstat_fetch_consistency;
+/*
+ * Variables in pgstat_backend.c
+ */
+
+extern PGDLLIMPORT PgStat_BackendPending PendingBackendStats;
+extern PGDLLIMPORT bool backend_has_relstats;
/*
* Variables in pgstat_bgwriter.c
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 6cf00008f63..286249c0f3a 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -616,7 +616,8 @@ extern void pgstat_archiver_snapshot_cb(void);
/* flags for pgstat_flush_backend() */
#define PGSTAT_BACKEND_FLUSH_IO (1 << 0) /* Flush I/O statistics */
#define PGSTAT_BACKEND_FLUSH_WAL (1 << 1) /* Flush WAL statistics */
-#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL)
+#define PGSTAT_BACKEND_FLUSH_REL (1 << 2) /* Flush relations related statistics */
+#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL | PGSTAT_BACKEND_FLUSH_REL)
extern bool pgstat_flush_backend(bool nowait, bits32 flags);
extern bool pgstat_backend_flush_cb(bool nowait);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index a13e8162890..e3f4c71466b 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2227,6 +2227,7 @@ PgStatShared_Wal
PgStat_ArchiverStats
PgStat_Backend
PgStat_BackendPending
+PgStat_BackendRelPending
PgStat_BackendSubEntry
PgStat_BgWriterStats
PgStat_BktypeIO
--
2.34.1
>From 32b6dc68cce147b918de471bda5d784e8d20c490 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Sat, 9 Aug 2025 14:22:36 +0000
Subject: [PATCH v3 2/2] Adding the pg_stat_backend_relation view
This view displays one row per server process, showing relation statistics related
to the current activity of that process. It currently displays the pid, the
number of sequential scans initiated on tables and the
time at which these statistics were last reset.
It's built on top of a new function (pg_stat_get_backend_relations()). The idea
is the same as pg_stat_activity and pg_stat_get_activity().
Adding documentation and tests.
XXX: Bump catversion
---
doc/src/sgml/monitoring.sgml | 95 ++++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 7 ++
src/backend/utils/adt/pgstatfuncs.c | 55 ++++++++++++++++
src/include/catalog/pg_proc.dat | 9 +++
src/test/regress/expected/rules.out | 4 ++
src/test/regress/expected/stats.out | 10 +++
src/test/regress/sql/stats.sql | 8 +++
7 files changed, 188 insertions(+)
51.4% doc/src/sgml/
26.1% src/backend/utils/adt/
7.8% src/include/catalog/
7.3% src/test/regress/expected/
4.2% src/test/regress/sql/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 3f4a27a736e..41b24c5e3b7 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -320,6 +320,19 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry>
+ <structname>pg_stat_backend_relation</structname>
+ <indexterm><primary>pg_stat_backend_relation</primary></indexterm>
+ </entry>
+ <entry>
+ One row per server process, showing relation statistics related to
+ the current activity of that process, such as number of sequential scans.
+ See <link linkend="monitoring-pg-stat-backend-relation-view">
+ <structname>pg_stat_backend_relation</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_replication</structname><indexterm><primary>pg_stat_replication</primary></indexterm></entry>
<entry>One row per WAL sender process, showing statistics about
@@ -1172,6 +1185,72 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</note>
</sect2>
+ <sect2 id="monitoring-pg-stat-backend-relation-view">
+ <title><structname>pg_stat_backend_relation</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_backend_relation</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_backend_relation</structname> view will have one row
+ per server process, showing relation statistics related to
+ the current activity of that process.
+ </para>
+
+ <table id="pg-stat-backend-relation-view" xreflabel="pg_stat_backend_relation">
+ <title><structname>pg_stat_backend_relation</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>pid</structfield> <type>integer</type>
+ </para>
+ <para>
+ Process ID of this backend
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>seq_scan</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The number of sequential scans initiated on tables.
+ </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>
+ </tgroup>
+ </table>
+
+ <note>
+ <para>
+ The view does not return statistics for the checkpointer,
+ the background writer, the startup process and the autovacuum launcher.
+ </para>
+ </note>
+ </sect2>
+
<sect2 id="monitoring-pg-stat-replication-view">
<title><structname>pg_stat_replication</structname></title>
@@ -4921,6 +5000,22 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_stat_get_backend_relations</primary>
+ </indexterm>
+ <function>pg_stat_get_backend_relations</function> ( <type>integer</type> )
+ <returnvalue>setof record</returnvalue>
+ </para>
+ <para>
+ Returns a record of statistics about the backend with the specified
+ process ID, or one record for each active backend in the system
+ if <literal>NULL</literal> is specified. The fields returned are a
+ subset of those in the <structname>pg_stat_backend_relation</structname> view.
+ </para></entry>
+ </row>
+
<row>
<entry id="pg-stat-get-backend-wal" role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 1b3c5a55882..f0d78b3aec2 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -911,6 +911,13 @@ CREATE VIEW pg_stat_activity AS
LEFT JOIN pg_database AS D ON (S.datid = D.oid)
LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
+CREATE VIEW pg_stat_backend_relation AS
+ SELECT
+ S.pid,
+ S.seq_scan,
+ S.stats_reset
+ FROM pg_stat_get_backend_relations(NULL) AS S;
+
CREATE VIEW pg_stat_replication AS
SELECT
S.pid,
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index c756c2bebaa..cb7a8013ee2 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -685,6 +685,61 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+/*
+ * Returns statistics of PG backends.
+ */
+Datum
+pg_stat_get_backend_relations(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_GET_BACKEND_STATS_COLS 3
+ int num_backends = pgstat_fetch_stat_numbackends();
+ int curr_backend;
+ int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ /* 1-based index */
+ for (curr_backend = 1; curr_backend <= num_backends; curr_backend++)
+ {
+ /* for each row */
+ Datum values[PG_STAT_GET_BACKEND_STATS_COLS] = {0};
+ bool nulls[PG_STAT_GET_BACKEND_STATS_COLS] = {0};
+ LocalPgBackendStatus *local_beentry;
+ PgBackendStatus *beentry;
+ PgStat_Backend *backend_stats;
+
+ /* Get the next one in the list */
+ local_beentry = pgstat_get_local_beentry_by_index(curr_backend);
+ beentry = &local_beentry->backendStatus;
+
+ /* If looking for specific PID, ignore all the others */
+ if (pid != -1 && beentry->st_procpid != pid)
+ continue;
+
+ backend_stats = pgstat_fetch_stat_backend_by_pid(beentry->st_procpid, NULL);
+
+ values[0] = Int32GetDatum(beentry->st_procpid);
+
+ if (!backend_stats)
+ continue;
+
+ values[1] = Int64GetDatum(backend_stats->heap_scan);
+
+ if (backend_stats->stat_reset_timestamp != 0)
+ values[2] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
+ else
+ nulls[2] = true;
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+
+ /* If only a single backend was requested, and we found it, break. */
+ if (pid != -1)
+ break;
+ }
+
+ return (Datum) 0;
+}
Datum
pg_backend_pid(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 118d6da1ace..bda148a49be 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5641,6 +5641,15 @@
proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
proargnames => '{pid,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,gss_delegation,leader_pid,query_id}',
prosrc => 'pg_stat_get_activity' },
+{ oid => '9555',
+ descr => 'statistics: statistics about currently active backends',
+ proname => 'pg_stat_get_backend_relations', prorows => '100', proisstrict => 'f',
+ proretset => 't', provolatile => 's', proparallel => 'r',
+ prorettype => 'record', proargtypes => 'int4',
+ proallargtypes => '{int4,int4,int8,timestamptz}',
+ proargmodes => '{i,o,o,o}',
+ proargnames => '{pid,pid,seq_scan,stats_reset}',
+ prosrc => 'pg_stat_get_backend_relations' },
{ oid => '6318', descr => 'describe wait events',
proname => 'pg_get_wait_events', procost => '10', prorows => '250',
proretset => 't', provolatile => 'v', prorettype => 'record',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 35e8aad7701..08063f49545 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1847,6 +1847,10 @@ pg_stat_archiver| SELECT archived_count,
last_failed_time,
stats_reset
FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset);
+pg_stat_backend_relation| SELECT pid,
+ seq_scan,
+ stats_reset
+ FROM pg_stat_get_backend_relations(NULL::integer) s(pid, seq_scan, stats_reset);
pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_alloc() AS buffers_alloc,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 605f5070376..7a5aec9cee8 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -118,6 +118,8 @@ SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
COMMIT;
+SELECT seq_scan AS seq_scan_before
+ FROM pg_stat_backend_relation WHERE pid = pg_backend_pid() \gset
-- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
CREATE TABLE trunc_stats_test(id serial);
CREATE TABLE trunc_stats_test1(id serial, stuff text);
@@ -236,6 +238,14 @@ FROM prevstats AS pr;
(1 row)
COMMIT;
+SELECT seq_scan AS seq_scan_after
+ FROM pg_stat_backend_relation WHERE pid = pg_backend_pid() \gset
+SELECT :seq_scan_after > :seq_scan_before;
+ ?column?
+----------
+ t
+(1 row)
+
----
-- Basic tests for track_functions
---
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 54e72866344..027f84baa6b 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -38,6 +38,9 @@ SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
WHERE t.relname='tenk2' AND b.relname='tenk2';
COMMIT;
+SELECT seq_scan AS seq_scan_before
+ FROM pg_stat_backend_relation WHERE pid = pg_backend_pid() \gset
+
-- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
CREATE TABLE trunc_stats_test(id serial);
CREATE TABLE trunc_stats_test1(id serial, stuff text);
@@ -132,6 +135,11 @@ FROM prevstats AS pr;
COMMIT;
+SELECT seq_scan AS seq_scan_after
+ FROM pg_stat_backend_relation WHERE pid = pg_backend_pid() \gset
+
+SELECT :seq_scan_after > :seq_scan_before;
+
----
-- Basic tests for track_functions
---
--
2.34.1
