Re: Turn dead tuples into temporal data?

From: SQL Padawan <sql_padawan(at)protonmail(dot)com>
To: Jean Baro <jfbaro(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Turn dead tuples into temporal data?
Date: 2021-11-26 15:00:38
Message-ID: kC6pTIPGGAm4NBV2RCqXtI7OzCG0QLAZDo2MNQxLmqXqPD0TwkeOeFK30h4MQUw-Vzxb7KOrqQKggQfEJi_t5jQ8WNasCM_KMWSkqI7n0gM=@protonmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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 BI in 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 PostgreSQL already does pretty well, to use the DEAD tuples as historical information for native BI-temporal capability in PostgreSQL?

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 answer BITemporal 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 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!

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!

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message SQL Padawan 2021-11-26 16:35:30 Simple question about running a function.
Previous Message Tom Lane 2021-11-23 15:37:37 Re: BI Temporality in PostgreSQL