Skip to content

Home

Jump to bottom
Alexander K edited this page Jan 10, 2018 · 64 revisions

MariaDB System Versioning

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.

Use cases

There is good description of Temporal Table Usage Scenarios from Microsoft. In short the feature is useful for:

  1. Point-in-time recovery - recover database state as of particular point in time;

  2. Forensic discovery & legal requirements to store data for N years

  3. Data analysis (retrospective, trends etc.), e.g. to get your staff information as of one year ago.

Difference from Flashback

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.

References

Contents

Clone this wiki locally