Rewinding Time in MariaDB Databases: System-Versioning and Application-Time
Have you ever needed to rewind time in your database to see data as it looked a year ago or maybe just a minute ago? And I’m not talking about restoring an old backup or writing complex SQL queries. I’m talking about a built-in easy-to-use syntax that you can simply append to your existing queries. If not, lucky you! But if you have, I recently discovered a feature in MariaDB Enterprise Server 11.4 that allows you to easily rewind time in your database. In fact, this feature already existed in previous versions, but MariaDB Enterprise Server 11.4 improves it.
Let’s dig into this and explore how system-versioned tables, application-time periods, and bitemporal tables transform the way your applications handle historical and time-sensitive data.
Why Temporal Features Matter
MariaDB allows you to manage data that varies over time. This involves tracking historical data changes, managing validity periods (defined by business rules), and enabling queries that can retrieve data as it existed at any given point in time. Different features bring different value to your applications. For example:
- System Versioning: Let MariaDB maintain the history of data changes. Imagine a payroll system. Let’s say a user updated an employee’s salary on January 25, 2025. What if you need to audit the database to see what the salary was on December 31, 2024. With system-versioned tables, you can query the state of the data as it existed at any point in time without custom logging or implementing triggers.
- Application Time: Define periods in which your data is applicable or relevant in a business context. This is useful for use cases that handle contracts, product availability, or schedules. Think of a hotel booking system. A booking is only valid (applicable) for the period its guest stays. The booking application can define application-time periods using date columns and easily query bookings valid for a specific week or adjust the price for stays that fall within a given date range.
- Bitemporal tables: Mix both system versioning and application-time periods to enable queries that show data as it was valid in the real world and as it was recorded in the database. Imagine a compliance system for financial contracts. A contract might be valid for a customer from January 1, 2025, to December 31, 2025, but the database history might show that the terms were edited on January 25, 2025. With bitemporal tables, you can query both:
- “As it actually was”: What the valid contract terms were on January 1, 2025.
- “As it was recorded”: The state of the database after the January 25 edit.
Creating System-Versioned Tables
System-versioned tables in MariaDB automatically capture every change to a row, along with timestamps for when those changes occurred. To enable this feature, you only need to add WITH SYSTEM VERSIONING to your table definition (or use an equivalent ALTER TABLE to enable it on an existing table). For example:
CREATE TABLE employees ( emp_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, position VARCHAR(50), salary DECIMAL(10, 2), dept_id INT ) WITH SYSTEM VERSIONING;
That’s it. Every update or delete automatically creates a historical version of the modified or deleted row. MariaDB even adds and manages two pseudo columns—ROW_START and ROW_END—behind the scenes. You could certainly use these columns directly, but there’s a better way to query historical data.
Suppose you need to see the employees table as it looked on January 1, 2025. The following simple query does the trick:
SELECT * FROM employees FOR SYSTEM_TIME AS OF '2025-01-01';
You can also explore the data in a period of time:
SELECT * FROM employees FOR SYSTEM_TIME FROM '2025-01-01' TO '2025-01-31';
New in MariaDB Enterprise Server 11.4: Automatic Partitioning
In version 11.4, MariaDB Enterprise Server introduced automatic partitioning for system-versioned tables. With this, historical data is organized into partitions based on time. This is useful for improving query performance and simplifying management. Here’s an example of how to enable it:
CREATE TABLE employees ( emp_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, position VARCHAR(50), salary DECIMAL(10, 2), dept_id INT ) WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME ( PARTITION p_history HISTORY, PARTITION p_current CURRENT );
MariaDB Server adds new partitions as needed. No manual upkeep required. When you insert a new row into the employees table, it is added to the p_current partition. For example:
INSERT INTO employees (emp_id, name, position, salary, dept_id) VALUES (1, 'Alice', 'Manager', 80000, 101);
This creates a new row in the p_current partition with a ROW_START timestamp (e.g., 2025-01-21 12:00:00) and a ROW_END value of 2106-02-07 08:28:15.999999 to indicate it is the active row.
When this row is updated, the existing version is moved to the p_history partition with its ROW_END timestamp set to the current time. A new version of the row is then inserted into the p_current partition. For example:
UPDATE employees SET salary = 85000 WHERE emp_id = 1;
The old row with salary = 80000 is moved to p_history, with ROW_END set to the update timestamp. A new row with salary = 85000 is added to p_current.
You can query the historical state of the table using the FOR SYSTEM_TIME clause. For example, to see the table as it was on January 1, 2025, just run the following query:
SELECT * FROM employees FOR SYSTEM_TIME AS OF '2025-01-01 00:00:00';
This query automatically retrieves data from both the p_current and p_history partitions as it existed at the specified time.
Creating Application-Time Periods
Application-time periods allow you to define when data is applicable from a business logic perspective. This is defined by two columns representing the start and end of the period and defining them as a logical time range. For example:
CREATE TABLE projects ( project_id INT PRIMARY KEY, project_name VARCHAR(100), valid_from DATE, valid_to DATE, PERIOD FOR valid_period (valid_from, valid_to) );
This table records when each project is valid. The PERIOD table option makes the two date columns NOT NULL. It also adds an invisible but operative constraint to check that valid_from is before valid_to. Besides adding these checks, MariaDB Server allows you to modify data for a specific period using the FOR PORTION OF clause. Suppose you want to update the name of a project that is valid between January and February 2025. You can do this as follows:
UPDATE projects FOR PORTION OF valid_period FROM '2025-01-01' TO '2025-02-28' SET project_name = 'Updated Project Name';
This statement ensures only the relevant portion of the application-time period is updated. Similarly, you can delete rows or portions of rows that overlap with a specified period. For example:
DELETE FROM projects FOR PORTION OF valid_period FROM '2025-01-01' TO '2025-02-28';
Check also the WITHOUT OVERLAPS clause. It can be very useful with things like hotel bookings or contracts where the valid periods cannot overlap.
Bitemporal Tables: Time²
When you combine system-versioned and application-time periods, you get bitemporal tables. Bitemporal tables allow you to manage data with two timelines—when it was valid in the real world and when it was recorded in the database.
Here’s an example:
CREATE TABLE contracts ( contract_id INT PRIMARY KEY, client_name VARCHAR(100), contract_details TEXT, valid_from DATE, valid_to DATE, PERIOD FOR valid_period (valid_from, valid_to), 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;
This table tracks both, when each contract is valid for business use, and when changes to the data were made.
Conclusion
The features showcased here solve real-world problems:
- Audits: Recreate past states of your database for legal or business audits.
- Scheduling: Manage overlapping schedules or validity periods with ease.
- Analytics: Analyze historical trends without the need for complex ETL processes.
Visit the KB pages to learn more about temporal tables.
MariaDB Enterprise Server 11.4 is part of MariaDB Enterprise Platform 2025 and is available for download now for all MariaDB customers. MariaDB users can try this feature in MariaDB Community Server and upgrade to enterprise when moving to production environments.
