5 ms·

Yeah, it seems you could be able to substitute thoughtful schema design avoiding updates/deletes for time-travel as a feature. I wonder if anyone has made a co

Yeah, it seems you could be able to substitute thoughtful schema design avoiding updates/deletes for time-travel as a feature. I wonder if anyone has made a collection of reference examples implemented this way (and in general think that a substantial compendium good examples of DB schema and thinking behind them could be worthwhile).

mjul 4y ago
Snodgrass wrote a whole book on that topic: Developing Time-Oriented Database Applications in SQL (1999). It is available as PDF on his publications page: https://www2.cs.arizona.edu/~rts/publications.html https://www2.cs.arizona.edu/~rts/publications.html
jskulski 4y ago
Ive built a couple systems that would have been datomic’s bread and butter. Each time the company was more comfortable with mainstream dbs, so we ended going with something like you’re talking about, built on top of a db. A couple of the projects were because a mainstream dbs wouldn’t scale. The systems definitely worked, but it was also a lot of implementation complexity on an other wise simple business prop: “store this data as facts”
twic 4y ago
I'm moderately confident you could mechanically transform a time-oblivious schema into a history-preserving one, and then write a view on top of it which gave a slice at a particular time. Moderately.
- ysleepy 4y ago
  That is essentially what MVCC does.
  - twic 4y ago
    That is an extremely good point.
  - JohnBooty 4y ago
    Yes, although AFAIK those hidden MVCC columns (xmin, xmax?) aren't very usable from an application standpoint -- the obsoleted rows only hang around until the next VACUUM, right? I realize you're not claiming those columns are useful from an application perspective. Just curious to know if I'm wrong and they are useful. Because as I understand it, the selling point of Datomic is their audit trail functionality and that is admittedly a bit onerous to implement in a RBDMS. Even though I feel like every project needs/requires that eventually.
    - twic 4y ago
      Oracle lets you use the MVCC data to query past states of the database, called "flashback": https://docs.oracle.com/en/database/oracle/oracle-database/21/adfns/flashback.html https://docs.oracle.com/en/database/oracle/oracle-database/2... You can configure how long the old data is kept: https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/UNDO_RETENTION.html https://docs.oracle.com/en/database/oracle/oracle-database/2... Worked examples: http://www.dba-oracle.com/t_rman_149_flasbback_query.htm http://www.dba-oracle.com/t_rman_149_flasbback_query.htm
      - JohnBooty 4y ago
        Wow, super informative. Thank you so much.
    - ysleepy 4y ago
      I meant MVCC is the proof that you can automate the transform of a schema into a versioned schema. How and if the DBMS exposes that is another concern. The garbage collection / VACUUM part of an MVCC system is the harder part, saving all versions and querying a point in time is the easy one.
rehevkor5 4y ago
Maybe search around on bitemporal database table modeling.
pjot 4y ago
It’s called a slowly changing dimension. In this example, it’s a type-2. https://en.m.wikipedia.org/wiki/Slowly_changing_dimension https://en.m.wikipedia.org/wiki/Slowly_changing_dimension

mjul 4y ago

Snodgrass wrote a whole book on that topic: Developing Time-Oriented Database Applications in SQL (1999). It is available as PDF on his publications page: https://www2.cs.arizona.edu/~rts/publications.html https://www2.cs.arizona.edu/~rts/publications.html

jskulski 4y ago

Ive built a couple systems that would have been datomic’s bread and butter. Each time the company was more comfortable with mainstream dbs, so we ended going with something like you’re talking about, built on top of a db. A couple of the projects were because a mainstream dbs wouldn’t scale. The systems definitely worked, but it was also a lot of implementation complexity on an other wise simple business prop: “store this data as facts”

twic 4y ago

I'm moderately confident you could mechanically transform a time-oblivious schema into a history-preserving one, and then write a view on top of it which gave a slice at a particular time. Moderately.

ysleepy 4y ago
That is essentially what MVCC does.
- twic 4y ago
  That is an extremely good point.
- JohnBooty 4y ago
  Yes, although AFAIK those hidden MVCC columns (xmin, xmax?) aren't very usable from an application standpoint -- the obsoleted rows only hang around until the next VACUUM, right? I realize you're not claiming those columns are useful from an application perspective. Just curious to know if I'm wrong and they are useful. Because as I understand it, the selling point of Datomic is their audit trail functionality and that is admittedly a bit onerous to implement in a RBDMS. Even though I feel like every project needs/requires that eventually.
  - twic 4y ago
    Oracle lets you use the MVCC data to query past states of the database, called "flashback": https://docs.oracle.com/en/database/oracle/oracle-database/21/adfns/flashback.html https://docs.oracle.com/en/database/oracle/oracle-database/2... You can configure how long the old data is kept: https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/UNDO_RETENTION.html https://docs.oracle.com/en/database/oracle/oracle-database/2... Worked examples: http://www.dba-oracle.com/t_rman_149_flasbback_query.htm http://www.dba-oracle.com/t_rman_149_flasbback_query.htm
    - JohnBooty 4y ago
      Wow, super informative. Thank you so much.
  - ysleepy 4y ago
    I meant MVCC is the proof that you can automate the transform of a schema into a versioned schema. How and if the DBMS exposes that is another concern. The garbage collection / VACUUM part of an MVCC system is the harder part, saving all versions and querying a point in time is the easy one.

rehevkor5 4y ago

Maybe search around on bitemporal database table modeling.

pjot 4y ago

It’s called a slowly changing dimension. In this example, it’s a type-2. https://en.m.wikipedia.org/wiki/Slowly_changing_dimension https://en.m.wikipedia.org/wiki/Slowly_changing_dimension