Audit rows without triggers using MariaDB system-versioned tables

The problem: you need audit history, but triggers are tedious

Your application must answer questions like "what did this customer record look like at 2 p.m. yesterday?" The usual fix is an audit trigger that copies old rows into a history table on every update. Triggers work, but they add code to maintain, easy-to-miss edge cases, and schema drift between the main table and its audit shadow.

What MariaDB system versioning does

A system-versioned table is a base table paired with an automatically managed history table. MariaDB adds row_start and row_end columns that mark when each row version was current. Every insert, update, or delete writes the previous version to the history table. You can then read old states with FOR SYSTEM_TIME AS OF or FOR SYSTEM_TIME BETWEEN instead of joining audit tables manually.

Worked example: enable versioning and query the past

Run this in a MariaDB SQL client such as the mysql command line. You need CREATE, ALTER, INSERT, UPDATE, and SELECT privileges on the target database.

CREATE TABLE customer_address (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  street VARCHAR(255) NOT NULL,
  city VARCHAR(100) NOT NULL,
  row_start TIMESTAMP(6) NOT NULL GENERATED ALWAYS AS ROW START,
  row_end TIMESTAMP(6) NOT NULL GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (row_start, row_end),
  PRIMARY KEY (id)
) WITH SYSTEM VERSIONING
ENGINE=InnoDB;

The WITH SYSTEM VERSIONING clause tells MariaDB to create a history table, typically named something like customer_address_history. Insert a row, then update it:

INSERT INTO customer_address (customer_id, street, city)
VALUES (42, '123 Main St', 'Springfield');

UPDATE customer_address
SET city = 'Shelbyville'
WHERE id = 1;

To see the row as it existed before the update, pick a timestamp between the insert and the update:

SELECT * FROM customer_address
FOR SYSTEM_TIME AS OF '2026-01-10 09:00:00'
WHERE id = 1;

Expected check: the query returns Springfield, while the current table returns Shelbyville. Run SHOW CREATE TABLE customer_address; to confirm the WITH SYSTEM VERSIONING clause and identify the generated history table name.

Schema changes and history growth

System versioning is not schema-neutral. To add or drop a column, you usually must disable versioning, alter the base table, and re-enable it. On a large table that sequence can lock or rewrite data, so plan it during a maintenance window. History retention is also not automatic by default. Every update appends a row to the history table, so disk use grows with write volume unless you purge old history or partition the history table.

Monitor growth with:

SELECT table_name, data_length, index_length
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name LIKE '%_history';

Trade-offs and limitations

• Write amplification: every update or delete writes at least one history row, increasing storage and I/O compared with a plain table.
• Engine support: system versioning is tied to InnoDB in most MariaDB builds; confirm the storage engine before enabling it on production tables.
• Version differences: syntax and history behavior can vary between MariaDB 10.x and 11.x, so test on the exact target version.
• No built-in expiration: you must implement a purge or partitioning strategy, or history will grow without bound.

Closing: enable it when audit questions outnumber schema changes

System versioning is a good fit for tables that are read often, updated occasionally, and need reliable point-in-time answers. It is a poor fit for high-write logs or tables that change shape weekly. Before enabling it on production, run the SHOW CREATE TABLE and information_schema.tables checks above, measure history growth on a staging copy, and decide how you will purge old versions. To reverse the change, use the ALTER TABLE syntax supported by your MariaDB version to drop system versioning; treat it as a state-changing operation and test it first.