Re: Per backend relation statistics tracking

Hi,

On Mon, Aug 25, 2025 at 05:51:38PM -0500, Sami Imseih wrote:
> Thanks for the patches.
>
> I have not gone through them in detail yet, but +1 on adding backend activity
> stats.

Thanks for sharing your thoughts.

> This provides another level of drill down to spot anomalous sessions or
> different patterns across applications. I also think we will want more than
> just relation stats. For example, columns from pg_statio already look useful on
> a per-backend aggregate level. Beyond that, I can imagine future additions like
> number of transactions, subtransactions, I/O stats, conflicts, etc. All of these
> seem like valuable per-backend aggregates.
>
> That is why I think we should be careful about naming. pg_stat_backend feels
> very generic, but right now it only shows relation stats. Maybe we call it
> pg_stat_backend_tables to start? Then if we later add I/O, we could have
> pg_stat_backend_io, or for conflicts, pg_stat_backend_conflicts, etc. That way
> we keep things more flexible, instead of trying to fit everything into
> one view. It also helps us avoid having to rename views in the future.
>
> What do you think?

My initial idea was to provide just a single view for "basic" counters (i.e
things that do not need multiple lines per backend (like pg_stat_get_backend_io()
output would need). That would mean a single view for "basic" counters and
a set of functions for more "sophisticated" ones (like pg_stat_get_backend_io())
that need multiple rows per backend. Having said that, we could imagine adding
pg_stat_get_backend_wal() output to pg_stat_backend too.

Thoughts?

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