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
begin:vcard fn:Chris Travers n:Travers;Chris email;internet:chris@xxxxxxxxxxxxxxxx x-mozilla-html:FALSE version:2.1 end:vcard
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
