Stop Hand-Rolling Audit Tables: MariaDB's System-Versioned Tables Do It for You

Every team eventually gets the same request: "What was the price of this product on March 1st?" If your answer involves a trigger writing to a products_audit table, a timestamp column you hope is accurate, and a prayer that nobody ever bypassed the trigger — there's a better option already built into MariaDB. System-versioned tables make the server itself retain every historical row version, and let you query the past with ordinary SQL.

The problem with DIY history

The classic pattern is a trigger that copies the old row into an audit table on every UPDATE or DELETE. It works, but it has sharp edges: someone alters the main table and forgets the audit table; a migration runs with triggers disabled; the audit insert fails silently in a code path nobody tests. The history you most need is the history you discover is incomplete.

System versioning moves this into the storage engine. When you declare a table WITH SYSTEM VERSIONING, MariaDB transparently keeps old row versions on every UPDATE and DELETE. Your application code doesn't change at all — normal queries still see only current rows.

How it works under the hood

MariaDB adds two hidden timestamp columns, conventionally row_start and row_end. When a row is updated, the old version's row_end is stamped with the transaction commit time and a new version is written with that same time as its row_start. Deletes just close the row's period instead of removing it. The timestamps reflect when the server committed the change — a point that matters later.

A worked example: point-in-time pricing

Say you have a pricing table and a report that must show the price effective on a given past date. Setup, run as a user with CREATE/ALTER privileges in a test schema:

CREATE TABLE product_price (
  product_id INT PRIMARY KEY,
  price DECIMAL(10,2) NOT NULL
) WITH SYSTEM VERSIONING;

INSERT INTO product_price VALUES (42, 19.99);
-- later...
UPDATE product_price SET price = 24.99 WHERE product_id = 42;

Now the point-in-time query is a single SELECT — no join to a history table:

-- What was the price at a specific moment?
SELECT price FROM product_price
FOR SYSTEM_TIME AS OF '2026-03-01 10:00:00'
WHERE product_id = 42;

-- See every version, including deleted rows:
SELECT *, row_start, row_end FROM product_price
FOR SYSTEM_TIME ALL WHERE product_id = 42;

Other variants include FOR SYSTEM_TIME BETWEEN a AND b and FROM a TO b for ranges. To verify it behaves as expected on your server: insert a row, wait a few seconds, update it, then run an AS OF query with a timestamp between the two operations and confirm you get the original value back. Check SELECT VERSION(); and confirm the syntax against the docs for your release line — details vary across versions.

The trade-off: history is not free

Every UPDATE writes a new row version, so a high-churn table can grow fast. Before enabling this on a hot production table, measure it: run a representative batch of updates in staging and compare table size before and after. If growth is a problem, the standard mitigation is partitioning the history by time and dropping old partitions, or periodically pruning with DELETE ... BEFORE SYSTEM_TIME — but confirm the exact pruning syntax your version supports.

Two more caveats worth internalizing:

• It's transaction time, not business time. Timestamps reflect when the server committed the change, subject to server clock behavior. If you need "this contract was effective from January 1st even though we entered it in February," that's application-time (valid-time) modeling — a different feature and a different design.
• Schema changes get fussier. Altering a versioned table (adding columns, changing types) has restrictions because existing history must remain readable. Test migrations against a versioned copy before running them in production.

When to reach for it

If your audit requirement is "show me what the database contained at time T" — regulatory lookups, price history, debugging "who changed this row" — system versioning replaces a trigger-based audit stack with one clause in the DDL and one clause in your queries. Start with a low-churn table, verify FOR SYSTEM_TIME ALL returns what you expect, measure the storage cost, and then roll it out where it earns its keep.