pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Noah Silverman <noah@allresearch.com>
To: pgsql-performance@postgresql.org
Subject: Re: One large v. many small
Date: Thu, 30 Jan 2003 09:56:56 -0800
Message-ID: <200301300956.56041.josh@agliodbs.com> (raw)
In-Reply-To: <1A107C56-3479-11D7-A3A3-000393AA8F3C@allresearch.com>
References: <1A107C56-3479-11D7-A3A3-000393AA8F3C@allresearch.com>

Noah,

> As we continue our evaluation of Postgres, another interesting topic
> has come up that I want to run by the group.
>
> In our current model, we have about 3,000 small tables that we use
> track data for our clients.  Each table is an identical structure, and
> holds the data for one client.

I'd list what's wrong with this structure, but frankly it would take me long 
enough that I'd need a consulting fee.   Suffice it to say that the above is 
a very, very bad (or at least antiquated) design idea and you need to 
transition out of it as soon as possible.

> Another idea that we are considering is one big table instead of 3,000
> smaller ones.  We could simply add a numeric field to indicate which
> client a particular record was for.

Yes.   Absolutely.  Although I'd suggest an Integer field.

> Each table has between 500 and 50,000 records, so the big table could
> have up to 10 million rows if we combined everything.

Sure.

> A query on our current system is (for client #4)
>
> Select (*) from client_4 where foo=2;
>
> A query from the new, proposed system would be
>
> Select (*) from big_results where client=4 and foo=2.
>
> The big questions is, WHICH WILL BE FASTER with Postgres.  Is there any
> performance improvement or cost to switching to this new structure.

Oh, no question query 1 will be faster ... FOR THAT QUERY.  You are asking the 
wrong question.

However, explain to me how, under the current system, you can find the client 
who ordered $3000 worth of widgets on January 12th if you don't already know 
who it is?   I'm not sure a 3000-table UNION query is even *possible*.

Or how about giving me the average number of customer transactions in a month, 
across all clients?

<rant>

You've enslaved your application design to performance considerations ... an 
approach which was valid in 1990, because processing power was so limited 
then.  But now that dual-processor servers with RAID can be had for less than 
$3000, there's simply no excuse for violating the principles of good 
relational database design just to speed up a query.   Buying more RAM is 
much cheaper than having an engineer spend 3 weeks fixing data integrity 
problems.

The proper way to go about application design is to build your application on 
paper or in a modelling program according to the best principles of software 
design available, and *then* to discuss performance issues -- addressing them 
*first* by buying hardware, and only compromising your applcation design when 
no other alternative is available.

</rant>

I strongly suggest that you purchase Pascal's "Practical Issues in Database 
Design" and give it a read.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



view thread (17+ messages)  latest in thread

Message-ID: <200301300956.56041.josh@agliodbs.com>
Permalink:  ../200301300956.56041.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200301300956.56041.josh@agliodbs.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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: josh@agliodbs.com, noah@allresearch.com
  Subject: Re: One large v. many small
  In-Reply-To: <200301300956.56041.josh@agliodbs.com>
  References: <1A107C56-3479-11D7-A3A3-000393AA8F3C@allresearch.com> <200301300956.56041.josh@agliodbs.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