possible to combine partitioning by SYSTEM_TIME and subpartitions:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME
  SUBPARTITION BY KEY (x)
  SUBPARTITIONS 4 (
  PARTITION ph HISTORY,
  PARTITION pc CURRENT
 );

Default Partitions
------------------

MariaDB starting with 10.5.0
----------------------------
Since partitioning by current and historical data is such a typical usecase,
from MariaDB 10.5, it is possible to use a simplified statement to do so. For
example, instead of

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING 
 PARTITION BY SYSTEM_TIME (
  PARTITION p0 HISTORY,
  PARTITION pn CURRENT
);

you can use

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING 
 PARTITION BY SYSTEM_TIME;

You can also specify the number of partitions, which is useful if you want to
rotate history by time, for example:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING 
 PARTITION BY SYSTEM_TIME
  INTERVAL 1 MONTH
  PARTITIONS 12;

Specifying the number of partitions without specifying a rotation condition
will result in a warning:

CREATE OR REPLACE TABLE t (x INT) WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME PARTITIONS 12;
Query OK, 0 rows affected, 1 warning (0.518 sec)

Warning (Code 4115): Maybe missing parameters: no rotation condition for
multiple HISTORY partitions.

while specifying only 1 partition will result in an error:

CREATE OR REPLACE TABLE t (x INT) WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME PARTITIONS 1;
ERROR 4128 (HY000): Wrong partitions for `t`: must have at least one HISTORY
and exactly one last CURRENT

Automatically Creating Partitions
---------------------------------

MariaDB starting with 10.9.1
----------------------------
From MariaDB 10.9.1, the AUTO keyword can be used to automatically create
history partitions.

For example

CREATE TABLE t1 (x int) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR AUTO;

CREATE TABLE t1 (x int) WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH
 STARTS '2021-01-01 00:00:00' AUTO PARTITIONS 12;

CREATE TABLE t1 (x int) WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME LIMIT 1000 AUTO;

Or with explicit partitions:

CREATE TABLE t1 (x int) WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR AUTO
 (PARTITION p0 HISTORY, PARTITION pn CURRENT);

To disable or enable auto-creation one can use ALTER TABLE by adding or
removing AUTO from the partitioning specification:

CREATE TABLE t1 (x int) WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR AUTO;

# Disables auto-creation:
ALTER TABLE t1 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR;

# Enables auto-creation:
ALTER TABLE t1 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR AUTO;

If the rest of the partitioning specification is identical to CREATE TABLE, no
repartitioning will be done (for details see MDEV-27328).

Removing Old History
--------------------

Because it stores all the history, a system-versioned table might grow very
large over time. There are many options to trim down the space and remove the
old history.

One can completely drop the versioning from the table and add it back again,
this will delete all the history:

ALTER TABLE t DROP SYSTEM VERSIONING;
ALTER TABLE t ADD SYSTEM VERSIONING;

It might be a rather time-consuming operation, though, as the table will need
to be rebuilt, possibly twice (depending on the storage engine).

Another option would be to use partitioning and drop some of historical
partitions:

ALTER TABLE t DROP PARTITION p0;

Note, that one cannot drop a current partition or the only historical
partition.

And the third option; one can use a variant of the DELETE statement to prune
the history:

DELETE HISTORY FROM t;

or only old history up to a specific point in time:

DELETE HISTORY FROM t BEFORE SYSTEM_TIME '2016-10-09 08:07:06';

or to a specific transaction (with BEFORE SYSTEM_TIME TRANSACTION xxx).

To protect the integrity of the history, this statement requires a special
DELETE HISTORY privilege.

Currently, using the DELETE HISTORY statement with a BEFORE SYSTEM_TIME
greater than the ROW_END of the »æô*