Implementing MariaDB System-Versioned Tables for Temporal Data Tracking

Desired Outcome

Enable automatic row-level history tracking on a MariaDB table so that every UPDATE and DELETE preserves the previous row state, allowing point-in-time queries without application-level audit logic.

Prerequisites

• MariaDB 10.3.4 or later (system versioning introduced in 10.3.4, enhanced in 10.4+).
• Account with ALTER privilege on the target table and CREATE privilege on the database (required for the hidden history table).
• InnoDB storage engine (system versioning is not supported on MyISAM, Aria, or MEMORY tables).
• Primary key defined on the table (required for versioning).

Procedure

1. Verify Current Table Structure

Run the following on the MariaDB server (mysql client or any SQL client connected as a user with SHOW VIEW privilege):

SHOW CREATE TABLE `orders`;

Confirm the table uses InnoDB and has a primary key. If not, add one:

ALTER TABLE `orders` ADD COLUMN `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

2. Enable System Versioning

Execute the ALTER TABLE statement to add system versioning. This creates a hidden history partition (MariaDB 10.4+) or a separate history table (10.3) and adds two invisible columns: row_start and row_end (TIMESTAMP(6) with microsecond precision).

ALTER TABLE `orders` ADD SYSTEM VERSIONING;

Expected result: Query OK, 0 rows affected. The table definition now includes WITH SYSTEM VERSIONING.

3. Verify Versioning Is Active

SHOW CREATE TABLE `orders`;

Look for WITH SYSTEM VERSIONING in the output and the two generated columns row_start and row_end (they are hidden from SELECT * but visible in SHOW COLUMNS).

4. Test Historical Tracking

Insert a row, then update it, then query the history.

INSERT INTO `orders` (`customer_id`, `amount`, `status`) VALUES (1001, 250.00, 'pending');

Note the id assigned (assume 42). Update the row:

UPDATE `orders` SET `status` = 'shipped', `amount` = 275.00 WHERE `id` = 42;

Query current state:

SELECT `id`, `customer_id`, `amount`, `status` FROM `orders` WHERE `id` = 42;

Query all versions:

SELECT `id`, `customer_id`, `amount`, `status`, `row_start`, `row_end` FROM `orders` FOR SYSTEM_TIME ALL WHERE `id` = 42;

You should see two rows: the original (row_end = timestamp of UPDATE) and the current (row_end = '9999-12-31 23:59:59.999999').

5. Point-in-Time Query

Retrieve the row as it existed at a specific moment (replace with a timestamp between the INSERT and UPDATE):

SELECT `id`, `customer_id`, `amount`, `status` FROM `orders` FOR SYSTEM_TIME AS OF '2026-10-14 12:00:00' WHERE `id` = 42;

This returns the pre-update values.

6. Manage History Retention (Optional)

To prevent unbounded growth, define a retention period (MariaDB 10.4+). Example: keep history for 90 days.

ALTER TABLE `orders` SET SYSTEM VERSIONING HISTORY RETENTION PERIOD 90 DAY;

MariaDB will automatically purge rows older than the retention window. You can also disable versioning entirely:

ALTER TABLE `orders` DROP SYSTEM VERSIONING;

This removes the hidden columns and the history partition/table. Data in the history partition is deleted.

Expected Checks

• SHOW CREATE TABLE includes WITH SYSTEM VERSIONING.
• SELECT ... FOR SYSTEM_TIME ALL returns multiple rows for a single primary key after updates.
• FOR SYSTEM_TIME AS OF returns the correct historical snapshot.
• Storage growth monitored: run SHOW TABLE STATUS LIKE 'orders' and observe Data_length and Index_length increase after updates.

Limitations and Risks

• Storage overhead: Every UPDATE and DELETE writes a full row copy to history. High-churn tables can grow rapidly. Plan capacity and retention.
• Primary key immutability: The primary key cannot be changed on a versioned table. To alter the PK, drop versioning first.
• No foreign keys to history: The history partition/table cannot be referenced by foreign keys.
• Replication: System versioning replicates as normal DML; ensure replicas have sufficient disk.
• Backup/restore: Logical backups (mysqldump) include history data by default. Use --skip-system-versioning to dump only current rows.

Recovery Options

If versioning was enabled unintentionally or causes performance issues:

1. Disable versioning: ALTER TABLE `orders` DROP SYSTEM VERSIONING; (removes history data).
2. If you need to keep history but reduce size, set a retention period: ALTER TABLE `orders` SET SYSTEM VERSIONING HISTORY RETENTION PERIOD 30 DAY;.
3. For a corrupted history partition (rare), drop and re-add versioning after verifying current data integrity.

Practical Verification Checklist