MariaDB Temporal Data Tables

This is fascinating. I've got two basic questions, however:

1) Is this always going to be performant with indices? It seems like "time" is kind of like another index here, and when designing queries which indices are used and in which order can be the difference between taking milliseconds and taking an hour. It's not obvious to me whether this will have hidden gotchas or query execution complexities, or if it's designed in a way that's so restricted and integrated into indices themselves that query performance will always remain within the same order of magnitude

2) What is the advantage of building this into the database, instead of adding your own timestamp columns e.g. 'created_timestamp' and 'expunged_timestamp'? Not only does that seem relatively simple, but it gives you the flexibility of creating indices across multiple columns (including them) for desired performance, the ability to work with tools like mysqldump, and it's just conceptually simpler to understand the database. And if the question is data security, is there a real difference between a "security layer" that is built around the database, versus one built into it? It would be fairly simple to write a command-line tool to change the MariaDB data files directly, no?

That does, of course, export complete data, but the problem is you can't then import it, because that breaks the entire guarantee about not being able to edit past data.

> temporal concepts should have been deeply native to SQL right to its core

Oh absolutely. I think the original intuition by Snodgrass et al. in TSQL2 to model temporality outside of the actual relational structure was a more promising direction, otherwise the complexity of composing joins across many tables, each with independent temporal versioning, seems rather overwhelming (note that the article doesn't discuss joins at all). Schema migration is another hairy topic that the 2011 standard barely addresses.

Modelling temporality outside of the database is what we've been pushing ahead with on Crux, which provides Datalog as the native query language but will imminently also support a simple bitemporal flavour of SQL for point-in-time querying (using Apache Calcite), where application & system time are defined by a single "as-of" prefix at the beginning of the statement that applies across the entire query: https://github.com/juxt/crux/tree/master/crux-sql#bitemporal... (queries over ranges and periods are currently out-of-scope)

Ah. There is that.

They really need to create a workaround for that, because sooner or later you will need to migrate your data and right now it's simply impossible.

Edit: Particularly as, reading further, you cannot ALTER a temporal table. Which does make sense, but the problem remains.

I work at a company where (many years ago) we built an extension to Postgres (and some helper libs in SQLAlchemy, Go) for implementing decently-performant bitemporal tables (biggest history tables have hundreds of millions of rows). Pretty much our entire company runs on it today.

We implemented the “minimum viable” features (i.e. automatic expiring, non-destructive updates, generated indexes and generated table declarations), but left some of the “harder” ideas up to the application designer (adding semantic versioning on top of temporal versioning, schema migrations).

It’s worked really well for us. I can’t think of anything we’ve done that’s had a higher ROI than this. I’ll really miss it when I leave!