There is one thing I'm curious about and never found a good answer: why there is no VACUUM equivalent in MySQL or MS SQL? I guess VACUUM is necessary because of the way Postgres stores data? How do those other RDBMS clean up indexes?
In Postgres, a new row is written, and both the new and old coexist in the table. Even after the transaction is committed, the old row stays in the table. VACUUM scans the table, finds rows that are no longer needed, and marks them as okay to overwrite.
In MySQL, the row is overwritten in the table immediately, and then the old row is temporarily written to a separate area (the UNDO Segment), so the old record is available if needed. Since old rows don't accumulate in the main table, there's no need for VACUUM.
MyRocks storage, however, only appends data, and requires regular Compaction to clean up old rows, similar to Postgres VACUUM.
Systems that closely adhere to traditional 2PL designs such as InnoDB or DB2 end up with tight coupling between recovery, concurrency control, and storage. This has many consequences, both good and bad. It's no coincidence that Postgres can support quite a variety of index access methods, including support for transactional full text search. You get the transactional stuff "as standard" with the Postgres approach to versioned storage. There is no need to bake concurrency control into each and every index access method.
https://www.microsoft.com/en-us/research/uploads/prod/2019/0...
SQL Server has something called "background cleanup" when this feature is enabled, which takes place in a separate process called "the cleanup process" - just like VACUUM. The paper itself explicitly acknowledges the similarity. All of the advantages the paper claims for CTR/ADR are also advantages for Postgres.
It's also true that both traditional SQL Server (SS with ADR/CTR disabled) and MySQL have background cleanup processes of their own for indexes, but that is a little different. It's about asynchronously reclaiming space for index entries that were already delete-marked.
> This means more writes to do on updates and makes access to old row versions quite a lot slower, but gets rid of the need for asynchronous vacuum and means you don't have table bloat issues. Instead you can have huge rollback segments or run out of space for rollback.
https://stackoverflow.com/questions/25153532/why-is-it-a-vac...
Edit: Just wanted to explicitly call out that the Robert Haas article linked in the answer is excellent (I am still reading my way through it).
http://amitkapila16.blogspot.com/2018/03/zheap-storage-engin...
I would hope and expect MySQL does that in reverse order, as “yeah, we have that row in memory” isn’t a good answer to “are you sure you can roll back that change if needed?”