8 ms·

There are a couple of good books on bitemporality, Richard Snodgrass' "Developing time-oriented database applications in SQL" is pretty much the bible on the su

There are a couple of good books on bitemporality, Richard Snodgrass' "Developing time-oriented database applications in SQL" is pretty much the bible on the subject and goes into great detail. "Managing Time in Relational Databases" by Tom Johnston and Randall Weis is another great book, a little more hands on with the code. I'd love to see someone translate the concepts in both books to a useful postgres package. As Snodgrass points out, "Unfortunately, due to the noncompliance of all existing DBMSs, a few of these fragments run on no existing platform" and thus only discusses how they can be applied to DB2, MSSQL, Sybase, Oracle8 and UniSQL.

pjungwir 7y ago
> I'd love to see someone translate the concepts in both books to a useful postgres package. This is some work on this. [1] is an amazing patch by some researchers in Europe that has already had several rounds of review. Their first published work was against Postgres 9.1! Their solution is way better than SQL:2011, but could be used behind-the-scenes to implement the features from the standard. Essentially they implement a temporal variant for every operator in the relational algebra (very elegantly btw, by combining standard operators with just two new functions). That approach solves some of the problems that led to Snodgrass's original temporal proposal getting rejected in the late 90s (and which are still holding back SQL:2011 IMO). Whereas the standard only gives temporal INNER JOIN, their work gives every join type, as well as aggregates (including a way to scale the inputs if the group only includes part of their time interval). It also lets queries compose better through subqueries, views, CTEs, set-returning functions, etc. In my opinion their work would make Postgres the most advanced temporal database in the world. I hope it gets the attention it needs! I also have a very modest WIP patch myself at [2], which adds temporal-aware primary and foreign keys. This is all based on the excellent work on ranges and exclusion constraints by Jeff Davis and others. Postgres has an excellent foundation, but needs the higher-level concepts so that users can more easily set up temporal tables and use them to answer queries and record changes. If you'd like to learn more about temporal databases, I wrote a summary of the research at [3]. I'm also giving a talk at the Postgres conference in Ottawa next month.... :-) EDIT: Btw, if all you need is transaction time (usually included for auditing and compliance), then the temporal_tables extension [4] should be all you need. Maybe also consider the pgaudit extension too [5]. [1] https://commitfest.postgresql.org/22/2045/ https://commitfest.postgresql.org/22/2045/ [2] https://commitfest.postgresql.org/22/2048/ https://commitfest.postgresql.org/22/2048/ [3] https://illuminatedcomputing.com/posts/2017/12/temporal-databases-bibliography/ https://illuminatedcomputing.com/posts/2017/12/temporal-data... [4] https://github.com/arkhipov/temporal_tables https://github.com/arkhipov/temporal_tables [5] https://github.com/pgaudit/pgaudit https://github.com/pgaudit/pgaudit
- vjt 7y ago
  I wrote a Ruby library[1] that builds on Active Record and implements SCD-Type 2 using updatable views and INSTEAD OF triggers. It has its pain points, mostly related to how hackish the AR insertion is, but it works quite well and the approach ensures that any INSERT/UPDATE/DELETE triggers bookkeeping of the history tables. [1] https://github.com/ifad/chronomodel https://github.com/ifad/chronomodel
  - pjungwir 7y ago
    Oh I really admire Chronomodel! Actually I link to it in my annotated bibliography ([3] above). Thanks for all your work on that! (Also: I exchanged some emails with one of the Chronomodel devs a few months ago. Perhaps that was you?) For valid-time DML, I think the most useful operation is an upsert/merge. Tom Johnston calls this an `INSERT WHENEVER` I think. I have a not-yet-published Rails project that also uses INSTEAD OF triggers to convert AR saves to valid-time upserts. Hopefully I can get that published soon. I would love to even contribute it to Chronomodel eventually if that makes sense, so that it can be bitemporal. Although there is tons of research in temporal relational database design, the rest of the stack really needs attention. What does temporal mean for REST and CRUD? Or your ORM? What are good UX patterns for viewing and editing a valid-time history? Can you make save-as-you-type work or do you need a Save button? I'd love to see people start working on that! My own Rails experiment landed on one way, and for me it validated that the db-level features would actually be useful, but there are other approaches possible.
