Temporal Tables for MariaDB Enterprise Server

Applying System-Versioning

You can add system-versioning to a table when you create the table or afterward by modifying the table. You can also apply system-versioning only to specific columns.

To enable system-versioning, use a CREATE TABLE statement such as:

CREATE TABLE accounts (
       id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(255),
       amount INT
) WITH SYSTEM VERSIONING;

In this example we create a table for tracking accounts. The WITH SYSTEM VERSIONING clause initializes hidden, system-managed row_start and row_end columns for tracking changes by row.

To add system-versioning to an existing table, use the ALTER TABLE statement.

First, create a table without system-versioning:

CREATE TABLE accounts_alt (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(255),
   amount INT
);

Then, use ALTER TABLE to add system-versioning:

ALTER TABLE accounts_alt ADD SYSTEM VERSIONING;

System-Versioned Columns

Enabling system-versioning for an entire table will record timestamps when any column is changed. This may not always be desirable. To record changes to specific columns, set the system-versioning for only those columns.

CREATE TABLE accounts_col (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(255),
   amount INT WITH SYSTEM VERSIONING);

Here, the table only records the changes when you modify the designated column. Changes made to un-versioned columns occur normally, as though the table did not use any system-versioning.

Removing System-Versioning

To remove system-versioning from a table, use an ALTER TABLE statement:

ALTER TABLE accounts_alt DROP SYSTEM VERSIONING;

Retrieve Current Data

When issuing queries against system-versioned tables, there are several features in MariaDB SQL to control access to historic data.

To demonstrate, add some data on the accounts table created above:

INSERT INTO accounts (name, amount)
VALUES ("Smith", 400),
       ("Orson", 300),
       ("Serio", 500),
       ("Wallace", 200),
       ("March", 600),
       ("Douglas", 100);

Then, update one or more of the rows:

UPDATE accounts
SET amount = 1000
WHERE id = 1
   OR id = 3;

Normal queries issued against system-versioned tables always default to returning the most current data available.

SELECT name, amount FROM accounts
WHERE amount > 900;

+-------+--------+
| name  | amount |
+-------+--------+
| Smith |   1000 |
| Serio |   1000 |
+-------+--------+

To make the system-versioning columns visible, add the row_start and row_end columns to the query:

SELECT *, row_start, row_end FROM accounts;

+----+---------+--------+----------------------------+----------------------------+
| id | name    | amount | row_start                  | row_end                    |
+----+---------+--------+----------------------------+----------------------------+
|  1 | Smith   |   1000 | 2019-07-06 15:38:04.516745 | 2038-01-19 03:14:07.999999 |
|  2 | Orson   |    300 | 2019-07-06 15:37:53.477688 | 2038-01-19 03:14:07.999999 |
|  3 | Serio   |   1000 | 2019-07-06 15:38:04.516745 | 2038-01-19 03:14:07.999999 |
|  4 | Wallace |    200 | 2019-07-06 15:37:53.477688 | 2038-01-19 03:14:07.999999 |
|  5 | March   |    600 | 2019-07-06 15:37:53.477688 | 2038-01-19 03:14:07.999999 |
|  6 | Douglas |    100 | 2019-07-06 15:37:53.477688 | 2038-01-19 03:14:07.999999 |
+----+---------+--------+----------------------------+----------------------------+

Retrieve Historical Data

System-versioning enables several SQL statements for accessing historic data. These statements all begin with the FOR SYSTEM TIME clause.

To view all current rows with all historic data, use the ALL keyword:

SELECT name, amount FROM accounts
FOR SYSTEM_TIME ALL;

+---------+--------+
| name    | amount |
+---------+--------+
| Smith   |    400 |
| Smith   |   1000 |
| Orson   |    300 |
| Serio   |    500 |
| Serio   |   1000 |
| Wallace |    200 |
| March   |    600 |
| Douglas |    100 |
+---------+--------+

