Re: PG and Temporal

On Tue, May 26, 2015 at 1:59 PM, Ravi Krishna <sravikrishna3(at)gmail(dot)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