- michelpp 7y ago
  Wow great summary, thank you!
gen220 7y ago
At my place of work, we leverage temporal and bitemporal tables in PostgreSQL extensively (I’d estimate that ⅔ of our Schemas are either temporal or bitemporal). We have a few custom tools to make them more ergonomic to create and update, but the core functionality is derived from an open source extension, I believe. Although, googling for it now, while away from my work computer, I can’t seem to find it... :/ ... after looking it up on my work computer, it turns out we wrote our own implementation. It’s less than 200 lines of SQL, and uses three relatively common extensions. I wish it was open source, because it’d be super helpful to have in the future. But hey, at least it’s good to know that it can be/has been practically done?
- phonon 7y ago
  Why not use https://pgxn.org/dist/temporal_tables/ https://pgxn.org/dist/temporal_tables/ ?
  - Hettie 7y ago
    This one requires to create historical tables for each "regular" table, like in SQL 2011, in AVF you keep everything in one table, this feature provides a unique level of querying flexibility for temporal queries.
    - phonon 7y ago
      So more like Oracle Flashback?
    - gen220 7y ago
      Our implementation creates two views derived from each bitemporal table (_history and _full, which is a union between the base table and _history). It's definitely "heavier" than it needs to be – we store a lot of redundant data, but it hasn't been a huge issue. But we have tools that build the tables/views, and generate sqlalchemy code, so that it's not difficult to manage, all told.
      - Hettie 7y ago
lichtenberger 7y ago
I always wonder why not going one step further? Nowadays random reads on SSDs are so much cheaper and I don't think the end is just a huge B+- or LSM tree, which has to cluster everything and store so much data redundantly (transaction time). I would say that restoring a specific revision isn't really efficient. I'm still working on a project which had been started around 2006 by Marc Kramis (his Ph.D. work) and where I began work on around 2007 :-) we borrowed quiet some ideas from ZFS mainly (as well as from Git now) and putted them to test on a sub-file level and added our own stuff as for instance record-level versioning via a sliding snapshot algorithm. There's still a lot to do, but I'm able to store and query revisions of both XML as well as JSON now via XQuery and I'll look into (cost based) query optimizations and partitioning/replication next. I know that it has been probably crazy to write a storage manager from scratch, but I think Marc's ideas are pretty good and I added my own ideas and Sebastian Graf, another Ph.D. student back then also did a lot of work on the project, just as many other students. Maybe I'm just crazy to keep working on it almost daily now besides my day to day software engineering job, but yeah... I guess you have to be a bit too convinced and too dedicated to something, maybe (even though sadly I don't know if anyone tried it lately) ;-) maybe I need to contact Marc after all this time again :-) https://sirix.io/concepts.html https://sirix.io/concepts.html http://pubsys.mmsp-kn.de/pubsys/publishedFiles/Kramis2014.pdf http://pubsys.mmsp-kn.de/pubsys/publishedFiles/Kramis2014.pd...
refset 7y ago
A huge practical challenge when implementing bitemporality is how to deal with schema migrations efficiently whilst supporting arbitrary time-travel queries. When we were building Crux, which is referenced at the end of the post, we took the view that this is a hard problem to solve in a generic way, hence why we have opted for a schemaless document-oriented design. I really hope to see other databases make it easier to use bitemporality in the near future, but I suspect that any DBMS which mandates a schema is fighting an up-hill battle. Disclosure: working on Crux at JUXT :)
- paulcarey 7y ago
  You could consider ‘partitioning’ your DB when you migrate your schema so each DB instance only deals with a single bitemporal slice eg after 5 migrations you have 5 distinct database instances. This approach avoids bloat assuming the vast majority of queries would be served by the most recent instance, while not precluding serving verbatim responses from earlier instances.
  - lichtenberger 7y ago
    I wonder why so few people seem to think about persistent data structures (persistent in the functional sense, that is immutability -- copy-on-write). Basically we implemented a form of hash array based tries for https://sirix.io https://sirix.io ... or it's just like how ZFS stores objects adding levels of indirect blocks/pages on demand with bitsets storing which indexes are really set to avoid having arrays with a lot of null references. For sure you have to copy the path to the root, just like snapshots work in ZFS or Btrfs, but I think that data is neglegable when you basically also do versioning on a per record-level/per revision level. At least thus we can restore each revision in the same time, you do not have to have a "history table", which is inefficient to query in contrast to the most recent revision table.
    - lichtenberger 7y ago
      But it's schema-less ;-)

