pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Travers <chris@metatrontech.com>
To: Matt.Casters@advalvas.be, pgsql-general <pgsql-perform@postgresql.org>
To: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [PERFORM] DWH on Postgresql
Date: Sat, 22 Jan 2005 10:41:52 -0800
Message-ID: <41F29E70.40808@metatrontech.com> (raw)
In-Reply-To: <20050118213218.3F1CE22401E@asia.telenet-ops.be>
References: <20050118213218.3F1CE22401E@asia.telenet-ops.be>

Cross-posting to GENERAL for additional comment.

Matt Casters wrote:

> Hi,
>  
> I have the go ahead of a customer to do some testing on Postgresql in 
> a couple of weeks as a replacement for Oracle.
> The reason for the test is that the number of users of the warehouse 
> is going to increase and this will have a serious impact on licencing 
> costs. (I bet that sounds familiar)
>  
> We're running a medium sized data warehouse on a Solaris box (4CPU, 
> 8Gb RAM) on Oracle.
> Basically we have 2 large fact tables to deal with: one going for 400M 
> rows, the other will be hitting 1B rows soon.
> (around 250Gb of data)

I have heard of databases larger than 1TB on PostgreSQL.  Don't have 
much experience with them.  but here are thoughts that come to mind.

>  
> My questions to the list are: has this sort of thing been attempted 
> before? If so, what where the results?

If you search the archives (of the General list, I think) and you will 
be able to find people talking about databases much larger than this.  
More "look what PostgreSQL can do" rather than "I need help."

> I've been reading up on partitioned tabes on pgsql, will the 
> performance benefit will be comparable to Oracle partitioned tables?

I am not aware of any data to base such a comparison on.

> What are the gotchas? 

A few I can think of:  Cross-table indexes don't really work for 
constraing purposes, so you need to assume that only one table will be 
actively getting inserts/updates.  Secondly, you will probably need to 
consider the level of transparency you need.  If you need more 
transparency, you can do it with views, rules, etc. (or simply having on 
insert rules on your base table and inheriting new tables from it 
regularly).

Also, I have seen posts in the past regarding performance issues 
specific to Solaris.   You may want to research this too.

> Should I be testing on 8 or the 7 version?
>
8.  Has better cache management, meaning will likely perform better.

Hope this helps.  It is not a typical question on the list, but if you 
start running into issues, this is a good list to ask question on :-)

Best Wishes,
Chris Travers
Metatron Technology Consulting

Attachments:

chris.vcf (text/x-vcard, 125B) download begin:vcard fn:Chris Travers n:Travers;Chris email;internet:chris@metatrontech.com x-mozilla-html:FALSE version:2.1 end:vcard

view thread (2+ messages)

Message-ID: <41F29E70.40808@metatrontech.com>
Permalink:  ../41F29E70.40808@metatrontech.com/
Also on:    postgresql.org/message-id/41F29E70.40808@metatrontech.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: chris@metatrontech.com, pgsql-perform@postgresql.org, pgsql-general@postgresql.org
  Subject: Re: [PERFORM] DWH on Postgresql
  In-Reply-To: <41F29E70.40808@metatrontech.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox