
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 active records (as a TIMESTAMP, this has a
maximum value of '2038-01-19 03:14:07' UTC) will result in the historical
records being dropped, and the active records being deleted and moved to
history. See MDEV-25468.

Prior to MariaDB 10.4.5, the TRUNCATE TABLE statement drops all historical
records from a system-versioned-table.

From MariaDB 10.4.5, historic data is protected from TRUNCATE statements, as
per the SQL standard, and an Error 4137 is instead raised:

TRUNCATE t;
ERROR 4137 (HY000): System-versioned tables do not support TRUNCATE TABLE

Excluding Columns From Versioning
---------------------------------

Another MariaDB extension allows one to version only a subset of columns in a
table. This is useful, for example, if you have a table with user information
that should be versioned, but one column is, let's say, a login counter that
is incremented often and is not interesting to version. Such a column can be
excluded from versioning by declaring it WITHOUT VERSIONING

CREATE TABLE t (
 x INT,
 y INT WITHOUT SYSTEM VERSIONING
) WITH SYSTEM VERSIONING;

A column can also be declared WITH VERSIONING, that will automatically make
the table versioned. The statement below is equivalent to the one above:

CREATE TABLE t (
 x INT WITH SYSTEM VERSIONING,
 y INT
);

Changes in other sections: https://mariadb.com/kb/en/create-table/
https://mariadb.com/kb/en/alter-table/ https://mariadb.com/kb/en/join-syntax/
https://mariadb.com/kb/en/partitioning-types-overview/
https://mariadb.com/kb/en/date-and-time-units/
https://mariadb.com/kb/en/delete/ https://mariadb.com/kb/en/grant/

they all reference back to this page

Also, TODO:

* limitations (size, speed, adding history to unique not nullable columns)

System Variables
----------------

There are a number of system variables related to system-versioned tables:

system_versioning_alter_history
-------------------------------

* Description: SQL:2011 does not allow ALTER TABLE on system-versioned tables.
When this variable is set to ERROR, an attempt to alter a system-versioned
table will result in an error. When this variable is set to KEEP, ALTER TABLE
will be allowed, but the history will become incorrect — querying historical
data will show the new table structure. This mode is still useful, for
example, when adding new columns to a table. Note that if historical data
contains or would contain nulls, attempting to ALTER these columns to be NOT
NULL will return an error (or warning if strict_mode is not set).
* Commandline: --system-versioning-alter-history=value
* Scope: Global, Session
* Dynamic: Yes
* Type: Enum
* Default Value: ERROR
* Valid Values: ERROR, KEEP

system_versioning_asof
----------------------

* Description: If set to a specific timestamp value, an implicit FOR
SYSTEM_TIME AS OF clause will be applied to all queries. This is useful if one
wants to do many queries for history at the specific point in time. Set it to
DEFAULT to restore the default behavior. Has no effect on DML, so queries such
as INSERT .. SELECT and REPLACE .. SELECT need to state AS OF explicitly.
* Commandline: None
* Scope: Global, Session
* Dynamic: Yes
* Type: Varchar
* Default Value: DEFAULT

system_versioning_innodb_algorithm_simple
-----------------------------------------

* Description: Never fully implemented and removed in the following release.
* Commandline: --system-versioning-innodb-algorithm-simple[={0|1}]
* Scope: Global, Session
* Dynamic: Yes
* Type: Boolean
* Default Value: ON
* Introduced: MariaDB 10.3.4
* Removed: MariaDB 10.3.5

system_versioning_insert_history
--------------------------------

* Description: Allows direct inserts into ROW_START and ROW_END columns if
secure_timestamp allows changing timestamp.
* Commandline: --system-versioning-insert-history[={0|1}]
* Scope: Global, Session
* Dynamic: Yes
* Type: Boolean
* Default Value: OFF
* Introduced: MariaDB 10.11.0

Limitations
-----------

* Versioning clauses can not be applied to generated (virtual and persistent)
columns.
* Before MariaDB 10.11, mariadb-dump did not read historical rows from
versioned tables, and so historical data would not be backed up. Also, a
restore of the timestamps would not be possible as they cannot be defined by
an insert/a user. From MariaDB 10.11, use the -H or --dump-history options to
include the history.

URL: https://mariadb.com/kb/en/system-versioned-tables/