MariaDB System‑Versioned Tables: Auditing Made Easy

The Problem: Auditing Without Triggers

In many applications you need to know who changed a row and when. The usual workaround is to add a trigger that writes every UPDATE or DELETE into a separate audit table, or to let the application keep a history column. Both approaches add code, increase maintenance, and can be error‑prone.

MariaDB, starting with 10.3, offers a baked‑in solution: system‑versioned tables. The engine automatically stores every superseded row in the same table, with hidden columns that record the period during which the row was valid. No trigger, no extra table, no manual bookkeeping.

How System‑Versioned Tables Work

Adding WITH SYSTEM VERSIONING to a table definition tells MariaDB to keep a history of every row change. Internally the engine adds two hidden columns: ROW_START and ROW_END. They are TIMESTAMP(6) values that capture the system time when the row became valid and when it was invalidated. The original data columns remain unchanged.

When you UPDATE or DELETE a row, MariaDB:

• Sets ROW_END of the current row to the current system timestamp.
• Inserts a new row with the new data and ROW_START set to the same timestamp, ROW_END set to NULL (meaning "currently valid").

Because ROW_START and ROW_END are hidden, a plain SELECT * returns only the user columns and the current value of the row.

Point‑in‑time queries are written with the FOR SYSTEM_TIME clause. MariaDB evaluates the clause against the hidden columns to return the row as it existed at the requested time.

Worked Example: Point‑in‑Time Query

Below is a self‑contained example that you can run on any MariaDB 10.3+ server. Replace your_user and your_password with real credentials.

# Connect to a fresh MariaDB instance
mysql -u your_user -p your_password -e \
  "CREATE DATABASE IF NOT EXISTS audit_demo;" 
mysql -u your_user -p your_password audit_demo -e \
  "DROP TABLE IF EXISTS price;"

# Create a versioned table
mysql -u your_user -p your_password audit_demo -e \
  "CREATE TABLE price (
    product_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (product_id)
  ) WITH SYSTEM VERSIONING;"

# Insert an initial price
mysql -u your_user -p your_password audit_demo -e \
  "INSERT INTO price VALUES (1, 19.99);"

# Update the price a day later
mysql -u your_user -p your_password audit_demo -e \
  "UPDATE price SET price = 24.99 WHERE product_id = 1;"

# Show the current price (normal SELECT)
mysql -u your_user -p your_password audit_demo -e \
  "SELECT * FROM price;"

# Point‑in‑time: what was the price yesterday?
mysql -u your_user -p your_password audit_demo -e \
  "SELECT * FROM price FOR SYSTEM_TIME AS OF '2026-10-18 00:00:00';"

# Show all history, including the deleted row
mysql -u your_user -p your_password audit_demo -e \
  "SELECT product_id, price, ROW_START, ROW_END
   FROM price FOR SYSTEM_TIME ALL;"
"

After the UPDATE, the FOR SYSTEM_TIME AS OF query returns the old price (19.99). The FOR SYSTEM_TIME ALL query shows both the old and new rows, along with their ROW_START and ROW_END timestamps. If you DELETE a row, it will still appear under FOR SYSTEM_TIME ALL until you prune it.

The Storage Trade‑Off and Pruning

By design, MariaDB keeps every historical row forever. That means the table can grow quickly, affecting storage, backups, and query performance. The engine does not delete history automatically, so you must decide how to prune it.

Two common strategies:

1. Partition by system time
   Create partitions that map to a time period (e.g., daily or monthly). Older partitions can be dropped in bulk. Example:

ALTER TABLE price
  PARTITION BY SYSTEM_TIME
  INTERVAL DAY
  (PARTITION p0 VALUES LESS THAN (CURRENT_TIMESTAMP));

This creates a p0 partition for the current day and automatically creates new partitions for each subsequent day. To keep only the last 30 days, drop older partitions:

ALTER TABLE price DROP PARTITION p_old;

Replace p_old with the name of the partition you want to remove.

1. DELETE HISTORY
   MariaDB 10.4+ adds a DELETE HISTORY statement that removes rows whose ROW_END is older than a given timestamp. Example:

DELETE HISTORY FROM price
  WHERE ROW_END < '2026-09-01 00:00:00';

Both methods require careful planning: dropping partitions is fast but irreversible; DELETE HISTORY is incremental but can lock the table for a while if the history set is large.

Note: If you need to keep a long‑term audit trail, consider offloading old history to a separate table or archival storage. The hidden columns can be exported with SELECT ... FOR SYSTEM_TIME ALL and then stored elsewhere.

Actionable Next Steps

1. Identify a table that changes frequently and is audit‑heavy (e.g., a subscription status table).

2. Add WITH SYSTEM VERSIONING to that table. If the table already exists, you can enable versioning with:

ALTER TABLE your_table
  ADD SYSTEM VERSIONING;

3. Run a small pilot: perform a few updates, then query FOR SYSTEM_TIME AS OF to confirm the history appears.

4. Decide on a retention policy. If you expect high churn, partition by system time from day one. If you prefer simplicity, schedule periodic DELETE HISTORY jobs.

5. Monitor table size and backup times. Adjust the pruning strategy as needed.

By leveraging MariaDB’s built‑in temporal tables, you eliminate the boilerplate of triggers and audit tables while still having full, queryable history. It’s a storage‑engine decision that keeps your application code clean and your audit trail reliable.