[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

Hi all,

On Mon, Sep 21, 2026 at 10:45 AM shihao zhong <zhong950419@gmail.com> wrote:

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.

Thank you for the updated patch. I tested the v7 patch and verified
that the regression suite completed successfully, with all 239 tests
passing, including the stats and tablespace tests. I also performed
targeted testing using a dedicated tablespace. The view reported
tuple activity for inserts, updates, and deletes, along with nonzero
block-write timing when I/O timing was enabled. The checkpoint
completed successfully, and the server log showed no errors in the
inspected output. After testing, I dropped the test table and
tablespace and shut down the separate test server cleanly. One concern
is that this targeted workload did not demonstrate nonzero
physical-read counters or read timing. Apart from this limitation, I
did not observe any functional or regression errors in the tests I
performed.

Regards,
Solai

Hi Solai,

Thanks for testing.

One concern is that this targeted workload did not demonstrate nonzero
physical-read counters or read timing.

That is expected. The pages you just wrote are still in shared buffers, so
later scans count as blks_hit and there is nothing to read from disk.
Restart
the server and scan the table again, and blks_read and blk_read_time should
go up.

The regression test covers this path.

Thanks,
Shihao

Hi,

On 2026-09-18 23:07:07 -0400, shihao zhong wrote:

@@ -1824,6 +1824,7 @@ WaitReadBuffers(ReadBuffersOperation *operation)
!pgaio_wref_check_done(&operation->io_wref))
{
instr_time io_start = pgstat_prepare_io_time(track_io_timing);
+ instr_time io_time;

pgaio_wref_wait(&operation->io_wref);
needed_wait = true;
@@ -1833,8 +1834,10 @@ WaitReadBuffers(ReadBuffersOperation *operation)
* itself was already counted earlier in AsyncReadBuffers() --
* either by us or by another backend if this is a foreign IO.
*/
-				pgstat_count_io_op_time(io_object, io_context, IOOP_READ,
-										io_start, 0, 0);
+				io_time = pgstat_count_io_op_time(io_object, io_context, IOOP_READ,
+												  io_start, 0, 0);
+				pgstat_count_tablespace_blk_read_time(operation->smgr->smgr_rlocator.locator.spcOid,
+													  io_time);
}
else
{

I am not on board to add duplicate counting to add these places, particularly
not if that requires a hash table lookup every single time.

*If* we want this, it really needs to be designed to avoid all this duplicate
work, both in the amount of code added to a bunch of places, and in the amount
of work that's needed for stats lookups.

Greetings,

Andres Freund

Hi Andres,

Thanks for looking.

I am not on board to add duplicate counting to add these places

The clock is already read only once, but you are right that each site still
makes a second call. That should go.

What I would like to do instead is count it in one place, inside
pgstat_count_io_op_time(), right next to pgstat_count_backend_io_op_time().
I would add a variant of that function that takes the tablespace OID, and
keep the current one as a wrapper, so the WAL callers are not touched.

particularly not if that requires a hash table lookup every single time.

The lookup is in a backend local table, no lock and no shared memory. It
only
runs when track_io_timing is on and a real read or write happened, so it
sits
next to two clock reads and a syscall. If that is still too much, I can
keep a
pointer to the last tablespace used, and the common case becomes one
compare.

*If* we want this

pg_stat_io cannot tell you which device is slow. With tablespaces on
different storage, that is the first question people ask, and today the only
answer is to go to OS tools and map files back by hand.

Would that shape work for you? I would rather agree on it before sending v8.

Thanks,
Shihao

Hi Andres,

v8 counts the time in pgstat_count_io_op_time(), next to the per-backend
time. bufmgr.c and localbuf.c call a variant that takes the tablespace OID
as one more argument, and that is their whole change. The last tablespace
entry used is cached, so the usual case is one compare and an add, with no
hash lookup. The times are flushed with the IO stats, so the checkpointer
and bgwriter changes are gone.

It also fixes an assertion failure at shutdown in v7, after dropping a
tablespace the checkpointer had written to. There is a test for it in
029_stats_restart.pl.

As for why, here is one tablespace on a RAM disk and one on the SSD, same
table, same 5000 random lookups, with debug_io_direct=data to keep the OS
cache out:

tablespace_name | blks_read | blk_read_time | us_per_block
-----------------+-----------+---------------+--------------
ts_ssd | 9645 | 667.8 | 69.2
ts_ram | 9645 | 170.1 | 17.6

pg_stat_io and pg_stat_database only show the sum.

Regards,
Shihao

Hi Shihao,

On 19/09/2026 05:07, shihao zhong wrote:

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.

Yes, this explains it.

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.

I like this: Having the test being strictly bigger and comparing to
beforehand values makes much sense to me.

v7 attached.

Thanks,
Shihao

Best
Bernd

Hi,

cfbot fails on v8 now and then. The stats test checks that
blk_read_time grows after reading the table back, and that is not
guaranteed.

The read time only counts how long the backend waits for a read. If
the read is already done when the backend looks, nothing is added.
pg_stat_database behaves the same way on master.

v9 drops that one check. No code changes. I also shortened the commit
message.

Thanks,
Shihao