From my humble experience, performance of such views will be suboptimal. When we started our implementation of AVF (Johnson and Weis), we just decided to believe the authors, that people only "think" that storing all of the history in the same table as current data will make the said tables growing too fast and inefficient, that people just presume and do not try. So we decided to believe and to try, and it worked.
Our implementation is based on GIST with exclusion, and we have it in production for over a year by now. Zero performance problems, lots of gains from the business perspective.
That’s awesome! I’m inclined to agree with you, that our solution’s suboptimal. We’re using btree_gist, plpgsql (fun), and hstore as extensions, if that tidbit is interesting to you.
Another totally valid complaint of our implementation is that it makes Schema migrations a major pain. I wasn’t around when we made our extension, but if I had been, I probably would have recommended using yours :). I suspect that we did it our way because it was some combination of (1) the most straightforward to migrate our existing data towards (2) it was the easiest to hack together.
Anyways, thank you for sharing your project! I’m excited to use it at home, and maybe in the future at work!
I am shamelessly promoting it, because we have no bandwith to make it to the extension, and for the past almost three years we've been promised by several people/organizations to create an extension or even to incorporate it into "next" version of Postgres (and I am not pointing fingers :)). Still hoping somebody will be willing to work with us to make it happen. First time I talked about it with Bruce exactly 3 years ago, he told me there should be a proven need. So... we have a business case :). but still ...
We definitely have a big business case (>$1bn/year flows through our bitemporal data). Happy to vouch for you if I can. I work in the insurance industry, and daily face questions like:
- What was X's address 2 years ago?
- What did our database think X's address was 2 years ago, 3 months ago?
It's fundamentally impossible to answer those kinds of queries in a scalable way (parsing application logs is not a scalable way) without bi-temporal data.
I'd readily acknowledge that there are some types of data, for which having the capability to answer those queries is overkill. But, in the insurance industry, this kind of introspection is essential from both a compliance and correctness point of view.
Personally, if I were an auditor/government regulator for this industry, I would raise a big red flag over any critical data that wasn't bitemporal.
I would definitely vouch for its business necessity – so please, keep up your advocacy!! :)
Our implementation is based on GIST with exclusion, and we have it in production for over a year by now. Zero performance problems, lots of gains from the business perspective.
Our code is here: https://github.com/scalegenius/pg_bitemporal