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

> On Jun 1, 2020, at 7:03 AM, Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> On Mon, Jun 1, 2020 at 12:48 PM Achilleas Mantzios
> <achill@matrix.gatewaynet.com> wrote:
>>
>> On 1/6/20 1:36 μ.μ., Julien Rouhaud wrote:
>>> On Mon, Jun 1, 2020 at 12:02 PM Achilleas Mantzios
>>> <achill@matrix.gatewaynet.com> wrote:
>>> 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.
>> Thank you, so what would be the official way to monitor txid consumption (from a MVCC / Freezing admin POV)?
>
> I'm not sure what you're really looking for.  If it's the txid
> consumption rate, you can use txid_current() at regular interval to
> compute it (note that this function will consume a txid).  For object
> freeeze, you can use age(datfrozenxid) from pg_database, or
> age(relfrozenxid) from pg_class.
>
>


I also record the stats tables every minute by either inserting into a table hist_ table or exporting the data out to
beinserted into another table.  When exporting the pg_stat_database table I added the following to record the
informationwithout the need to call txid_current(): 

coalesce(txid_current_if_assigned(), txid_snapshot_xmax(txid_current_snapshot())) as xact_txid, …