System-Versioned Tables, Temporal Queries, Retention, and Audit/History Use Cases

Learning outcomes

ServiceHub must answer a deceptively simple support question: “What did this customer record look like when ticket 9481 was closed?” Keeping only the current row cannot answer it, while hand-written audit triggers are easy to omit from one code path. MariaDB system-versioned tables can preserve prior row versions automatically—but they are history, not an immutable security ledger.

1. Mental model: the server maintains transaction-time history

A normal row answers “what is true now?” A system-versioned row also carries a validity interval determined by the database: the system records when that version became current and when it stopped being current. MariaDB can create explicit generated ROW START/ROW END columns or use simplified WITH SYSTEM VERSIONING syntax with pseudo-columns.

The important boundary is control: system-time history reflects database change timing. If the business says a price was valid last Tuesday even though it was entered today, that is application time, taught in Lesson 2.

2. Build a disposable system-versioned table

DROP DATABASE IF EXISTS advanced21_l1;CREATE DATABASE advanced21_l1;USE advanced21_l1;CREATE TABLE customer_profile (  customer_id BIGINT PRIMARY KEY,  tier VARCHAR(20) NOT NULL,  credit_limit DECIMAL(12,2) NOT NULL,  updated_by VARCHAR(80) NOT NULL) ENGINE=InnoDB WITH SYSTEM VERSIONING;INSERT INTO customer_profile VALUES (101,'standard',500.00,'seed');SELECT customer_id,tier,credit_limit,updated_by,ROW_START,ROW_ENDFROM customer_profile FOR SYSTEM_TIME ALLORDER BY ROW_START;

Record the first ROW_START. Then update the row twice with short pauses between commands if you want visibly different wall-clock timestamps. The system assigns the history boundaries; your application does not invent them.

UPDATE customer_profileSET tier='gold', credit_limit=1500.00, updated_by='support-42'WHERE customer_id=101;UPDATE customer_profileSET credit_limit=1750.00, updated_by='risk-review'WHERE customer_id=101;SELECT customer_id,tier,credit_limit,updated_by,ROW_START,ROW_ENDFROM customer_profile FOR SYSTEM_TIME ALLWHERE customer_id=101ORDER BY ROW_START;

3. Query a point, interval, and complete history

-- Replace the timestamp with one captured from your own history.SELECT *FROM customer_profile FOR SYSTEM_TIME AS OF TIMESTAMP '2026-08-20 18:00:00'WHERE customer_id=101;SELECT customer_id,tier,credit_limit,ROW_START,ROW_ENDFROM customer_profile FOR SYSTEM_TIME BETWEEN  TIMESTAMP '2026-08-20 17:00:00' AND TIMESTAMP '2026-08-20 19:00:00'WHERE customer_id=101ORDER BY ROW_START;SELECT customer_id,tier,credit_limit,ROW_START,ROW_ENDFROM customer_profile FOR SYSTEM_TIME ALLWHERE customer_id=101ORDER BY ROW_START;

An empty AS OF result does not prove history is broken—it may simply mean the row did not exist at that instant. Record timestamps from the same server/session and remember time-zone handling when correlating with application logs.

4. Wrong approach: call system-versioning an immutable audit log

Versioning is stored in the database and managed by privileged database operations. Administrators can remove history, alter versioning, restore from older backups, or change surrounding security controls. Therefore it can support forensic and regulatory history workflows, but it is not tamper-proof merely because it is automatic.

5. History growth, partitioning, and retention

History can eventually dwarf current data. MariaDB can partition system-versioned tables by SYSTEM_TIME, separating CURRENT from HISTORY partitions and enabling partition pruning for current-only access. Later versions also support interval-driven history partition creation. Retention must be a business/compliance decision, not a fixed “keep 30 days” recipe.

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTHFROM information_schema.TABLESWHERE TABLE_SCHEMA='advanced21_l1';-- Verify your exact version before using production retention commands.DELETE HISTORY FROM customer_profileBEFORE SYSTEM_TIME '2025-01-01 00:00:00';

