Redesign per-backend statistics

Hi hackers,

The current per-backend statistics design excludes several process types and
duplicates accounting and storage: WAL, lock, and IO activity is reported both to
the global statistics and to PGSTAT_KIND_BACKEND.

This duplicated work was raised as a concern in [1], and its cost would grow as
more statistics acquire per-backend reporting.

Andres proposed a new design in [2]:

"
I think what we'd want is rather something where for each applicable stats kind
we have a shared counter for all exited backends and then per-backend counters
for live backends, with helpers to aggregate the exited + live stats to a total
"

and this is what the attached patch series is implementing.

Its main benefits are:

1/ each counter is reported and stored once, rather than in both the fixed global
statistics and PGSTAT_KIND_BACKEND.

2/ WAL, lock, and IO flushes update a per backend entry without acquiring
the corresponding global statistics lock or performing a hash lookup. This removes
global lock contention from the flush path.

3/ WAL, lock, and IO statistics have independent storage and locking instead of
sharing one PGSTAT_KIND_BACKEND entry.

4/ per-backend statistics become available for auxiliary processes previously
excluded by the backend type filtering.

There are 2 tradeoff though:

1/ fetching global statistics now requires combining all live entries.

2/ a shared statistics reset now clears both the global stats and all live entries.
Otherwise, values from live entries would immediately reappear in the global view.

The first tradeoff moves work from the frequent flush path to the comparatively
infrequent query path. The second is a documented behavior change.

The patch series is organized that way:

0001: add tests for per-backend statistics

It adds new tests that will serve as compatibility coverage for the redesign.
It could be applied while we are discussing the other patches.

0002: add new infrastructure for per-backend statistics

It introduces the common per-backend entry header, the kind metadata needed to
describe per-backend storage, and backend-local state holding the attached dshash
and cached entry pointer. It provides the common operations for creating and
attaching the hashes, creating and caching the current process's entries, fetching
an individual entry, constructing consistent snapshots, transferring entries into
global statistics, removing entries, and the accumulation at clean server shutdown.

No statistics kind registers per-backend metadata in this patch.

0003 moves WAL statistics to the infrastructure introduced by 0002.
0004 performs the corresponding conversion for lock statistics.
0005 performs the corresponding conversion for IO statistics. Once WAL, lock,
and IO have moved, PGSTAT_KIND_BACKEND contains no data, so this patch also removes
that kind and pgstat_backend.c, together with their obsolete infrastructure.

Design explanation for the new hashes:

- a fixed array indexed by ProcNumber would avoid hash operations, but it would
reserve shared memory for every possible process slot and every participating kind.
Queries could also have to inspect unused slots. The dshash allocates entries
for processes that actually exist and lets queries iterate those entries directly.

- reusing the current variable numbered statistics hash would require aggregate
queries to scan unrelated statistics entries or require another structure for
enumerating only the live entries of that kind. A dedicated per kind hash provides
that enumeration directly.

Remark:

The patch series limits this new infrastructure to built in fixed numbered
statistics kinds as this is the only use case we have had so far. We could extend
to variable ones later on if needed.

[1]: https://postgr.es/m/7fhpds4xqk6bnudzmzkqi33pinsxammpljwde5gfkjdygvejrj@ojkzfr7dxkmm
[2]: https://postgr.es/m/et272fdhdx6yphlgzvrgsf7bgwnf3vqciwp4gxqubro42uaflp%40ohslaocvwgvi

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

On Mon, Aug 03, 2026 at 02:20:16PM +0000, Bertrand Drouvot wrote:
> 0001: add tests for per-backend statistics
>
> It adds new tests that will serve as compatibility coverage for the redesign.
> It could be applied while we are discussing the other patches.

That seems useful on its own. Will look at that in details first.

> 0002: add new infrastructure for per-backend statistics
>
> It introduces the common per-backend entry header, the kind metadata needed to
> describe per-backend storage, and backend-local state holding the attached dshash
> and cached entry pointer. It provides the common operations for creating and
> attaching the hashes, creating and caching the current process's entries, fetching
> an individual entry, constructing consistent snapshots, transferring entries into
> global statistics, removing entries, and the accumulation at clean server shutdown.

Okay, so if I get it right when a backend start we get a new reference
to the new dshash you are introducing, then each backend uses this
reference to push its stats updates. This is moving the cost of
aggregating the data when querying the data of each backend for WAL,
IO and lock stats, rather than do twice the aggregate for the central
WAL/IO/lock data plus the backend counterpart on HEAD.

