pg_bitemporal

Do you have any plans/aspirations in adding Temporal "immutable DB" functionality? [0] https://en.wikipedia.org/wiki/Temporal_database [1] https://github.com/scalegenius/pg_bitemporal/blob/master/doc... [2] https://github.com/ifad/chronomodel

There are also other way, such as bitemporal tables. - https://aiven.io/blog/two-dimensional-time-with-bitemporal-d... - https://github.com/scalegenius/pg_bitemporal 4 timestamps and some ugly queries.

People here may also be interested to see this analysis of the state of SQL:2011 "temporal table" feature adoption: https://illuminatedcomputing.com/posts/2019/08/sql2011-surve... I don't think much has really changed since, and I'm not sure Postgres is any closer to addressing this natively (although there have been extensions, e.g. https://github.com/scalegenius/pg_bitemporal).

There’s also temporal modeling or bitemporal modeling. It’s verbose and can make table length pretty large, but eh.. - https://martinfowler.com/articles/bitemporal-history.html - https://hdombrovskaya.files.wordpress.com/2016/09/bi_tempora... - https://github.com/scalegenius/pg_bitemporal

One of these Postgres-based implementations of SQL:2011's temporal versioning might get you close enough: - https://github.com/nearform/temporal_tables - https://github.com/xocolatl/periods - https://github.com/scalegenius/pg_bitemporal - https://github.com/arkhipov/temporal_tables I haven't used any of them but I work on https://xtdb.com which is also implementing SQL:2011's temporal features :)

How does this compare to https://github.com/scalegenius/pg_bitemporal ?

Sure, I can appreciate that native support for a feature like this is nice. As I understand it, most implementations (including another one for bitemporality[1]) involve either audit tables, as you mention, and/or additional support columns. It's as if the "now" representation is simply a narrowed view within the full set of underlying, (bi)temporal data. That said, PostgreSQL encodes and has battle-tested decades of database functionality, including a surrounding ecosystem, so I'd be a little wary of switching technology even if it does solve one individual problem thoroughly. Everything has to start somewhere, though. [1] - https://github.com/scalegenius/pg_bitemporal

Check out our framework for bitemporlity: https://github.com/scalegenius/pg_bitemporal We use it in production for 5 years. I am ready to answer any performance/indexing and other questions

> and the database for everyone else I also love postgresql but lets not get ahead of ourselves! It's great for SQL and as a traditional relational database, but there are plenty of use cases that are better served using other models (especially if you care about _time_ as a concept, then Datomic or crux is probably better suited) [although there are attempts on making bitemporality accessible in postgresql too, like https://github.com/scalegenius/pg_bitemporal]

Had no idea until recently that MariaDB supported this out of the box. Does anyone have experience using this? How does it compare to https://github.com/scalegenius/pg_bitemporal ?

Check out https://github.com/arkhipov/temporal_tables or https://github.com/scalegenius/pg_bitemporal

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. Our code is here: https://github.com/scalegenius/pg_bitemporal

Anyone know whether the [pg_bitemporal](https://github.com/scalegenius/pg_bitemporal) project is still being developed? There's a talk on YouTube which showed promise but the Github repo seems pretty dead.