Thread: Turn dead tuples into temporal data?
Hello there,
I am not a databse engineer but I always loved the beauty and sophistication of RDBMSes.
I love PostgreSQL and its community and I have also followed Datomic (BI temporal database) since its beginning.
Would it be possible,(within 1 or 2 releases of PostgreSQL), without having to change many of other things that PostgreSQL already does pretty well, to use the DEAD tuples as historical information for native BI-temporal capability in PostgreSQL?
The main idea (without me knowing much about the internals of PostgreSQL) is to keep the dead tuples as a mean to answer BITemporal queries...
If small changes can be done (maybe an ON/OFF Toggle) to enable this behavior per table, it might even free the BITemporal enabled table from needing Vaccum, at the expense of consuming MUCH more storage.
Specially for financial application BITemporality is an important feature, and if that is "native" to a DB like PostgreSQL it 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!
Thanks.
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!