Re: pg_stat_database.xact_commit + pg_stat_database.xact_rollback asa reliable metric for xid consumption ?

On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
<achill@matrix.gatewaynet.com> wrote:
>
> Hello
> I attended two interesting online courses by 2ndQuadrant recently, about MVCC and Freezing , and I started to
seriouslydig into this for the first time in our almost 20-yrs postgersql installation
 
> (running 10.x currently).
> I thought of using the info from the statistics collector : pg_stat_database xact_commit and
pg_stat_database.xact_rollbackbut to my surprise this gave very high numbers, almost 20 times up , so I
 
> wrote a cron to insert every morning into an archive/historic table : pgstat_database_arc which is basically the
schemaof pgstat_database plus a timestamp col. And I got two instances of it one
 
> yesterday and one this morning :
>
> dynacom=# select xmin,datname,xact_commit+xact_rollback as num_of_xacts,ts from pgstat_database_arc WHERE
datname='dynacom';
>     xmin    | datname | num_of_xacts | ts
> -----------+---------+--------------+-------------------------------
>   813560437 | dynacom |   7734784237 | 2020-05-31 18:37:08.950399+03
>   813889272 | dynacom |   7740424341 | 2020-06-01 08:00:01.912157+03
> (2 rows)
>
> But the reported increase in the total number of xacts is 7740424341 - 7734784237 = 5640104 , while the actual xid
consumption(using xmin, this table only does inserts) is : 813889272 - 813560437 =
 
> 328835. The xid difference (via xmin) is compatible with another system that I maintain (for our custom replication
solution),so I am wondering why this almost 20-fold difference between actual xid
 
> consumption and pg_stat_database metrics.

Because read only transaction usually don't consume an xid.  So yes
pg_stat_database gives a more reasonable approximation of the real
number of transactions happening on the server.