MariaDB System-Versioned Tables for Automatic Row History

Track row changes without building audit tables

If your application needs to see what a row looked like before an update, the usual options are manual audit tables or application triggers. Both require extra schema, application code, and ongoing maintenance. MariaDB 10.3 and later includes system-versioned tables, a built-in feature that automatically preserves previous row images in a hidden history table.

Useful takeaway: define a table with WITH SYSTEM VERSIONING, update and delete rows normally, then query history with FOR SYSTEM_TIME ALL. The server handles the old-row copies; you do not need trigger code.

How system versioning stores history

When system versioning is enabled, MariaDB keeps the current rows in the visible table and copies each prior row version to an internal history table before an update or delete. A SELECT without time syntax returns only current rows. Adding FOR SYSTEM_TIME ALL returns current and historical versions. System time is managed by the server, not by application timestamps.

Worked configuration: create and query a versioned table

Run the following in a MariaDB client such as the mysql command-line tool, connected to a server running MariaDB 10.3 or later. You need CREATE privileges on the target database.

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

Placeholder meanings: employees is the table name; adjust column names and types for your schema. After inserting and updating rows:

INSERT INTO employees (id, name, salary) VALUES (1, 'A. Example', 80000.00);
UPDATE employees SET salary = 85000.00 WHERE id = 1;

Query current data normally:

SELECT * FROM employees WHERE id = 1;

Query every retained version of that row:

SELECT * FROM employees FOR SYSTEM_TIME ALL WHERE id = 1;

Verify the feature is active

Run these checks in the same client session. Required permission: access to the table and database metadata.

SELECT VERSION();
SHOW CREATE TABLE employees;

Expected checks: the version string starts with 10.3 or higher, and the SHOW CREATE TABLE output includes WITH SYSTEM VERSIONING. Then insert, update, and run the FOR SYSTEM_TIME ALL query to confirm previous versions appear. Do not treat this as proof of tamper resistance; privileged users can disable versioning.

Limits and common mistakes

• Storage growth. Every update and delete writes a prior image. High-change tables can consume significant disk space. Plan capacity and review retention needs before enabling on large tables.
• Blocked operations. TRUNCATE is not permitted on system-versioned tables. Some structural changes require disabling versioning first.
• Not a backup substitute. System versioning retains row history only while the table exists. Dropping the table removes its history. Use backups and binary logs for point-in-time recovery.
• Not tamper-proof. Users with sufficient privileges can alter or disable versioning and modify history.
• Write amplification. Each change writes to both the current table and the history table, which can affect throughput on write-heavy workloads.

Disable system versioning when it is no longer needed

Because enabling versioning changes table state, the corresponding rollback is to disable it. Run this in the MariaDB client with ALTER privilege:

ALTER TABLE employees WITHOUT SYSTEM VERSIONING;

This stops automatic history capture. Existing history is not preserved after disabling; only current rows remain. Use this before structural changes that are blocked while versioning is active.

Practical verification checklist

• Confirm server version is MariaDB 10.3 or later.
• Confirm SHOW CREATE TABLE includes WITH SYSTEM VERSIONING.
• Insert a row, update it, and verify FOR SYSTEM_TIME ALL returns more than one version.
• Monitor disk usage on tables with frequent updates.
• Test any planned TRUNCATE or DDL in a non-production environment first.