pgsql-general
❮
correct formula for calculating distance t transaction and distance to snapshot
- Jump to comment-1Ishan joshi<ishanjoshi@live.com>Mar 13, 2026, 9:33 AM UTCHi Team,
I am trying to understand the details for calculating the "distance to earliest transaction" and "distance to earlier snapshot". I can see many post that provide the details to perform age (current transactionid, min(backendxmin)) but It is not the same as current transaction id increase with every operation an it will be high and if we use old backendxmin values that would be very low compare to current transaction id. Also as per my understanding "age" function does not have override function that calculate xid difference [age(xid,xid) not exists].
age( pgcurrentxactid()::xid, (SELECT min(backendxmin::text::bigint)::text::xid FROM pgstatactivity WHERE backend_xmin IS NOT NULL)
So if we performing type cast for current transaction id to number or changing backend_xmin to number and calculate the age, This will also not correct way.
(pgcurrentxactid()::text::bigint) - (backendxmin::text::bigint)
So, is there any method to calculate the distance to transactionid and distance to snapshot.
In my case it is showing very big number (8597422911) but while checking more details, I dont have any long running transaction/session. Number of dead tuples are less, running vacuum (analyze,freeze) everyday hence the wraparound situation age % is very less (4.1% that is away from wraparound situation about 95.9%) , min(backend_xmin) is giving low (123707539).
So, is there any way to calculate the correct value?
Thanks & Regards,
Ishan Joshi