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
>