To access rows visible at a specific point in the past:

SELECT name, amount FROM accounts
FOR SYSTEM_TIME AS OF '2019-06-18 11:00';

To access all rows visible within a certain period:

SELECT name, amount FROM accounts
FOR SYSTEM_TIME
   BETWEEN (NOW() - INTERVAL 1 YEAR)
   AND NOW();

Rows changed during this period show as duplicate entries for the Primary Key. This is because system-versioned tables include the timestamp columns with the Primary Key when testing uniqueness.

This query displays all rows from a specified time period.

SELECT name, amount FROM accounts
FOR SYSTEM_TIME
   FROM '2019-06-01 11:00'
   TO   '2019-06-30 11:00';

These statements also work in subqueries and with views.

CREATE VIEW amount AS
SELECT * FROM accounts;

SELECT name, amount FROM amount
FOR SYSTEM_TIME ALL;

Deletion and Truncation

When you delete a row from a system-versioned table, the historic data is not removed from the table. Queries for current data will exclude the deleted row(s) whereas queries for historic data will include the deleted row(s).

To protect historic data, the TRUNCATE statement raises Error 4137.

TRUNCATE accounts;

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

To prune the history of a table (in this case two records), use DELETE HISTORY.

DELETE HISTORY FROM accounts;

Use system-versioning to delete historical data before a specified date:

DELETE HISTORY FROM accounts
BEFORE SYSTEM_TIME '2019-06-11 12:00';

Applying Application-Time Periods

You can add application-time periods to a table when you create the table or afterward by modifying the table to add support for time period operations.

To create a table with a time period, set two columns with temporal data types (such as DATE or TIMESTAMP, but not TIME, which is not supported), then provide a period specification using these columns.

CREATE TABLE coupons (
   id INT UNSIGNED,
   name VARCHAR(255) DEFAULT 'Daily Special',
   date_start DATE,
   date_end DATE,
   PERIOD FOR valid_period(date_start, date_end)
);

Alternatively, you can add a time period to an existing table. First, create a table with the relevant temporal columns:

CREATE TABLE coupons_alt (
   id INT UNSIGNED,
   name VARCHAR(255),
   date_start DATE,
   date_end DATE);

Then, use an ALTER TABLE statement to add the time period:

ALTER TABLE coupons_alt
ADD PERIOD FOR valid_period(date_start, date_end);

Removing Application-Time Periods

To remove an application time period, use DROP PERIOD:

ALTER TABLE coupons_alt
DROP PERIOD FOR valid_period

Updating by Portion

Application-time period tables support UPDATE operations by portion of the row's time period.

Using an UPDATE FOR PORTION statement, you can modify rows with periods that fall within that of the given portion. The statement modifies all rows that fall completely within the portions. Rows that overlap with the portion are split, with the changes applied to the relevant portion only.

This statement cannot modify or reference the temporal columns used by the time period. It also does not support operating on multiple portions in a single statement.

First, remove all data from the test table:

TRUNCATE coupons;

Then, use an INSERT statement to add data to operate on:

INSERT INTO coupons (id, date_start, date_end)
VALUES (1, '2019-05-03', '2019-05-14'),
       (2, '2019-05-20', '2019-06-14'),
       (3, '2019-04-01', '2019-05-30'),
       (4, '2019-04-01', '2019-06-12');

For reference purposes, query the starting value with a SELECT statement:

SELECT * FROM coupons;

+------+---------------+------------+------------+
| id   | name          | date_start | date_end   |
+------+---------------+------------+------------+
|    1 | Daily Special | 2019-05-03 | 2019-05-14 |
|    2 | Daily Special | 2019-05-20 | 2019-06-14 |
|    3 | Daily Special | 2019-04-01 | 2019-05-30 |
|    4 | Daily Special | 2019-04-01 | 2019-06-12 |
+------+---------------+------------+------------+

To update by time period, use the FOR PORTION clause:

