Re: [PERFORM] DWH on Postgresql

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