Then the reason why you are using a new dshash to keep track of the
backend data is cost: you need to read all the backend-side data when
querying pg_stat_io, pg_stat_wal or pg_stat_lock, and you don't want
to trigger a full sequential scan of the dshash.

Hmm. First, do we need a dshash at all? The number of backends is
fixed at startup so we could use a set of arrays instead for a cheaper
access (lock, WAL and IO), allocated in shmem? The locking gets fuzzy
then:
- Data is written by each backend.
- Data could be read by anything else.
Using one LWLock for each backend sounds costly just for more
correctness with the stats, and we don't have that many writes anyway?

Second, you may be interested in this patch:
https://www.postgresql.org/message-id/CAA5RZ0supQBxSkh=CWB39=j+cL3hHcLPki3tcBk0B1r4fesg_g@mail.gmail.com
This is for PGSS, but could be applied to your patch set. The idea is
simple: keep the stats kind for backends, but register a dedicated
dshash for it rather than having more dsa facilities in the area of
pgstat.c.

pgstat.c becomes much larger, with a bunch of knowledge now related to
backends. Would it make sense to invent a pgstat_snapshot.c where the
existing snapshot-related code now in pgstat.c could be moved? Or
perhaps pgstat_backend.c should be kept, with its snapshot code there?
Keeping all that knowledge in pgstat.c is not really nice, at least
IMV.

> No statistics kind registers per-backend metadata in this patch.
>
> 0003 moves WAL statistics to the infrastructure introduced by 0002.
> 0004 performs the corresponding conversion for lock statistics.
> 0005 performs the corresponding conversion for IO statistics. Once WAL, lock,
> and IO have moved, PGSTAT_KIND_BACKEND contains no data, so this patch also removes
> that kind and pgstat_backend.c, together with their obsolete infrastructure.

0003~0005 are a bit boring, in the good sense. Perhaps you should
split 0005 into a 0005 for the IO move to these new APIs and a 0006 to
remove the stats kind. Based on my other comments, I am not actually
convinced that we absolutely have to drop PGSTAT_KIND_BACKEND, quite
the contrary.
--
Michael

Hi,

On Fri, Aug 07, 2026 at 10:39:04AM +0900, Michael Paquier wrote:
> On Mon, Aug 03, 2026 at 02:20:16PM +0000, Bertrand Drouvot wrote:
> > 0001: add tests for per-backend statistics
> >
> > It adds new tests that will serve as compatibility coverage for the redesign.
> > It could be applied while we are discussing the other patches.
>
> That seems useful on its own. Will look at that in details first.

Thanks!

> > 0002: add new infrastructure for per-backend statistics
> >
> Okay, so if I get it right when a backend start we get a new reference
> to the new dshash you are introducing, then each backend uses this
> reference to push its stats updates.

Yeah, that's the idea. Each process creates and caches its entries during
initialization. Flushes then use the cached pointers, without a hash lookup.
Only the global WAL, Lock and I/O queries need to scan and aggregate all live
entries.

> This is moving the cost of
> aggregating the data when querying the data of each backend for WAL,
> IO and lock stats, rather than do twice the aggregate for the central
> WAL/IO/lock data plus the backend counterpart on HEAD.

Right, with one precision: the aggregation cost moves to queries of the global
views, not to queries of an individual backend. A per-backend fetch still looks
up only one ProcNumber entry. On current master, a flush updates both the fixed
global statistics and PGSTAT_KIND_BACKEND. With the new design it updates
only the live entry, and a global query combines the fixed accumulator with all
live entries.

> Then the reason why you are using a new dshash to keep track of the
> backend data is cost: you need to read all the backend-side data when
> querying pg_stat_io, pg_stat_wal or pg_stat_lock, and you don't want
> to trigger a full sequential scan of the dshash.
>
> Hmm. First, do we need a dshash at all? The number of backends is
> fixed at startup so we could use a set of arrays instead for a cheaper
> access (lock, WAL and IO), allocated in shmem?

Yeah, that was my first comment in the "Design explanation for the new hashes"
section of my first email in this thread:

an earlier POC version used arrays indexed by ProcNumber. The main concern was
reserving storage for every possible process slot and every kind, even with
few active processes. With that, max_connections=10000 would reserve about 33.5MB
for the three kinds. Queries would also need to scan unused slots or use another
structure to track active ones.

I don't think that the fixed allocation would be the right design, particularly
because it grows with MaxBackends even when most slots are unused. Since v1
already avoids hash lookups on the flush path, I don't think the simpler access
justifies that memory cost.

