public inbox for [email protected]
help / color / mirror / Atom feedFrom: shihao zhong <[email protected]>
To: PostgreSQL-development <[email protected]>
Subject: [Patch] New pg_stat_tablespace view
Date: Mon, 23 Mar 2026 15:08:52 -0400
Message-ID: <CAGRkXqRHsZw3+aeNZgnBYduQSY7qg1O4MBdmyFjK-A+TU1-b-A@mail.gmail.com> (raw)
Hi hackers,
I’ve been working on extending the cumulative statistics system to
provide better visibility into tablespace-level workloads, and I'd
like to propose a patch to add a new system view: pg_stat_tablespace.
Currently, PostgreSQL provides statistics per database (e.g.,
pg_stat_database) and per relation (e.g., pg_statio_user_tables).
However, because tablespaces can span multiple databases, it is
difficult for DBAs to analyze storage hotspots across the cluster or
verify if a specific tablespace (such as a high-performance SSD vs a
slow HDD array) is experiencing I/O bottlenecks or excessive temporary
file usage.
The pg_stat_tablespace view bridges this gap by providing an aggregate
view of block I/O and temporary file usage grouped by tablespace,
making it easier to optimize storage architectures.
Thanks,
Shihao
Attachments:
[application/octet-stream] pg_stat_tablespace_final.patch (30.8K, 2-pg_stat_tablespace_final.patch)
download | inline diff:
From ed6bafcf4a27262577f0bc1c100adb907505a47d Mon Sep 17 00:00:00 2001
From: shihao zhong <[email protected]>
Date: Mon, 23 Mar 2026 18:27:46 +0000
Subject: [PATCH] Add pg_stat_tablespace statistics view
Implement pg_stat_tablespace to track block reads, hits, I/O timing,
and temporary file usage per tablespace. This allows DBAs to analyze
tablespace-level workload hotspots.
The view includes:
- tablespace_id
- tablespace_name
- blocks_fetched
- blocks_hit
- blk_read_time
- blk_write_time
- temp_files
- temp_bytes
Includes comprehensive field coverage checks in stats.sql.
---
doc/src/sgml/monitoring.sgml | 143 ++++++++++++++++++
src/backend/catalog/system_views.sql | 14 ++
src/backend/storage/file/fd.c | 2 +-
src/backend/utils/activity/Makefile | 1 +
src/backend/utils/activity/meson.build | 1 +
src/backend/utils/activity/pgstat.c | 16 ++
src/backend/utils/activity/pgstat_database.c | 47 +++++-
src/backend/utils/activity/pgstat_relation.c | 18 +++
.../utils/activity/pgstat_tablespace.c | 99 ++++++++++++
src/backend/utils/adt/pgstatfuncs.c | 80 +++++++++-
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.dat | 8 +
src/include/pgstat.h | 22 +++
src/include/utils/backend_status.h | 2 +-
src/include/utils/pgstat_internal.h | 8 +
src/include/utils/pgstat_kind.h | 3 +-
src/test/regress/expected/rules.out | 11 ++
src/test/regress/expected/stats.out | 83 +++++++++-
src/test/regress/sql/stats.sql | 36 +++++
19 files changed, 589 insertions(+), 7 deletions(-)
create mode 100644 src/backend/utils/activity/pgstat_tablespace.c
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 462019a972c..d8a0b528a8f 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -526,6 +526,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_tablespace</structname><indexterm><primary>pg_stat_tablespace</primary></indexterm></entry>
+ <entry>One row per tablespace, showing statistics about I/O and temporary files. See
+ <link linkend="monitoring-pg-stat-tablespace-view">
+ <structname>pg_stat_tablespace</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_subscription_stats</structname><indexterm><primary>pg_stat_subscription_stats</primary></indexterm></entry>
<entry>One row per subscription, showing statistics about errors and conflicts.
@@ -5152,6 +5160,141 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</sect2>
+ <sect2 id="monitoring-pg-stat-tablespace-view">
+ <title><structname>pg_stat_tablespace</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_tablespace</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_tablespace</structname> view will contain one row
+ for each tablespace, showing statistics about I/O operations and temporary
+ file usage in that tablespace.
+ </para>
+
+ <table id="pg-stat-tablespace-view" xreflabel="pg_stat_tablespace">
+ <title><structname>pg_stat_tablespace</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>tablespace_id</structfield> <type>oid</type>
+ </para>
+ <para>
+ OID of the tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>tablespace_name</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of the tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>blk_read_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Time spent reading data blocks by backends in this tablespace, in milliseconds
+ (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>blk_write_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Time spent writing data blocks by backends in this tablespace, in milliseconds
+ (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>blocks_fetched</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of data blocks read from disk in this tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>blocks_hit</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of data blocks found in shared buffer cache in this tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>temp_files</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of temporary files created in this tablespace
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>temp_bytes</structfield> <type>numeric</type>
+ </para>
+ <para>
+ Total amount of data written to temporary files in this tablespace
+ </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>
+ </sect2>
+
<sect2 id="monitoring-stats-functions">
<title>Statistics Functions</title>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index f1ed7b58f13..2876c5fd643 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1119,6 +1119,20 @@ CREATE VIEW pg_stat_replication_slots AS
LATERAL pg_stat_get_replication_slot(slot_name) as s
WHERE r.datoid IS NOT NULL; -- excluding physical slots
+CREATE VIEW pg_stat_tablespace AS
+ SELECT
+ T.oid AS tablespace_id,
+ T.spcname AS tablespace_name,
+ S.blk_read_time,
+ S.blk_write_time,
+ S.blocks_fetched,
+ S.blocks_hit,
+ S.temp_files,
+ S.temp_bytes,
+ S.stats_reset
+ FROM pg_tablespace T
+ LEFT JOIN LATERAL pg_stat_get_tablespace(T.oid) S ON true;
+
CREATE VIEW pg_stat_database AS
SELECT
D.oid AS datid,
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index 01f1bd6e687..03c47aba17f 100644
--- a/src/backend/storage/file/fd.c
+++ b/src/backend/storage/file/fd.c
@@ -1515,7 +1515,7 @@ FileAccess(File file)
static void
ReportTemporaryFileUsage(const char *path, pgoff_t size)
{
- pgstat_report_tempfile(size);
+ pgstat_report_tempfile(size, path);
if (log_temp_files >= 0)
{
diff --git a/src/backend/utils/activity/Makefile b/src/backend/utils/activity/Makefile
index c37bfb350bb..2556eb30821 100644
--- a/src/backend/utils/activity/Makefile
+++ b/src/backend/utils/activity/Makefile
@@ -31,6 +31,7 @@ OBJS = \
pgstat_shmem.o \
pgstat_slru.o \
pgstat_subscription.o \
+ pgstat_tablespace.o \
pgstat_wal.o \
pgstat_xact.o \
wait_event.o \
diff --git a/src/backend/utils/activity/meson.build b/src/backend/utils/activity/meson.build
index 53bd5a246ca..97d12566af9 100644
--- a/src/backend/utils/activity/meson.build
+++ b/src/backend/utils/activity/meson.build
@@ -16,6 +16,7 @@ backend_sources += files(
'pgstat_shmem.c',
'pgstat_slru.c',
'pgstat_subscription.c',
+ 'pgstat_tablespace.c',
'pgstat_wal.c',
'pgstat_xact.c',
)
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 11bb71cad5a..60c2c80adc3 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -300,6 +300,22 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.reset_timestamp_cb = pgstat_database_reset_timestamp_cb,
},
+ [PGSTAT_KIND_TABLESPACE] = {
+ .name = "tablespace",
+
+ .fixed_amount = false,
+ .write_to_file = true,
+ .accessed_across_databases = true,
+
+ .shared_size = sizeof(PgStatShared_Tablespace),
+ .shared_data_off = offsetof(PgStatShared_Tablespace, stats),
+ .shared_data_len = sizeof(((PgStatShared_Tablespace *) 0)->stats),
+ .pending_size = sizeof(PgStat_StatTabspaceEntry),
+
+ .flush_pending_cb = pgstat_tablespace_flush_cb,
+ .reset_timestamp_cb = pgstat_tablespace_reset_timestamp_cb,
+ },
+
[PGSTAT_KIND_RELATION] = {
.name = "relation",
diff --git a/src/backend/utils/activity/pgstat_database.c b/src/backend/utils/activity/pgstat_database.c
index 933dcb5cae5..83d2675d511 100644
--- a/src/backend/utils/activity/pgstat_database.c
+++ b/src/backend/utils/activity/pgstat_database.c
@@ -17,9 +17,11 @@
#include "postgres.h"
+#include "miscadmin.h"
#include "storage/standby.h"
#include "utils/pgstat_internal.h"
#include "utils/timestamp.h"
+#include "catalog/pg_tablespace_d.h"
static bool pgstat_should_report_connstat(void);
@@ -214,20 +216,63 @@ pgstat_report_checksum_failures_in_db(Oid dboid, int failurecount)
pgstat_unlock_entry(entry_ref);
}
+/*
+ * Helper function to parse tablespace oid from temporary file path.
+ */
+static Oid
+get_tablespace_from_tempfile_path(const char *path)
+{
+ /*
+ * We match the path against known tablespace prefixes to avoid modifying
+ * fd.c/fileset.c and Vfd structures.
+ */
+ if (path == NULL)
+ return InvalidOid;
+
+ if (strncmp(path, "pg_tblspc/", 10) == 0)
+ {
+ return atooid(path + 10);
+ }
+ else if (strncmp(path, "base/", 5) == 0)
+ {
+ return DEFAULTTABLESPACE_OID;
+ }
+ else if (strncmp(path, "global/", 7) == 0)
+ {
+ return GLOBALTABLESPACE_OID;
+ }
+
+ return InvalidOid;
+}
+
/*
* Report creation of temporary file.
*/
void
-pgstat_report_tempfile(size_t filesize)
+pgstat_report_tempfile(size_t filesize, const char *path)
{
PgStat_StatDBEntry *dbent;
+ PgStat_StatTabspaceEntry *tsent;
+ Oid tablespace_oid;
if (!pgstat_track_counts)
return;
+ tablespace_oid = get_tablespace_from_tempfile_path(path);
+
dbent = pgstat_prep_database_pending(MyDatabaseId);
dbent->temp_bytes += filesize;
dbent->temp_files++;
+
+ if (OidIsValid(tablespace_oid))
+ {
+ tsent = pgstat_prep_tablespace_pending(tablespace_oid);
+ if (tsent)
+ {
+ tsent->temp_bytes += filesize;
+ tsent->temp_files++;
+ }
+ }
}
/*
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index bc8c43b96aa..16bfa285f83 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -20,6 +20,7 @@
#include "access/twophase_rmgr.h"
#include "access/xact.h"
#include "catalog/catalog.h"
+#include "miscadmin.h"
#include "utils/memutils.h"
#include "utils/pgstat_internal.h"
#include "utils/rel.h"
@@ -142,6 +143,7 @@ pgstat_assoc_relation(Relation rel)
/* mark this relation as the owner */
rel->pgstat_info->relation = rel;
+ rel->pgstat_info->reltablespace = rel->rd_locator.spcOid;
}
/*
@@ -897,6 +899,22 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
dbentry->blocks_fetched += lstats->counts.blocks_fetched;
dbentry->blocks_hit += lstats->counts.blocks_hit;
+ /* The entry was successfully flushed, add the same to tablespace stats */
+ {
+ Oid tsid = (lstats->reltablespace == InvalidOid) ? MyDatabaseTableSpace : lstats->reltablespace;
+
+ if (OidIsValid(tsid))
+ {
+ PgStat_StatTabspaceEntry *tsentry = pgstat_prep_tablespace_pending(tsid);
+
+ if (tsentry)
+ {
+ tsentry->blocks_fetched += lstats->counts.blocks_fetched;
+ tsentry->blocks_hit += lstats->counts.blocks_hit;
+ }
+ }
+ }
+
return true;
}
diff --git a/src/backend/utils/activity/pgstat_tablespace.c b/src/backend/utils/activity/pgstat_tablespace.c
new file mode 100644
index 00000000000..e85fff7569b
--- /dev/null
+++ b/src/backend/utils/activity/pgstat_tablespace.c
@@ -0,0 +1,99 @@
+/* -------------------------------------------------------------------------
+ *
+ * pgstat_tablespace.c
+ * Implementation of tablespace statistics.
+ *
+ * Copyright (c) 2001-2025, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * src/backend/utils/activity/pgstat_tablespace.c
+ * -------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "utils/pgstat_internal.h"
+#include "utils/timestamp.h"
+
+
+/*
+ * Remove entry for the tablespace being dropped.
+ */
+void
+pgstat_drop_tablespace(Oid tablespaceid)
+{
+ pgstat_drop_transactional(PGSTAT_KIND_TABLESPACE, InvalidOid, tablespaceid);
+}
+
+/*
+ * Fetch tablespace statistics.
+ */
+PgStat_StatTabspaceEntry *
+pgstat_fetch_stat_tabspaceentry(Oid tablespaceid)
+{
+ return (PgStat_StatTabspaceEntry *)
+ pgstat_fetch_entry(PGSTAT_KIND_TABLESPACE, InvalidOid, tablespaceid);
+}
+
+/*
+ * Flush out pending stats for the entry.
+ */
+bool
+pgstat_tablespace_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
+{
+ PgStatShared_Tablespace *sharedent;
+ PgStat_StatTabspaceEntry *pendingent;
+
+ pendingent = (PgStat_StatTabspaceEntry *) entry_ref->pending;
+ sharedent = (PgStatShared_Tablespace *) entry_ref->shared_stats;
+
+ if (!pgstat_lock_entry(entry_ref, nowait))
+ return false;
+
+#define PGSTAT_ACCUM_TABSPACECOUNT(item) \
+ (sharedent)->stats.item += (pendingent)->item
+
+ PGSTAT_ACCUM_TABSPACECOUNT(blocks_fetched);
+ PGSTAT_ACCUM_TABSPACECOUNT(blocks_hit);
+ PGSTAT_ACCUM_TABSPACECOUNT(blk_read_time);
+ PGSTAT_ACCUM_TABSPACECOUNT(blk_write_time);
+ PGSTAT_ACCUM_TABSPACECOUNT(temp_files);
+ PGSTAT_ACCUM_TABSPACECOUNT(temp_bytes);
+
+#undef PGSTAT_ACCUM_TABSPACECOUNT
+
+ pgstat_unlock_entry(entry_ref);
+
+ /* Clear pending stats since they have been flushed */
+ memset(pendingent, 0, sizeof(*pendingent));
+
+ return true;
+}
+
+/*
+ * Reset stats reset timestamp.
+ */
+void
+pgstat_tablespace_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
+{
+ ((PgStatShared_Tablespace *) header)->stats.stat_reset_timestamp = ts;
+}
+
+/*
+ * Prepare for reporting tablespace stats.
+ */
+PgStat_StatTabspaceEntry *
+pgstat_prep_tablespace_pending(Oid tablespaceid)
+{
+ PgStat_EntryRef *entry_ref;
+
+ /*
+ * If stats collection is disabled, we don't have anywhere to put the counters.
+ */
+ if (!pgstat_track_counts)
+ return NULL;
+
+ entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_TABLESPACE,
+ InvalidOid, tablespaceid, NULL);
+
+ return (PgStat_StatTabspaceEntry *) entry_ref->pending;
+}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 5f907335990..5e453d11e58 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1928,6 +1928,7 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
XLogPrefetchResetStats();
pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
pgstat_reset_of_kind(PGSTAT_KIND_WAL);
+ pgstat_reset_of_kind(PGSTAT_KIND_TABLESPACE);
PG_RETURN_VOID();
}
@@ -1948,11 +1949,13 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
else if (strcmp(target, "wal") == 0)
pgstat_reset_of_kind(PGSTAT_KIND_WAL);
+ else if (strcmp(target, "tablespace") == 0)
+ pgstat_reset_of_kind(PGSTAT_KIND_TABLESPACE);
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized reset target: \"%s\"", target),
- errhint("Target must be \"archiver\", \"bgwriter\", \"checkpointer\", \"io\", \"recovery_prefetch\", \"slru\", or \"wal\".")));
+ errhint("Target must be \"archiver\", \"bgwriter\", \"checkpointer\", \"io\", \"recovery_prefetch\", \"slru\", \"wal\", or \"tablespace\".")));
PG_RETURN_VOID();
}
@@ -2309,6 +2312,81 @@ pg_stat_get_subscription_stats(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
}
+/*
+ * Returns tablespace statistics for the given tablespace. If the tablespace
+ * statistics is not available, return all-zeros stats.
+ */
+Datum
+pg_stat_get_tablespace(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_GET_TABLESPACE_COLS 7
+ Oid spcoid = PG_GETARG_OID(0);
+ TupleDesc tupdesc;
+ Datum values[PG_STAT_GET_TABLESPACE_COLS] = {0};
+ bool nulls[PG_STAT_GET_TABLESPACE_COLS] = {0};
+ PgStat_StatTabspaceEntry *tsentry;
+ PgStat_StatTabspaceEntry allzero;
+ int i = 0;
+
+ /* Get tablespace stats */
+ tsentry = pgstat_fetch_stat_tabspaceentry(spcoid);
+
+ /* Initialise attributes information in the tuple descriptor */
+ tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_TABLESPACE_COLS);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "blk_read_time",
+ FLOAT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "blk_write_time",
+ FLOAT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "blocks_fetched",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "blocks_hit",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 5, "temp_files",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 6, "temp_bytes",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 7, "stats_reset",
+ TIMESTAMPTZOID, -1, 0);
+
+ tupdesc = BlessTupleDesc(tupdesc);
+
+ if (!tsentry)
+ {
+ /* If the tablespace is not found, initialise its stats */
+ memset(&allzero, 0, sizeof(PgStat_StatTabspaceEntry));
+ tsentry = &allzero;
+ }
+
+ /* blk_read_time */
+ values[i++] = Float8GetDatum(pg_stat_us_to_ms(tsentry->blk_read_time));
+
+ /* blk_write_time */
+ values[i++] = Float8GetDatum(pg_stat_us_to_ms(tsentry->blk_write_time));
+
+ /* blocks_fetched */
+ values[i++] = Int64GetDatum(tsentry->blocks_fetched);
+
+ /* blocks_hit */
+ values[i++] = Int64GetDatum(tsentry->blocks_hit);
+
+ /* temp_files */
+ values[i++] = Int64GetDatum(tsentry->temp_files);
+
+ /* temp_bytes */
+ values[i++] = Int64GetDatum(tsentry->temp_bytes);
+
+ /* stats_reset */
+ if (tsentry->stat_reset_timestamp == 0)
+ nulls[i] = true;
+ else
+ values[i] = TimestampTzGetDatum(tsentry->stat_reset_timestamp);
+
+ Assert(i + 1 == PG_STAT_GET_TABLESPACE_COLS);
+
+ /* Returns the record as Datum */
+ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
+}
+
/*
* Checks for presence of stats for object with provided kind, database oid,
* object oid.
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 420850293f8..359c1453f40 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202603201
+#define CATALOG_VERSION_NO 202603202
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 84e7adde0e5..b8a60b9f30a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6088,6 +6088,14 @@
proargnames => '{name,blks_zeroed,blks_hit,blks_read,blks_written,blks_exists,flushes,truncates,stats_reset}',
prosrc => 'pg_stat_get_slru' },
+{ oid => '8459', descr => 'statistics: tablespace statistics',
+ proname => 'pg_stat_get_tablespace', provolatile => 's',
+ proparallel => 'r', prorettype => 'record', proargtypes => 'oid',
+ proallargtypes => '{oid,float8,float8,int8,int8,int8,int8,timestamptz}',
+ proargmodes => '{i,o,o,o,o,o,o,o}',
+ proargnames => '{tablespaceid,blk_read_time,blk_write_time,blocks_fetched,blocks_hit,temp_files,temp_bytes,stats_reset}',
+ prosrc => 'pg_stat_get_tablespace' },
+
{ oid => '2978', descr => 'statistics: number of function calls',
proname => 'pg_stat_get_function_calls', provolatile => 's',
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 216b93492ba..d51ea208ddc 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -180,6 +180,7 @@ typedef struct PgStat_TableStatus
{
Oid id; /* table's OID */
bool shared; /* is it a shared catalog? */
+ Oid reltablespace; /* tablespace OID */
struct PgStat_TableXactStatus *trans; /* lowest subxact's counts */
PgStat_TableCounts counts; /* event counts to be sent */
Relation relation; /* rel that is using this entry */
@@ -383,6 +384,18 @@ typedef struct PgStat_StatDBEntry
TimestampTz stat_reset_timestamp;
} PgStat_StatDBEntry;
+typedef struct PgStat_StatTabspaceEntry
+{
+ PgStat_Counter blk_read_time; /* times in microseconds */
+ PgStat_Counter blk_write_time;
+ PgStat_Counter blocks_fetched;
+ PgStat_Counter blocks_hit;
+ PgStat_Counter temp_files;
+ PgStat_Counter temp_bytes;
+
+ TimestampTz stat_reset_timestamp;
+} PgStat_StatTabspaceEntry;
+
typedef struct PgStat_StatFuncEntry
{
PgStat_Counter numcalls;
@@ -743,6 +756,15 @@ extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry_ext(bool shared,
extern PgStat_TableStatus *find_tabstat_entry(Oid rel_id);
+/*
+ * Functions in pgstat_tablespace.c
+ */
+
+extern void pgstat_drop_tablespace(Oid tablespaceid);
+extern PgStat_StatTabspaceEntry *pgstat_fetch_stat_tabspaceentry(Oid tablespaceid);
+extern PgStat_StatTabspaceEntry *pgstat_prep_tablespace_pending(Oid tablespaceid);
+
+
/*
* Functions in pgstat_replslot.c
*/
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index ddd06304e97..a2c501edf00 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -323,7 +323,7 @@ extern void pgstat_clear_backend_activity_snapshot(void);
extern void pgstat_report_activity(BackendState state, const char *cmd_str);
extern void pgstat_report_query_id(int64 query_id, bool force);
extern void pgstat_report_plan_id(int64 plan_id, bool force);
-extern void pgstat_report_tempfile(size_t filesize);
+extern void pgstat_report_tempfile(size_t filesize, const char *path);
extern void pgstat_report_appname(const char *appname);
extern void pgstat_report_xact_timestamp(TimestampTz tstamp);
extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 9b8fbae00ed..ff0ad4fda54 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -494,6 +494,12 @@ typedef struct PgStatShared_Database
PgStat_StatDBEntry stats;
} PgStatShared_Database;
+typedef struct PgStatShared_Tablespace
+{
+ PgStatShared_Common header;
+ PgStat_StatTabspaceEntry stats;
+} PgStatShared_Tablespace;
+
typedef struct PgStatShared_Relation
{
PgStatShared_Common header;
@@ -731,6 +737,8 @@ extern PgStat_StatDBEntry *pgstat_prep_database_pending(Oid dboid);
extern void pgstat_reset_database_timestamp(Oid dboid, TimestampTz ts);
extern bool pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait);
extern void pgstat_database_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts);
+extern bool pgstat_tablespace_flush_cb(PgStat_EntryRef *entry_ref, bool nowait);
+extern void pgstat_tablespace_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts);
/*
diff --git a/src/include/utils/pgstat_kind.h b/src/include/utils/pgstat_kind.h
index c30b6235623..a21d6c3b925 100644
--- a/src/include/utils/pgstat_kind.h
+++ b/src/include/utils/pgstat_kind.h
@@ -38,9 +38,10 @@
#define PGSTAT_KIND_IO 10
#define PGSTAT_KIND_SLRU 11
#define PGSTAT_KIND_WAL 12
+#define PGSTAT_KIND_TABLESPACE 13
#define PGSTAT_KIND_BUILTIN_MIN PGSTAT_KIND_DATABASE
-#define PGSTAT_KIND_BUILTIN_MAX PGSTAT_KIND_WAL
+#define PGSTAT_KIND_BUILTIN_MAX PGSTAT_KIND_TABLESPACE
#define PGSTAT_KIND_BUILTIN_SIZE (PGSTAT_KIND_BUILTIN_MAX + 1)
/* Custom stats kinds */
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 32bea58db2c..72da2a77d7d 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2301,6 +2301,17 @@ pg_stat_sys_tables| SELECT relid,
stats_reset
FROM pg_stat_all_tables
WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
+pg_stat_tablespace| SELECT t.oid AS tablespace_id,
+ t.spcname AS tablespace_name,
+ s.blk_read_time,
+ s.blk_write_time,
+ s.blocks_fetched,
+ s.blocks_hit,
+ s.temp_files,
+ s.temp_bytes,
+ s.stats_reset
+ FROM (pg_tablespace t
+ LEFT JOIN LATERAL pg_stat_get_tablespace(t.oid) s(blk_read_time, blk_write_time, blocks_fetched, blocks_hit, temp_files, temp_bytes, stats_reset) ON (true));
pg_stat_user_functions| SELECT p.oid AS funcid,
n.nspname AS schemaname,
p.proname AS funcname,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index b99462bf946..4d2db312e47 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1130,7 +1130,7 @@ SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
-- Test error case for reset_shared with unknown stats type
SELECT pg_stat_reset_shared('unknown');
ERROR: unrecognized reset target: "unknown"
-HINT: Target must be "archiver", "bgwriter", "checkpointer", "io", "recovery_prefetch", "slru", or "wal".
+HINT: Target must be "archiver", "bgwriter", "checkpointer", "io", "recovery_prefetch", "slru", "wal", or "tablespace".
-- Test that reset works for pg_stat_database and pg_stat_database_conflicts
-- Since pg_stat_database stats_reset starts out as NULL, reset it once first so that we
-- have a baseline for comparison. The same for pg_stat_database_conflicts as it shares
@@ -1958,4 +1958,85 @@ SELECT * FROM check_estimated_rows('SELECT * FROM table_fillfactor');
(1 row)
DROP TABLE table_fillfactor;
+-- Test pg_stat_tablespace
+SELECT count(*) > 0 FROM pg_stat_tablespace;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT tablespace_name FROM pg_stat_tablespace WHERE tablespace_name IN ('pg_default', 'pg_global') ORDER BY tablespace_name;
+ tablespace_name
+-----------------
+ pg_default
+ pg_global
+(2 rows)
+
+-- Test block stats in pg_stat_tablespace
+SET track_io_timing = on;
+CREATE TABLE test_tablespace_stats (a int);
+INSERT INTO test_tablespace_stats SELECT generate_series(1, 100);
+SELECT count(*) FROM test_tablespace_stats;
+ count
+-------
+ 100
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT blocks_fetched >= 0 AS has_blocks_fetched, blocks_hit >= 0 AS has_blocks_hit, blk_read_time >= 0 AS has_blk_read_time, blk_write_time >= 0 AS has_blk_write_time FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default';
+ has_blocks_fetched | has_blocks_hit | has_blk_read_time | has_blk_write_time
+--------------------+----------------+-------------------+--------------------
+ t | t | t | t
+(1 row)
+
+DROP TABLE test_tablespace_stats;
+-- Test temp file stats in pg_stat_tablespace
+-- Use a sort that exceeds work_mem to force temp file usage
+SET work_mem = '64kB';
+SELECT count(*) FROM (SELECT * FROM generate_series(1, 10000) AS s ORDER BY s DESC) AS foo;
+ count
+-------
+ 10000
+(1 row)
+
+RESET work_mem;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+-- We expect temp files to be in pg_default if not specified otherwise
+SELECT temp_files > 0 AS has_temp_files, temp_bytes > 0 AS has_temp_bytes FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default';
+ has_temp_files | has_temp_bytes
+----------------+----------------
+ t | t
+(1 row)
+
+-- Test reset for pg_stat_tablespace
+-- Ensure we have a timestamp to compare
+SELECT pg_stat_reset_shared('tablespace');
+ pg_stat_reset_shared
+----------------------
+
+(1 row)
+
+SELECT stats_reset AS ts_reset_before FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default' \gset
+SELECT pg_stat_reset_shared('tablespace');
+ pg_stat_reset_shared
+----------------------
+
+(1 row)
+
+SELECT stats_reset > :'ts_reset_before'::timestamptz FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default';
+ ?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 941222cf0be..59d92c06f2b 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -964,4 +964,40 @@ SELECT * FROM check_estimated_rows('SELECT * FROM table_fillfactor');
DROP TABLE table_fillfactor;
+-- Test pg_stat_tablespace
+SELECT count(*) > 0 FROM pg_stat_tablespace;
+
+SELECT tablespace_name FROM pg_stat_tablespace WHERE tablespace_name IN ('pg_default', 'pg_global') ORDER BY tablespace_name;
+
+-- Test block stats in pg_stat_tablespace
+SET track_io_timing = on;
+CREATE TABLE test_tablespace_stats (a int);
+INSERT INTO test_tablespace_stats SELECT generate_series(1, 100);
+SELECT count(*) FROM test_tablespace_stats;
+
+SELECT pg_stat_force_next_flush();
+
+SELECT blocks_fetched >= 0 AS has_blocks_fetched, blocks_hit >= 0 AS has_blocks_hit, blk_read_time >= 0 AS has_blk_read_time, blk_write_time >= 0 AS has_blk_write_time FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default';
+
+DROP TABLE test_tablespace_stats;
+-- Test temp file stats in pg_stat_tablespace
+-- Use a sort that exceeds work_mem to force temp file usage
+SET work_mem = '64kB';
+SELECT count(*) FROM (SELECT * FROM generate_series(1, 10000) AS s ORDER BY s DESC) AS foo;
+
+RESET work_mem;
+SELECT pg_stat_force_next_flush();
+
+-- We expect temp files to be in pg_default if not specified otherwise
+SELECT temp_files > 0 AS has_temp_files, temp_bytes > 0 AS has_temp_bytes FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default';
+
+-- Test reset for pg_stat_tablespace
+-- Ensure we have a timestamp to compare
+SELECT pg_stat_reset_shared('tablespace');
+
+SELECT stats_reset AS ts_reset_before FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default' \gset
+SELECT pg_stat_reset_shared('tablespace');
+
+SELECT stats_reset > :'ts_reset_before'::timestamptz FROM pg_stat_tablespace WHERE tablespace_name = 'pg_default';
+
-- End of Stats Test
--
2.53.0.983.g0bb29b3bc5-goog
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [Patch] New pg_stat_tablespace view
In-Reply-To: <CAGRkXqRHsZw3+aeNZgnBYduQSY7qg1O4MBdmyFjK-A+TU1-b-A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox