[Patch] New pg_stat_tablespace view

On Mon, Mar 23, 2026 at 3:08 PM shihao zhong <zhong950419@gmail.com> wrote:

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

New version fix the CI/CD

Hello!

I get assertion failures with the patch, all it takes is a simple select:

SELECT * FROM pg_stat_tablespace;
TRAP: failed Assert("tupdesc->firstNonCachedOffsetAttr >= 0"),
File: execTuples.c, Line: 2341

The test suite also fails with similar errors.

在 2026/3/24 10:49, shihao zhong 写道:

On Mon, Mar 23, 2026 at 3:08 PM shihao zhong <zhong950419@gmail.com> wrote:

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

New version fix the CI/CD

Hello, shihao

I applied it on master and did a simple test. Here are some minor review
comments:

1. The type of temp_bytes in monitoring.sgml should be bigint, but it
was written as numeric here.

2. The pgstat_drop_tablespace function doesn't seem to be called.

Thank you.

--
regards,
songjinzhou

On Tue, Mar 24, 2026 at 3:22 AM songjinzhou
<tsinghualucky912@foxmail.com> wrote:

在 2026/3/24 10:49, shihao zhong 写道:

On Mon, Mar 23, 2026 at 3:08 PM shihao zhong <zhong950419@gmail.com> wrote:

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

New version fix the CI/CD

Hello, shihao

I applied it on master and did a simple test. Here are some minor review
comments:

1. The type of temp_bytes in monitoring.sgml should be bigint, but it
was written as numeric here.

2. The pgstat_drop_tablespace function doesn't seem to be called.

Thank you.

--
regards,
songjinzhou

Hi SongJin,

Thanks for your reviewing, the v2 patch addresses both 1 and 2.

Thanks,
Shihao

Hello!

blk_read_time and blk_write_time doesn't seem to work, they show 0 to
me even after some workloads, and I don't see any assignments in the
code. The testcase also checks for "blk_read_time >= 0" which
trivially succeeds.

blocks_fetched is also misleading, it includes both reads and cache
hits. pg_stat_database calls this column blocks_read, and properly
substracts blocks_hit from it.

+ rel->pgstat_info->reltablespace = rel->rd_locator.spcOid;

Shouldn't this be included in TwoPhasePgStatRecord / pgstat_twophase_postcommit?

On Tue, Mar 24, 2026 at 6:11 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

Hello!

blk_read_time and blk_write_time doesn't seem to work, they show 0 to
me even after some workloads, and I don't see any assignments in the
code. The testcase also checks for "blk_read_time >= 0" which
trivially succeeds.

blocks_fetched is also misleading, it includes both reads and cache
hits. pg_stat_database calls this column blocks_read, and properly
substracts blocks_hit from it.

+ rel->pgstat_info->reltablespace = rel->rd_locator.spcOid;

Shouldn't this be included in TwoPhasePgStatRecord / pgstat_twophase_postcommit?

Hi Zsolt and Jian,

Thanks for the feedback. I've attached v3, addressing all comments.
Notably, I've included tuple-level stats in the pg_stat_tablespace
view to align with the addition of SpaceOid in TwoPhasePgStatRecord.

Thanks,
Shihao

On Fri, Mar 27, 2026 at 2:05 PM shihao zhong <zhong950419@gmail.com> wrote:

On Tue, Mar 24, 2026 at 6:11 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

Hello!

blk_read_time and blk_write_time doesn't seem to work, they show 0 to
me even after some workloads, and I don't see any assignments in the
code. The testcase also checks for "blk_read_time >= 0" which
trivially succeeds.

blocks_fetched is also misleading, it includes both reads and cache
hits. pg_stat_database calls this column blocks_read, and properly
substracts blocks_hit from it.

+ rel->pgstat_info->reltablespace = rel->rd_locator.spcOid;

Shouldn't this be included in TwoPhasePgStatRecord / pgstat_twophase_postcommit?

Hi Zsolt and Jian,

Thanks for the feedback. I've attached v3, addressing all comments.
Notably, I've included tuple-level stats in the pg_stat_tablespace
view to align with the addition of SpaceOid in TwoPhasePgStatRecord.

Thanks,
Shihao

Rebase with head.

On Wed, Apr 1, 2026 at 9:54 PM shihao zhong <zhong950419@gmail.com> wrote:

On Fri, Mar 27, 2026 at 2:05 PM shihao zhong <zhong950419@gmail.com> wrote:

On Tue, Mar 24, 2026 at 6:11 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

Hello!

blk_read_time and blk_write_time doesn't seem to work, they show 0 to
me even after some workloads, and I don't see any assignments in the
code. The testcase also checks for "blk_read_time >= 0" which
trivially succeeds.

blocks_fetched is also misleading, it includes both reads and cache
hits. pg_stat_database calls this column blocks_read, and properly
substracts blocks_hit from it.

+ rel->pgstat_info->reltablespace = rel->rd_locator.spcOid;

Shouldn't this be included in TwoPhasePgStatRecord / pgstat_twophase_postcommit?

