
 PARTITION BY SYSTEM_TIME (
  PARTITION p_hist HISTORY,
  PARTITION p_cur CURRENT
 );

In this example all history will be stored in the partition p_hist while all
current data will be in the partition p_cur. The table must have exactly one
current partition and at least one historical partition.

Partitioning by SYSTEM_TIME also supports automatic partition rotation. One
can rotate historical partitions by time or by size. This example shows how to
rotate partitions by size:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME LIMIT 100000 (
  PARTITION p0 HISTORY,
  PARTITION p1 HISTORY,
  PARTITION pcur CURRENT
 );

MariaDB will start writing history rows into partition p0, and when it reaches
a size of 100000 rows, MariaDB will switch to partition p1. There are only two
historical partitions, so when p1 overflows, MariaDB will issue a warning, but
will continue writing into it.

Similarly, one can rotate partitions by time:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 WEEK (
  PARTITION p0 HISTORY,
  PARTITION p1 HISTORY,
  PARTITION p2 HISTORY,
  PARTITION pcur CURRENT
 );

This means that the history for the first week after the table was created
will be stored in p0. The history for the second week — in p1, and all later
history will go into p2. One can see the exact rotation time for each
partition in the INFORMATION_SCHEMA.PARTITIONS table.

It is 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