-
Notifications
You must be signed in to change notification settings - Fork 5
MariaDB System Versioning (Temporal Tables)
The Wiki is outdated. The current description is available at the MariaDB Knowledge Base page.
System versioning is defined by SQL:2011.
System versioned tables contain historical data for all transactions ever run
against the table. Consider following example (note the new syntax extensions
with system versioning):
> create table t(x int,
sys_start timestamp(6) as row start invisible,
sys_end timestamp(6) as row end invisible,
period for system_time(st, en)
) with system versioning;
> insert into t values (1);
> update t set x=2;
> delete from t;Now the table t doesn't have any data:
> select * from t;
Empty set (0.00 sec)However, the system versioned table stores all versions of the row and we can query any of them:
> select *,sys_start,sys_end from t for system_time all;
+------+----------------------------+----------------------------+
| x | sys_start | sys_end |
+------+----------------------------+----------------------------+
| 2 | 2018-01-10 20:08:54.436627 | 2038-01-19 06:14:07.999999 |
| 1 | 2018-01-10 20:08:47.756550 | 2018-01-10 20:08:54.436627 |
+------+----------------------------+----------------------------+Note the invisible columns sys_start and sys_end. The columns contain
transaction timestamps for the appropriate row versions: sys_start describes
which transaction created the row version and sys_end describes which
transaction deleted the row version. Besides the new hidden columns, the example
above uses for system_time syntax extension, which is also new.
This is the most trivial example. Of course you can run much more complicated historical queries.
There is good description of Temporal Table Usage Scenarios from Microsoft. In short the feature is useful for:
-
Point-in-time recovery - recover database state as of particular point in time;
-
Forensic discovery & legal requirements to store data for N years
-
Data analysis (retrospective, trends etc.), e.g. to get your staff information as of one year ago.
MariaDB Flashback was introduced in 10.2.4 and is based on binlogs, so it's limited by binlog size and has lower performance than System versioning. Since historical records is restored from binlog in reverse order as they were modified, it's still possible to restore a database state at some point in time, but it's quite hard to get some MVCC-like view of a database state for particular time or transaction ID. Meantime, System versioning was designed to provide accurate MVCC-like views of a database state, either by time or transaction ID (the last one works for InnoDB only). Also System versioning provides reach SQL syntax extensions for querying historical data.