Hi Zsolt and Jian,

Thanks for the feedback. I've attached v3, addressing all comments.
Notably, I've included tuple-level stats in the pg_stat_tablespace
view to align with the addition of SpaceOid in TwoPhasePgStatRecord.

Thanks,
Shihao

Rebase with head.

Rebase the patch and fix a crash in standby.

Thanks,
Shihao

Hi Shihao,

I am having a problem building the v5 patch locally. I get the following
compile errors:

```
pgstat_relation.c: In function ‘pgstat_relation_flush_cb’:
pgstat_relation.c:1009:64: error: ‘PgStat_TableCounts’ has no member named
‘tuples_inserted’; did you mean ‘tuples_returned’?
1009 | tsentry->tuples_inserted +=
lstats->tab.counts.tuples_inserted;
|
^~~~~~~~~~~~~~~
|
tuples_returned
pgstat_relation.c:1010:62: error: ‘PgStat_TableCounts’ has no member named
‘tuples_updated’
1010 | tsentry->tuples_updated +=
lstats->tab.counts.tuples_updated;
| ^
pgstat_relation.c:1011:63: error: ‘PgStat_TableCounts’ has no member named
‘tuples_deleted’; did you mean ‘tuples_fetched’?
1011 | tsentry->tuples_deleted +=
lstats->tab.counts.tuples_deleted;
|
^~~~~~~~~~~~~~
|
tuples_fetched
```

I believe these entries should be updated to use
`lstats->tab.counts_xact....`??

See:
https://github.com/postgres/postgres/commit/3f2f5e7c4cc5a917e77a3d5a0c4353b702aa7307

Regards

Hi Ahmed,

Thanks for reviewing my patch!

I am having a problem building the v5 patch locally. I get the following

compile errors:
You are right: 3f2f5e7c4c moved those counters to
PgStat_TableCountsXact and my v5 missed it. Fixed in v6.

While testing that fix I found a worse bug, which v6 also fixes.

The buffer manager reports block I/O timings from the checkpointer and the
background writer too. Those two processes never call pgstat_report_stat(),
so the pending entry they created was never flushed. That caused two
problems.

First, an unflushed pending entry keeps its shared entry alive, because
pgstat_gc_entry_refs() skips refs that still have pending data. So after
DROP TABLESPACE the entry could not be freed, and the checkpointer crashed
while writing the stats file at shutdown:

TRAP: failed Assert("!ps->dropped"), File: "pgstat.c", Line: 1776
LOG: checkpointer process (PID ...) was terminated by signal 6

To reproduce, on an assert build with track_io_timing=on: create a
tablespace, insert enough rows that the checkpointer writes to it,
CHECKPOINT, DROP TABLE, DROP TABLESPACE, then pg_ctl stop -m fast.

Second, blk_write_time stayed at zero while the server ran. In one test the
checkpointer wrote 161 buffers in 3 ms and the view did not move at all.
The value only appeared after a restart. Most writes come from the
checkpointer, so the column was close to useless.

v6 keeps these timings in process-local memory and flushes them through
flush_static_cb, the same way PGSTAT_KIND_BACKEND handles its own data.
Backends flush through pgstat_report_stat(). The checkpointer and the
background writer call pgstat_flush_tablespace_times() from
pgstat_report_checkpointer() and pgstat_report_bgwriter().

v6 also adds pgstat_create_tablespace(), called from CreateTableSpace().
v5 had a drop but no create, and this clears old stats if a tablespace OID
is ever reused.

Thanks,

Shihao

Hi Shihao,

On 14/09/2026 04:46, shihao zhong wrote:

Hi Ahmed,

Thanks for reviewing my patch!

I am having a problem building the v5 patch locally. I get the

following compile errors:
You are right: 3f2f5e7c4c moved those counters to
PgStat_TableCountsXact and my v5 missed it. Fixed in v6.

While testing that fix I found a worse bug, which v6 also fixes.

The buffer manager reports block I/O timings from the checkpointer and the
background writer too. Those two processes never call pgstat_report_stat(),
so the pending entry they created was never flushed. That caused two
problems.

First, an unflushed pending entry keeps its shared entry alive, because
pgstat_gc_entry_refs() skips refs that still have pending data. So after
DROP TABLESPACE the entry could not be freed, and the checkpointer crashed
while writing the stats file at shutdown:

TRAP: failed Assert("!ps->dropped"), File: "pgstat.c", Line: 1776
LOG: checkpointer process (PID ...) was terminated by signal 6

To reproduce, on an assert build with track_io_timing=on: create a
tablespace, insert enough rows that the checkpointer writes to it,
CHECKPOINT, DROP TABLE, DROP TABLESPACE, then pg_ctl stop -m fast.

Second, blk_write_time stayed at zero while the server ran. In one test the
checkpointer wrote 161 buffers in 3 ms and the view did not move at all.
The value only appeared after a restart. Most writes come from the
checkpointer, so the column was close to useless.

