Thread

1. Adding per backend commit and rollback counters

   Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2025-08-04T14:20:48Z

   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
   

2. Re: Adding per backend commit and rollback counters

   Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2025-08-07T08:17:26Z

   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
   

3. Re: Adding per backend commit and rollback counters

   Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2025-08-10T07:47:09Z

   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
   

4. Re: Adding per backend commit and rollback counters

   Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2025-08-28T16:07:40Z

   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
   

5. Re: Adding per backend commit and rollback counters

   Kuba Knysiak <thesniboy@gmail.com> — 2026-03-30T20:19:13Z

   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
   

6. Re: Adding per backend commit and rollback counters

   Bertrand Drouvot <bertranddrouvot.pg@gmail.com> — 2026-03-31T09:24:35Z

   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
   

7. Re: Adding per backend commit and rollback counters

   Nishant Sharma <nishant.sharma@enterprisedb.com> — 2026-07-06T11:41:37Z

   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/