MariaDB System-Versioned Tables and Application-Time Periods

Native row-history, point-in-time queries, and bitemporal modelling. A MariaDB-only feature set since 10.3+. MySQL has no equivalent ; PostgreSQL has periods but no system-versioning. Read this skill before hand-rolling audit tables, before designing GDPR-compliant history retention, or before assuming MySQL behaviour applies.

Quick Reference

• WITH SYSTEM VERSIONING is MariaDB-only. MySQL has neither system-versioning nor application-time periods. PostgreSQL has periods but no system-time. NEVER port these patterns to either without a rewrite.
• Default row_start / row_end precision is transaction-id (BIGINT UNSIGNED) on InnoDB. ALWAYS declare them as TIMESTAMP(6) when you need wall-clock time-travel.
• Transaction-precision tables CANNOT use PARTITION BY SYSTEM_TIME. Choose timestamp precision when you plan to partition.
• Without PARTITION BY SYSTEM_TIME the history grows in the same physical structure as live rows. ALWAYS plan retention up front.
• DELETE HISTORY FROM t BEFORE SYSTEM_TIME 'ts' is the GDPR-friendly purge. Requires the DELETE HISTORY privilege.
• TRUNCATE TABLE is BLOCKED on system-versioned tables (error 4137). Use DELETE HISTORY FROM t to wipe history, DELETE FROM t to remove current rows.
• Plain SELECT * FROM t returns only current rows. NEVER add FOR SYSTEM_TIME AS OF NOW() to "be safe" ; it is redundant and slower.
• Application-time periods (10.4+) describe business-time (valid_from/valid_to). System-time describes when the row was written. NEVER confuse the two.
• Bitemporal = WITH SYSTEM VERSIONING + a user PERIOD FOR application_time(...) clause. Since 10.5+. FOR PORTION OF works on application-time only, NEVER on system_time.
• INTERVAL ... AUTO (10.9.1+) auto-rotates history partitions. Prefer it on 10.11+ over manual ALTER TABLE ... REORGANIZE PARTITION.

Decision Tree : When to Use Each Feature