I with people would stop with the "Uber migrated from Postgres to MySQL" thing. Uber migrated from Postgres used as relational database to something that is basically their own non-relational database using MySQL as distributed key-value store. It is not really situation applicable to most users of Postgres.
Anyway, this design of MVCC which moves older data into undo logs / segments is used by Oracle DB, so it definitely works. The common challenge with it is that reading older versions of data is slower, because you have to look it up in a log, and sometimes the data is removed from the log before your transactions finishes, getting the dreaded "Snapshot Too Old" error.
E: I don't see in the article when rows get evicted from the undo logs. If when they are no longer needed, I'm not sure where the improvement comes from because it should be similar amount of bookkeeping? If it's a circular buffer that can ran out of space like Oracle does it that would mean under high write load long-running transactions starts to fail which is pretty unpleasant.
> E: I don't see in the article when rows get evicted from the undo logs.
The undo records are truncated once they aren't needed for any transaction.
> If when they are no longer needed, I'm not sure where the improvement comes from because it should be similar amount of bookkeeping?
It depends on what exactly is "bookkeeping".
If we consider amount of work, then improvement comes because old undo records can be just bulk deleted very cheap (corresponding files get unliked). No vacuum scan is needed.
If we consider amount of space occupied, then indeed the same amount of versions take the same amount of space. But saving old versions of rows in the separate storage can save their primary storage from long-term degradation. Also, note that OrioleDB implements automatic merging of sparse pages.
> If it's a circular buffer that can ran out of space like Oracle does it that would mean under high write load long-running transactions starts to fail which is pretty unpleasant.
OrioleDB implements in-memory circular buffer for undo logs. Once circular buffer can't handle all the undo records, least recent records are evicted to the storage. Currently, we don't place limitation on the site of undo logs. Undo records are kept while any transaction can need them. So, no "Snapshot Too Old" errors. However, we can consider implementing this Oracle-like error as an option, which allows to limit the undo size.
That's because they don't store non-current versions of rows in the table itself, so why would they need a vacuum? MySQL does need to vacuum indexes, however.
I'm pretty sure SQL Server and MySQL use locking instead of MultiVersion Concurrency Control so they don't keep more copies of data around. No vacuum needed but there's a possibility of things blocking.
InnoDB (MySQL's default storage engine) implements MVCC using undo logging and background purge threads. It scales to highly concurrent OLTP workloads quite well. It doesn't work well with OLAP workloads / long-running transactions though. The oldest active transaction will block purging of anything newer than that transaction's snapshot.
Yes, but doesn't it require opt-in to enable snapshopt isolation? Most T-SQL devs will probably default to locking (TABLLOCK, etc) becuase that's what the bulk of google search results for "how do I fix my broken query?" tell people to do: it's only very, very rarely do I see a stackoverflow or dba.se answer that mentions MVCC-related topics.
Anyway, this design of MVCC which moves older data into undo logs / segments is used by Oracle DB, so it definitely works. The common challenge with it is that reading older versions of data is slower, because you have to look it up in a log, and sometimes the data is removed from the log before your transactions finishes, getting the dreaded "Snapshot Too Old" error.
E: I don't see in the article when rows get evicted from the undo logs. If when they are no longer needed, I'm not sure where the improvement comes from because it should be similar amount of bookkeeping? If it's a circular buffer that can ran out of space like Oracle does it that would mean under high write load long-running transactions starts to fail which is pretty unpleasant.