Postgres txid wraparounds

[bhavishyachandra]

I've been researching how to use txids for one of our use cases. I ran into a few articles that talk about how the current transaction id wraps around after 4 billion transactions. It has caused a few outages for large companies that relied on it.

https://malisper.me/transaction-id-wraparound-in-postgres

What caught my attention is this answer from a fairly experienced user of PostgresSQL. https://stackoverflow.com/a/32644144/4096071

Xid and Txid_current are not exactly the same type. Xid is a 32 bit bigint whereas Txid_current is a 64 bit epoch bigint. Looks like pgsync uses xid as a checkpoint to determine the ordering of operations it needs to catch up to. Based on my findings, xid might not be something you can use as a checkpoint after a database wraparound at 4 Billion Transactions. This is because the query to find records that have been updated after the checkpoint xid become hidden once the xid value reaches 4 billion.

Is this something that's already addressed by pgsync?

[toluaina]

pgsync relies on the triggers and pg_notify for the bulk of routine operations. txid_current is used as a checkpoint and this does not wrap around However the internal xids attached to each row do wrap around it seems we can actually make a more accurate comparison between

txid_current and xmin by stripping the epoch off the txid_current() before feeding into the where clause.

There is a discussion about this here: https://www.postgresql.org/message-id/[email protected]

Let me know if you have a better approach.