> Using one LWLock for each backend sounds costly just for more
> correctness with the stats, and we don't have that many writes anyway?

I think that an array would not remove the need for content synchronization. The
dshash partition lock protects the entry lifetime, but the owner updates its
cached entry without holding that lock. Queries read the entry, while shared
and per-backend resets can modify it from another process. In particular, a
concurrent reset with a flush could lose counters.

v1 uses one LWLock per kind and live process for that.

> Second, you may be interested in this patch:
> https://www.postgresql.org/message-id/CAA5RZ0supQBxSkh=CWB39=j+cL3hHcLPki3tcBk0B1r4fesg_g@mail.gmail.com
> This is for PGSS, but could be applied to your patch set. The idea is
> simple: keep the stats kind for backends, but register a dedicated
> dshash for it rather than having more dsa facilities in the area of
> pgstat.c.

Yeah, I started looking at Sami's patch yesterday, after you mentioned it to me
off-list.

PGSTAT_KIND_BACKEND is variable-numbered, so it could directly use own_hash.

Also, keeping PGSTAT_KIND_BACKEND and using own_hash could still eliminate duplicate
accounting if routine flushes updated only its live entry, while the fixed WAL,
Lock and I/O structures retained on exit transferred statistics.

That said, I don't think keeping the combined kind is a good fit for this design:

A WAL snapshot or reset would acquire the same entry lock used by Lock and I/O
flushes. Exit and ProcNumber reuse would also have to coordinate one combined
entry with the three fixed accumulators and their locks.

In the new design, each fixed kind owns both parts of its statistics: its fixed
accumulator and its live per-process entries. A snapshot, reset, or transfer
therefore involves only that kind's lock and hash.

IIUC, own_hash would only change where the generic variable-statistics entries
are stored: it would retain their refcount, drop and garbage collection machinery.

V1 instead uses ProcNumber keyed entries with a process lifetime lifecycle, so
that machinery is not needed.

One detail is that v1 does not add another DSA: all three hashes share the
existing pgstat DSA, while Sami's patch creates a dedicated DSA for an own_hash
kind.

So, while own_hash could be used to implement a combined one hash alternative,
I don't think it provides the same per-kind isolation or ownership as v1.

> pgstat.c becomes much larger, with a bunch of knowledge now related to
> backends.

Agreed. I wonder if introducing pgstat_per_backend.c wouldn't make more sense.
The added code handles entry creation, fetching, transfer and removal in addition
to snapshots, so pgstat_snapshot.c seems too narrow.

> 0003~0005 are a bit boring, in the good sense. Perhaps you should
> split 0005 into a 0005 for the IO move to these new APIs and a 0006 to
> remove the stats kind.

Yeah, good point. Moving the removal into 0006 would make more sense.

> Based on my other comments, I am not actually
> convinced that we absolutely have to drop PGSTAT_KIND_BACKEND, quite
> the contrary.

Right, as mentioned above, it could be retained, but I don't think it should be.
It would keep WAL, Lock and I/O coupled through one entry and content lock, while
exit and ProcNumber reuse would need to coordinate that entry with three fixed
accumulators. Every additional per-backend statistic would then add more contention.

In the proposed design, each kind owns both its live entries and transferred
statistics.

Adding another per-backend statistic does not make it share an entry or content
lock with existing kinds.

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

On Fri, Aug 07, 2026 at 12:31:47PM +0000, Bertrand Drouvot wrote:
> On Fri, Aug 07, 2026 at 10:39:04AM +0900, Michael Paquier wrote:
> an earlier POC version used arrays indexed by ProcNumber. The main concern was
> reserving storage for every possible process slot and every kind, even with
> few active processes. With that, max_connections=10000 would reserve about 33.5MB
> for the three kinds. Queries would also need to scan unused slots or use another
> structure to track active ones.

Hmm, okay, that feels like a waste. I doubt that anybody would use
that many max_connections, but if these are idle most of the time..

> I don't think that the fixed allocation would be the right design, particularly
> because it grows with MaxBackends even when most slots are unused. Since v1
> already avoids hash lookups on the flush path, I don't think the simpler access
> justifies that memory cost.

Accessing an array indexed by procnumber should be slightly cheaper
than a hash lookup when grabbing the stats of an individual backend,
as this is just a BackendPidGetProc() -> GetNumberFromPGProc() to get
a location.

