Managing Data History with MariaDB System-Versioned Tables

The problem with manual audit logs

When a production value changes unexpectedly, the first question is usually: "What was this value yesterday?" Solving this typically requires building custom audit tables and writing application-level triggers or middleware to log every change. This approach introduces code complexity, increases the risk of logging gaps, and adds latency to every write operation.

MariaDB's system-versioned tables solve this by moving the history tracking into the storage engine. Instead of managing a separate log, the database automatically preserves previous versions of rows, allowing you to query the state of the data at any specific point in time using standard SQL.

Implementing System Versioning

To use this feature, you must be running MariaDB 10.3 or later and using the InnoDB engine. You define the table with a specific period for system time, which the database uses to track the lifespan of each row version.

Run the following on your MariaDB instance (requires CREATE permissions):

CREATE TABLE product_prices (
    product_id INT PRIMARY KEY,
    price DECIMAL(10,2),
    row_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
    row_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (row_start, row_end)
) ENGINE=InnoDB WITH SYSTEM VERSIONING;

The GENERATED ALWAYS AS ROW START/END columns are managed by the server. They record exactly when a row became current and when it was superseded by an update or deleted.

Querying the Timeline

Standard SELECT statements only return the current version of the data. To access the history, you use the FOR SYSTEM_TIME clause.

Consider this sequence of changes:

INSERT INTO product_prices (product_id, price) VALUES (101, 19.99);
-- Wait a few seconds --
UPDATE product_prices SET price = 24.99 WHERE product_id = 101;
-- Wait a few seconds --
UPDATE product_prices SET price = 21.50 WHERE product_id = 101;

To see every version of product 101 that has ever existed, run:

SELECT * FROM product_prices FOR SYSTEM_TIME ALL WHERE product_id = 101;

To find the price as it existed at a specific timestamp (useful for point-in-time reporting), run:

SELECT price FROM product_prices FOR SYSTEM_TIME AS OF '2026-09-27 10:00:00' WHERE product_id = 101;

Operational Trade-offs

System versioning is not a "free" feature; it trades disk space for data recoverability. Every UPDATE or DELETE operation results in a new row being written to a hidden history storage area. In high-write environments, this can lead to rapid disk consumption.

Key Limitations:

• Storage Growth: History tables grow linearly with the number of changes. You may need to implement partitioning or a purging strategy to remove data older than your compliance window.
• DDL Constraints: Certain schema changes, such as dropping a column or changing a data type, cannot be performed while versioning is active. You must temporarily disable it:

ALTER TABLE product_prices SET (SYSTEM_VERSIONING = OFF);
-- Perform your column change here
ALTER TABLE product_prices SET (SYSTEM_VERSIONING = ON);

Note that any changes made while versioning is OFF will not be captured in the history.

Verifying Feature Availability

Before deploying this to production, verify your environment supports temporal tables. Run these checks in your MariaDB client:

1. Version Check: SELECT VERSION(); (Must be 10.3+).
2. Engine Check: SHOW ENGINES; (InnoDB must be SUPPORTED).
3. Functional Test: Create a temporary versioned table, perform one update, and run SELECT * FROM [table] FOR SYSTEM_TIME ALL;. If you see two rows for a single primary key, the feature is active.

Closing Recommendation

System-versioned tables are ideal for configuration tables, pricing history, and user profile audits where the read-to-write ratio is high. If you are dealing with a table that undergoes thousands of updates per second, monitor your disk I/O and storage growth closely before enabling this feature globally.