3 ms·

Temporal Databases (1986) [pdf]

70 points by LAC-Tech 3y ago

synthc 3y ago
XTDB is a recent implementation of these ideas. In their docs they call it a bitemporal database, but it seems to match what is called a temporal database in the paper
- dragonwriter 3y ago
  Right, “bitemporal” later became established for the two-time-dimensions model here, with “temporal” being broader and including valid-time-only systems.
  - refset 3y ago
    Agreed, this is accurate. I work on XTDB and was able to speak with Richard Snodgrass (author of the paper) a couple of years ago. During our conversation we discussed how his research was primarily focused on handling valid-time, with transaction-time (a.k.a. system-time per SQL:2011) as a secondary concern. In contrast XTDB was inspired by the functional programming / immutability / database-as-a-value vision behind Datomic but with a desire for something more powerful than just the transaction-time capabilities. Essentially we arrived at 'bitemporal' from the opposite direction. This blog post covers the perspective somewhat: https://vvvvalvalval.github.io/posts/2017-07-08-Datomic-this-is-not-the-history-youre-looking-for.html https://vvvvalvalval.github.io/posts/2017-07-08-Datomic-this...
    - jddj 3y ago
      Nice, thanks. It seems like sometimes what we want is the state at a point in time (eg. for rolling back the database of a managed networked embedded device). Other times what we really want in these systems is state as of a point in time based on the current set of timestamped, (effectively) mutable facts.
      - refset 3y ago
        I believe people interested in the latter are almost always interested in the former also, at least in the context of transactional systems (and particularly anything subject to complex regulatory requirements). Analytical systems are a different world where time seems to often be regarded as "just another dimension", but strong consistency and auditability are increasingly relevant there too, which is also a natural reflection of how a lot of modern OLAP systems operate internally using immutable snapshots of blob storage (see Snowflake, Iceberg, LakeFS etc.). I know there are definitely lots of potential tricks for making better use of a first-class understanding of time in analytics though, and not just treating it the same as other dimensions, e.g. https://duckdb.org/2023/09/15/asof-joins-fuzzy-temporal-lookups.html https://duckdb.org/2023/09/15/asof-joins-fuzzy-temporal-look... and https://materialize.com/blog/temporal-filters/ https://materialize.com/blog/temporal-filters/
        jddj 3y ago
        Both great links, thanks. You're right, of course. We want both. As it turns out, AsOf in SQL is a problem I ran into just yesterday when ensuring that serialized report snapshots point to the correct historical versions of their resources.
withinboredom 3y ago
Oh oh! I built one of these at work a 3-4 years ago! It was awesome! We were using it to store features for the AI which needed to query them as of a specific time. It was built on top of Hadoop/HBase. AMA, it was scrapped eventually, so I don’t think anyone cares if I share details.
- nandhinianand 3y ago
  Oh.. please do share the details... may be a blog post or a longer comment here.
- lysecret 3y ago
  Why was it scrapped?
  - withinboredom 3y ago
    It was a hard-sell to the rest of the org. Most people didn't understand why it needed to exist or why it ever started to exist in the first place (this was before the AI renaissance fwiw, so AI wasn't sexy again, even if it made millions of dollars). After a reorg and re-prioritization, resources were moved away from it.
    - refset 3y ago
      A lot of "feature store" systems seem to make the case for time travel these days, e.g. https://docs.featurebase.com/docs/concepts/time-quantums/ https://docs.featurebase.com/docs/concepts/time-quantums/
fipar 3y ago
I found Time and Relational Theory a very interesting read on this topic: https://www.oreilly.com/library/view/time-and-relational/9780128006313/ https://www.oreilly.com/library/view/time-and-relational/978...
refset 3y ago
I really like Kent Beck's recent re-framing of this overly technical topic in terms of "Eventual Business Consistency": https://tidyfirst.substack.com/p/eventual-business-consistency https://tidyfirst.substack.com/p/eventual-business-consisten... > In a nutshell, we want what’s recorded in the system to match the real world. We know this is impossible (delays, mistakes, changes) but are getting as close as we can. The promise is that if what’s in the system matches the real world as closely as possible, costs go down, customer satisfaction goes up, & we are able to scale further faster.