v6 keeps these timings in process-local memory and flushes them through
flush_static_cb, the same way PGSTAT_KIND_BACKEND handles its own data.
Backends flush through pgstat_report_stat(). The checkpointer and the
background writer call pgstat_flush_tablespace_times() from
pgstat_report_checkpointer() and pgstat_report_bgwriter().

v6 also adds pgstat_create_tablespace(), called from CreateTableSpace().
v5 had a drop but no create, and this clears old stats if a tablespace OID
is ever reused.

Thanks,

Shihao

thanks for the updated patch.

I noticed that one test still checks for >= 0 for some columns. As Zsolt
Parragi mentioned, this condition is trivially always true. This might
still need to be addressed.

I watched the numbers in the view after some workload on a database that
is completely in one tablespace that only contains this database. They
add up to what pg_stat_database reports. One thing worth mentioning,
though, is that there is some skew in blk_read_time and blk_write_time.
The following queries compare the database (first row) to the tablespace
(second row).

test=# select
d.blks_read,
d.blks_hit,
d.blk_read_time,
d.blk_write_time
from
pg_stat_database d where datname = 'test'
union all
select
t.blks_read,
t.blks_hit,
t.blk_read_time,
t.blk_write_time
from pg_stat_tablespace t where tablespace_name = 'tbs_alt';

blks_read | blks_hit | blk_read_time | blk_write_time
-----------+----------+---------------+----------------
129030 | 50180798 | 3.317 | 457.384
129030 | 50180798 | 3.442 | 467.34
(2 rows)

I attribute the difference to pgstat_count_tablespace_blk_*_time being
called after pgstat_count_io_op_time and therefore registering the time
spent a little later.

The tuples add up fine:

test=# select
d.tup_returned,
d.tup_fetched,
d.tup_inserted,
d.tup_updated,
d.tup_deleted
from
pg_stat_database d where datname = 'test'
union all
select
t.tup_returned,
t.tup_fetched,
t.tup_inserted,
t.tup_updated,
t.tup_deleted
from pg_stat_tablespace t where tablespace_name = 'tbs_alt';

tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted
--------------+-------------+--------------+-------------+-------------
20000926 | 32 | 10000000 | 10000001 | 10000000
20000926 | 32 | 10000000 | 10000001 | 10000000
(2 rows)

The temp file stats also add up to what pg_stat_database reports:

test=# select
d.temp_files,
d.temp_bytes
from pg_stat_database d where datname='test'
union all
select
t.temp_files,
t.temp_bytes
from pg_stat_tablespace t where tablespace_name ='tbs_alt';

temp_files | temp_bytes
------------+------------
2 | 1202405376
2 | 1202405376

One problem I came across while testing was a mismatch in numbers after
a server restart when there is an open session in the database.

The setup involves the following:

postgres=# create tablespace tbs_alt location '/var/lib/postgresql/tbs_alt';
CREATE TABLESPACE
postgres=# create database test tablespace tbs_alt;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table test (id bigint);
CREATE TABLE
test=#

I ran the following several times (the FATAL message represents pg_ctl
restart):

test=# truncate test;
TRUNCATE TABLE
test=# select pg_stat_reset();
pg_stat_reset
---------------

(1 row)

test=# select pg_stat_reset_shared();
pg_stat_reset_shared
----------------------

(1 row)

test=# insert into test select generate_series(1,10000);
INSERT 0 10000

test=# select 1;

FATAL: terminating connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

test=# select
datname,
d.blks_read,
d.blks_hit
from
pg_stat_database d where datname = 'test'
union all
select
tablespace_name,
t.blks_read,
t.blks_hit
from pg_stat_tablespace t where tablespace_name = 'tbs_alt';

Examples of results from 3 different (non-consecutive) runs:

datname | blks_read | blks_hit
---------+-----------+----------
test | 105 | 11713
tbs_alt | 102 | 11706
(2 rows)

datname | blks_read | blks_hit
---------+-----------+----------
test | 112 | 12055
tbs_alt | 115 | 12062
(2 rows)

datname | blks_read | blks_hit
---------+-----------+----------
test | 99 | 11692
tbs_alt | 99 | 11692
(2 rows)

I am not quite sure why these numbers differ, but whenever they are not
the same, blks_read differs exactly by 3 and blks_hit by 7. The
difference stays constant at 3 and 7 as more activity accumulates. This
might be worth investigating.

All in all this is a very interesting patch. It could be very useful for
monitoring and day-to-day DBA workflows.

Best
Bernd

Hi Bernd,

Thanks for testing.

blks_read differs exactly by 3 and blks_hit by 7

I can reproduce this, and it does not need a restart. pg_stat_reset()
clears the database entry and pg_stat_reset_shared() clears the tablespace
entry. If more than a second passes between them, the backend flushes its
pending counts in the gap, and the entry that was reset first keeps them.
Swapping the two resets flips the sign. With both resets in one statement
the numbers always match.

there is some skew in blk_read_time and blk_write_time

Part of it was the second clock read. v7 reuses the time that
pgstat_count_io_op_time() already computed.

one test still checks for >= 0

Fixed.

v7 attached.

Thanks,
Shihao