Re: PG and Temporal - Mailing list pgsql-sql

From Steve Midgley
Subject Re: PG and Temporal
Date
Msg-id CAJexoSJX+LvKJPpXmMDwPuPE-xxx1nGcPNpKzcZB0gAJJRwtZA@mail.gmail.com
Whole thread Raw
In response to PG and Temporal  (Ravi Krishna <sravikrishna3@gmail.com>)
Responses Re: PG and Temporal  (Ravi Krishna <sravikrishna3@gmail.com>)
List pgsql-sql
On Tue, May 26, 2015 at 1:59 PM, Ravi Krishna <sravikrishna3@gmail.com> wrote:
Hi,

I am researching temporal (both system time and business time) in PG.

I got the following hits:

https://wiki.postgresql.org/wiki/SQL2011Temporal

http://pgxn.org/dist/temporal_tables/

Is there anything else I am missing.



I think there are a lot of theories as to how to make temporal table systems work. It hugely depends on your requirements. That said, the data warehouse community has built a kind of solution with dimension tables representing time, and fact tables aligning to those dimensions via relations. It makes certain temporal "grain size" problems much easier to solve (and usually faster). Though using Pg's date extraction functions I've gotten pretty amazing performance as well: basically creating on-demand time dimensions as needed.. Read Ralph Kimball's work on data warehousing for a good introduction.

This isn't the same as creating versioned tables per your references above but I hope will be useful in your research..

Steve 

pgsql-sql by date:

Previous
From: Shashwat Arghode
Date:
Subject: Re: [NOVICE] Drop or disable or bypass "_return" rule on select on a view.
Next
From: Ravi Krishna
Date:
Subject: Re: PG and Temporal