2

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?

Ash_this
  • 53
  • 5

2 Answers2

2

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.)

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Thank you for the response jjanes. What happens to the age function, if the xmin is changed or not changed. If the xmin is changed, will the age for that specific row still remain the same as before. If it is not change, that means there are other transaction with the same xmin, but their potential age has to be different, right? Does postgres interally know, which generation of xmin it is ? I mean do they have another counter to differentiate between those xmin ? Does the age function know about that? Sorry for all the questions, there is too much conflicting information to understand. – Ash_this Feb 06 '22 at 20:12
  • Hard to believe that someone wrote (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID) instead of 0x0300 when we now clearly have 2 bits that can be used in combination to give up to 4 states. The code implies that frozen is somehow logically committed and invalid – NealeU Feb 18 '22 at 12:12
0

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,

mshabou
  • 524
  • 3
  • 6
  • Thank you for responding. Yeah, i understand wraparound happens, i am more curious about what the autovacuum does. What happens to the XMIN of the my rows? If i want to query records where the XMIN > 100000 and XMIN < 500000, what records will i get? – Ash_this Feb 05 '22 at 21:01
  • You shouldn't query against xmin/xmax. Vacuuming does different things depending on when it is run, or the age of the table when it runs. Look into vacuum age settings. These can set at a table level. Rows are marked as always visible. – VynlJunkie Feb 06 '22 at 01:12
  • we are trying to do an incremental sync of our database, we are using xmin for it, because xmin is sequential until to the point for transaction wraparound. We also have other options(WAL), just wondering about this. – Ash_this Feb 06 '22 at 01:59