Re: Add per-backend lock statistics

Hi Kawata-san,

On Wed, Jun 24, 2026 at 10:49:44PM +0900, Tatsuya Kawata wrote:
> Hi Bertrand-san,
>
> I tested the patch locally and did not find any functional issue.

Thanks!

> I have two suggestions below.
>
> == Doc suggestion ==
>
> When the workload uses parallel scans, pg_stat_lock.fastpath_exceeded
> grows more than what pg_stat_get_backend_lock shows for any
> individual pid. The gap is the parallel workers' contribution:
> each worker locks the valid subplans independently, accumulates into
> its own per-backend entry, and the entry is dropped at worker exit
> -- so the contribution is not folded into the leader's per-backend
> view.
>
> This is a property of the per-backend stats infrastructure rather
> than something this patch introduces, but since one of the stated
> motivations is "Isolate problematic sessions", users may
> intuitively expect parallel-worker contributions to be visible
> under the leader's pid. A short note in the docs of the per-backend
> functions clarifying that
> parallel-worker contributions are not aggregated into the leader's
> entry would help avoid that misunderstanding.

That's right, and the same could be said for per-backend I/O and WAL stats.
The stats are flushed when the transaction finish and then are visible from that
moment. The stats are gone once the backend exit. For parallel workers this window
is very short (between the flush and the exit) so that we can say that their stats
are not visible in practice.

I think that flushing statistics within running transactions [1] could help to
see what's going on for parallel workers too.

That said, I'm not sure the doc needs any clarifications given that those functions
take a PID as parameter and that they state something like "Returns I/O
/WAL statistics about the backend with the specified process ID".

> == Column suggestion for pg_stat_lock ==
>
> pg_stat_io has a backend_type column, which lets users still see
> parallel-worker contributions in aggregate (via WHERE
> backend_type='background worker') after workers exit. pg_stat_lock
> has only locktype, so worker contributions blend into the relation
> row and cannot be separated even in aggregate.
>
> This may be out of scope for the present patch, but I wonder if
> adding a backend_type axis to pg_stat_lock could be considered in a
> follow-up patch. It would give an alternative attribution path
> (similar to pg_stat_io's backend_type column) when per-backend
> statistics cannot help.

It's not related to this thread so that might be worth a dedicated one but I'm
not sure that would be more actionable while consuming more resources.

[1]: https://postgr.es/m/CAA5RZ0uA-4qcD3%2B2hjcE_-zQUBhvWf5foPM2vzYneFKrJLsBDQ%40mail.gmail.com

Regards,

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