As we are working on complex trading systems it is frequently necessary to correct past record and then be able to make consistent reports based on "as of" date. This means answering questions like "I want to get the state of this population of trades as it was yesterday at 5 pm but based on our knowledge yesterday at 10 pm."<p>Pretty fun actually when you have billions of these records:)
Shout out to Crux (<a href="https://opencrux.com/" rel="nofollow">https://opencrux.com/</a>), a bitemporal database. I've been using it for a year now and it's fantastic.
Check out our framework for bitemporlity:
<a href="https://github.com/scalegenius/pg_bitemporal" rel="nofollow">https://github.com/scalegenius/pg_bitemporal</a>
We use it in production for 5 years. I am ready to answer any performance/indexing and other questions
I have not read the article, but I have written a bi-temporal ORM, and I find it to be such a useful concept in practice. One of the primary benefits is optimistic locking. You can say goodbye to explicitly locking during a db transaction.
I ran across this notion for the first time when I started a job in finance a few years back and as an interview question they asked me about it. I hadn't heard of the notion and so afterwards tried to find some good resources online to read up about it.<p>What I found was not great. So glad to see this idea getting a good write up.
Reminds me of rebasing Git branches in order to clean up commit histories, or rewriting Git repositories in order to change usernames/emails stored inside.<p>I just hope that whatever systems get created (bitemporal history, version control, etc.) have support for "replacing" previous usernames, or deleting events prior to a specific "record time" and replacing them with newer understandings of history. Doing so in Git (a more-or-less append-only system) results in multiple histories which diverge at the moment of the change.
Related to bitemporality, I'd be curious to hear whether anyone has experience with the Temporal Tables[1] extension in PostgreSQL?<p>[1] - <a href="https://github.com/arkhipov/temporal_tables" rel="nofollow">https://github.com/arkhipov/temporal_tables</a>