I understand that xmin is limited to 2^32-1, about 4 billion entries. Vacuum cleans up dead tuples, postgres maintains a transaction for every update to a row and those are cleaned up as a process of vacuum. Lets say, i am just inserting 5 billion records all independent transactions.

1. What is the dead tuple, in the above scenario?

2. When does the vacuum run automatically, around the 2 billion entries, what does it clean at this point?

3. what happens to the xmin for the 1st 2 billion records as the vacuum process starts ?

4. Let's say, i stored the xmin of the 100,000 transactions, as in keep pushing the insert transactions. After i inserted the 5 billion transactions, i want to query for all of my transaction after the 100,000 xmin and xmin < 500,000. Will this work?

CodePudding user response：

Postgresql will trigger an autovacuum to avoid WRAPAROUND, the autovacuum will freez old transaction.

you can read: https://www.cybertec-postgresql.com/en/autovacuum-wraparound-protection-in-postgresql/

Best regards,

CodePudding user response：

In old versions, freezing a tuple involved changing the xmin to a magic value of 2. see src/include/access/transam.h:

#define FrozenTransactionId            ((TransactionId) 2)

This might still be seen on disk for upgraded servers, and is still passed around in memory as an indicator the tuple is frozen.

But since 9.4 freezing a tuple involves leaving xmin alone and setting two specific bits in infomask to be on. See src/include/access/htup_details.h

#define HEAP_XMIN_COMMITTED     0x0100  /* t_xmin committed */
#define HEAP_XMIN_INVALID       0x0200  /* t_xmin invalid/aborted */
#define HEAP_XMIN_FROZEN        (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)

(Since it doesn't make sense for xmin to be committed and invalid at the same time, that combination was free to get a new meaning.)