Every long-lived application eventually grows an audit requirement. Someone — compliance, support, or an engineer chasing a data bug — needs to answer "what did this row look like on March 1st?" The hand-rolled answer is a shadow table plus triggers, and I have maintained schemas with dozens of trigger pairs keeping those shadows in sync. They drift. Columns get added to the main table and forgotten in the shadow. Triggers fire in surprising orders. MariaDB 10.3 gave us the built-in version of this idea — system-versioned tables — and it removes most of that machinery.
With system versioning, the table keeps its own history. Every UPDATE and DELETE preserves the previous row version automatically, ordinary queries see only current data, and history is reachable through a dedicated query syntax, FOR SYSTEM_TIME. No triggers, no shadow tables, no application changes required to start collecting history. The machinery is real, though, and it has a cost curve you should understand before enabling it on your hottest tables.
Turning it on
CREATE TABLE accounts (
id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
email varchar(190) NOT NULL,
balance decimal(19,4) NOT NULL DEFAULT 0,
status varchar(20) NOT NULL DEFAULT 'active'
) WITH SYSTEM VERSIONING;
-- or retrofit an existing table:
ALTER TABLE accounts ADD SYSTEM VERSIONING;MariaDB adds two invisible pseudo-columns, row_start and row_end, each a TIMESTAMP(6) in UTC. An INSERT creates the row's first version. An UPDATE atomically closes the current version (stamping its row_end) and inserts a new version with a fresh row_start. A DELETE closes the current version instead. Because the columns are invisible, SELECT * behaves exactly as before — existing code keeps working — and you name row_start and row_end explicitly when you want them. One refinement worth knowing: on InnoDB you can opt into transaction-precise history, which versions rows by transaction identifier rather than timestamp, so multiple rows changed by one transaction read consistently as of any point in time.
Two operational notes on the retrofit path. Adding versioning to a table that already has data is a light operation in recent versions, and the existing rows simply become current versions — there is no fabricated history to trip over later. Removing versioning with DROP SYSTEM VERSIONING is one statement, but do not mistake it for a metadata-only toggle: for InnoDB it does not support the INSTANT or NOCOPY algorithms, so expect an INPLACE operation, and it is irreversible — the history is discarded, so export anything you owe an auditor first.
Querying history
-- what did account 42 look like at a moment in time?
SELECT id, email, balance, status, row_start, row_end
FROM accounts
FOR SYSTEM_TIME AS OF '2026-03-01 12:00:00'
WHERE id = 42;
-- every version of that row across a window:
SELECT id, balance, row_start, row_end
FROM accounts
FOR SYSTEM_TIME BETWEEN '2026-03-01 00:00:00' AND '2026-03-02 00:00:00'
WHERE id = 42;
-- everything, current and history:
SELECT COUNT(*) AS all_versions FROM accounts FOR SYSTEM_TIME ALL;The semantics are worth one careful read. AS OF t returns the version whose lifetime contains t — row_start at or before t, row_end after t. BETWEEN a AND b returns versions whose lifetimes touch the closed interval; FROM a TO b uses a half-open interval, which is usually what you want for adjacent reporting windows. ALL returns current rows plus every historical version. And the default, with no clause, returns only current rows — which is precisely why you can retrofit an existing application without touching its queries.
A practical note on reading history efficiently: FOR SYSTEM_TIME queries are ordinary selects and they use your existing indexes. The primary key of a versioned table even carries row_end implicitly — it has to, since the same primary value must coexist across versions — but there is no automatic time-leading index for queries that search by time first, across every row. If your audit queries always filter by a business key plus a time range — as the examples above do — the primary or secondary key does the heavy lifting and the time clause filters what is found. Table-wide scans over ALL versions are the expensive shape; give them a partition layout that can prune, or run them against a replica.
The bill: storage and write amplification
History is not free, and the invoice arrives as storage and write amplification. Every UPDATE now leaves the old version behind in addition to writing the new one, and every secondary index carries entries for historical versions too. A hot row updated hourly accumulates thousands of versions per year. Table growth cascades into backup size, buffer pool pressure, and replication volume. Before enabling versioning on a table, measure its update rate and project the growth; the top five hottest tables deserve individual arithmetic, not a blanket policy.
Pruning is the other half of the discipline. DELETE HISTORY FROM accounts removes historical versions, and you can bound it with BEFORE SYSTEM_TIME to keep a retention window — say, everything newer than two years. Treat pruning like backups: scheduled, monitored, and run off-peak, because it is itself a delete workload with the I/O that implies.
Partitioning history away from the hot set
The structural fix is to separate history from current data physically. MariaDB lets you partition by system time so current rows live in one partition and closed versions in others:
ALTER TABLE accounts
PARTITION BY SYSTEM_TIME (
PARTITION p_hist HISTORY,
PARTITION p_cur CURRENT
);Even this simple two-partition layout keeps the hot partition small and cache-friendly. Beyond it, MariaDB can rotate history partitions automatically on an interval or a size limit, which unlocks the two operations you actually want in production: time-based pruning, where a FOR SYSTEM_TIME AS OF query can skip partitions that cannot contain the requested moment, and cheap retention — dropping an old history partition is metadata-fast, where DELETE HISTORY has to find and remove rows one by one.
Honest limitations
- Online schema change tools do not understand history. Trigger-based copiers like gh-ost or pt-online-schema-change will either lose history or fight the versioning machinery. Prefer native ALGORITHM=INSTANT or INPLACE where possible, and rebuild deliberately when you cannot.
- Foreign keys have sharp edges. Referential actions interact with versioning in restricted ways; test your foreign key graph on a copy before enabling it in production.
- Verify your logical dumps. By default mariadb-dump captures only the current rows; the --dump-history option that includes historical versions arrived in 10.11, so on 10.6 and earlier a logical backup silently drops your audit trail. Know exactly what your dump tool captures from a versioned table before you need a restore to stand up as audit evidence.
- System time is not business time. Versioning records when the database learned a fact, not when the fact was true in the world. If you need "price effective from March 1st" semantics, that is application-time periods — a separate MariaDB feature — or full bitemporal modeling. Do not conflate the two.
- The clock is the server clock. row_start and row_end are UTC microseconds from the database host; do not build cross-system correlations on them.
The use cases that justify the cost
Three come up constantly. Compliance lookbacks: "what did we believe about this customer on March 1st?" becomes one query instead of a restore. Debugging: watching how a corrupted row evolved usually isolates the bad write within minutes. And undo: a fat-fingered UPDATE no longer means point-in-time recovery of the whole instance, because the previous version is sitting right there:
-- restore one row to what it was just before a bad update:
UPDATE accounts a
JOIN (
SELECT id, email, balance, status
FROM accounts
FOR SYSTEM_TIME AS OF '2026-03-01 11:59:00'
WHERE id = 42
) old ON old.id = a.id
SET a.email = old.email,
a.balance = old.balance,
a.status = old.status
WHERE a.id = 42;That one statement replaces what used to be a restore-to-staging-and-copy exercise. It is the honest pitch for system versioning: the ninety-five percent audit use case becomes boring, and boring is what you want from audit infrastructure. If you are weighing MariaDB against engines that make you build this yourself, the comparison pages cover the broader feature tradeoffs.
Watching versioned tables with MonPG (coming soon)
System-versioned tables fail quietly — a prune job that stops running, history growth that eats the backup window — so they deserve trended metrics rather than annual surprises. That class of signal (history-attributable table growth, index bloat from version churn, prune-job success) is on the design list for MonPG's MariaDB support, and to be clear about where things stand: MonPG monitors PostgreSQL today, and the MariaDB side is in active development, not shipped. The MariaDB monitoring (coming soon) page is the honest source for status. In the meantime, a scheduled query against data and index lengths from the information schema, trended anywhere durable, covers the essentials.