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
