pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alex Turner <armtuk@gmail.com>
To: Matt Casters <Matt.Casters@advalvas.be>
To: pgsql-performance@postgresql.org
Subject: Re:
Date: Thu, 20 Jan 2005 11:31:29 -0500
Message-ID: <33c6269f050120083177fa0e83@mail.gmail.com> (raw)
In-Reply-To: <20050120142603.GM10437@ns.snowman.net>
References: <16153.193.190.212.113.1106213675.squirrel@193.190.212.113>
	<20050120142603.GM10437@ns.snowman.net>

I am curious - I wasn't aware that postgresql supported partitioned tables,
Could someone point me to the docs on this.

Thanks,

Alex Turner
NetEconomist


On Thu, 20 Jan 2005 09:26:03 -0500, Stephen Frost <sfrost@snowman.net> wrote:
> * 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
> 
> 
>



view thread (15+ messages)  latest in thread

Message-ID: <33c6269f050120083177fa0e83@mail.gmail.com>
Permalink:  ../33c6269f050120083177fa0e83@mail.gmail.com/
Also on:    postgresql.org/message-id/33c6269f050120083177fa0e83@mail.gmail.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: armtuk@gmail.com, Matt.Casters@advalvas.be
  Subject: Re:
  In-Reply-To: <33c6269f050120083177fa0e83@mail.gmail.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