Re: Strategies/Best Practises Handling Large Tables

From: Shaun Thomas <sthomas(at)optionshouse(dot)com>
To: Chitra Creta <chitracreta(at)gmail(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Strategies/Best Practises Handling Large Tables
Date: 2012-10-16 16:21:40
Message-ID: 507D8994.70309@optionshouse.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 10/12/2012 09:44 AM, Chitra Creta wrote:

> 4. Creation of daily, monthly, yearly summary tables that contains
> aggregated data specific to the statistics required

If the partitioning doesn't help you, I strongly urge you to build one
or more Fact tables with appropriate Diminsions. If you don't know what
these terms mean, there are lots of books on the subject. They're very
versatile for producing fast reports on varying inputs. You can also
"layer" them by having cascading levels of aggregation from day -> month
-> year, and so on.

These kinds of reporting structures are perfect for huge data
accumulation warehouses. The book most recommended to me back in the day
was The Data Warehouse Toolkit, and I can also vouch that it covers
these subjects pretty well.

I still recommend partitioning simply due to maintenance overhead, but
you'll want to look into this too.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Thalis Kalfigkopoulos 2012-10-16 17:53:14 Re: PostgreSQL training recommendations?
Previous Message Matthew Kappel 2012-10-16 15:23:43 PostgreSQL training recommendations?