pjungwir 7y ago

> I'd love to see someone translate the concepts in both books to a useful postgres package. This is some work on this. [1] is an amazing patch by some researchers in Europe that has already had several rounds of review. Their first published work was against Postgres 9.1! Their solution is way better than SQL:2011, but could be used behind-the-scenes to implement the features from the standard. Essentially they implement a temporal variant for every operator in the relational algebra (very elegantly btw, by combining standard operators with just two new functions). That approach solves some of the problems that led to Snodgrass's original temporal proposal getting rejected in the late 90s (and which are still holding back SQL:2011 IMO). Whereas the standard only gives temporal INNER JOIN, their work gives every join type, as well as aggregates (including a way to scale the inputs if the group only includes part of their time interval). It also lets queries compose better through subqueries, views, CTEs, set-returning functions, etc. In my opinion their work would make Postgres the most advanced temporal database in the world. I hope it gets the attention it needs! I also have a very modest WIP patch myself at [2], which adds temporal-aware primary and foreign keys. This is all based on the excellent work on ranges and exclusion constraints by Jeff Davis and others. Postgres has an excellent foundation, but needs the higher-level concepts so that users can more easily set up temporal tables and use them to answer queries and record changes. If you'd like to learn more about temporal databases, I wrote a summary of the research at [3]. I'm also giving a talk at the Postgres conference in Ottawa next month.... :-) EDIT: Btw, if all you need is transaction time (usually included for auditing and compliance), then the temporal_tables extension [4] should be all you need. Maybe also consider the pgaudit extension too [5]. [1] https://commitfest.postgresql.org/22/2045/ https://commitfest.postgresql.org/22/2045/ [2] https://commitfest.postgresql.org/22/2048/ https://commitfest.postgresql.org/22/2048/ [3] https://illuminatedcomputing.com/posts/2017/12/temporal-databases-bibliography/ https://illuminatedcomputing.com/posts/2017/12/temporal-data... [4] https://github.com/arkhipov/temporal_tables https://github.com/arkhipov/temporal_tables [5] https://github.com/pgaudit/pgaudit https://github.com/pgaudit/pgaudit

vjt 7y ago
I wrote a Ruby library[1] that builds on Active Record and implements SCD-Type 2 using updatable views and INSTEAD OF triggers. It has its pain points, mostly related to how hackish the AR insertion is, but it works quite well and the approach ensures that any INSERT/UPDATE/DELETE triggers bookkeeping of the history tables. [1] https://github.com/ifad/chronomodel https://github.com/ifad/chronomodel
- pjungwir 7y ago
  Oh I really admire Chronomodel! Actually I link to it in my annotated bibliography ([3] above). Thanks for all your work on that! (Also: I exchanged some emails with one of the Chronomodel devs a few months ago. Perhaps that was you?) For valid-time DML, I think the most useful operation is an upsert/merge. Tom Johnston calls this an `INSERT WHENEVER` I think. I have a not-yet-published Rails project that also uses INSTEAD OF triggers to convert AR saves to valid-time upserts. Hopefully I can get that published soon. I would love to even contribute it to Chronomodel eventually if that makes sense, so that it can be bitemporal. Although there is tons of research in temporal relational database design, the rest of the stack really needs attention. What does temporal mean for REST and CRUD? Or your ORM? What are good UX patterns for viewing and editing a valid-time history? Can you make save-as-you-type work or do you need a Save button? I'd love to see people start working on that! My own Rails experiment landed on one way, and for me it validated that the db-level features would actually be useful, but there are other approaches possible.
michelpp 7y ago
Wow great summary, thank you!

gen220 7y ago

