MariaDB 10.3 System-Versioned Tables

"What did this row look like last Tuesday?" Every time someone asks me that, I groan, because it means an audit table, a trigger copying every change into it, and application code that never quite queries it right. MariaDB 10.3 implements the SQL:2011 system-versioned tables, which does the same job inside the storage engine. Every UPDATE and DELETE keeps the old row version with a validity period, and FOR SYSTEM_TIME lets you query the table as it was at any point in the past.

It needs MariaDB 10.3.x (not in 10.2, and not in MySQL). I only use it on InnoDB; MyISAM and Aria work, but the transactional semantics only make sense with InnoDB.

Turning versioning on

New table:

CREATE TABLE prices (
  product_id INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'EUR',
  PRIMARY KEY (product_id)
) ENGINE=InnoDB WITH SYSTEM VERSIONING;

Existing table:

ALTER TABLE prices ADD SYSTEM VERSIONING;

MariaDB adds two invisible columns, ROW_START and ROW_END, of type TIMESTAMP(6). They are hidden from SELECT * but can be selected by name. If you want explicit names, declare them yourself:

CREATE TABLE prices (
  product_id INT NOT NULL PRIMARY KEY,
  price DECIMAL(10,2) NOT NULL,
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) WITH SYSTEM VERSIONING;

Look at the history

INSERT INTO prices VALUES (1, 9.99, 'EUR');
UPDATE prices SET price = 10.99 WHERE product_id = 1;
UPDATE prices SET price = 11.49 WHERE product_id = 1;
DELETE FROM prices WHERE product_id = 1;

A plain SELECT now returns nothing, because the current version was deleted. The history is still there:

SELECT product_id, price, ROW_START, ROW_END
FROM prices FOR SYSTEM_TIME ALL
WHERE product_id = 1;

Each row shows the interval during which that version was current. The last version has a ROW_END equal to the deletion time; rows that are still current have ROW_END set to the maximum timestamp 2038-01-19 03:14:07.999999.

Query as of a point in time

-- as it was at a specific moment
SELECT * FROM prices FOR SYSTEM_TIME AS OF TIMESTAMP '2019-01-15 10:00:00';

-- everything that was current at any point in a range
SELECT * FROM prices FOR SYSTEM_TIME BETWEEN TIMESTAMP '2019-01-10 00:00:00' AND TIMESTAMP '2019-01-16 00:00:00';

-- same, but with an exclusive upper bound
SELECT * FROM prices FOR SYSTEM_TIME FROM '2019-01-10 00:00:00' TO '2019-01-16 00:00:00';

AS OF works on joins too, so a report that joins orders to prices FOR SYSTEM_TIME AS OF o.created_at is possible with a lateral-style subquery per row, although for large reports it is cheaper to pick a single timestamp for the whole query.

You can also set it once for a session and keep writing normal queries:

SET @@system_versioning_asof = '2019-01-15 10:00:00';
SELECT * FROM prices;   -- now historical
SET @@system_versioning_asof = DEFAULT;

Keeping history from eating the disk

If a table has columns that change constantly and are not worth versioning (a last_seen timestamp, a counter), exclude them so that updates to those columns alone do not create a new version:

ALTER TABLE users MODIFY last_login TIMESTAMP WITHOUT SYSTEM VERSIONING;

History rows are never deleted by normal DELETE; only a privileged statement removes them:

DELETE HISTORY FROM prices BEFORE SYSTEM_TIME '2018-07-01 00:00:00';

For tables with heavy churn, partition the history so purging is a partition drop instead of a row-by-row delete:

ALTER TABLE prices PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH (
  PARTITION p_hist HISTORY,
  PARTITION p_cur CURRENT
);

With INTERVAL, MariaDB creates new history partitions automatically as time passes when combined with PARTITIONS n, and old ones can be dropped with ALTER TABLE ... DROP PARTITION.

Checking it is on

SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'shop' AND TABLE_TYPE = 'SYSTEM VERSIONED';
SHOW CREATE TABLE prices\G

The SHOW CREATE TABLE output ends with WITH SYSTEM VERSIONING. Count versions to confirm updates are being recorded:

SELECT product_id, COUNT(*) AS versions FROM prices FOR SYSTEM_TIME ALL GROUP BY product_id;

Restrictions and surprises

• Not allowed for system-versioned shop.prices. Change @@system_versioning_alter_history to proceed with ALTER. on ALTER TABLE: by default, schema changes on versioned tables are refused because they would make old versions inconsistent. Run SET @@system_versioning_alter_history = KEEP; in the session before the ALTER, which applies the change to history rows as well.
• Table shop.prices is not system-versioned: FOR SYSTEM_TIME was used on a normal table, typically after a restore. mysqldump writes only the current rows and, depending on the version, may not preserve the WITH SYSTEM VERSIONING clause; check SHOW CREATE TABLE after a restore and re-add versioning if needed.
• Replication to a non-versioned replica or to MySQL is not possible for versioned tables; all nodes must be MariaDB 10.3+.
• The history includes every intermediate version, so a table updated a thousand times per second grows fast. Measure with SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL after a day before committing to it on a hot table.
• Foreign keys are enforced only against current rows, and TRUNCATE TABLE removes history as well.

I'd use it for reference data like prices, tariffs and configuration. Decide the retention (DELETE HISTORY or partitioning) before you enable it, not after the disk fills.