MariaDB supports temporal data tables in the form of system-versioning tables (allowing you to query and operate on historic data, discussed below),application-time periods (allow you to query and operate on a temporal range of data), andbitemporal tables (which combine both system-versioning andapplication-time periods).

System-Versioned Tables

System-versioned tables store the history of all changes, not only data which is currently applicable. This allows data analysis for any point in time, auditing of changes and comparison of data from different points in time.Typical uses cases are:

• Forensic analysis & legal requirements to store data for N years.
• Data analytics (retrospective, trends etc.), e.g. to get your staff information as of one year ago.
• Point-in-time recovery - recover a table state as of particular point in time.

System-versioned tables were first introduced in the SQL:2011 standard.

Creating a System-Versioned Table

TheCREATE TABLE syntax has been extended to permit creating a system-versioned table. To be system-versioned, according to SQL:2011, a table must have two generated columns, a period, and a special table option clause:

CREATETABLEt(xINT,start_timestampTIMESTAMP(6)GENERATEDALWAYSASROWSTART,end_timestampTIMESTAMP(6)GENERATEDALWAYSASROWEND,PERIODFORSYSTEM_TIME(start_timestamp,end_timestamp))WITHSYSTEMVERSIONING;

In MariaDB one can also use a simplified syntax:

CREATETABLEt(xINT)WITHSYSTEMVERSIONING;

In the latter case no extra columns will be created and they won't clutter the output of, say,SELECT * FROM t. The versioning information will still be stored, and it can be accessed via the pseudo-columnsROW_START andROW_END:

SELECTx,ROW_START,ROW_ENDFROMt;

Adding or Removing System Versioning To/From a Table

An existing table can bealtered to enable system versioning for it.

CREATETABLEt(xINT);

ALTERTABLEtADDSYSTEMVERSIONING;

