We have been using IBM DB2 system versioning since 2015. It is a great feature.
We are trying to switch to MariaDB and have run into a roadblock with system versioning.
DB2 stores historic data in a separate table.
Table ADDRESS has associated history table ADDRESS_HIST
This makes it very easy to backup and restore history, it doesn't effect main table query performance, and allows for dropping versioning on the main table without wiping out history; in order to do ALTERing of columns, indexes, constraints, etc.
Utilizing the main table or partitioning presents a whole host of problems. We have almost all of our tables versioned, so if we partition all of them, we lose referential integrity on all of them due to disallowed foreign keys on partitions. Without partitions, and utilizing the default behavior of just storing the history in the main table, we are unable to insert our existing history into the main table, so we would be starting from scratch. Removing versioning on the main table wipes out the history; making it difficult to do ALTERing maintenance. We currently don't use replication, but will in the future. I can imagine replication is effected by the current versioning design.
Was system versioning designed this way due to the MariaDB architecture? I've read about it to some degree and it seems very advanced. Is there any hope for MariaDB storing history in separate tables? If we can assist in making this happen sooner than later, please contact me, as we have to make a decision to switch very soon.