MariaDB Temporal Data Tables

#1

Post by alecbenzer » Sun, Jul 12, 2020, 3:04 AM UTC

MariaDB Temporal Data Tables
mariadb.com

Re: MariaDB Temporal Data Tables

#2

Post by alecbenzer » Sun, Jul 12, 2020, 3:06 AM UTC

Had no idea until recently that MariaDB supported this out of the box. Does anyone have experience using this? How does it compare to https://github.com/scalegenius/pg_bitemporal ?

Re: MariaDB Temporal Data Tables

#3

Post by docsapp_io » Sun, Jul 12, 2020, 6:42 AM UTC

I really hope Postgres can support temporal table out of the box. Temporal table can simplify development for the feature that need audits.

Re: MariaDB Temporal Data Tables

#4

Post by TekMol » Sun, Jul 12, 2020, 7:36 AM UTC

Is there a diff tool? Like show me all differences between now and 5 minutes ago?

Could be nice to see what magic goes on behind the scene in some applications.

For example when you do some clicks in the backend of WordPress and wonder what it actually did to the data.

Re: MariaDB Temporal Data Tables

#5

Post by polskibus » Sun, Jul 12, 2020, 8:35 AM UTC

How does this feature compare to MS SQL's Temporal Tables https://docs.microsoft.com/en-us/sql/relational-databases/ta...?

This feature seems to be well fitted to support some of the cases where event sourcing is introduced, I wonder if someone successfully applied event sourcing with use of temporal tables to reduce the amount of work that has to be done in the application code (Akka, etc.).

Re: MariaDB Temporal Data Tables

#6

Post by Drdrdrq » Sun, Jul 12, 2020, 10:18 AM UTC

I understand the benefits of this feature for audits, but how does one deal with GDPR requirements? Is there some way to alter historic data to remove PII, or should the affected columns be excluded?

Re: MariaDB Temporal Data Tables

#7

Post by sjwright » Sun, Jul 12, 2020, 10:26 AM UTC

I've been begging for exactly this for quite some time. Because of the way I use databases, I've always been bewildered why this wasn't a core part of SQL from the very beginning.

From what I'm reading there's still a lot to be fleshed out to be maximally useful to me, but even in its current state I could imagine using this.

— I'd like to have a field property that limits stored values to a single version and thus is automatically cleared whenever the row is updated. This would be useful for inlining change annotations, and for associating a user_id to specific changes.

— I'd like to be able to arbitrarily select the n-1 value of fields regardless of their time period. E.g.

  select username, previous(username)
  from users

— When viewing a specific version, I'd like to know whether a field's value was supplied in that revision. That's distinct from if the field was changed. I want to know if the value was supplied—even if it was identical to the previous value.

— This might be possible already (it's hard to tell) but I'd like to be able to query/join on any revision. For example I might want to ask the question "show me all products that james has ever modified". That could then get more specific, e.g. "show me all products where james changed the price".

Re: MariaDB Temporal Data Tables

#8

Post by refset » Sun, Jul 12, 2020, 11:16 AM UTC

docsapp_io wrote: post #3

I really hope Postgres can support temporal table out of the box. Temporal table can simplify development for the feature that need audits.

System time (aka "transaction time") is also invaluable for debugging if you annotate it with release versions. Unless an application is particularly strapped for storage costs, which is rare in this day and age, it ought to be the default choice to use built-in system time versioning wherever it exists.

Re: MariaDB Temporal Data Tables

#9

Post by mulander » Sun, Jul 12, 2020, 11:19 AM UTC

docsapp_io wrote: post #3

I really hope Postgres can support temporal table out of the box. Temporal table can simplify development for the feature that need audits.

Funny historical and architecture fact about PostgreSQL. It actually can do this, for all tables without special features. Unfortunately the facility to perform a query like this is no longer exposed but it shouldn't be impossible to re-add in a more modern way.

Essentially PostgreSQL has copy-on-write semantics, so historical records exist unless a vacuum marks them as no longer needed and subsequent insert/updates overwrite the values.

In the past when PostgreSQL had the postquel language (before SQL was added) there was special syntax to access data at specific points in time:

This is nicely outlined in "THE IMPLEMENTATION OF POSTGRES" by Michael Stonebraker, Lawrence A. Rowe and Michael Hirohama[1]. Go ahead open the PDF and search for "time travel" or read the quotes below.

> The second benefit of a no-overwrite storage manager is the possibility of time travel. As noted earlier, a user can ask a historical query and POSTGRES will automatically return information from the record valid at the correct time.

Quoting the paper again:

> For example to find the salary of Sam at time T one would query:

    retrieve (EMP.salary)
    using EMP [T]
    where EMP.name = "Sam"

> POSTGRES will automatically find the version of Sam’s record valid at the correct time and get the appropriate salary.

[1] - https://dsf.berkeley.edu/papers/ERL-M90-34.pdf

Re: MariaDB Temporal Data Tables

#10

Post by mildbyte » Sun, Jul 12, 2020, 11:27 AM UTC

docsapp_io wrote: post #3

I really hope Postgres can support temporal table out of the box. Temporal table can simplify development for the feature that need audits.

Shameless plug (I'm a co-founder) but this is basically what we've built with Splitgraph[0]: we can add change tracking to tables using PostgreSQL's audit triggers and let the user switch between different versions of the table / query past versions.

[0] https://www.splitgraph.com/product/data-lifecycle/research