X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D755C4760F5 for ; Fri, 31 Jan 2003 12:39:53 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 0F1A3475DC0 for ; Fri, 31 Jan 2003 12:39:52 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2814877 for pgsql-performance@postgresql.org; Fri, 31 Jan 2003 09:35:29 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: pgsql-performance@postgresql.org Subject: Re: One large v. many small Date: Fri, 31 Jan 2003 09:34:28 -0800 User-Agent: KMail/1.4.3 References: In-Reply-To: MIME-Version: 1.0 Message-Id: <200301310934.28980.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/401 X-Sequence-Number: 1048 Folks, Many, many replies on this topic: Jeff: > App design & performance go hand-in-hand. the trick is to balance them. > Who wants a _wonderful_ design that runs like a piece of poo? in this >Select somevalue from sometable where othervalue not in (select badvalues > from badvaluetable where id =3D 12345) > we all know this runs horrifically on postgres. using your method I should > go out, spend thousands on multi-processor boxes, raid, ram Sorry, no, Jeff. The above is what one calls a "bad query" and is not,=20 therefore, a performance vs. design issue: that query is bad design-wise, a= nd=20 bad performance-wise. Perhpas another example of your argument? Since you do not seem to have understood my argument, it is this:=20=20 Design changes, made for the sake of performance or rapid app building, whi= ch=20 completely violate good RDBMS design and normalization principles, almost= =20 always cost you more over the life of the application than you gain in=20 performance in the short term.=20=20=20 Curt: > It's all very nice to have a general system that can do well on all > sorts of queries, but if you lose time on the queries you do do, in > order to save time on queries you don't do, you're definitely not > getting the best performance out of the system. This is a good point; I tend to build for posterity because, so far, 90% of= my=20 clients who started out having me build a "single-purpose" database ended u= p=20 expanding the application to cover 2-10 additional needs, thus forcing me t= o=20 clean up any design shortcuts I took with the original app. However, Noa= h=20 may have more control over his company than that. > RAM is not cheap enough yet for me to buy several hundred gigabytes of > it for typical applications, even if I could find a server that I could > put it in. Disk performance is not growing the way CPU performance is. > And three weeks of engineering time plus a ten thousand dollar server > is, even at my top billing rate, still a heck of a lot cheaper than a > quarter-million dollar server. I was thinking more of the difference between a $3000 server and a $9000=20 server myself; unless you're doing nuclear test modelling, I don't see any= =20 need for a $250,000 server for anything.=20=20=20 To give an extreme example, I have a client who purchased a $150,000=20 accounting system that turned out to be badly designed, normalization-wise,= =20 partly because the accounting system engineers were focusing on 8-year-old= =20 technology with performance restrictions which were no longer really=20 applicable (for example, they talked the client into buying a quad-processo= r=20 server and then wrote all of their middleware code on a platform that does= =20 not do SMP). Over the last two years, they have paid my company $175,000 t= o=20=20 "fix" this accounting database ... more, in fact, than I would have charged= =20 them to write a better system from scratch. > Applying your strategy to all situations is not always going to produce > the most cost-effective solution. That's very true. In fact, that could be taken as a "general truism" ... = no=20 one strategy applies to *all* situations. > 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? Yes, you're correct. Sorry! --=20 Josh Berkus Aglio Database Solutions San Francisco