Обсуждение: [Patch] New pg_stat_tablespace view

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

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.