Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 14708475FD9 for ; Thu, 30 Jan 2003 14:14:05 -0500 (EST) Received: from localhost.localdomain (unknown [65.217.53.66]) by postgresql.org (Postfix) with ESMTP id 2D362475F1C for ; Thu, 30 Jan 2003 14:14:04 -0500 (EST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by localhost.localdomain (8.12.5/8.12.5) with ESMTP id h0UJaH6P020140; Thu, 30 Jan 2003 14:36:17 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id h0UJE1j28863; Thu, 30 Jan 2003 14:14:02 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id CWVLCV3C; Thu, 30 Jan 2003 14:14:01 -0500 Subject: Re: One large v. many small From: Robert Treat To: Noah Silverman Cc: pgsql-performance@postgresql.org In-Reply-To: <176C96F6-3480-11D7-A3A3-000393AA8F3C@allresearch.com> References: <176C96F6-3480-11D7-A3A3-000393AA8F3C@allresearch.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 30 Jan 2003 14:14:01 -0500 Message-Id: <1043954041.2644.59.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/394 X-Sequence-Number: 1041 I'm going to go against the grain here and say that if you already have all of the code and schema worked out, you probably should stick with the many table design. While there are many reasons you'd be better off with the one big table design, a speed increase really isn't one of them. If you we're starting from scratch, or even had a slew of development work you we're planning to do, I'd probably recommend the one big table approach, but if you don't have any bottlenecks in your current system and the type of query you've given is typical of the majority of what your application is doing, there's no sense redesigning your application in the middle of a database switch. Robert Treat PS. Josh, are you referring to Pascal's "Practical Issues In Database Management" book or does he have a different book out that I'm not aware of? On Thu, 2003-01-30 at 13:24, Noah Silverman wrote: > OK, > > Thanks for the quick responses. > > A bit more information. > > We are in the business of gathering data for our clients. (We're a news > service). Subsequently, we do a lot of inserting and very rarely do > any deleting. (We periodically clear out results that are over 6 months > old.) > > On a give day, we will insert around 100,000 records in total. > (Currently split across all the client tables). > > A challenging part of the process is that we have to keep track of > previous content that may be similar. We CAN'T do this with a unique > index (don't ask, it would take too long to explain, but trust me, it > isn't possible). So, we have to query the table first and then compare > the results of that query to what we are inserting. SO, we probably do > close to 1 million queries, but then only make about 100,000 inserts. > The basic flow is 1) our system finds something it likes, 2) query the > table to see if something similar already exists, 3) if nothing similar > exists, insert. > > While all this is going on, our clients are accessing our online > reporting system. This system makes a variety of count and record > requests from the database. > > As I mentioned in our earlier post, we are attempting to decide if > Postgres will run faster/better/ with one big table, or a bunch of > smaller ones. It really doesn't make much difference to us, we just > want whatever structure will be faster. > > Thanks, > > -N >