System-Versioned Tables

System-versioned tables is a feature first introduced in SQL:2011 standard. System-versioned tables store the history of all changes, not only the data which is valid at the current moment in time. 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 discovery & legal requirements to store data for N years
• Data analysis (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

Creating a system-versioned table

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

CREATE TABLE t(
   x INT,
   start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
   end_timestamp  TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
   PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
) WITH SYSTEM VERSIONING;

In MariaDB one can also use a simplified syntax

CREATE TABLE t (
   x INT
) WITH SYSTEM VERSIONING;

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 pseudo-columns ROW_START and ROW_END:

SELECT x, ROW_START, ROW_END FROM t;

Adding or removing system versioning to/from a table

An existing table can be altered to enable system versioning for it.

CREATE TABLE t(
  x INT
);

ALTER TABLE t ADD SYSTEM VERSIONING;

SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create 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:

ALTER TABLE t DROP SYSTEM VERSIONING;

SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create 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:

ALTER TABLE t ADD COLUMN ts TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
              ADD COLUMN te TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
              ADD PERIOD FOR SYSTEM_TIME(ts, te),
              ADD SYSTEM VERSIONING;

SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create 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=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING

Querying historical data

SELECT

To query the historical data one uses the clause FOR 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 as a specific point in time in the past:

SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'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 rows visible exactly at start or exactly at end will be shown too.

SELECT * FROM t FOR SYSTEM_TIME BETWEEN TIMESTAMP'2016-01-01 00:00:00' AND TIMESTAMP'2016-12-31 23:59:59'

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

SELECT * FROM t FOR SYSTEM_TIME FROM TIMESTAMP'2016-01-01 00:00:00' TO TIMESTAMP'2017-01-01 00:00:00'

Additionally MariaDB implements a non-standard extension

• ALL will show all rows, historical and current

SELECT * FROM t FOR SYSTEM_TIME ALL

If FOR SYSTEM_TIME clause is not used, the table will show the current data, as if one had specified FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP.

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 a SELECT:

CREATE VIEW v1 AS SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06'

Or

CREATE VIEW v1 AS SELECT * FROM t;
SELECT * FROM v1 FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';

Transaction-exact history in InnoDB

Storing the history separately

Removing old history