MariaDB System-Versioned Tables: Automatic Row History Without Triggers

If you need to know what a row looked like last Tuesday—for an audit, a debugging session, or undoing a bad UPDATE—the traditional answer is hand-written triggers copying rows into shadow tables. MariaDB 10.3 and later provide a built-in alternative: system-versioned tables. By adding one clause to the table definition, every UPDATE and DELETE automatically preserves the old row version, queryable with standard SQL.

The useful answer first

Declare the table with WITH SYSTEM VERSIONING, and MariaDB maintains two hidden timestamp columns (row_start and row_end) that bracket the period during which each row version was valid. Current rows have row_end set to the maximum timestamp; historical rows carry the real interval. You query history with FOR SYSTEM_TIME clauses instead of joining against a manual audit table.

A worked configuration

Run these statements in any MariaDB client (mysql/mariadb CLI, DBeaver, etc.) against a database where you have CREATE privileges. Version assumption: MariaDB 10.3 or newer; verify with SELECT VERSION(); first.

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

INSERT INTO employees VALUES (1, 'Ada', 90000.00);

-- later...
UPDATE employees SET salary = 95000.00 WHERE id = 1;
DELETE FROM employees WHERE id = 1;

Now inspect the full history, including the deleted row:

SELECT id, name, salary, row_start, row_end
FROM employees
FOR SYSTEM_TIME ALL
WHERE id = 1;

You should see three versions: the original salary, the updated salary, and the deleted final state, each with its validity interval. A plain SELECT * FROM employees returns only current rows—history is invisible unless you ask for it, so existing application queries keep working unchanged.

To answer "what did the table look like at a moment in time?":

SELECT * FROM employees
FOR SYSTEM_TIME AS OF TIMESTAMP '2026-10-12 09:30:00';

FOR SYSTEM_TIME FROM t1 TO t2 and BETWEEN t1 AND t2 are also supported for range analysis.

How the mechanism works

On UPDATE, MariaDB writes the pre-update image as a historical row (closing its row_end) and stores the new values as the current version. On DELETE, the current row is closed and removed from the visible set. Nothing is ever overwritten in place, which is what makes point-in-time queries possible. The timestamps come from the transaction, so versions are consistent with commit order rather than client wall-clock time.

System versioning is per-table and can be toggled later with ALTER TABLE employees DROP SYSTEM VERSIONING;. Note that this is a state-changing operation; test on a copy first as it may detach or discard history depending on the specific table configuration.

Controlling history growth with partitioning

Every UPDATE creates a new row version, so a hot table accumulates history quickly. MariaDB lets you separate history into partitions and prune old ones:

CREATE TABLE events_log (
  id INT PRIMARY KEY,
  payload TEXT
) WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME (
  PARTITION p_hist HISTORY,
  PARTITION p_cur CURRENT
);

You can also use PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH with a fixed number of partitions, or LIMIT-based rotation, so old history is dropped cheaply as whole partitions rather than row-by-row deletes. For tables with heavy churn, plan this up front—retrofitting partitioning onto a huge versioned table is resource-intensive.

Limits and common mistakes

• Storage cost is real. A row updated 50 times exists 51 times. Estimate churn rate times row size before enabling versioning on write-heavy tables, and monitor growth with information_schema.TABLES (DATA_LENGTH) over a week.
• Primary keys only constrain current rows. The same id appears many times across history. Any reporting query using FOR SYSTEM_TIME ALL must account for this—a naive COUNT(DISTINCT id) versus COUNT(*) discrepancy is expected.
• It is not a backup. DROP TABLE removes history too, and history can be pruned. Keep real backups for disaster recovery; use versioning for audit and application-level recovery.
• TRUNCATE is not versioned. It destroys history. Applications must use DELETE if history matters.
• Schema changes interact with history. Adding a column applies to historical rows (they get the default/NULL). Dropping a column loses that data from history permanently.
• Replication and dumps: Historical rows are included in logical dumps only if you explicitly select them; verify your backup tooling's behavior.

Verifying it works

After enabling versioning on a test table, run the INSERT/UPDATE/DELETE sequence above and confirm FOR SYSTEM_TIME ALL returns the expected number of versions with non-overlapping row_start/row_end intervals. Then check SHOW CREATE TABLE employees\G—you should see the system-versioning clause and the generated row period columns. If FOR SYSTEM_TIME returns a syntax error, your server version is older than 10.3.

System versioning replaces the most common reason people write audit triggers. It does not replace triggers that capture who made a change—the row period tracks time, not the user—so pair it with an application-level actor column if your audit requirements include attribution.