11. Temporal tables: WITH SYSTEM VERSIONING

A table with system versioning keeps the history of its rows: an UPDATE or a DELETE preserves the old version, along with the time it ceased to be current. You can then answer "what was the value on December 31?" without a hand-written audit log. The syntax is MariaDB's.

11.1 Creating a versioned table#

CREATE TABLE stock (
    article INT PRIMARY KEY,
    qte     INT NOT NULL
) WITH SYSTEM VERSIONING;

Miraj adds two invisible columns, row_start and row_end, which delimit the validity period of each version. SELECT * does not show them, an INSERT without a column list gives them no value, and SHOW CREATE TABLE and DESCRIBE omit them; you refer to them by name:

SELECT article, qte, row_start, row_end FROM stock;

A current row has the maximum value as its row_end, 2038-01-19 03:14:07. You can also declare the period columns yourself, in which case they remain visible:

CREATE TABLE stock (
    article INT PRIMARY KEY,
    qte     INT NOT NULL,
    debut   TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
    fin     TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (debut, fin)
) WITH SYSTEM VERSIONING;

Period columns are of type TIMESTAMP or DATETIME (otherwise error 4146). Miraj overwrites any value that an INSERT or an UPDATE gives them.

11.2 What writes preserve#

The history follows the transaction: a ROLLBACK undoes it, and after a crash only committed transactions are kept. Ordinary reads (SELECT * FROM stock) see only the current rows; keys (PRIMARY KEY, UNIQUE) apply to them alone.

A versioned table combines with the engine's other objects. A column that takes its value from a sequence (id BIGINT DEFAULT NEXTVAL(ids), see chapter 6) works as on an ordinary table, including after ALTER TABLE … ADD SYSTEM VERSIONING: the history keeps the identifier as it was written, and archive copies do not consume the sequence. A disk-based table (ENGINE = Aria) can be versioned (see 11.7).

11.3 Reading the past: FOR SYSTEM_TIME#

SELECT * FROM stock FOR SYSTEM_TIME AS OF '2025-12-31 23:59:59';
SELECT * FROM stock FOR SYSTEM_TIME AS OF TIMESTAMP '2025-12-31 23:59:59';
SELECT * FROM stock FOR SYSTEM_TIME FROM '2025-01-01' TO '2026-01-01';
SELECT * FROM stock FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2025-12-31';
SELECT * FROM stock FOR SYSTEM_TIME ALL;

The clause goes after the table name, before the alias, and applies to each table of a join, a subquery, a derived table, a UNION, and to the source of an INSERT … SELECT:

SELECT c.nom, s.qte
FROM clients c JOIN stock FOR SYSTEM_TIME AS OF '2025-12-31' s ON s.article = c.article;

The bounds are expressions (NOW() - INTERVAL 1 DAY, @instant, ?). A table that is not versioned gives error 4174. AS OF TRANSACTION (versioning by transaction ID) is not supported (1235).

11.4 Deleting from the history: DELETE HISTORY#

DELETE HISTORY FROM stock;                                   -- the whole history
DELETE HISTORY FROM stock BEFORE SYSTEM_TIME '2024-01-01';   -- versions ended before this date

Current rows are never touched. The statement returns the number of versions deleted.

11.5 Adding or removing versioning#

ALTER TABLE stock ADD SYSTEM VERSIONING;
ALTER TABLE stock DROP SYSTEM VERSIONING;

On adding, existing rows start at the time of the ALTER. On removal, the history is deleted and the implicit period columns disappear. The other ALTER TABLE operations on a versioned table (column added, dropped, modified, renamed) also apply to the history; an added column takes its default value there. Period columns cannot be modified (1235). RENAME TABLE, DROP TABLE, CREATE TABLE … LIKE and CREATE OR REPLACE TABLE follow the table.

11.6 Under the hood#

The history lives in a companion table __sv_<table> in the same database, with no keys or constraints, which SHOW TABLES and information_schema hide. It can be read like an ordinary table (SELECT * FROM __sv_stock), with the privileges of its table. information_schema.TABLES.TABLE_TYPE is SYSTEM VERSIONED. BACKUP / RESTORE copy it along with the table; miraj-dump exports only the current rows (the script recreates a versioned table, without history). A table name that starts with __sv_ is reserved.

11.7 Differences from MariaDB#

• One-second resolution: NOW() and the period columns have no fractional seconds. Two versions written in the same second have the same row_start; the replaced version is kept with an empty period, which AS OF never sees but ALL lists.
• A single transaction that modifies a row twice leaves two versions of it (MariaDB leaves only one).
• Current period end 2038-01-19 03:14:07 (MariaDB: 2038-01-19 03:14:07.999999).
• Not supported: versioning by transaction ID (BIGINT UNSIGNED, AS OF TRANSACTION), PARTITION BY SYSTEM_TIME, per-column WITH / WITHOUT SYSTEM VERSIONING, temporary and partitioned tables, FOR SYSTEM_TIME inside a trigger body.
• No application-time periods or bitemporal tables: PERIOD FOR name (start, end) (other than SYSTEM_TIME) and ADD PERIOD FOR return error 1235; WITHOUT OVERLAPS and FOR PORTION OF are not recognized. System versioning is therefore the only temporal dimension available.
• Period columns are TIMESTAMPs: they follow the session time_zone like any TIMESTAMP column, and FOR SYSTEM_TIME compares instants, not displayed times. The period end of a current row, written as 2038-01-19 03:14:07 in the time zone of the writing session, therefore displays shifted under another time zone. Period columns written as TIMESTAMP(6) display with six zero decimals.
• A disk-based table (ENGINE = Aria…) can be versioned; its companion table remains an in-memory table.
• Deletions and modifications made by a foreign key action (ON DELETE CASCADE…) leave no history: they fire nothing, as with triggers.
• Error numbers 4146, 4174 and 4185 are those of MariaDB 10.11, noted from memory, to be confirmed.