Per backend relation statistics tracking

Hi hackers,

We currently have some stats in pg_stat_all_tables[|indexes] but we lack visibility
into which specific backends are generating this activity.

PFA, a patch series to add $SUBJECT.

It currently displays a few stats through a new pg_stat_backend view (same as
the one already introduced in [1]). This view displays one row per server process,
showing statistics related to the current activity of that process.

It currently provides something like:

pid | seq_scan | seq_tup_read | idx_tup_fetch | idx_scan | idx_tup_read | vacuum_count | last_vacuum | analyze_count | last_analyze | stats_reset
---------+----------+--------------+---------------+----------+--------------+--------------+-------------------------------+---------------+--------------+-------------
3583274 | 0 | 0 | 20 | 11 | 20 | 0 | | 0 | |
3583278 | 3 | 420 | 77 | 60 | 77 | 1 | 2025-08-12 05:47:37.546794+00 | 0 | |
3583371 | 4 | 1 | 11444 | 11428 | 13167 | 0 | | 0 | |
3583372 | 4 | 1 | 11404 | 11388 | 13110 | 0 | | 0 | |
3583373 | 4 | 1 | 11412 | 11396 | 13116 | 0 | | 0 | |
3583272 | 0 | 0 | 0 | 0 | 0 | 0 | | 0 | |
3583270 | 0 | 0 | 0 | 0 | 0 | 0 | | 0 | |
(7 rows)

Let's see some use case examples:

- Debug a specific increase in IO reads: When pg_stat_get_backend_io() shows
an increase in reads/read_bytes for backends, this new view allows deeper
investigation to determine if those reads are caused by table sequential scans
and how many rows are involved.

- One could check if some backends are triggering manual vacuums or analyzes and
check the last time they did so.

- Verify load distribution among sessions: Check if database activities are
evenly distributed across backends (in the pgbench example above, backends
3583371, 3583372, 3583373 show similar activity patterns). Uneven distribution
could indicate outdated application versions on some hosts or suboptimal
connection pool configurations.

- Set up monitoring alerts for backends showing unusual database access patterns.

Also thanks to the pid, we can join pg_stat_activity and then, for example:

- If backends from one host show significantly more sequential scans
than others, this could indicate an outdated application version on that
host running inefficient queries.

- One could categorize applications based on workload patterns and track how
different applications use the engine.

Patch series structure:

0001 -

Adds a new PgStat_BackendRelPending struct to store those pending statistics and
then relies on the existing per backend statistics machinery that has been added
in 9aea73fc61d. It also adds a new counter (heap_scan) to record the number of
sequential scans initiated on tables.

0002 -

Adds the pg_stat_backend view

This view displays one row per server process, showing statistics related to
the current activity of that process. It currently displays the pid, the
number of sequential scans initiated on a table and the
time at which these statistics were last reset.

It's built on top of a new function (pg_stat_get_backend_statistics()). The idea
is the same as pg_stat_activity and pg_stat_get_activity().

From 0003 to 0010 -

Adds one counter at a time. These are purely mechanical changes split for easier
review (they don't need to be committed separately).

A few remarks:

- What I don't like that much is that due to the fact that we want to avoid
exposing PendingBackendStats to the outside world (see the related comment in
pgstat_backend.c): then those counters need extra functions calls. Those functions
are very simple and could just be inlined as macros (avoiding the extra functions
calls). Maybe we could re-consider hiding PendingBackendStats to the outside world?

- We could add more stats, for example the n_tup_* ones. I did add those in
this patch series, as I want to get your feedback first about the whole idea.

- With those new counters in place, PgStat_BackendPending grows from 2880
to 2952 (72) bytes and PgStat_Backend from 2920 to 2992 (72) bytes. I think
that the memory increase is relatively small.

[1]: https://www.postgresql.org/message-id/aJhOfZ2c7XdHXOAU%40ip-10-97-1-34.eu-west-3.compute.internal

Looking forward to your feedback,

Regards,

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