Обсуждение: Adding per backend commit and rollback counters

Hi hackers,

PFA a patch for $SUBJECT.

Currently we can find xact_commit and xact_rollback in pg_stat_database but we
don't have this information per backend.

This patch adds 2 functions: pg_stat_get_backend_xact_commit() and
pg_stat_get_backend_xact_rollback() to report the number of transactions that
have been committed/rolled back for a given backend PID.

I think having this information per-backend could be useful, for example, to:

- check which application is producing the highest number of commit / rollback
- check if the application's hosts have "uniform" commit/rollback pattern
- check if some application's hosts are doing a lot of rollback (as compared
to the other hosts): that could mean those hosts are not using an up-to-date
application version

This patch is pretty straightforward as it relies on the existing per backend
statistics machinery that has been added in 9aea73fc61d (so that there is not
that much design to discuss).

On a side note, I noticed that when a transaction fails, say this way:

postgres=# insert into bdt2 values(1);
ERROR:  relation "bdt2" does not exist

Then the existing pg_stat_get_db_xact_rollback() does not return the rollback
increment (so does pg_stat_database.xact_rollback). Indeed, the flush is done
during the next commit or explicit rollback.

Maybe we could add an extra counter, that tracks the transactions that have not
been explicitly rolled back (xact_error or such) and flush it at the right time.

Looking forward to your feedback,

Regards,

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

Hi,

On Mon, Aug 04, 2025 at 02:20:48PM +0000, Bertrand Drouvot wrote:
> This patch is pretty straightforward as it relies on the existing per backend
> statistics machinery that has been added in 9aea73fc61d (so that there is not
> that much design to discuss).

Still, while working on adding more backend stats (more on that later), I 
realized that in v1, I missed to use pgstat_report_fixed (recently added in 
793928c2d5a): the attached fixes that.

Also, I think it's better to put the new xact pending counters in the existing
PgStat_BackendPending, done that way in the attached.

Regards,

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

Hi,

On Thu, Aug 07, 2025 at 08:17:26AM +0000, Bertrand Drouvot wrote:
> Hi,
> 
> On Mon, Aug 04, 2025 at 02:20:48PM +0000, Bertrand Drouvot wrote:
> > This patch is pretty straightforward as it relies on the existing per backend
> > statistics machinery that has been added in 9aea73fc61d (so that there is not
> > that much design to discuss).
> 
> Still, while working on adding more backend stats (more on that later), I 
> realized that in v1, I missed to use pgstat_report_fixed (recently added in 
> 793928c2d5a): the attached fixes that.
> 
> Also, I think it's better to put the new xact pending counters in the existing
> PgStat_BackendPending, done that way in the attached.

Another metric that could be useful is to track the XIDs generated by backend.
That would help to see if a backend is consuming XIDs at a high rate.

We can not rely on the number of commits or rollbacks as they take into account
the virtual transactions.

So a new counter has been added in 0002 attached. Also, v3 changes the way
the statistics are displayed. I've in mind to add much more statistics per backend
(such a number of seqscans, vacuum count, analyze count..., I'll open a dedicated
thread for those) and I think that a single view to display them all makes
more sense than a lot of individual functions. This view is added in 0003.

To sum up, v3 contains:

0001 -

Adding per backend commit and rollback counters

It relies on the existing per backend statistics that has been added in
9aea73fc61d. A new function is called in AtEOXact_PgStat() to increment those
two new counters.

0002 - 

Adding XID generation count per backend

This patch adds a new counter to record the number of XIDs generated per
backend. It will help to detect if a backend is consuming XIDs at a high rate.

Virtual transactions are not taken into account on purpose, we do want to track
only the XID where there is a risk of wraparound.

The counter is not part of PgStat_BackendPending, because we want to avoid
an extra function call in this code path to increment the counter in
PendingBackendStats. The counter increment here behaves more or less the same as
we do for WAL statistics.

0003 -

Adding 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 XIDs generated, the number of commits, the number of rollbacks 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().

Adding documentation and tests.

Regards,

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

Hi,

On Sun, Aug 10, 2025 at 07:47:09AM +0000, Bertrand Drouvot wrote:
> To sum up, v3 contains:
> 
> 0001 -
> Adding per backend commit and rollback counters
> 0002 - 
> Adding XID generation count per backend
> 0003 -
> Adding the pg_stat_backend view

Following recent conversations in [1], those changes have been made in v4
attached:

- avoid tracking the commit and rollback counters twice (for databases and for
backends) but increment the backend stats when the database ones are flushed. Same
idea as [2].

- pg_stat_backend is too generic (see [3]), let's use pg_stat_backend_transaction 
instead. I deliberately did not use pg_stat_backend_xact to not confuse with the
other "*xact*" functions/views where the meaning is not the same. I'm open to
other naming suggestion though.

[1]: https://www.postgresql.org/message-id/flat/aJrxug4LCg4Hm5Mm%40ip-10-97-1-34.eu-west-3.compute.internal
[2]: https://www.postgresql.org/message-id/7fhpds4xqk6bnudzmzkqi33pinsxammpljwde5gfkjdygvejrj%40ojkzfr7dxkmm
[3]: https://www.postgresql.org/message-id/aK8OuVPmmDTc9CFX%40ip-10-97-1-34.eu-west-3.compute.internal

Regards,

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