Sorry that not sure this question is more related to PostgreSQL or MySQL^^"
I raise the questions here due to vacuum related. Hoping to consult with team.
After reading the concept of PostgreSQL Vacuum, I learned auto-vacuum could mainly handle 2 things:
transaction wraparound → recycle the transaction id for reuse
dead tuples → release the dead tuples space for reuse (not really shrink the file size,right~?)
Then, a few questions came to my mind. Q1: Why transaction wraparound only happens in PostgreSQL but not MySQL~?
Does MySQL’s transaction id use different scenario which no need to handle the wraparound~? Q2: When PostgreSQL row data is modified, it’s more like to create a “new version”, so there will be dead tuples. But if we update a column (which has a btree index), the btree still needs to be updated for index search, right~? How PostgreSQL handle the dead tuples & btree~?
Any concept or direction for my further research are highly appreciate ><
(please also feel free to correct me if my understanding about vacuum is not correct.)
I’m going to take a stab at this, others feel free to clarify/correct the math:
MySQL/InnoDB uses an unsigned 6-byte transaction id (48 bits). This give the range of 0 - 281,474,976,710,655 txn ids before wrap around is needed. At 20,000 transactions/sec, that’s would take 14,073,748,835 seconds, which is 162,890 days (446 years) before wraparound would be needed. Thus, no vacuum is needed.
MySQL/InnoDB modify pages directly. The page with the row in question (or empty page for INSERT) is loaded into the buffer pool (memory cache), modified, then on commit, flushed back to disk. If, in the process of modifying this page, the new value to be updated causes the page size to exceed 16K, the page is split, re-org’d and then flushed to disk. For DELETE, pages are delete-marked and cleaned up as part of internal garbage collection.
I call out “MySQL/InnoDB” because, in case you didn’t know, MySQL can use different “engines” for storing and managing data. MyRocks is another popular engine with it’s own behaviors/rules.
Adding to Matthew’s statements, I would like to add that there is a fundamental difference in the way trx id is used for ReadView aka Snapshot in MVCC in MySQL vs PostgreSQL
In InnoDB/MySQL, a SELECT gets a ReadView, but this doesn’t increase the global trx_id counter which is 8 bytes. Instead, a Readview in InnoDB is a combination of {Lowest_trx_id, List of running trx, Max trx_id(future trxs)}.
Thus, no vacuum is needed.