I'm trying to get a better understanding of how XID wraparound affects the results of the query to find the snapshot xmin (that is txid_snapshot_xmin(txid_current_snapshot()) or pg_snapshot_xmin(pg_snapshot_current()) after Postgres 13) and the stored xmin value in Postgres tuples:

My understanding of XID wraparound is :

  1. After 4 billion transactions, the xmin value wraps around (starts from 0, 1, 2...). This is internally represented as an xid, which is essentially a 32 bit integer.
  2. However, the results of txid_snapshot_xmin(txid_current_snapshot()) or pg_snapshot_xmin(pg_snapshot_current()), according to the documentation, return an xid8 which increases monotonically, and never wraps around. ref
  3. The snapshot value in step 2 has an epoch value associated with it which can be calculated as txid_snapshot_xmin(txid_current_snapshot()) >> 32. This essentially represents the number of times wraparound has occurred. More importantly, if this value is different from a previous queried value, this means that wraparound has occurred. Conversely, by executing txid_snapshot_xmin(txid_current_snapshot()) % 2^32 one can get the xmin value that would is assigned to a row (the 32-bit value)

I'm trying to simulate this behavior to verify my theory, but I don't currently have access to a high volume database (with > 4 billion transactions).

Is there a good way to simulate a large volume of transactions on a test DB to see this wraparound in action? That is, without actually executing 4B transactions?

I'm thinking that maybe there is some internal state/counter of the current number of transaction that I can modify, but I haven't had any luck with this.

  • Please don't ask the same question in multiple Stackexchange sites. Answered on Stackoverflow. Commented Apr 15, 2023 at 3:04
  • Apologies, I will not do so in the future.
    – Dev K
    Commented Apr 20, 2023 at 0:02


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.