Unlock Data History with MariaDB Temporal Tables

Have you ever wished your database could show not just the current state of your data, but also how it looked yesterday, last month, or even a year ago?
MariaDB’s temporal tables make that possible — letting you time-travel through your data effortlessly.

In this post, we’ll explore how MariaDB’s system-versioned, application-time, and bitemporal tables work — along with partitioning, history management, and key limitations

What Are Temporal Tables?

Temporal tables are designed to store and query historical versions of your data automatically.
They keep track of what changed, when it changed, and optionally, when it was valid in real-world terms.

MariaDB implements temporal features based on the SQL:2011 standard, offering robust tools for tracking both system and business time periods — something many other databases adopted later.

In a traditional table, when you update or delete data, the old version is gone forever.
In a temporal table, MariaDB automatically keeps those old versions for you — preserving a full timeline of changes.

There are two main “dimensions of time” you can track:

1. System Time – When the data was valid in the database (managed automatically by MariaDB).
2. Application Time – When the data was valid in real-world business terms (defined by your columns).

When combined, they form Bitemporal Tables — letting you query both timelines simultaneously.

Note: As of MariaDB 11.7 (2025), temporal features are fully stable and production-ready, with added flexibility to convert implicit versioning columns into explicit ones.

Why Use Temporal Tables?

• Recover deleted or modified data easily
• Audit who changed what and when
• Analyze data trends and historical patterns
• Simplify regulatory compliance and change tracking

Performance Note: Temporal tables typically add around 10–20% overhead on write operations since each change generates a new history row.

System-Versioned Tables (Also Called Transaction-Time Tables)

Think of this as MariaDB’s built-in “change history.”
Every time you insert, update, or delete data, MariaDB quietly keeps the old version of the row — along with the time it was valid inside the database.

What is “System Time” or “Transaction Time”?

System time represents when the database recorded a change.
It’s not about when the event happened in the real world — it’s about when the database saw it.

For example:

• You update a record on Oct 10, 2025, at 10:00 AM.
• MariaDB closes the old version (valid until 10:00 AM) and starts a new one from that timestamp.

So you can go back later and ask, “What did the data look like yesterday?”

System-versioned tables track when each row was modified in the database system’s timeline.
MariaDB manages timestamps automatically using hidden columns: ROW_START and ROW_END.

CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  name VARCHAR(50),
  salary DECIMAL(10,2)
) WITH SYSTEM VERSIONING;

This creates hidden columns behind the scenes:

• ROW_START TIMESTAMP(6) – start time
• ROW_END TIMESTAMP(6) – end time
• The period is defined as PERIOD FOR SYSTEM_TIME (ROW_START, ROW_END)

When you update or delete a record, MariaDB moves the old row into a history segment automatically.

Querying Historical Data

SELECT * FROM employee FOR SYSTEM_TIME AS OF '2025-10-01 12:00:00';

View All Versions of a Row

SELECT *, ROW_START, ROW_END
FROM employee FOR SYSTEM_TIME ALL;

Application-Time Periods (Also Called Valid-Time or Business-Time Tables)

Now let’s step into the real world.

Sometimes, it’s not enough to know when the database changed something.
We need to track when the data was valid in the business sense.

For example:

• A product price might be valid only between two dates.
• An employee’s promotion might take effect next month.
• A contract might start and end within a defined period.

These are Application-Time Periods (also called Valid-Time or Business-Time).

Example: Creating a Valid-Time Table

CREATE TABLE room_booking (
  booking_id INT PRIMARY KEY,
  room_no INT,
  booked_by VARCHAR(50),
  valid_from DATE NOT NULL,
  valid_to DATE NOT NULL,
  PERIOD FOR valid_period (valid_from, valid_to),
  WITHOUT OVERLAPS
);

Here:

• WITHOUT OVERLAPS prevents overlapping booking periods for the same room.
• Columns must share the same type (DATE, DATETIME, or TIMESTAMP).

