Adding per backend commit and rollback counters
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t52039psql -h localhost -U postgresBuilt from patchset v6 (message #6), July 27, 2026 at 02:31 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t52039_6 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t52039_6 && git checkout t52039_6Patchset v6 (message #6) is on t52039_6
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
Attachments:
v1-0001-Adding-per-backend-commit-and-rollback-counters.patchtext/x-diff; charset=us-asciiDownload+160-2
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
Attachments:
v2-0001-Adding-per-backend-commit-and-rollback-counters.patchtext/x-diff; charset=us-asciiDownload+166-2
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]/messages/by-id/aJrxug4LCg4Hm5Mm@ip-10-97-1-34.eu-west-3.compute.internal, 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]/messages/by-id/7fhpds4xqk6bnudzmzkqi33pinsxammpljwde5gfkjdygvejrj@ojkzfr7dxkmm.
- pg_stat_backend is too generic (see [3]/messages/by-id/aK8OuVPmmDTc9CFX@ip-10-97-1-34.eu-west-3.compute.internal), 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]: /messages/by-id/aJrxug4LCg4Hm5Mm@ip-10-97-1-34.eu-west-3.compute.internal
[2]: /messages/by-id/7fhpds4xqk6bnudzmzkqi33pinsxammpljwde5gfkjdygvejrj@ojkzfr7dxkmm
[3]: /messages/by-id/aK8OuVPmmDTc9CFX@ip-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
Attachments:
v4-0001-Adding-per-backend-commit-and-rollback-counters.patchtext/x-diff; charset=us-asciiDownload+65-3
v4-0002-Adding-XID-generation-count-per-backend.patchtext/x-diff; charset=us-asciiDownload+8-1
v4-0003-Adding-the-pg_stat_backend_transaction-view.patchtext/x-diff; charset=us-asciiDownload+223-1
Hello,
after reviewing the patch together with Miłosz, we found the following:
- In pgstatfuncs.c, we call pgstat_fetch_stat_backend_by_pid(beentry->st_procpid, NULL) for each backend row. That path acquires ProcArrayLock via BackendPidGetProc(), so this repeats lock acquisition for every row. We could simplify this and avoid taking the lock altogether by fetching directly with pgstat_fetch_stat_backend(local_beentry->proc_number).
Also, shouldn't this patch bump catversion?
Regards,
Kuba
The new status of this patch is: Waiting on Author
Hi,
On Mon, Mar 30, 2026 at 08:19:13PM +0000, Kuba Knysiak wrote:
Hello,
after reviewing the patch together with Miłosz, we found the following:
Thanks for the review!
- In pgstatfuncs.c, we call pgstat_fetch_stat_backend_by_pid(beentry->st_procpid, NULL)
for each backend row. That path acquires ProcArrayLock via BackendPidGetProc(),
so this repeats lock acquisition for every row. We could simplify this and avoid
taking the lock altogether by fetching directly with
pgstat_fetch_stat_backend(local_beentry->proc_number).
Yeah, I think that's a good point. Done that way in the attached. Also adding a
check on the backend type as it was done in pgstat_fetch_stat_backend_by_pid().
Also, shouldn't this patch bump catversion?
Yes and that was mentioned in the 0003 commit message. We usually don't change
it in the patch itself (could easily produce rebase noise) but just put an
XXX in the commit message so that it's not forgoten when pushed.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
t52039_6v5-0001-Adding-per-backend-commit-and-rollback-counters.patchtext/x-diff; charset=us-asciiDownload+65-3
v5-0002-Adding-XID-generation-count-per-backend.patchtext/x-diff; charset=us-asciiDownload+8-1
v5-0003-Adding-the-pg_stat_backend_transaction-view.patchtext/x-diff; charset=us-asciiDownload+231-1
Thanks for the v5 patch set!
Here are my review comments:
1) Patches need re-base. Not able to apply.
2) PGSTAT_BACKEND_FLUSH_XACT collision with newly committed
PGSTAT_BACKEND_FLUSH_LOCK.
3) What if pgStatXactCommit and pgStatXactRollback are 0? We unnecessarily
make backend_has_xactstats as true while updating the pending struct
values. In this process unnecessary ref_lock would be taken and
stats.xact_commit, stats.xact_rollback would be updated with 0 and then
ref_lock is unlocked.
4) PendingBackendStats was static but it was removed in this change, yet
the comment above still mentions it. Also it is killing the purpose of
"Utility routines to report I/O stats for backends ..." comment mentioned
in pgstat_backend.c. It appears the existing helper I/O routines' purpose
is eliminated. This doesn't look good.
4) I don't see any function using plural form like
pg_stat_get_backend_transactions in pg_proc.dat
5) The thread started with only commit and rollback counters, but xid was
added in v3, along with the idea of adding more, such as "seqscans, vacuum
count, analyze count...". This makes me curious: why can't these other
counters be added in this same thread? If these others aren't added now,
why was xid added? I suggest this thread should only include commit and
rollback counters, and xid should be added with the other metrics in a new
thread.
Regards,
Nishant Sharma,
EDB, Pune.
https://www.enterprisedb.com/