Partitioning in fact is MariaDB way of using multiple tables. It'd be not very good to develop multiple mechanisms that solves the same thing. What other problems apart from prohibited FK partitioning rises? I see future development in allowing FK for working partition of versioned tables. Maybe it will be more easy with shorter syntax for CREATE TABLE that will automatically create working partition and history partition.

The foreign key problem is only part of the problem. We cannot lose our history. If we need to remove versioning on a table, under MariaDB, the history is wiped out. That's unacceptable. In DB2, the separate history table does not have foreign key relationships or referential integrity; nor is it needed. Just the fact that it exists and retains the history when versioning in disabled, is what is important. Also, when versioning is enabled, it resumes where it left off, utilizing all the history in the separate history table.

As for replication. I can't even begin to imagine the amount of difficulty involved in replicating a partitioned table, much less a table partitioned exclusively for versioning.

We would never use versioning as it is currently defaulted to in MariaDB, where the history is mixed in with the table data. It's a performance hit and unmanageable.

So you basically saying about 3 problems:

1. FK don't work with partitions;
2. Unable to turn off system versioning and retain history;
3. Doubtful replication usefulness on partitioned table.

If all 3 points are arranged does that satisfy your needs? There will be 2 partitions: CURRENT and HISTORY. Foreign keys will work only against CURRENT. Two partitions are actually two InnoDB tables. Expect not much more trouble with replication as it is now with any kind of tables.

> Also, when versioning is enabled, it resumes where it left off, utilizing all the history in the separate history table.

What will happen if the schema is altered on working table while system versioning is off?

In DB2, when versioning is disabled, the separate history table remains as just a stand alone table. The linkage to it's parent is lost. When versioning is enabled, any changes to the parent's schema must be applied to the history table, otherwise it errors.

My colleagues asked me to point out that, in MariaDB, when the parent table is dropped or truncated, we lose the history too. This is not the case for DB2.

They also said they had difficulties adding a timestamp column to a partitioned table

Sam, thank you for outlining drawbacks! I must admit that separate history table has its own advantages over partitioning. We can consider this as a complementary feature after partitioning tasks are finished.

Your very welcome Aleksey. We can do testing or try to help get answers to your questions. If there's anything else you think we can help with, please let me know

May we have more details on how this will be implemented (if details are already decided)?

For example, will the 2 tables need to use exactly the same structure (indexes, etc) and the same storage engine? What will happen in case of an ALTER TABLE? Will it be possible to exclude the history table from replication? Will the same triggers work for both the tables?

No, details are not decided yet.

I thought that if we'll be looking into this, we'll start from checking how it works in DB2.

So, thanks for the questions, now we will not forget to consider them!