MariaDB Temporal Tables: Uncut & Uncensored (Part I)
Tags:
Performance,
Site Reliability Engineering,
Technical Track,
Open Source,
Devops,
Grafana,
Nginx,
Lua,
Reverse Proxy,
Prometheus
(This is the first of a series of blog posts covering MariaDB Temporal Tables.)
"You must remember this, a kiss is still a kiss. A sigh is just a sigh; the fundamental things apply, as time goes by."
Sam (Dooley Wilson) in Casablanca
Unfortunately, out of kisses, sighs and fundamental things, time has an impact on almost everything in the universe. Everything has a beginning, an end, and is modified in the in-between. Databases systems often fail to store information about these modifications. They fail to capture the full lifecycle of data, and thus it is implemented using triggers or programmatically. The SQL:2011 standard addressed this issue by adding the time dimension to tables. MariaDB, since version 10.3, started to implement this part of the standard. In this blog post series, we will talk about these features as implemented in MariaDB 10.4. These features are known as Temporal Tables. This name can lead to some confusion because of the similarity between temporal and temporary. Temporal means "relating to time" and temporary means "lasting for only a limited period of time; not permanent". This confusion is frequent in Spanish because the same word is used for both meanings: temporal. But other languages have two words, French, for example, also has the words temporel and temporaire. After this linguistic digression, how does the SQL:2011 standard address this issue? Databases are a representation of the real world, they mimic certain aspects of life. The users table is just a representation of real human beings. And this representation is subject to two kinds of changes: those modifications that represent changes in the real world and those that are inherent to the representation itself. Your birthdate is not the same as the date you joined your gym. And again, the date you joined your gym probably is not when your data was entered into the gym's database. Let's imagine that somebody made a typo while entering the data. A few days later you realize that some information is wrong and you request this data to be changed. Data is modified to reflect the real world. The representation (as stored in the database) is subject to a lifecycle that sometimes does not correspond to the real world lifecycle. SQL:2011 tries to cover both lifecycles by providing two different features: system-versioned tables and application-time period tables. System-versioned tables describe the lifecycle of the representation, application-time period tables describe the lifecycle of the "real world" equivalent.System-Versioned
The first step is to create a table and insert some data:MariaDB [pythian]> CREATE TABLE `sv_table_ts` ( -> `id` bigint(20) NOT NULL AUTO_INCREMENT, -> `x` int(11) DEFAULT NULL, -> `text` varchar(256) DEFAULT NULL, PRIMARY KEY (`id`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.012 sec) MariaDB [pythian]> INSERT INTO sv_table_ts VALUES (null,1,'First row'); Query OK, 1 row affected (0.014 sec)Then we enable system versioning on this table using a simplified syntax:
MariaDB [pythian]> ALTER TABLE sv_table_ts ADD SYSTEM VERSIONING; Query OK, 1 row affected (0.033 sec) Records: 1 Duplicates: 0 Warnings: 0After that, we add and modify existing data. We store the time of each change into variables:
MariaDB [pythian]> select now() into @time1; # capture time Query OK, 1 row affected (0.000 sec) MariaDB [pythian]> insert into sv_table_ts values (null,2,'Second row'); Query OK, 1 row affected (0.002 sec) MariaDB [pythian]> select now() into @time2; # capture time Query OK, 1 row affected (0.000 sec) MariaDB [pythian]> update sv_table_ts set x=3 where id=1; Query OK, 1 row affected (0.004 sec) Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0The first interesting thing we see is that the update changed one row, but also inserted one row! This gives us a clue about what really has happened. We will come back to this later. This is the data the table contains right now:
MariaDB [pythian]> select * from sv_table_ts; +----+------+------------+ | id | x | text | +----+------+------------+ | 1 | 3 | First row | | 2 | 2 | Second row | +----+------+------------+ 2 rows in set (0.000 sec)
How do we query system versions?
If we want to see all the versions of all the rows from our table you need to run a SELECT FOR SYSTEM_TIME ALL:MariaDB [pythian]> select * from sv_table_ts for system_time all; +----+------+------------+ | id | x | text | +----+------+------------+ | 1 | 1 | First row | | 1 | 3 | First row | | 2 | 2 | Second row | +----+------+------------+ 3 rows in set (0.000 sec)So we have a history of all the data on the table... but what if I want to view the contents at a certain time? In this case, we need to replace FOR SYSTEM_TIME ALL with FOR SYSTEM_TIME AS OF <timestamp>. Remember that we stored some data into variables in the previous step.
MariaDB [pythian]> select * from sv_table_ts for system_time as of timestamp@time1; +----+------+-----------+ | id | x | text | +----+------+-----------+ | 1 | 1 | First row | +----+------+-----------+ 1 row in set (0.000 sec) MariaDB [pythian]> select * from sv_table_ts for system_time as of timestamp@time2; +----+------+------------+ | id | x | text | +----+------+------------+ | 1 | 1 | First row | | 2 | 2 | Second row | +----+------+------------+ 2 rows in set (0.000 sec)What if I remove one row?
MariaDB [pythian]> delete from sv_table_ts where id=2; Query OK, 1 row affected (0.002 sec) MariaDB [pythian]> select * from sv_table_ts; +----+------+-----------+ | id | x | text | +----+------+-----------+ | 1 | 3 | First row | +----+------+-----------+ 1 row in set (0.000 sec) MariaDB [pythian]> select * from sv_table_ts for system_time all; +----+------+------------+ | id | x | text | +----+------+------------+ | 1 | 1 | First row | | 1 | 3 | First row | | 2 | 2 | Second row | +----+------+------------+ 3 rows in set (0.000 sec)In this first post, we have talked about the concept of Temporal Tables and seen how System-Versioned tables work in MariaDB. In the second post, we will dive into the internals of system versioning, we will also discuss performance impact, and learn the extended syntax.