>> Using one LWLock for each backend sounds costly just for more
>> correctness with the stats, and we don't have that many writes anyway?
>
> I think that an array would not remove the need for content
> synchronization.

We would need some level of concurrent protection even with an array.

> The dshash partition lock protects the entry lifetime, but the owner updates its
> cached entry without holding that lock. Queries read the entry, while shared
> and per-backend resets can modify it from another process. In particular, a
> concurrent reset with a flush could lose counters.
>
> v1 uses one LWLock per kind and live process for that.

I can see that:

+pgstat_per_backend_snapshot(PgStat_Kind kind, dshash_table *hash, void *snap)
[...]
+ while ((entry = dshash_seq_next(&hstat)) != NULL)
+ {
+ LWLockAcquire(&entry->lock, LW_SHARED);

That's a sequential scan combined with potentially hundreds of LWLocks
acquired and released successivelly. That looks expensive here for a
single IO/lock/WAL data scan. That's the level of locking required
because a mutex cannot be hold while doing external calls, and here we
have one per_backend_acc_cb callback and one
pgstat_cache_per_backend_entry(). Not sure I like much this costly
locking level. I'm concerned by this cost.
--
Michael

Hi,

On Mon, Aug 10, 2026 at 01:38:31PM +0900, Michael Paquier wrote:
> Accessing an array indexed by procnumber should be slightly cheaper
> than a hash lookup when grabbing the stats of an individual backend,
> as this is just a BackendPidGetProc() -> GetNumberFromPGProc() to get
> a location.

Right, an array would make fetching an individual backend slightly cheaper.
My point was only that routine flushes use cached entry pointers and therefore
avoid hash lookups.

> I can see that:
>
> +pgstat_per_backend_snapshot(PgStat_Kind kind, dshash_table *hash, void *snap)
> [...]
> + while ((entry = dshash_seq_next(&hstat)) != NULL)
> + {
> + LWLockAcquire(&entry->lock, LW_SHARED);
>
> That's a sequential scan combined with potentially hundreds of LWLocks
> acquired and released successivelly. That looks expensive here for a
> single IO/lock/WAL data scan. That's the level of locking required
> because a mutex cannot be hold while doing external calls, and here we
> have one per_backend_acc_cb callback and one
> pgstat_cache_per_backend_entry(). Not sure I like much this costly
> locking level. I'm concerned by this cost.

Yeah, I benchmarked this against unpatched master (-O2 and assertions disabled).
Each sessions generated and flushed WAL/IO statistics, then remained connected
and idle. Then queried pg_stat_wal, pg_stat_lock and pg_stat_io:

Mean latency in ms:

400 backends 10000 backends
master v1 master v1
WAL 0.017 0.029 0.017 0.354
Lock 0.018 0.033 0.018 0.575
IO 0.069 0.143 0.069 2.847

Those are warmed, continuously repeated queries.

Now the impact:

- the extra timing is only when querying the corresponding global view

- the shared entry lock conflicts only with exclusive operations on the same
backend's entry for that statistics kind. The usual statistics flush uses
LWLockConditionalAcquire(), so it leaves counters pending rather than waiting.
Forced flushes, resets, and backend exit processing may wait, but other backends
can continue flushing their own entries.

FWIW, this kind of scan is not new:

- pg_locks walks the PGPROC slots and takes each live process's fpInfoLock in
shared mode.

- pg_stat_activity also performs a scan, although it uses a lockless changecount
and retry protocol rather than taking one LWLock per backend.

- the current statistics implementation with stats_fetch_consistency = snapshot
also scans the shared statistics dshash and takes each entry's content LWLock in
shared mode while copying it.

For comparison, select count(*) FROM pg_stat_activity took about 57 ms and select
count(*) FROM pg_locks took about 4ms, both with the same 10000 connections.

Given that the cost is still sub millisecond at hundreds of connections and a few
milliseconds at 10000 and given the impact mentioned above, I don't think this is
a practical blocker though. What do you think?

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Hi,

On Mon, Aug 10, 2026 at 02:45:16PM +0000, Bertrand Drouvot wrote:
> Given that the cost is still sub millisecond at hundreds of connections and a few
> milliseconds at 10000 and given the impact mentioned above, I don't think this is
> a practical blocker though. What do you think?
>

A rebase was needed, so while at it, I implemented what we have discussed so far,
means:

- moved the generic per-backend statistics infrastructure to a new pgstat_per_backend.c
file
- split the previous 0005 into the IO migration and the removal of PGSTAT_KIND_BACKEND
and pgstat_backend.c.

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com