70 points by LAC-Tech 3y ago

XTDB is a recent implementation of these ideas. In their docs they call it a bitemporal database, but it seems to match what is called a temporal database in the paper

dragonwriter 3y ago
Right, “bitemporal” later became established for the two-time-dimensions model here, with “temporal” being broader and including valid-time-only systems.
- refset 3y ago
  Agreed, this is accurate. I work on XTDB and was able to speak with Richard Snodgrass (author of the paper) a couple of years ago. During our conversation we discussed how his research was primarily focused on handling valid-time, with transaction-time (a.k.a. system-time per SQL:2011) as a secondary concern. In contrast XTDB was inspired by the functional programming / immutability / database-as-a-value vision behind Datomic but with a desire for something more powerful than just the transaction-time capabilities. Essentially we arrived at 'bitemporal' from the opposite direction. This blog post covers the perspective somewhat: https://vvvvalvalval.github.io/posts/2017-07-08-Datomic-this-is-not-the-history-youre-looking-for.html https://vvvvalvalval.github.io/posts/2017-07-08-Datomic-this...
  - jddj 3y ago
    Nice, thanks. It seems like sometimes what we want is the state at a point in time (eg. for rolling back the database of a managed networked embedded device). Other times what we really want in these systems is state as of a point in time based on the current set of timestamped, (effectively) mutable facts.
    - refset 3y ago
      I believe people interested in the latter are almost always interested in the former also, at least in the context of transactional systems (and particularly anything subject to complex regulatory requirements). Analytical systems are a different world where time seems to often be regarded as "just another dimension", but strong consistency and auditability are increasingly relevant there too, which is also a natural reflection of how a lot of modern OLAP systems operate internally using immutable snapshots of blob storage (see Snowflake, Iceberg, LakeFS etc.). I know there are definitely lots of potential tricks for making better use of a first-class understanding of time in analytics though, and not just treating it the same as other dimensions, e.g. https://duckdb.org/2023/09/15/asof-joins-fuzzy-temporal-lookups.html https://duckdb.org/2023/09/15/asof-joins-fuzzy-temporal-look... and https://materialize.com/blog/temporal-filters/ https://materialize.com/blog/temporal-filters/
      - jddj 3y ago
        Both great links, thanks. You're right, of course. We want both. As it turns out, AsOf in SQL is a problem I ran into just yesterday when ensuring that serialized report snapshots point to the correct historical versions of their resources.

withinboredom 3y ago

Oh oh! I built one of these at work a 3-4 years ago! It was awesome! We were using it to store features for the AI which needed to query them as of a specific time. It was built on top of Hadoop/HBase. AMA, it was scrapped eventually, so I don’t think anyone cares if I share details.

nandhinianand 3y ago
Oh.. please do share the details... may be a blog post or a longer comment here.
lysecret 3y ago
Why was it scrapped?
- withinboredom 3y ago
  It was a hard-sell to the rest of the org. Most people didn't understand why it needed to exist or why it ever started to exist in the first place (this was before the AI renaissance fwiw, so AI wasn't sexy again, even if it made millions of dollars). After a reorg and re-prioritization, resources were moved away from it.
  - refset 3y ago
    A lot of "feature store" systems seem to make the case for time travel these days, e.g. https://docs.featurebase.com/docs/concepts/time-quantums/ https://docs.featurebase.com/docs/concepts/time-quantums/

fipar 3y ago

I found Time and Relational Theory a very interesting read on this topic: https://www.oreilly.com/library/view/time-and-relational/9780128006313/ https://www.oreilly.com/library/view/time-and-relational/978...

refset 3y ago

I really like Kent Beck's recent re-framing of this overly technical topic in terms of "Eventual Business Consistency": https://tidyfirst.substack.com/p/eventual-business-consistency https://tidyfirst.substack.com/p/eventual-business-consisten... > In a nutshell, we want what’s recorded in the system to match the real world. We know this is impossible (delays, mistakes, changes) but are getting as close as we can. The promise is that if what’s in the system matches the real world as closely as possible, costs go down, customer satisfaction goes up, & we are able to scale further faster.