Using MariaDB System-Versioned Tables for Point‑in‑Time Queries

Quick answer

Enable MariaDB system‑versioned (temporal) tables so you can query the state of a row at any past moment with FOR SYSTEM_TIME AS OF, without building your own audit tables.

How it works

When you create a table with the clause WITH SYSTEM VERSIONING, MariaDB adds two hidden columns (start_time and end_time) and creates a hidden history table (named _history by default). Every INSERT, UPDATE, or DELETE writes the old row version into the history table with timestamps, allowing the engine to reconstruct past states.

Worked example

1. Ensure the InnoDB file‑per‑table setting is enabled (required for the hidden history table). Run this with a user that has SUPER or SYSTEM_VARIABLES_ADMIN privilege:

   SET GLOBAL innodb_file_per_table = ON;

2. Create a versioned table. Replace employees with your table name.

   CREATE TABLE employees (
       id INT PRIMARY KEY,
       name VARCHAR(50),
       salary DECIMAL(10,2)
   ) WITH SYSTEM VERSIONING;

3. Insert a row and commit.

   INSERT INTO employees VALUES (1,'Alice',70000);
   COMMIT;

4. Wait a few seconds (or explicitly set a timestamp) then update the row.

   UPDATE employees SET salary = 75000 WHERE id = 1;
   COMMIT;

5. Query the row as it existed 10 seconds ago.

   SELECT * FROM employees
   FOR SYSTEM_TIME AS OF NOW() - INTERVAL 10 SECOND
   WHERE id = 1;

The result set will show salary = 70000, the value before the update, demonstrating point‑in‑time retrieval.

Limits and prerequisites

• Only the InnoDB storage engine is supported.
• innodb_file_per_table must be ON; otherwise the hidden history table cannot be created.
• Each versioned table gets two hidden TIMESTAMP(6) columns and a separate history table, increasing storage use.
• Certain ALTER TABLE operations (e.g., dropping a column, changing a column type) are prohibited while system versioning is active; you must first drop versioning with ALTER TABLE … SET WITHOUT SYSTEM VERSIONING.
• Foreign keys that reference a versioned table are allowed, but foreign keys from a versioned table to another table may be unsupported in some MariaDB releases.
• Triggers on versioned tables fire for both the base and history tables, which can lead to unexpected duplicate execution.

Common mistakes

• Forgot WITH SYSTEM VERSIONING when creating the table – the table behaves like a regular table and FOR SYSTEM_TIME returns an error.
• Attempting to query history on a non‑versioned table; the parser will reject the FOR SYSTEM_TIME clause.
• Assuming the feature works identically with asynchronous replication; the history table is replicated, but if the replica uses a different innodb_file_per_table setting, creation may fail.
• Not accounting for extra disk usage; the history table grows with every change, so monitor SHOW TABLE STATUS LIKE 'employees_history' and consider a purge policy (e.g., periodic DELETE FROM employees_history WHERE end_time < NOW() - INTERVAL 30 DAY after removing versioning).

Practical verification

After creating the table, you can confirm the setup:

1. Run SHOW CREATE TABLE employees\G – the output should include WITH SYSTEM VERSIONING and show the hidden columns start_time and end_time.
2. Insert, update, and run the FOR SYSTEM_TIME AS OF query as shown above; the result set will contain the earlier row version.

If the query returns the current row instead of the past version, check that the table was indeed created with system versioning and that the session is not using a read‑only replica where the history table may be lagging.

Rollback considerations

Dropping system versioning (ALTER TABLE … SET WITHOUT SYSTEM VERSIONING) removes the hidden columns and history table, which is a destructive operation. There is no automatic rollback; ensure you have a backup or export of the history data before disabling versioning if you need to retain audit information.