4 ms·

It's about time – Approaching bitemporality

52 points by lichtenberger 4y ago

inhumantsar 4y ago
- dang 4y ago
  "Please don't pick the most provocative thing in an article or post to complain about in the thread. Find something interesting to respond to instead." "Please don't complain about tangential annoyances—things like article or website formats, name collisions, or back-button breakage. They're too common to be interesting." https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
raydiatian 4y ago
It looks like I might get flagged for this, please show me some love Dang, I’m only trying to be helpful to the author. > What is bitemporality? You have this as a header but I’m really bummed out that the article doesn’t have a sentence that begins with “bitemporality is..” I think some illustrations to accompany your ideas might go a long way as well, as I still have no idea what bitemporality is in this scenario, although it sounds cool. > The “bi” in bitemporal database hints at the fact, that for the use case described above we have two different times we’d like to track: On one axis we have the validity of the data. I genuinely don’t mean to be rude, only to provide constructive feedback. This section kind of broke my flow, because you’re implying that validity is a form of time: “ two different times we’d like to track: On one axis we have the validity.” Is that intended?
- lichtenberger 4y ago
  There's the valid time on the one hand when something is valid in the real world and the transaction time, the time at which it's known to the database system, when it's recorded/committed.
- refset 4y ago
  > I think some illustrations to accompany your ideas might go a long way as well I'm not the OP, but I built this visual tool recently which might be of interest https://bitemporal-visualizer.github.io/ https://bitemporal-visualizer.github.io/
  - raydiatian 4y ago
    A picture really is worth a thousand words. I totally get what bitemporality means now.
- javahippie 4y ago
  Author here. Thanks for pointing this out, I obviously did not manage to provide a good definition of the term and will update the section accordingly
  - alexvoda 4y ago
    A calendar app is a database that tracks app time. Git is a database that tracks system time. A calendar on git is bitemporal.
daurnimator 4y ago
Note that Postgres has built in range types which makes much of this cleaner. See https://www.postgresql.org/docs/15/rangetypes.html#RANGETYPES-BUILTIN https://www.postgresql.org/docs/15/rangetypes.html#RANGETYPE...
- lichtenberger 4y ago
  I guess Datomic is the most mature system, which uses a persistent in-memory index structure, mapped to a common on-disk storage system. That said, nowadays it should probably be possible to store the persistent data structure in a log-structured file for instance and to make use of fast random-reads of enterprise PCIe SSDs without the indirection of another data store / index to traverse for each index page.
laerus 4y ago
Isn't this the use case for event sourcing?
- javahippie 4y ago
  Many database products with built in bitemporality support use event sourcing under the hood, yes.
- lichtenberger 4y ago
  I think this is a good (short) read about the relation: https://event-driven.io/en/temporal_tables_and_event_sourcing/ https://event-driven.io/en/temporal_tables_and_event_sourcin...
- lichtenberger 4y ago
  Ideally the temporal database system stores both the state after the modification as well as the events/changes which lead on to the new state.
denton-scratch 4y ago
It's About Time is the name of the second album by Tonto's Expanding Head Band: Malcolm Cecil and Robert Margouleff (and the biggest custom Moog synthesizer you ever saw).
layer8 4y ago
One important caveat when implementing this manually (as opposed to using a temporal DBMS) is foreign-key constraints, which have to be emulated with check constraints, and not all DBMSs support cross-table check constraints. Furthermore, even with such constraints in place, I believe it can still happen that concurrent modifications of parent and child rows may result in an inconsistent state, because the reads e.g. on the child table for the constraint triggered by an update/insert on the parent table may see an outdated state that is in the process of being updated by a concurrent transaction on the child table, and vice versa. It is very difficult, if not impossible, to implement airtight constraint checks for bitemporal data across tables if the DBMS doesn’t have native support for temporal tables.
_boffin_ 4y ago
If anyone is looking for a good book on this subject, check out https://www.amazon.com/Developing-Time-Oriented-Database-Applications-Management/dp/1558604367 https://www.amazon.com/Developing-Time-Oriented-Database-App...

52 points by lichtenberger 4y ago

dang 4y ago
"Please don't pick the most provocative thing in an article or post to complain about in the thread. Find something interesting to respond to instead." "Please don't complain about tangential annoyances—things like article or website formats, name collisions, or back-button breakage. They're too common to be interesting." https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html

It looks like I might get flagged for this, please show me some love Dang, I’m only trying to be helpful to the author. > What is bitemporality? You have this as a header but I’m really bummed out that the article doesn’t have a sentence that begins with “bitemporality is..” I think some illustrations to accompany your ideas might go a long way as well, as I still have no idea what bitemporality is in this scenario, although it sounds cool. > The “bi” in bitemporal database hints at the fact, that for the use case described above we have two different times we’d like to track: On one axis we have the validity of the data. I genuinely don’t mean to be rude, only to provide constructive feedback. This section kind of broke my flow, because you’re implying that validity is a form of time: “ two different times we’d like to track: On one axis we have the validity.” Is that intended?

lichtenberger 4y ago
There's the valid time on the one hand when something is valid in the real world and the transaction time, the time at which it's known to the database system, when it's recorded/committed.
refset 4y ago
> I think some illustrations to accompany your ideas might go a long way as well I'm not the OP, but I built this visual tool recently which might be of interest https://bitemporal-visualizer.github.io/ https://bitemporal-visualizer.github.io/
- raydiatian 4y ago
  A picture really is worth a thousand words. I totally get what bitemporality means now.
javahippie 4y ago
Author here. Thanks for pointing this out, I obviously did not manage to provide a good definition of the term and will update the section accordingly
- alexvoda 4y ago
  A calendar app is a database that tracks app time. Git is a database that tracks system time. A calendar on git is bitemporal.

daurnimator 4y ago

Note that Postgres has built in range types which makes much of this cleaner. See https://www.postgresql.org/docs/15/rangetypes.html#RANGETYPES-BUILTIN https://www.postgresql.org/docs/15/rangetypes.html#RANGETYPE...

lichtenberger 4y ago
I guess Datomic is the most mature system, which uses a persistent in-memory index structure, mapped to a common on-disk storage system. That said, nowadays it should probably be possible to store the persistent data structure in a log-structured file for instance and to make use of fast random-reads of enterprise PCIe SSDs without the indirection of another data store / index to traverse for each index page.

laerus 4y ago

Isn't this the use case for event sourcing?

javahippie 4y ago
Many database products with built in bitemporality support use event sourcing under the hood, yes.
lichtenberger 4y ago
I think this is a good (short) read about the relation: https://event-driven.io/en/temporal_tables_and_event_sourcing/ https://event-driven.io/en/temporal_tables_and_event_sourcin...
lichtenberger 4y ago
Ideally the temporal database system stores both the state after the modification as well as the events/changes which lead on to the new state.

denton-scratch 4y ago

It's About Time is the name of the second album by Tonto's Expanding Head Band: Malcolm Cecil and Robert Margouleff (and the biggest custom Moog synthesizer you ever saw).

layer8 4y ago

One important caveat when implementing this manually (as opposed to using a temporal DBMS) is foreign-key constraints, which have to be emulated with check constraints, and not all DBMSs support cross-table check constraints. Furthermore, even with such constraints in place, I believe it can still happen that concurrent modifications of parent and child rows may result in an inconsistent state, because the reads e.g. on the child table for the constraint triggered by an update/insert on the parent table may see an outdated state that is in the process of being updated by a concurrent transaction on the child table, and vice versa. It is very difficult, if not impossible, to implement airtight constraint checks for bitemporal data across tables if the DBMS doesn’t have native support for temporal tables.

_boffin_ 4y ago

If anyone is looking for a good book on this subject, check out https://www.amazon.com/Developing-Time-Oriented-Database-Applications-Management/dp/1558604367 https://www.amazon.com/Developing-Time-Oriented-Database-App...