Date: Mon, 10 Aug 2026 14:45:16 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: pgsql-hackers@lists.postgresql.org, Andres Freund <andres@anarazel.de>,
	Sami Imseih <samimseih@gmail.com>
Subject: Re: Redesign per-backend statistics
Message-ID: <annj/ETYOFgbrGsn@bdtpg>
References: <anCjoFkGah/uB7lw@bdtpg>
 <anU3OE2403PZdwJL@paquier.xyz>
 <anXQM2PoJ+6iYYT2@bdtpg>
 <anlVx_TD_Q8He5uf@paquier.xyz>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <anlVx_TD_Q8He5uf@paquier.xyz>
Archived-At: <https://www.postgresql.org/message-id/annj/ETYOFgbrGsn%40bdtpg>
Precedence: bulk

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