X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 290863A47B2 for ; Thu, 20 Jan 2005 14:26:00 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 06214-09 for ; Thu, 20 Jan 2005 14:25:48 +0000 (GMT) Received: from relay.snowman.net (relay.snowman.net [66.92.160.56]) by svr1.postgresql.org (Postfix) with ESMTP id 9E5843A47AF for ; Thu, 20 Jan 2005 14:25:50 +0000 (GMT) Received: from ns.snowman.net (ns.snowman.net [10.10.0.2]) by relay.snowman.net (8.13.1/8.13.1/Debian-19) with ESMTP id j0KEPlo1017721 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Thu, 20 Jan 2005 09:25:47 -0500 Received: from ns.snowman.net (localhost [127.0.0.1]) by ns.snowman.net (8.13.1/8.13.1/Debian-19) with ESMTP id j0KEQ5Vp010029 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Thu, 20 Jan 2005 09:26:05 -0500 Received: (from sfrost@localhost) by ns.snowman.net (8.13.1/8.13.1/Submit) id j0KEQ3p1010027; Thu, 20 Jan 2005 09:26:03 -0500 Date: Thu, 20 Jan 2005 09:26:03 -0500 From: Stephen Frost To: Matt Casters Cc: pgsql-performance@postgresql.org Subject: Re: Message-ID: <20050120142603.GM10437@ns.snowman.net> Mail-Followup-To: Matt Casters , pgsql-performance@postgresql.org References: <16153.193.190.212.113.1106213675.squirrel@193.190.212.113> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="LvBAm8/CQHvFF6wS" Content-Disposition: inline In-Reply-To: <16153.193.190.212.113.1106213675.squirrel@193.190.212.113> X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/2.4.24ns.3.0 (i686) X-Uptime: 08:59:04 up 355 days, 8:54, 8 users, load average: 0.12, 0.18, 0.12 User-Agent: Mutt/1.5.6+20040907i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.018 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/271 X-Sequence-Number: 9961 --LvBAm8/CQHvFF6wS Content-Type: text/plain; charset=us-ascii Content-Disposition: inline * Matt Casters (Matt.Casters@advalvas.be) wrote: > 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) Rather familiar, yes... :) > 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) Quite a bit of data. There's one big thing to note here I think- Postgres will not take advantage of multiple CPUs for a given query, Oracle will. So, it depends on your workload as to how that may impact you. Situations where this will be unlikely to affect you: Your main bottle-neck is IO/disk and not CPU. You run multiple queries in parallel frequently. There are other processes on the system which chew up CPU time anyway. Situations where you're likely to be affected would be: You periodically run one big query. You run a set of queries in sequential order. > My questions to the list are: has this sort of thing been attempted before? If so, what where the > performance results compared to Oracle? I'm pretty sure it's been attempted before but unfortunately I don't have any numbers on it myself. My data sets aren't that large (couple million rows) but I've found PostgreSQL at least as fast as Oracle for what we do, and much easier to work with. > I've been reading up on partitioned tabes on pgsql, will the performance benefit will be > comparable to Oracle partitioned tables? In this case I would think so, except that PostgreSQL still won't use multiple CPUs for a given query, even against partitioned tables, aiui. > What are the gotchas? See above? :) Other issues are things having to do w/ your specific SQL- Oracle's old join syntax isn't supported by PostgreSQL (what is it, something like select x,y from a,b where x=%y; to do a right-join, iirc). > Should I be testing on 8 or the 7 version? Now that 8.0 is out I'd say probably test with that and just watch for 8.0.x releases before you go production, if you have time before you have to go into production with the new solution (sounds like you do- changing databases takes time anyway). > Thanks in advance for any help you may have, I'll do my best to keep pgsql-performance up to date > on the results. Hope that helps. Others on here will correct me if I misspoke. :) Stephen --LvBAm8/CQHvFF6wS Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFB7796rzgMPqB3kigRAiECAKCXPFaCtJYBLFCan8Z5AgFY9WvEMgCfTarI F2uLv5LUn5WmW00BMaetYkc= =AHNC -----END PGP SIGNATURE----- --LvBAm8/CQHvFF6wS--