What Are MariaDB Temporal Tables?

Starting from 10.3.4, MariaDB comes with temporal tables. It is still quite an uncommon feature and we would like to discuss a bit what those tables are and what they can be useful for.

First of all, in case someone has misread the title of this blog, we are talking here about temporal tables, not temporary tables, which as well exist in MariaDB. They do have something in common, though. Time. Temporary tables are short-lived, temporal tables on the other hand are designed to give access to the data over time. In short, you can see temporal tables as a versioned table that can be used to access and modify past data, find what changes have been made and when. It can also be used to rollback data to a particular point in time.

How to Use Temporal Tables in MariaDB

To create a temporal table we only have to add “WITH SYSTEM VERSIONING” to the CREATE TABLE command. If you want to convert regular table into a temporal one, you can run:

ALTER TABLE mytable ADD SYSTEM VERSIONING;

This is pretty much all. A temporal table will be created and you can start querying its data. There are a couple of ways to do that.

First, we can use SELECT to query data as of particular time:

SELECT * FROM mytable FOR SYSTEM_TIME AS OF TIMESTAMP ‘2020-06-26 10:00:00’;

You can also do a query for a range:

SELECT * FROM mytable FOR SYSTEM_TIME FROM ‘2020-06-26 08:00:00’ TO ‘2020-06-26 10:00:00’;

It is also possible to show all data:

SELECT * FROM mytable FOR SYSTEM_TIME ALL;

If needed, you can create views from temporal tables, following the same pattern as we have shown above.

Given that the same rows may not be updated on all of the nodes at the same time (for example, delays caused by replication), if you want to see exactly the same state of the data across the multiple slaves, you can define the point of time using InnoDB transaction id:

SELECT * FROM mytable FOR SYSTEM_TIME AS OF TRANSACTION 123;

By default all data is stored in the same table, both current and old versions of the rows. This may add some overhead when you query only the recent data. It is possible to use partitions to reduce this overhead by creating one or more partitions to store historical data and one to store recent versions of the rows. Then, using partition pruning, MariaDB will be able to reduce the amount of data it has to query to come up with the result for the query:

CREATE TABLE mytable (a INT) WITH SYSTEM VERSIONING

  PARTITION BY SYSTEM_TIME INTERVAL 1 WEEK (

    PARTITION p0 HISTORY,

    PARTITION p1 HISTORY,

    PARTITION p2 HISTORY,

    PARTITION pcur CURRENT

  );

You can also use other means of partitioning it like, for example, defining the number of rows to store per partition.

When using partitioning, we can now apply regular partitioning best practices like data rotation by removing old partitions. If you did not create partitions, you can still do that through commands like:

DELETE HISTORY FROM mytable;

DELETE HISTORY FROM mytable BEFORE SYSTEM_TIME '2020-06-01 00:00:00';

If needed, you can exclude some of the columns from the versioning:

CREATE TABLE mytable (

   a INT,

   b INT WITHOUT SYSTEM VERSIONING

) WITH SYSTEM VERSIONING;

In MariaDB 10.4 a new option has been added, application-time periods. What it means is, basically, that instead of system time it is possible to create versioning based on two columns (time-based) in the table:

CREATE TABLE mytable (

   a INT, 

   date1 DATE,

   date2 DATE,

   PERIOD FOR date_period(date1, date2));

It is also possible to update or delete rows based on the time (UPDATE FOR PORTION and DELETE FOR PORTION). It is also possible to mix application-time and system-time versioning in one table.

Examples of Temporal Tables in MariaDB

Ok, we have discussed the possibilities, let’s take a look at some of things we can do with temporal tables.

At first, let’s create a table and populate it with some data:

MariaDB [(none)]> CREATE DATABASE versioned;

Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> use versioned

Database changed

MariaDB [versioned]> CREATE TABLE mytable (a INT, b INT) WITH SYSTEM VERSIONING;

Query OK, 0 rows affected (0.005 sec)



MariaDB [versioned]> INSERT INTO mytable VALUES (1,1);

Query OK, 1 row affected (0.001 sec)

MariaDB [versioned]> INSERT INTO mytable VALUES (2,1);

Query OK, 1 row affected (0.001 sec)

MariaDB [versioned]> INSERT INTO mytable VALUES (3,1);

Query OK, 1 row affected (0.000 sec)

Now, let’s update couple of rows:

MariaDB [versioned]> UPDATE mytable SET b = 2 WHERE a < 3;

Query OK, 2 rows affected (0.001 sec)

Rows matched: 2  Changed: 2  Inserted: 2  Warnings: 0

Now, let’s see all the rows that are stored in the table:

MariaDB [versioned]> SELECT * FROM mytable FOR SYSTEM_TIME ALL ;

+------+------+

| a    | b    |

+------+------+

|    1 |    2 |

|    2 |    2 |

|    3 |    1 |

|    1 |    1 |

|    2 |    1 |

+------+------+

5 rows in set (0.000 sec)

As you can see, the table contains not only current versions of the rows but also original values, from before we updated them.

Now, let’s check what the time is and then add some more rows. We’ll see if we can see the current and the past versions.

MariaDB [versioned]> SELECT NOW();

+---------------------+

| NOW()               |

+---------------------+

| 2020-06-26 11:24:55 |

+---------------------+

1 row in set (0.000 sec)

MariaDB [versioned]> INSERT INTO mytable VALUES (4,1);

Query OK, 1 row affected (0.001 sec)

MariaDB [versioned]> INSERT INTO mytable VALUES (5,1);

Query OK, 1 row affected (0.000 sec)

MariaDB [versioned]> UPDATE mytable SET b = 3 WHERE a < 2;

Query OK, 1 row affected (0.001 sec)

Rows matched: 1  Changed: 1  Inserted: 1  Warnings: 0;

Now, let’s check the contents of the table. Only current versions of the rows:

MariaDB [versioned]> SELECT * FROM mytable;

+------+------+

| a    | b    |

+------+------+

|    1 |    3 |

|    2 |    2 |

|    3 |    1 |

|    4 |    1 |

|    5 |    1 |

+------+------+

5 rows in set (0.000 sec)

Then, let’s access the state of the table before we made the inserts and updates:

MariaDB [versioned]> SELECT * FROM mytable FOR SYSTEM_TIME AS OF TIMESTAMP '2020-06-26 11:24:55';

+------+------+

| a    | b    |

+------+------+

|    2 |    2 |

|    3 |    1 |

|    1 |    2 |

+------+------+

3 rows in set (0.000 sec)

Works as expected, we only see three rows in the table.

This short example is by no means extensive. We wanted to give you some idea how you can operate the temporal tables. Applications of this are numerous. Better tracking the state of the order in e-commerce, versioning the contents (configuration files, documents), insight into the past data for analytical purposes.

To make it clear, this feature can be implemented using “traditional” tables, as long as you keep inserting rows, not updating them, but the management is way easier to do when using temporal tables.