per backend WAL statistics

Hi hackers,

Now that commit 9aea73fc61 added backend-level statistics to pgstats (and
per backend IO statistics), we can more easily add per backend statistics.

Please find attached a patch to implement $SUBJECT.

It's using the same layer as pg_stat_wal, except that it is now possible to know
how much WAL activity is happening in each backend rather than an overall
aggregate of all the activity.

A function called pg_stat_get_backend_wal() is added to access this data
depending on the PID of a backend.

=== Outcome ===

With this in place, one could for example:

1. Get the WAL statistics for a backend pid:

postgres=# select * from pg_stat_get_backend_wal(473278);
-[ RECORD 1 ]----+---------
wal_records | 300008
wal_fpi | 7
wal_bytes | 17753097
wal_buffers_full | 933
wal_write | 937
wal_sync | 2
wal_write_time | 0
wal_sync_time | 0
stats_reset |

2. Get the wal_bytes generated by application:

postgres=# SELECT application_name, wal_bytes, round(100 * wal_bytes/sum(wal_bytes) over(),2) AS "%"
FROM (SELECT application_name, sum(wal_bytes) AS wal_bytes
FROM pg_stat_activity, pg_stat_get_backend_wal(pid)
WHERE wal_bytes != 0 GROUP BY application_name);
application_name | wal_bytes | %
------------------+-----------+-------
app1 | 17708761 | 39.95
app2 | 26614797 | 60.05
(2 rows)

3. Get the wal_bytes generated by database:

postgres=# SELECT datname, wal_bytes, round(100 * wal_bytes/sum(wal_bytes) over(),2) AS "%"
FROM (SELECT datname, sum(wal_bytes) AS wal_bytes
FROM pg_stat_activity, pg_stat_get_backend_wal(pid)
WHERE wal_bytes != 0 GROUP BY datname);
datname | wal_bytes | %
---------+-----------+-------
db1 | 35461858 | 80.01
db2 | 8861700 | 19.99
(2 rows)

and much more...

=== Implementation ===

The same limitation as in 9aea73fc61 persists, meaning that Auxiliary processes
are not included in this set of statistics.

The patch is made of 3 sub-patches:

0001: to extract the logic filling pg_stat_get_wal()'s tuple into its own routine.
It adds pg_stat_wal_build_tuple(), a helper routine for pg_stat_get_wal(), that
fills its tuple based on the contents of PgStat_WalStats. Same idea as ff7c40d7fd.

0002: PGSTAT_KIND_BACKEND code refactoring. It refactors some come related to per
backend statistics. It makes the code more generic or more IO statistics focused
as it will be used in 0003 that will introduce per backend WAL statistics. It does
not add any new feature, that's 100% code refactoring to ease 0003 review.

0003: it adds the per backend WAL statistics and the new pg_stat_get_backend_wal()
function, documentation and related test.

=== Remarks ===

R1:

0003 does not rely on pgstat_prep_backend_pending() for its pending statistics
but on a new PendingBackendWalStats variable. The reason is that the pending wal
statistics are incremented in a critical section (see XLogWrite(), and so
a call to pgstat_prep_pending_entry() could trigger a failed assertion:
MemoryContextAllocZero()->"CritSectionCount == 0 || (context)->allowInCritSection"

R2:

Instead of relying on a new PendingBackendWalStats, we could rely on the
existing PendingWalStats variable. But that would complicate the flush of
per backend and existing wal stats as that would need some coordination. I think
that it's better that each kind has its own pending variable.

R3:

Instead of incrementing the PendingBackendWalStats members individually we could
also "just" assign the PendingWalStats ones once incremented. I thought it's
better to make them "fully independent" though.

R4:

0002 introduces a new PgStat_BackendPending struct. Due to R1, that's not needed
per say but could have been if pgstat_prep_backend_pending() would have been
used. I keep this change as we may want to add more per backend stats in the future.

Looking forward to your feedback,

Regards,

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