Calendar tables in PostgreSQL 9

database-design postgresql data-warehouse rails-postgresql
NJ. picture NJ. · Apr 28, 2011 · Viewed 8.7k times · Source

I am building an analytics database (I have a firm understanding of the data and the business objectives and only basic-to-moderate database skills).

I have come across some references to building similar warehouses which implement the concept of 'calendar tables'. This makes sense and is easily enough done. Most examples I see, however, are calendar tables that limit scope to 'day'. My data will need to be analyzed down to hour-level. Possibly minutes.

My question: would an implementation of calendar tables for hour/minute-level granularity be of value in terms of space-efficiency and query/sorting speed? If so, can you recommend a table structure and population method/example?

My primary data table will contain 20+ million rows of data at any given time and typical subsets for analysis are in the 1 to 5 million range. So, as you can see, that is a lot of timestamp fields.

Answer

Quassnoi picture Quassnoi · Apr 28, 2011

In PostgreSQL, you can generate calendar tables of arbitrary length and granularity on the fly:

SELECT  CAST('2011-01-01' AS DATE) + (n || ' hour')::INTERVAL
FROM    generate_series(0, 23) n

This does not require recursion (as with the other systems) and is a preferred method to generate the volatile resultsets.

Related questions

what is the right data type for unique key in postgresql DB?

which data type should I choose for a unique key (id of a user for example) in postgresql database's table? does bigint is the one? thanks

Read More
Grant all on a specific schema in the db to a group role in PostgreSQL

Using PostgreSQL 9.0, I have a group role called "staff" and would like to grant all (or certain) privileges to this role on tables in a particular schema. None of the following work GRANT ALL ON SCHEMA foo TO staff; GRANT …

Read More
Create unique constraint with null columns

I have a table with this layout: CREATE TABLE Favorites ( FavoriteId uuid NOT NULL PRIMARY KEY, UserId uuid NOT NULL, RecipeId uuid NOT NULL, MenuId uuid ) I want to create a unique constraint similar to this: ALTER TABLE Favorites ADD …

Read More