Hi Jean,
> I love PostgreSQL and its community and I have also followed Datomic (BI temporal database) since its beginning.
Datomic certainly is interesting - a very different data model!
BTW, it's Bi-Temporal (or bi-temporal or whatever), but not BI - it's not Business Intelligence - the normal meaning of
BIin ICT, here "Bi" refers to two!
> Would it be possible,(within 1 or 2 releases of PostgreSQL), without having to change many of other things that
PostgreSQLalready does pretty well, to use the DEAD tuples as historical information for native BI-temporal capability
inPostgreSQL?
This should be of interest to you:
https://blog.dbi-services.com/temporal-tables-for-postgresql-15/
But, there are no guarantees.
> The main idea (without me knowing much about the internals of PostgreSQL) is to keep the dead tuples as a mean to
answerBITemporal queries...
There is Vlad Arkhipov's extension, which is v. limited.
https://pgxn.org/dist/temporal_tables/
Near Form have an interesting approach (never used):
https://www.nearform.com/blog/time-travel-with-postgresql-on-amazon-rds/
and
https://github.com/xocolatl/periods (never tried).
Have fun! :-)
> Specially for financial application BITemporality is an important feature, and if that is "native" to a DB like
PostgreSQLit would put PG ahead of the game.
> Sorry for the "dream" question here, I just want someone experienced to explain to me why this is a terrible ideal in
PostgreSQL..só that I can learn more!
I agree with you - it's a great feature to have at one's fingertips without having to set up triggers &c.
It appeared in SQL 2011!
rgs,
SQLP!
