Re: PostgreSQL for Data Warehouse

Hi,

i'm actually evaluating different dbms for data warehouse tasks.
has anybody expirience with it?

i have to store 50 mio. records p.m. in a fact table.
something like this:

date; cust; group; product; value

...with unique index on date,cust,group,product.

will there be any problem's in response time in let's say 12 months?

thank you very much.

On Thu, Oct 2, 2008 at 6:28 PM, Jasmin Dizdarevic
<jasmin(dot)dizdarevic(at)gmail(dot)com> wrote:
> Hi,
>
> i'm actually evaluating different dbms for data warehouse tasks.
> has anybody expirience with it?
>
> i have to store 50 mio. records p.m. in a fact table.
> something like this:
>
> date; cust; group; product; value
>
> ...with unique index on date,cust,group,product.
>
> will there be any problem's in response time in let's say 12 months?

It totally depends on hardware, the queries you will be running, under
what load, and what you mean by "response time". If I were you, I
would simply simulate your 600 m rows and then run the queries that
you want to see how the system performs.

Sean

Am 2008-10-03 00:28:40, schrieb Jasmin Dizdarevic:
> Hi,
>
> i'm actually evaluating different dbms for data warehouse tasks.
> has anybody expirience with it?
>
> i have to store 50 mio. records p.m. in a fact table.
> something like this:
>
> date; cust; group; product; value

I have not a warehouse but a WAR database where I had for 100 years one
table... The table from the last century had let me re-thinking...

2.5 mio rows with arround 240 columns and 43 languages had killed all.

It was a SINGEL table of arround 800 MByte.

Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant

--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack Apt. 917 ICQ #328449886
+49/177/9351947 50, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)

Hmm,

I'm seeing about 20M facts/day input into the system I work on (and
we've been going for a few years now) and our average response time is
pretty snappy (<15s for the most common use cases).

IIRC, the online part of the database is ~2.5-3TB right now.

-Mark

On Fri, 2008-10-03 at 00:28 +0200, Jasmin Dizdarevic wrote:
> Hi,
>
> i'm actually evaluating different dbms for data warehouse tasks.
> has anybody expirience with it?
>
> i have to store 50 mio. records p.m. in a fact table.
> something like this:
>
> date; cust; group; product; value
>
> ...with unique index on date,cust,group,product.
>
> will there be any problem's in response time in let's say 12 months?
>
> thank you very much.
>

Hi Mark,

thank you for your information. That sound's great!
May I know your hardware configuration on which this database is running?

Jasmin

On Wed, Oct 8, 2008 at 11:21 PM, Mark Roberts <mailing_lists(at)pandapocket(dot)com
> wrote:

> Hmm,
>
> I'm seeing about 20M facts/day input into the system I work on (and
> we've been going for a few years now) and our average response time is
> pretty snappy (<15s for the most common use cases).
>
> IIRC, the online part of the database is ~2.5-3TB right now.
>
> -Mark
>
> On Fri, 2008-10-03 at 00:28 +0200, Jasmin Dizdarevic wrote:
> > Hi,
> >
> > i'm actually evaluating different dbms for data warehouse tasks.
> > has anybody expirience with it?
> >
> > i have to store 50 mio. records p.m. in a fact table.
> > something like this:
> >
> > date; cust; group; product; value
> >
> > ...with unique index on date,cust,group,product.
> >
> > will there be any problem's in response time in let's say 12 months?
> >
> > thank you very much.
> >
>
>

--
Mit freundlichen Grüßen

Dizdarevic Jasmin
Sonnenbergstr. 3
6714 Nüziders, AUT

jasmin(dot)dizdarevic(at)gmail(dot)com
+43 664 411 79 29

Hmmm, I'm just a developer and not in charge of hardware or pg
configuration and don't know any specifics, but:
- 4 Cores
- Generous helping of RAM
- Tons of disk space because of lots of "low" performance disks
- ~600MB/s Seq I/O, ~150MB/s random

-Mark

On Wed, 2008-10-08 at 23:29 +0200, Jasmin Dizdarevic wrote:
> Hi Mark, thank you for your information. That sound's great! May I
> know your hardware configuration on which this database is running?
> Jasmin
>