SHOW CREATE TABLE t\G*************************** 1. row ***************************       Table: tCreate Table: CREATE TABLE `t` (  `x` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING

Similarly, system versioning can be removed from a table:

ALTERTABLEtDROPSYSTEMVERSIONING;

SHOW CREATE TABLE t\G*************************** 1. row ***************************       Table: tCreate Table: CREATE TABLE `t` (  `x` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1

One can also add system versioning with all columns created explicitly:

ALTERTABLEtADDCOLUMNtsTIMESTAMP(6)GENERATEDALWAYSASROWSTART,ADDCOLUMNteTIMESTAMP(6)GENERATEDALWAYSASROWEND,ADDPERIODFORSYSTEM_TIME(ts,te),ADDSYSTEMVERSIONING;

SHOW CREATE TABLE t\G*************************** 1. row ***************************       Table: tCreate Table: CREATE TABLE `t` (  `x` int(11) DEFAULT NULL,  `ts` timestamp(6) GENERATED ALWAYS AS ROW START,  `te` timestamp(6) GENERATED ALWAYS AS ROW END,  PERIOD FOR SYSTEM_TIME (`ts`, `te`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING

CREATEORREPLACETABLEt1(xINT)WITHSYSTEMVERSIONING;SETsystem_versioning_alter_history=keep;ALTERTABLEt1ADDCOLUMNrsTIMESTAMP(6)ASROWSTART,ADDCOLUMNreTIMESTAMP(6)ASROWEND,ADDPERIODFORSYSTEM_TIME(rs,re)

CREATEORREPLACETABLEt1(xINT)WITHSYSTEMVERSIONING;SETsystem_versioning_alter_history=keep;ALTERTABLEt1ADDCOLUMNrsTIMESTAMP(6)ASROWSTART,ADDCOLUMNreTIMESTAMP(6)ASROWEND,ADDPERIODFORSYSTEM_TIME(rs,re);ERROR4134(HY000):DuplicateROWSTARTcolumn`rs`

Inserting Data

When data is inserted into a system-versioned table, it is given arow_start value of the current timestamp, and arow_end value ofFROM_UNIXTIME(2147483647.999999). The current timestamp can be adjusted by setting thetimestamp system variable, for example:

SELECTNOW();+---------------------+|NOW()|+---------------------+|2022-10-2423:09:38|+---------------------+INSERTINTOtVALUES(1);SET@@timestamp=UNIX_TIMESTAMP('2033-10-24');INSERTINTOtVALUES(2);SET@@timestamp=default;INSERTINTOtVALUES(3);SELECTa,row_start,row_endFROMt;+------+----------------------------+----------------------------+|a|row_start|row_end|+------+----------------------------+----------------------------+|1|2022-10-2423:09:38.951347|2038-01-1905:14:07.999999||2|2033-10-2400:00:00.000000|2038-01-1905:14:07.999999||3|2022-10-2423:09:38.961857|2038-01-1905:14:07.999999|+------+----------------------------+----------------------------+

Querying Historical Data

SELECT

To query the historical data one uses the clauseFOR SYSTEM_TIME directly after the table name (before the table alias, if any). SQL:2011 provides three syntactic extensions:

• AS OF is used to see the table as it was at a specific point in time in the past:

SELECT*FROMtFORSYSTEM_TIMEASOFTIMESTAMP'2016-10-09 08:07:06';

• BETWEEN start AND end will show all rows that were visible at any point between two specified points in time. It works inclusively, a row visible exactly atstart or exactly atend will be shown too.

SELECT*FROMtFORSYSTEM_TIMEBETWEEN(NOW()-INTERVAL1YEAR)ANDNOW();

• FROM start TO end will also show all rows that were visible at any point between two specified points in time, includingstart, butexcludingend.

SELECT*FROMtFORSYSTEM_TIMEFROM'2016-01-01 00:00:00'TO'2017-01-01 00:00:00';

Additionally MariaDB implements a non-standard extension:

• ALL will show all rows, historical and current.

SELECT*FROMtFORSYSTEM_TIMEALL;

If theFOR SYSTEM_TIME clause is not used, the table will show thecurrent data. This is usually the same as if one had specifiedFOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP, unless one has adjusted therow_start value (untilMariaDB 10.11, only possible by setting thesecure_timestamp variable). For example:

CREATEORREPLACETABLEt(aint)WITHSYSTEMVERSIONING;SELECTNOW();+---------------------+|NOW()|+---------------------+|2022-10-2423:43:37|+---------------------+INSERTINTOtVALUES(1);SET@@timestamp=UNIX_TIMESTAMP('2033-03-03');INSERTINTOtVALUES(2);DELETEFROMt;SET@@timestamp=default;SELECTa,row_start,row_endFROMtFORSYSTEM_TIMEALL;+------+----------------------------+----------------------------+|a|row_start|row_end|+------+----------------------------+----------------------------+|1|2022-10-2423:43:37.192725|2033-03-0300:00:00.000000||2|2033-03-0300:00:00.000000|2033-03-0300:00:00.000000|+------+----------------------------+----------------------------+2rowsinset(0.000sec)SELECTa,row_start,row_endFROMtFORSYSTEM_TIMEASOFCURRENT_TIMESTAMP;+------+----------------------------+----------------------------+|a|row_start|row_end|+------+----------------------------+----------------------------+|1|2022-10-2423:43:37.192725|2033-03-0300:00:00.000000|+------+----------------------------+----------------------------+1rowinset(0.000sec)SELECTa,row_start,row_endFROMt;Emptyset(0.001sec)

Views and Subqueries

When a system-versioned tables is used in a view or in a subquery in the from clause,FOR SYSTEM_TIME can be used directly in the view or subquery body, or (non-standard) applied to the whole view when it's being used in aSELECT:

CREATEVIEWv1ASSELECT*FROMtFORSYSTEM_TIMEASOFTIMESTAMP'2016-10-09 08:07:06';

Or

CREATEVIEWv1ASSELECT*FROMt;SELECT*FROMv1FORSYSTEM_TIMEASOFTIMESTAMP'2016-10-09 08:07:06';

Use in Replication and Binary Logs

Tables that use system-versioning implicitly add therow_end column to the Primary Key. While this is generally not an issue for most use cases, it can lead to problems when re-applying write statements from the binary log or in replication environments, where a primary retries an SQL statement on the replica.

Specifically, these writes include a value on therow_end column containing the timestamp from when the write was initially made. The re-occurrence of the Primary Key with the old system-versioning columns raises an error due to the duplication.

To mitigate this with MariaDB Replication, set thesecure_timestamp system variable toYES on the replica. When set, the replica uses its own system clock when applying to the row log, meaning that the primary can retry as many times as needed without causing a conflict. The retries generate new historical rows with new values for therow_start androw_end columns.

Transaction-Precise History in InnoDB

A point in time when a row was inserted or deleted does not necessarily mean that a change became visible at the same moment. With transactional tables, a row might have been inserted in a long transaction, and became visible hours after it was inserted.

For some applications — for example, when doing data analytics on one-year-old data — this distinction does not matter much. For others — forensic analysis — it might be crucial.

MariaDB supports transaction-precise history (only for theInnoDB storage engine) that allows seeing the data exactly as it would've been seen by a new connection doing aSELECT at the specified point in time — rows insertedbefore that point, but committedafter will not be shown.

To use transaction-precise history, InnoDB needs to remember not timestamps, but transaction identifier per row. This is done by creating generated columns asBIGINT UNSIGNED, notTIMESTAMP(6):

CREATETABLEt(xINT,start_trxidBIGINTUNSIGNEDGENERATEDALWAYSASROWSTART,end_trxidBIGINTUNSIGNEDGENERATEDALWAYSASROWEND,PERIODFORSYSTEM_TIME(start_trxid,end_trxid))WITHSYSTEMVERSIONING;

These columns must be specified explicitly, but they can be madeINVISIBLE to avoid clutteringSELECT * output.

Note that if you are using an engine that does not support system versioning with transaction ids, you will get an error like "`start_trxid` must be of type TIMESTAMP(6) for system-versioned table `t`".

When one uses transaction-precise history, one can optionally use transaction identifiers in theFOR SYSTEM_TIME clause:

SELECT*FROMtFORSYSTEM_TIMEASOFTRANSACTION12345;

This will show the data, exactly as it was seen by the transaction with the identifier 12345.

Data for this feature is stored in themysql.transaction_registry table.

Storing the History Separately

When the history is stored together with the current data, it increases the size of the table, so current data queries — table scans and index searches — will take more time, because they will need to skip over historical data. If most queries on that table use only current data, it might make sense to store the history separately, to reduce the overhead from versioning.

This is done by partitioning the table bySYSTEM_TIME. Because of thepartition pruning optimization, all current data queries will only access one partition, the one that stores current data.

This example shows how to create such a partitioned table:

CREATETABLEt(xINT)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIME(PARTITIONp_histHISTORY,PARTITIONp_curCURRENT);

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

Partitioning bySYSTEM_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:

CREATETABLEt(xINT)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIMELIMIT100000(PARTITIONp0HISTORY,PARTITIONp1HISTORY,PARTITIONpcurCURRENT);

MariaDB will start writing history rows into partitionp0, and at the end of the statement that wrote the 100000th row, MariaDB will switch to partitionp1. There are only two historical partitions, so whenp1 overflows, MariaDB will issue a warning, but will continue writing into it.

Similarly, one can rotate partitions by time:

CREATETABLEt(xINT)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIMEINTERVAL1WEEK(PARTITIONp0HISTORY,PARTITIONp1HISTORY,PARTITIONp2HISTORY,PARTITIONpcurCURRENT);

This means that the history for the first week after the table was created will be stored inp0. The history for the second week — inp1, and all later history will go intop2. One can see the exact rotation time for each partition in theINFORMATION_SCHEMA.PARTITIONS table.

It is possible to combine partitioning bySYSTEM_TIME and subpartitions:

CREATETABLEt(xINT)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIMESUBPARTITIONBYKEY(x)SUBPARTITIONS4(PARTITIONphHISTORY,PARTITIONpcCURRENT);

Default Partitions

CREATETABLEt(xINT)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIME(PARTITIONp0HISTORY,PARTITIONpnCURRENT);

CREATETABLEt(xINT)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIME;

CREATETABLEt(xINT)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIMEINTERVAL1MONTHPARTITIONS12;

CREATEORREPLACETABLEt(xINT)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIMEPARTITIONS12;QueryOK,0rowsaffected,1warning(0.518sec)Warning(Code4115):Maybemissingparameters:norotationconditionformultipleHISTORYpartitions.

CREATEORREPLACETABLEt(xINT)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIMEPARTITIONS1;ERROR4128(HY000):Wrongpartitionsfor`t`:musthaveatleastoneHISTORYandexactlyonelastCURRENT

Automatically Creating Partitions

CREATETABLEt1(xint)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIMEINTERVAL1HOURAUTO;CREATETABLEt1(xint)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIMEINTERVAL1MONTHSTARTS'2021-01-01 00:00:00'AUTOPARTITIONS12;CREATETABLEt1(xint)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIMELIMIT1000AUTO;

CREATETABLEt1(xint)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIMEINTERVAL1HOURAUTO(PARTITIONp0HISTORY,PARTITIONpnCURRENT);

CREATETABLEt1(xint)WITHSYSTEMVERSIONINGPARTITIONBYSYSTEM_TIMEINTERVAL1HOURAUTO;#Disablesauto-creation:ALTERTABLEt1PARTITIONBYSYSTEM_TIMEINTERVAL1HOUR;#Enablesauto-creation:ALTERTABLEt1PARTITIONBYSYSTEM_TIMEINTERVAL1HOURAUTO;

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:

ALTERTABLEtDROPSYSTEMVERSIONING;ALTERTABLEtADDSYSTEMVERSIONING;

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:

ALTERTABLEtDROPPARTITIONp0;

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

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

DELETEHISTORYFROMt;

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

DELETEHISTORYFROMtBEFORESYSTEM_TIME'2016-10-09 08:07:06';

or to a specific transaction (withBEFORE SYSTEM_TIME TRANSACTION xxx).

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

Currently, using the DELETE HISTORY statement with a BEFORE SYSTEM_TIME greater than the ROW_END of the active records (as aTIMESTAMP, 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. SeeMDEV-25468.

TheTRUNCATE TABLE statement drops all historical records from a system-versioned-table.

Historic data is protected from TRUNCATE statements, as per the SQL standard, and an Error 4137 is instead raised:

TRUNCATEt;ERROR4137(HY000):System-versionedtablesdonotsupportTRUNCATETABLE

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 itWITHOUT VERSIONING

CREATETABLEt(xINT,yINTWITHOUTSYSTEMVERSIONING)WITHSYSTEMVERSIONING;

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

CREATETABLEt(xINTWITHSYSTEMVERSIONING,yINT);

System Variables

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

system_versioning_alter_history

• Description: SQL:2011 does not allowALTER TABLE on system-versioned tables. When this variable is set toERROR, an attempt to alter a system-versioned table will result in an error. When this variable is set toKEEP, 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 beNOT NULL will return an error (or warning ifstrict_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 implicitFOR 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 asINSERT .. SELECT andREPLACE .. SELECT need to state AS OF explicitly.

Note: You need to use quotes around the name'DEFAULT' when setting the session value, unquoted literalDEFAULT will restore the current global value instead.

• 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 ifsecure_timestamp allows changingtimestamp.
• 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 togenerated (virtual and persistent) columns.
• 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. FromMariaDB 10.11, use the-H or--dump-history options to include the history.

See Also

• Application-Time Periods
• Bitemporal Tables
• mysql.transaction_registry Table
• MariaDB Temporal Tables (video)

• ↑ Temporal Tables ↑
• Application-Time Periods →