A history delete changes the evidence available to future temporal queries. Back up/verify first when policy requires preservation. For large temporal tables, benchmark current-only queries and historical queries separately because they have different access patterns.

6. Backup, restore, replication, and recovery consequences

Historical rows are part of recoverability. MariaDB 10.11+ can include them in logical dumps using --dump-history; without the version-appropriate option, a logical backup may preserve current rows while omitting history. Physical backups capture the underlying files, but restore validation still must include temporal queries. Replication/binlog behavior also needs exact-version testing because system-versioning adds row-history semantics to replay.

mariadb-dump --versionmariadb-dump --single-transaction --routines --events --triggers   --dump-history advanced21_l1 > advanced21_l1_with_history.sql# Restore only into a disposable target first, then verify FOR SYSTEM_TIME ALL.

7. Lab verification and cleanup

SELECT COUNT(*) AS all_versionsFROM advanced21_l1.customer_profile FOR SYSTEM_TIME ALLWHERE customer_id=101;SELECT COUNT(*) AS current_versionsFROM advanced21_l1.customer_profileWHERE customer_id=101;SHOW CREATE TABLE advanced21_l1.customer_profile\GDROP DATABASE advanced21_l1;

Production judgment and bridge to Lesson 2

Use system-versioning when database-managed transaction-time history directly serves reconstruction, analytics, support or compliance requirements and the storage/retention cost is accepted. Do not adopt it merely because “audit is good.” Lesson 2 adds a second timeline—business-valid time—and shows why bitemporal models need interval constraints rather than more timestamps scattered through application code.

History lifecycle: system versioning changes storage, retention, and recovery questions

System-versioned rows are ordinary operational data with extra temporal semantics, so history consumes storage and participates in backup, replication, indexing, and query planning. Estimate change rate as well as current-row count: a frequently updated small table can generate more history than a large mostly-static table. Monitor the history growth path and decide how long historical versions must remain queryable in the primary operational system.

Index design should reflect temporal access patterns. Current-row lookups and “what did we know at time T?” queries may need different access paths, and history-heavy scans can compete with OLTP work. Partitioning history can support retention and maintenance where the target version permits the chosen design, but partition boundaries must follow the temporal semantics and operational procedure. Test FOR SYSTEM_TIME query plans against realistic history volume rather than a handful of versions.

Backup policy must state whether history is required in each backup type and how restore verification proves it. A restore that returns only the current row may satisfy an operational recovery test but fail a compliance/history requirement. Replication likewise copies data changes according to the topology; it is not an independent audit vault. If an operator with sufficient privileges can alter/drop the table or its history, system versioning alone cannot provide tamper-evident or immutable audit guarantees.

Use temporal history when the application needs database-maintained “what was stored when” behavior and accepts the storage/retention cost. For security audit, legal evidence, or immutable event retention, combine or replace it with an appropriately protected audit/event system whose threat model, access controls, retention, and integrity guarantees are explicit.

Temporal-query correctness tests: assert intervals, not just returned values

Tests for versioned tables should assert temporal boundaries. Capture a row before and after controlled updates, then query FOR SYSTEM_TIME at points inside each interval and at the boundary between versions. Verify whether the intended interval semantics include or exclude the boundary as documented for the syntax in use. This catches assumptions that a simple “ALL versions” query cannot reveal.

For retention, test the oldest history that must remain available after maintenance and restore. Include a recovery drill where the current row and at least one historical version are both validated. If a downstream replica or reporting system is part of the history strategy, verify its temporal queries independently; replication health alone does not prove the required historical interval is present and queryable.

Authoritative references

• MariaDB Documentation — System-Versioned Tables
• MariaDB Documentation — Temporal Tables
• MariaDB Documentation — mariadb-dump