Tag Archives: bitemporal

More Updates on the State of Bitemporality

A month ago, I’ve proudly announced that I was able to modify bitemporal correction to allow correction of any effective interval of past and present, not just the current one. I was planning to embed this capability into the regular bitemporal correction, but after a couple of weeks of hesitation I decided against it.

The reason is very egotistical :). At Braviant Holdings, we use bitemporal correction in our production application, and the response time is critical. I already know that the generalized case of bitemporal correction is slightly slower due to the additional checks, and I am not going to risk the performance of what’s already in place. This being said, it shouldn’t make a significant difference for those who is using pg_bitemporal at their own risk, since the difference in the execution time is just “times two,” so if your original correction is optimized to 0.8 sec, the new one may be 1.7 sec. Makes difference for us, though.

Howeer, the function is out there and available, so if you want to try it out, check out ll_bitemporal_correction_hist.

Another update is relate to my attempts to create a barrier for straight insert/update to bitemporal tables (without compromising performance).

My original idea, that it would be possible to revoke  simple INSERT/UPDATE/DELETE from bitemporal tables and make bitemporal operations SECURITY DEFINER won’t work.


The reason is that if we create bitemporal functions as SECURITY DEFINER, and definer is a database owner, then when we grant any user permission to execute this bitemporal function on ANY bitemporal table. That means, we won’t be able to restrict access to specific tables, because we can’t grant execution based on the function parameters (again, theoretically possible, but requires creating additional objects)Now I am back to the trigger idea again.  What could be theoretically done is to have INSTEAD triggers, which would just disable insert/update/delete and then to disable these triggers inside functions. But this again requires a higher level of permissions. 

Obviously, anybody who want to instill this level of data safeguarding, can create their own aliased to bitemporal functions, related to specific tables or schemas, but that will be a custom build, not anything I can provide generically.

At the moment I am out of new ideas, just wanted to state it officially. And if anybody is interested in how to create these custom-built functions – please feel free to reach out!

pg_bitemporal public repo can be found here.

Leave a comment

Filed under Data management, SQL

New Bitemporal Correction

It might take several more days, till the new ll_bitemporal_correction function will appear in pg_bitemporal GitHub repo, but it is ready. I finished development and basic testing, and it works.

It means nothing for those who do not care about “the state of bitemporality”, but I know that some people care. For those people, I am happy to report, that finally I delivered what you guys have asked for a long time. Now, you can make bitemporal correction not just to the last effective interval, but to any stretch of time. It may cover several historical intervals, it may start in the middle. It can’t end i the middle, because I didn’t come up with any business case for such situation, but if there will be a demand, I am sure I can provide :). Overall – I am very happy about this result :). Stay tunes – I will post when it will be officially available.

On a different note – we are almost P12 compatible, only bitemporal referential integrity still does not work, but it will be fixed shortly.

Huge thank you for everybody who show interest in this activity:)

2 Comments

Filed under news, SQL

Funny thing happened…

I have to share that:). I had a ticket to develop one operational report. Not only the report requirements were complex, but also it was very difficult to debug. We just started to collect the data required for this report, and we do not have enough of it to cover all potential issues. Moreover, since the data volumes are so small, the issues are resolved fast, so in two days, I never had a chance to catch a single case of exception. Until this morning. When a thought suddenly came to my mind, and I asked myself: Hettie, why in the world you are waiting for an exception to happen in real-time?! All your tables are bitemporal, so you can time-travel to any moment of the past, including the time, when exceptions occurred!

It’s funny and not funny that it took me two days to figure this out! Especially because I was the one who introduced bitemporality!

Worked as expected 🙂

Leave a comment

Filed under Data management, Development and testing

About My Nomination, And How To Vote

First of all, a big THANK YOU to everybody who reached out congratulating me for becoming a finalist in the “Technologist of the Year” nomination. This nomination is especially important for me, because I’ve always strived to apply the best CS theories for the success of the business. I do not believe in approaches, which can’t be used in practice. However, I think that applying the right theoretical principles in the industry can have a tremendous impact.

Another aspect important to me is that all my innovations are related to PostgreSQL. If I were asked to name the three most important things which I’ve introduced at Braviant Holdings, it would be

  • The wide usage of FDW both in OLAP and OLTP
  • The usage of pg_bitemporal in both OLAP and OLTP
  • Abandoning ORM and using JSON -based data exchange between applications and databases

There is more in my blog about all of the above, but what I want to point now – each of these Top 3 is about using PostgreSQL in an innovative way.

The award descriptions say:

Presented to the individual whose talent has championed true innovation, either through new applications of existing technology or the development of technology to achieve a truly unique product or service.

Isn’t it precisely what I just said :)? Do I want to win? Absolutely! Do I think I can win? Yes! Can you help me :)?…

Several people reach out to me, telling me that they have difficulties casting their votes. I agree that the voting process is at least contra-intuitive. So let me explain it step by step.

First, you go to that link.

