MariaDB temporal tables let you see data as it was at any point in time, but the real magic is how they achieve this without dramatically impacting performance.
Let’s watch it in action. Imagine a simple products table:
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10, 2)
);
Now, we enable temporal features on it. This adds two hidden columns: _valid_from and _valid_to. _valid_from records when the row became valid, and _valid_to records when it ceased to be valid (or is still valid).
ALTER TABLE products TIMESTAMP ON UPDATE NOW() TO NOW() ADD PERIOD FOR SYSTEM_TIME (_valid_from, _valid_to);
Let’s insert some data:
INSERT INTO products (name, price) VALUES ('Laptop', 1200.00);
-- Assuming this is inserted at 2023-10-27 10:00:00 UTC
At this point, _valid_from is 2023-10-27 10:00:00 and _valid_to is infinity (represented by a very large timestamp).
Now, we update the price:
UPDATE products SET price = 1150.00 WHERE id = 1;
-- Assuming this is updated at 2023-10-27 10:05:00 UTC
Here’s what happens under the hood: instead of overwriting the original row, MariaDB expires the old row by setting its _valid_to to 2023-10-27 10:05:00. Then, it inserts a new row with the updated price, setting its _valid_from to 2023-10-27 10:05:00 and _valid_to to infinity.
So, our products table now effectively has two versions of the row for id = 1:
id=1, name='Laptop', price=1200.00, _valid_from='2023-10-27 10:00:00', _valid_to='2023-10-27 10:05:00'id=1, name='Laptop', price=1150.00, _valid_from='2023-10-27 10:05:00', _valid_to='infinity'
To query historical data, you use the AS OF clause. To see the state of the table at 2023-10-27 10:02:00 UTC:
SELECT id, name, price FROM products FOR SYSTEM_TIME AS OF '2023-10-27 10:02:00' WHERE id = 1;
This query will return: id=1, name='Laptop', price=1200.00. It found the row where _valid_from is less than or equal to the specified time, and _valid_to is greater than the specified time.
To see the current state (which is the default behavior):
SELECT id, name, price FROM products FOR SYSTEM_TIME AS OF NOW() WHERE id = 1;
-- Or simply:
SELECT id, name, price FROM products WHERE id = 1;
This returns: id=1, name='Laptop', price=1150.00.
The FOR SYSTEM_TIME clause is the key. It tells MariaDB to filter rows based on their _valid_from and _valid_to timestamps. You can also query a period of time, showing all versions within that range:
SELECT id, name, price FROM products FOR SYSTEM_TIME BETWEEN '2023-10-27 10:00:00' AND '2023-10-27 10:05:00' WHERE id = 1;
This would return both versions of the row that existed between those two times.
The temporal feature is implemented by an internal storage engine, often B-trees, that efficiently handles these time-based queries. When you query AS OF a specific time, MariaDB uses the timestamp on the index nodes to quickly locate the correct version of the row without scanning the entire table. It’s not just adding extra columns; it’s a fundamental change in how data is managed and retrieved for time-aware operations. You can also specify a time in the past or future using AS OF TIMESTAMP with a specific datetime value.
Most people assume temporal tables create an entirely separate history table, which is a common pattern but not how MariaDB’s system-time temporal tables work. They maintain history within the primary table itself by versioning rows and using _valid_from and _valid_to to define their lifespan. This approach significantly reduces overhead for writes and makes historical queries efficient.
The next step is understanding how to manage the retention of this historical data to prevent runaway disk usage.