Stop Writing Audit Triggers: MariaDB System-Versioned Tables Do It for You

Every team eventually gets the same request: "We need to know what this row looked like last Tuesday." The traditional answer is a pile of triggers writing to shadow audit tables, plus application code that soft-deletes instead of deleting. It works, but it's fragile — one forgotten trigger and your audit trail has a hole. MariaDB's system-versioned tables (available since 10.3) move that bookkeeping into the storage engine, so every UPDATE and DELETE is captured automatically and you can query the past with ordinary SQL.

What system versioning actually does

When you declare a table with WITH SYSTEM VERSIONING, MariaDB adds two hidden timestamp columns — conventionally row_start and row_end — and stores superseded row versions alongside the current data. An UPDATE doesn't overwrite the row; it closes the old version (setting its row_end) and opens a new one. A DELETE closes the current version instead of removing it. Your normal SELECT statements keep working unchanged and only see current rows, which means you can enable versioning on an existing application without touching its queries.

The payoff is that "what did this look like then?" becomes a query, not a restore-from-backup exercise:

-- Everything that ever happened to the table
SELECT * FROM accounts FOR SYSTEM_TIME ALL;

-- The row as it existed at a moment in time
SELECT * FROM accounts FOR SYSTEM_TIME AS OF
  TIMESTAMP '2026-10-01 09:30:00'
WHERE account_id = 4182;

You can also use FOR SYSTEM_TIME BETWEEN ... AND ... to reconstruct how a row evolved over a window — useful for investigating when a bad value first appeared.

A worked example: auditing an accounts table

Run this in any MariaDB 10.3+ client (the mysql CLI, DBeaver, etc.) with a user that has CREATE and ALTER privileges on the target schema:

CREATE TABLE accounts (
  account_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  owner      VARCHAR(100) NOT NULL,
  balance    DECIMAL(12,2) NOT NULL
) WITH SYSTEM VERSIONING;

INSERT INTO accounts (owner, balance) VALUES ('ada', 500.00);
UPDATE accounts SET balance = 475.50 WHERE owner = 'ada';
DELETE FROM accounts WHERE owner = 'ada';

After the DELETE, a plain SELECT * FROM accounts returns nothing — as expected. But the full history is intact:

SELECT owner, balance, row_start, row_end
FROM accounts FOR SYSTEM_TIME ALL
ORDER BY row_start;

You should see two versions of the row: one with balance 500.00 closed at the moment of the UPDATE, and one with 475.50 closed at the DELETE. If row_start/row_end don't appear in your output, check that you're selecting them explicitly — they're hidden from SELECT * by default in some versions, so naming the columns is the reliable check.

Adding versioning to an existing table

You don't have to rebuild. For an existing table, run:

ALTER TABLE accounts ADD SYSTEM VERSIONING;

Caveats worth knowing before you run this in production: it requires an exclusive lock while it rewrites the table (plan it like any blocking ALTER on a large table), and all pre-existing rows become current versions — there is no retroactive history. Verify afterward with SHOW CREATE TABLE accounts\G and confirm the versioning clause is present, then do one UPDATE and confirm a second version appears under FOR SYSTEM_TIME ALL.

The trade-offs you sign up for

Nothing about this is free, and the costs are predictable:

• Storage growth. Every UPDATE and DELETE keeps the old row forever. A churn-heavy table (think session state or counters) can balloon. Mitigate by versioning only tables with genuine audit requirements, and consider pruning with DELETE HISTORY FROM accounts BEFORE SYSTEM_TIME '...' on a schedule.
• History query performance. FOR SYSTEM_TIME ALL scans current rows plus all history. On large tables, partition the history (WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME ...) so old versions can be dropped or archived in chunks rather than scanned.
• Portability. This is a MariaDB extension. MySQL 8.0 does not support the syntax, and ORMs generally don't know about it — migrations and schema dumps need review if you ever plan to move engines.

When to reach for it

System versioning is a strong default for tables where auditors, regulators, or your own incident reviews will ask "what changed and when": account balances, pricing, permissions, configuration. It's a poor fit for high-frequency mutable state where nobody will ever look at the history. The actionable step: pick one table where you currently maintain a trigger-based audit table, enable versioning on a staging copy, replay a day of writes against it, and compare the storage overhead against the trigger code you get to delete. That comparison — measured on your own workload — is the only benchmark that matters.