Then, click where it is said to CREATE LOGIN. It says that you can login with your Facebook account, but this does not work. So you will need to create a login. After that, you need to click on the large grey “Like” on the very top. Wait for a response to make sure your vote is counted.

Also, there are SHARE buttons, and unfortunately, the most important one – Share on LinkedIn – does not work. Others work fine, so you can help me by sharing with your network 🙂

And one more thing – this voting is only opened till August 16, so please don’t delay 🙂

Once again – THANK YOU!

2 Comments

Filed under events, news, Systems

Let’s Go Bitemporal!

Dear friends and followers from the Postgres community! Today, let’s talk more about the bitemporal library (as if I did not speak enough about it yet!).

We have been developing Postgres functions, which support bitemporal operation for almost four years by now. We have found our initial inspiration in the Asserted Versioning Framework (AVF), first introduced by Jonson and Weiss nearly twenty years ago. There is nothing new in the concept of incorporating time dimension into data, and even the concept of two-dimensional time is not new. However, we believe that AVF approaches the task in the best possible way and that it allows making the time a true and integral part of data.

We believe that Postgres is suited the best to support a two-dimensional time due to the tow factors: the presence of the interval types and GIST with exclusion constraints. Having these two available made the process of implementation of the concept more or less trivial.

Implementation of bitemporal operations took some time, though, and we are still in the process of improving some of the functions. However, we are happy to share with the world, that Bravinat Holdings runs both OLTP and OLAP databases on the bitemporal framework with no performance degradation. Since we had an opportunity to develop as we go, we could address lots of issues in this implementation, which we initially did not even expect.
Recently we have uploaded several files into the docs section of the pg_bitemporal GitHub repo, including several presentations and short papers so that those who are interested can read more on the theory of bitemporality. We hope that people will give it a try – it works! Also, we are always looking for volunteers who will be interested in collaboration.

Please check us out at https://github.com/scalegenius/pg_bitemporal

Leave a comment

Filed under research, SQL, Systems

Bitemporal documentation is available!

Everybody who was curious enough to start using our pg_bitemporal github repo would complain about the lack of documentation, so we’ve tried really hard to provide our follows with some guidance.

What we have now, is very far from perfect, but if you go to the docs directory, there is a lot of documentation, including our old presentations, explanations of the basic bitemporal concepts and most importantly first ever bitemporal functions manual, which we promise to make more readable in the nearest future. Meanwhile – please share your feedback! Thank you!

Leave a comment

Filed under Data management, research

New features are available in the bitemporal repo – and I am so happy about it!

I Really hope that most of my follows know something about the pg_bitemporal project, because if you didn’t hear about it, you won’t be able to share my excitement!

We started to build our bitemporal library for PostgreSQL about four years ago, it was merely a “proof of concept”, and Chad Slaughter, who initiated all this work, knowing my work habits way too well, was re-iterating again and again – do not optimize it yet!

Well, I didn’t, but then I’ve joined Braviant Holdings, and a year later I was granted a permission to use our bitemporal framework in production. Some of the performance flaws became apparent even during the test run, and I was able to fix them. Later, while we were using it in production more and more, I’ve come up with new functions, UPDATE_SELECT and CORRECT_SELECT, since we actually needed them, and since the bitemporal operations were supposed to behave the same way as regular database operations.

About three weeks ago we had a very important release, which along with addressing multiple business needs, included some significant changes on the technical side. One of the consequences was, that it significantly increased the traffic on our new planform, and as a result we started to see some timeouts.

Although these timeouts were pretty rare, we saw them as a problem. I personally pledged the system will remain scalable, and now I couldn’t just go with “bitemporal updates are slow”. Yes, the execution time was at 2 to 3 seconds most of the time, but sometimes it would spike, and our microservices have a hard timeout at 10 seconds.

Some time ago I’ve already mentioned in this blog, how thankful I am for those timeouts! Nothing else foster innovation more than a necessity to address performance problems immediately, because they have a direct impact on production.

This time around I was 99.9% sure that the periodic slowness happens during the remote query, which is a part of the problematic function. Turned out, though, that this 0.01% was the case, and together with our DB team we were able to determine, that the problematic statement was the last UPDATE in the bitemporal update function. If you’d ask me a week before that, I would say, that I am not going to address the bitemporal performance for the next several months, but I had no choice.

Thanks to Boris Novikov, who helped me immensely in testing and verifying several different approaches, and eventually identified the best one, and to Chad Slaughter, who was merging my commits from 7-30 AM to 9-30 PM, so that the master branch of the bitemporal library would have the latest updates by the time of the release, and thanks to our amazing QA team, who had to run and rerun tests that day multiple times, the new bitemporal functions are now on place. Not only for Braviant Holdings, but for the whole community.

I would also like to mention, that since I was already changing the functions, I’ve fixed one long-overdue issues: all functions have versions, which are PG 10 compliant. We’ve left the old versions there, because some of the are used in the existing production systems but if you are just starting, you can use the new ones.

Check it out at https://github.com/scalegenius/pg_bitemporal

Leave a comment

Filed under news, research, SQL, Team and teamwork