Re: Per backend relation statistics tracking

On Mon, Aug 25, 2025 at 05:51:38PM -0500, Sami Imseih wrote:
> I have not gone through them in detail yet, but +1 on adding backend activity
> stats. 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.

FWIW, I am not excited by this proposal.  WAL and pg_stat_io are
interesting for monitoring purposes because they can be used to check
the balance of the activity across all the backends.  The current
pg_statio_* relations are interesting because they provide data to the
fields for each relation.

Adding these fields to the backend level stats spread based on the
backend PID without the knowledge of the relation they're related with
makes it much less interesting IMO, because we lose a lot of
granularity value that we have with the pg_statio_* relations, at the
cost of more bloat, particularly if these numbers are distributed
across many relations.  Even if I see this data, I am not sure how I
would use it in correlation with the existing pg_statio_* to tune
something, the existing views being sufficient to tune relation-level
parameters, no?

I have equally some doubts about the value of the vacuum and analyze
count, including the time of their latest runs.  Manual vacuums are
something that some do because autovacuum is not able to keep up,
still the encouraged patterns involve running these in periods of
lower-activity.  How would knowing about the whole number of vacuums
and/or analyze be useful if these are run with cron jobs, which I
suspect involve connections that live only for the duration of one
single job?

Transactions and subtransactions may be interesting to consider.
Perhaps mainly useful to evaluate the balance activity, say with a
connection pooler with some specific configuration or when checking
transaction activity across multiple databases by joining with
pg_stat_activity?
--
Michael