Bitemporal Tables — Tracking Both System and Application Time

Now comes the most powerful part: Bitemporal Tables.

This combines both:

• System Time (when the database recorded changes), and
• Application Time (when the data was valid in real-world logic).

Why do we need both?

Because in many cases, data can be entered late or corrected later.
For example:

• You record a contract today that was valid last month.
• You fix a price that was wrongly entered two weeks ago.

Bitemporal tables let you capture both realities:

• When did the event occur in business logic?
• When did the database know about it?

Example: Bitemporal Table

CREATE TABLE project_status (
  project_id INT PRIMARY KEY,
  status VARCHAR(50),
  valid_from DATE NOT NULL,
  valid_to DATE NOT NULL,
  PERIOD FOR valid_period (valid_from, valid_to),
  WITH SYSTEM VERSIONING
);

You can query both dimensions:

SELECT * FROM project_status
FOR SYSTEM_TIME AS OF '2025-10-10 00:00:00'
FOR valid_period AS OF DATE '2025-09-15';

Managing Temporal Data with Partitioning

Partitioning helps optimize performance for history-heavy temporal tables.

When did It start?

• SYSTEM_TIME partitioning was introduced in MariaDB 10.3.4
• Default partitions (no explicit names) from 10.5
• Automatic partition management (AUTO) since 10.9.1
• 11.7 adds improved metadata and conversion support

CREATE TABLE employee_audit (
  emp_id INT,
  name VARCHAR(50),
  salary 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)
) WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME INTERVAL 1 YEAR PARTITIONS 6;

This creates 5 history partitions and 1 current partition automatically.

AUTO Example:

PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH AUTO;

MariaDB will create and rotate partitions automatically.

Benefits:

• Old data can be dropped via partition pruning
• Queries using FOR SYSTEM_TIME scan only relevant partitions
• Simplifies historical cleanup

Purging and Managing History

Over time, historical data can grow large. You can safely remove old versions using:

DELETE HISTORY FROM employee BEFORE SYSTEM_TIME '2024-01-01 00:00:00';

Requires the DELETE HISTORY privilege.

Or to remove by transaction ID (for transaction-precise versioning):

DELETE HISTORY FROM employee BEFORE SYSTEM_TIME TRANSACTION 1001;

To temporarily disable history tracking:

ALTER TABLE employee DROP SYSTEM VERSIONING;

Limitations and Best Practices

While temporal tables are powerful, they also come with certain considerations.

Limitations

• Foreign keys: Work normally, but cascading deletes/updates do not propagate to historical data.
• ALTER TABLE: May drop versioning info unless you set SET @@system_versioning_alter_history = KEEP;.
• No TRUNCATE: Use DELETE for data removal.
• Storage overhead: Each update stores another version — plan your storage accordingly.
• Triggers: Triggers can fire for versioned operations, so test carefully.

Best Practices

• Always use FOR SYSTEM_TIME or FOR valid_period filters in queries to avoid scanning all history.
• Index your period columns for better performance.
• Archive or delete old data periodically.
• Use transaction-precise versioning if your system requires replication accuracy.

Real-World Scenarios

Here’s where temporal tables shine:

• Finance and HR: Keeping audit logs of salary or transaction changes
• E-commerce: Managing time-based discounts and price updates
• Legal/Compliance: Retaining contract histories for regulatory purposes
• IoT systems: Tracking both event time and recorded time for data streams
• Data warehousing: Building Slowly Changing Dimensions (SCDs) with native SQL

Conclusion

Temporal tables bring the concept of “data with memory” into MariaDB.
They allow you to:

• See how your data looked in the past
• Track both real-world and recorded timelines
• Keep compliance-ready audit trails — automatically

With System-Versioned, Application-Time, and Bitemporal tables, plus partitioning and automatic history management in recent versions, MariaDB makes time-travel queries not just possible — but practical.

So next time you wish you could “rewind” your database, remember:
MariaDB already knows how.