UPDATE coupons FOR PORTION OF valid_period
   FROM '2019-06-01'
     TO '2019-06-30'
SET name = "June Special";

SELECT * FROM coupons;

+------+---------------+------------+------------+
| id   | name          | date_start | date_end   |
+------+---------------+------------+------------+
|    1 | Daily Special | 2019-05-03 | 2019-05-14 |
|    2 | June Special  | 2019-06-01 | 2019-06-14 |
|    3 | Daily Special | 2019-04-01 | 2019-05-30 |
|    4 | June Special  | 2019-06-01 | 2019-06-12 |
|    2 | Daily Special | 2019-05-20 | 2019-06-01 |
|    4 | Daily Special | 2019-04-01 | 2019-06-01 |
+------+---------------+------------+------------+

Notice that row 1 was ignored, since it falls within the month of May. Rows 2 and 4 have periods in June so they were split: the June portion given the update while the remainder put into a new row. Row 3 was ignored, as it falls within April and May.

Deleting by Portion

Application-time period tables support DELETE operations by portion of the row's time period.

Using the DELETE FOR PORTION, you can remove rows with periods that fall within that of the given portion. The statement removes rows that fall completely within the portion, shrinks rows that overlap with the portion, and splits rows that encompass the portion.

This statement does not support deletion by multiple portions in a single statement.

First, remove all data from the test table:

TRUNCATE coupons;

Then, use an INSERT statement to add data to operate on:

INSERT INTO coupons (id, date_start, date_end)
VALUES (1, '2019-05-03', '2019-05-14'),
       (2, '2019-05-20', '2019-06-14'),
       (3, '2019-04-01', '2019-05-30'),
       (4, '2019-04-01', '2019-06-12');

For reference purposes, query the starting value with a SELECT statement:

SELECT * FROM coupons;

+------+---------------+------------+------------+
| id   | name          | date_start | date_end   |
+------+---------------+------------+------------+
|    1 | Daily Special | 2019-05-03 | 2019-05-14 |
|    2 | Daily Special | 2019-05-20 | 2019-06-14 |
|    3 | Daily Special | 2019-04-01 | 2019-05-30 |
|    4 | Daily Special | 2019-04-01 | 2019-06-12 |
+------+---------------+------------+------------+

To remove rows by time period, use the FOR PORTION clause:

DELETE FROM coupons FOR PORTION OF valid_period
   FROM '2019-05-01'
     TO '2019-05-31';

Issue another SELECT statement to view the results:

SELECT * FROM coupons;

+------+---------------+------------+------------+
| id   | name          | date_start | date_end   |
+------+---------------+------------+------------+
|    2 | Daily Special | 2019-05-31 | 2019-06-14 |
|    3 | Daily Special | 2019-04-01 | 2019-05-01 |
|    4 | Daily Special | 2019-04-01 | 2019-05-01 |
|    4 | Daily Special | 2019-05-31 | 2019-06-12 |
+------+---------------+------------+------------+

Notice that row 1 was completely removed as its period occurs within the month of May. The periods for rows 2 and 3 were shrunk to exclude days in May, excepting the 1st and 31st, which defined the portion. Row 4, which ran from April to June was split into separate rows for its April and June components.

Partitions and Sharding

Using system-versioning with a table partitioned by SYSTEM_TIME, MariaDB Enterprise Server writes historic data to separate partitions from current data.

When combined with the Spider storage engine, MariaDB Enterprise Server shards historic data across multiple Servers, enabling storage scale-out.

Delete History

When older history is not important, you can use the DELETE HISTORY statement.

Replace System-Versioning

If data retention is not needed for auditing or other mission-critical purposes, you can periodically remove all historic data by dropping system versioning from the table and then adding it back:

ALTER TABLE accounts DROP SYSTEM VERSIONING;

ALTER TABLE accounts ADD SYSTEM VERSIONING;

This removes all historic data and history tracking begins anew with the current rows.