At my place of work, we leverage temporal and bitemporal tables in PostgreSQL extensively (I’d estimate that ⅔ of our Schemas are either temporal or bitemporal). We have a few custom tools to make them more ergonomic to create and update, but the core functionality is derived from an open source extension, I believe. Although, googling for it now, while away from my work computer, I can’t seem to find it... :/ ... after looking it up on my work computer, it turns out we wrote our own implementation. It’s less than 200 lines of SQL, and uses three relatively common extensions. I wish it was open source, because it’d be super helpful to have in the future. But hey, at least it’s good to know that it can be/has been practically done?

phonon 7y ago
Why not use https://pgxn.org/dist/temporal_tables/ https://pgxn.org/dist/temporal_tables/ ?
- Hettie 7y ago
  This one requires to create historical tables for each "regular" table, like in SQL 2011, in AVF you keep everything in one table, this feature provides a unique level of querying flexibility for temporal queries.
  - phonon 7y ago
    So more like Oracle Flashback?
  - gen220 7y ago
    Our implementation creates two views derived from each bitemporal table (_history and _full, which is a union between the base table and _history). It's definitely "heavier" than it needs to be – we store a lot of redundant data, but it hasn't been a huge issue. But we have tools that build the tables/views, and generate sqlalchemy code, so that it's not difficult to manage, all told.
    - Hettie 7y ago

lichtenberger 7y ago

I always wonder why not going one step further? Nowadays random reads on SSDs are so much cheaper and I don't think the end is just a huge B+- or LSM tree, which has to cluster everything and store so much data redundantly (transaction time). I would say that restoring a specific revision isn't really efficient. I'm still working on a project which had been started around 2006 by Marc Kramis (his Ph.D. work) and where I began work on around 2007 :-) we borrowed quiet some ideas from ZFS mainly (as well as from Git now) and putted them to test on a sub-file level and added our own stuff as for instance record-level versioning via a sliding snapshot algorithm. There's still a lot to do, but I'm able to store and query revisions of both XML as well as JSON now via XQuery and I'll look into (cost based) query optimizations and partitioning/replication next. I know that it has been probably crazy to write a storage manager from scratch, but I think Marc's ideas are pretty good and I added my own ideas and Sebastian Graf, another Ph.D. student back then also did a lot of work on the project, just as many other students. Maybe I'm just crazy to keep working on it almost daily now besides my day to day software engineering job, but yeah... I guess you have to be a bit too convinced and too dedicated to something, maybe (even though sadly I don't know if anyone tried it lately) ;-) maybe I need to contact Marc after all this time again :-) https://sirix.io/concepts.html https://sirix.io/concepts.html http://pubsys.mmsp-kn.de/pubsys/publishedFiles/Kramis2014.pdf http://pubsys.mmsp-kn.de/pubsys/publishedFiles/Kramis2014.pd...

refset 7y ago

A huge practical challenge when implementing bitemporality is how to deal with schema migrations efficiently whilst supporting arbitrary time-travel queries. When we were building Crux, which is referenced at the end of the post, we took the view that this is a hard problem to solve in a generic way, hence why we have opted for a schemaless document-oriented design. I really hope to see other databases make it easier to use bitemporality in the near future, but I suspect that any DBMS which mandates a schema is fighting an up-hill battle. Disclosure: working on Crux at JUXT :)

paulcarey 7y ago
You could consider ‘partitioning’ your DB when you migrate your schema so each DB instance only deals with a single bitemporal slice eg after 5 migrations you have 5 distinct database instances. This approach avoids bloat assuming the vast majority of queries would be served by the most recent instance, while not precluding serving verbatim responses from earlier instances.
- lichtenberger 7y ago
  I wonder why so few people seem to think about persistent data structures (persistent in the functional sense, that is immutability -- copy-on-write). Basically we implemented a form of hash array based tries for https://sirix.io https://sirix.io ... or it's just like how ZFS stores objects adding levels of indirect blocks/pages on demand with bitsets storing which indexes are really set to avoid having arrays with a lot of null references. For sure you have to copy the path to the root, just like snapshots work in ZFS or Btrfs, but I think that data is neglegable when you basically also do versioning on a per record-level/per revision level. At least thus we can restore each revision in the same time, you do not have to have a "history table", which is inefficient to query in contrast to the most recent revision table.
  - lichtenberger 7y ago
    But it's schema-less ;-)