MariaDB System-Versioned Tables as a Built-In Audit Log

The Audit Table Tax

If you have ever built an audit trail in MariaDB, you know the pattern: a shadow table, a few triggers, and a hope that every future code path remembers to write to it. Manual audit tables work, but they drift. Someone forgets a trigger on a new code path, or a bulk load bypasses it, and your compliance story develops holes. MariaDB's system-versioned tables, available since 10.3, offer a different approach: let the storage engine keep the history for you.

Thesis: With a single clause in your CREATE TABLE statement, MariaDB can automatically preserve every version of a row, giving you a queryable audit log without trigger maintenance or application-level bookkeeping.

How System Versioning Works

A system-versioned table is a normal InnoDB table paired with a hidden history table. MariaDB adds two invisible columns, row_start and row_end, that record the system time when a row was inserted or changed. When you UPDATE or DELETE a row, MariaDB does not destroy the old version; it moves it to the history table and stamps the end time. The PERIOD FOR SYSTEM_TIME definition ties these columns together, and the FOR SYSTEM_TIME clause in your SELECT statements lets you travel back to a specific moment.

Because the history lives in a separate table, your application queries remain simple. You run SELECT * FROM employees FOR SYSTEM_TIME AS OF '2026-01-01' and MariaDB reconstructs the state of that table at that timestamp.

Enabling Versioning and Required Privileges

You create a system-versioned table by adding WITH SYSTEM VERSIONING to your CREATE TABLE statement. The table must use the InnoDB storage engine. You need the standard CREATE privilege on the database, plus INSERT, UPDATE, and DELETE on the base table. No special privilege is required to read history, though you may want to restrict access to the history table itself for compliance. If you are retrofitting an existing table, you can enable versioning with ALTER TABLE employees SET (SYSTEM_VERSIONING = ON);, though the history table will only capture changes made after that point.

Worked Example: Tracking Salary Changes

Suppose you want to audit salary adjustments. Run these statements in your MariaDB client (mysql or a SQL IDE) against a test database:

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

MariaDB creates a companion table named employees_history by default. Insert a row:

INSERT INTO employees (id, name, salary) VALUES (1, 'A. Rivera', 75000.00);

Now simulate a raise:

UPDATE employees SET salary = 82000.00 WHERE id = 1;

The old row—salary 75000.00—now lives in employees_history. To see the past state, query:

SELECT * FROM employees FOR SYSTEM_TIME AS OF '2026-01-01 12:00:00';

The query should return the row as it existed at that timestamp, showing the salary of 75000.00 rather than the updated 82000.00. If you omit FOR SYSTEM_TIME, you see only the live row.

You can also inspect the history table directly:

SELECT id, name, salary, row_start, row_end FROM employees_history;

Note that row_start and row_end are hidden from normal SELECT * queries on the base table, but they are visible when you query the history table explicitly.

Storage and DDL Trade-offs

System versioning is not free. The history table grows with every UPDATE and DELETE. A high-churn table can generate history faster than you expect, and without a retention policy you may exhaust disk. Monitor information_schema.tables for both the base and history tables:

SELECT table_name, table_rows,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database'
  AND table_name LIKE 'employees%';

Replace your_database with the actual schema name. This query requires only standard SELECT access to information_schema.

DDL is the second sharp edge. Some DDL operations, such as dropping a column, are not automatically propagated to the history table and may require manual intervention. Always test DDL changes in staging before applying them to a versioned production table.

To control growth, use the event scheduler to purge old history. The following example deletes rows older than 30 days:

CREATE EVENT purge_employee_history
ON SCHEDULE EVERY 1 DAY
DO
  DELETE FROM employees_history
  WHERE row_end < NOW() - INTERVAL 30 DAY;

You need the EVENT privilege to create this, and the event scheduler must be enabled (SET GLOBAL event_scheduler = ON;), which requires an administrative privilege. Adjust the interval to match your compliance window. Be aware that purging the history table generates binlog entries, so factor that into your replication load.

A Practical Pilot Plan

Start with a low-to-medium churn table in staging. Verify your server version with SELECT VERSION(); and confirm it is 10.3 or newer. Create the versioned table, run your normal INSERT, UPDATE, and DELETE workload, and confirm that FOR SYSTEM_TIME AS OF returns the states you expect. Measure the history table size after a realistic day of traffic. If storage growth is acceptable, define your retention event and document the manual DDL procedure for your team. Only then promote the pattern to production.

System-versioned tables will not replace every audit requirement—if you need to capture who made a change, you still need application context or a separate mechanism. But for the common question "what did this row look like last quarter?", MariaDB can answer it natively, without a single trigger.