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
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