It's about time – Approaching bitemporality | Hacker News Reader

It's about time – Approaching bitemporality

javahippie.net

52 pointslichtenberger16 comments

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?

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.

> 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/

A picture really is worth a thousand words. I totally get what bitemporality means now.

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

A calendar app is a database that tracks app time.

Git is a database that tracks system time.

A calendar on git is bitemporal.

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

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.

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.

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).

Isn't this the use case for event sourcing?

Many database products with built in bitemporality support use event sourcing under the hood, yes.

I think this is a good (short) read about the relation: https://event-driven.io/en/temporal_tables_and_event_sourcin...

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.

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

Reply on news.ycombinator.com