Using MariaDB SYSTEM VERSIONED Tables for Built‑In Audit Trails

Problem: Needing a reliable audit trail without application code

When regulatory requirements demand that every change to a table be retained, developers often build triggers or separate log tables. MariaDB’s SYSTEM VERSIONED tables move that work into the engine, giving you a point‑in‑time view of data with a single SQL clause.

Enabling SYSTEM VERSIONING

Add the WITH SYSTEM VERSIONING clause when you create the table. MariaDB automatically creates a hidden history table (named tablename_history) and two hidden columns: row_start and row_end that mark the validity period of each version.

CREATE TABLE orders (
    order_id   INT PRIMARY KEY,
    customer   VARCHAR(100) NOT NULL,
    amount     DECIMAL(10,2) NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) WITH SYSTEM VERSIONING;

Run this statement with a user that has the CREATE privilege on the database. No extra privileges are needed for the history table; it is managed internally.

Querying historical data

To see a row as it existed at a specific moment, use the FOR SYSTEM_TIME clause.

-- Insert a row
INSERT INTO orders (order_id, customer, amount) VALUES (1, 'Acme Corp', 250.00);

-- Update the same row
UPDATE orders SET amount = 300.00 WHERE order_id = 1;

-- View the row as it was before the update
SELECT * FROM orders
FOR SYSTEM_TIME AS OF '2026-10-12 14:00:00'
WHERE order_id = 1;

Replace the timestamp with a value that falls between the row’s row_start and row_end values. The result shows the pre‑update amount (250.00). To see a range, use BETWEEN:

SELECT * FROM orders
FOR SYSTEM_TIME BETWEEN '2026-10-12 13:55:00' AND '2026-10-12 14:10:00'
WHERE order_id = 1;

Trade‑offs and operational considerations

• Storage overhead: Every UPDATE or DELETE writes a copy of the old row to the hidden history table. If a table receives thousands of updates per hour, the history can grow quickly.
• Write performance: Each change incurs two writes (base + history), which can increase latency on write‑heavy workloads.
• Purge strategy: MariaDB does not automatically purge old versions. You must periodically delete rows from the history table based on your retention policy, e.g., DELETE FROM orders_history WHERE row_end < DATE_SUB(NOW(), INTERVAL 90 DAY);

To check that versioning is active, query the information schema:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db'
  AND TABLE_NAME LIKE '%_history';

If you see a table named orders_history, SYSTEM VERSIONING is enabled.

Actionable closing

1. Create the table with WITH SYSTEM VERSIONING.
2. Insert and update sample data to confirm the history table populates.
3. Run a FOR SYSTEM_TIME AS OF query to retrieve a past version.
4. Monitor the size of the hidden history table (e.g., SHOW TABLE STATUS LIKE 'orders_history';) and set up a scheduled purge job that matches your retention requirement.

By letting MariaDB handle versioning, you remove custom trigger code and gain a standard SQL interface for auditing—just remember to plan for the extra storage and periodic cleanup.