X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F09E43A49E7 for ; Thu, 6 Jan 2005 18:24:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35220-07 for ; Thu, 6 Jan 2005 18:23:35 +0000 (GMT) Received: from frank.wiles.org (frank.wiles.org [24.124.39.75]) by svr1.postgresql.org (Postfix) with ESMTP id D124B3A4767 for ; Thu, 6 Jan 2005 18:23:31 +0000 (GMT) Received: from kungfu (frank.wiles.org [127.0.0.1]) by frank.wiles.org (8.13.1/8.13.1) with SMTP id j06IO5in006933; Thu, 6 Jan 2005 12:24:05 -0600 Date: Thu, 6 Jan 2005 12:24:40 -0600 From: Frank Wiles To: Josh Berkus Cc: pgsql-performance@postgresql.org Subject: Re: Denormalization WAS: Low Performance for big hospital Message-Id: <20050106122440.4b64fc3d.frank@wiles.org> In-Reply-To: <200501060938.45688.josh@agliodbs.com> References: <1104831107.41da6283add0c@webmail.moph.go.th> <200501060906.55832.josh@agliodbs.com> <20050106111207.52e39d04.frank@wiles.org> <200501060938.45688.josh@agliodbs.com> X-Mailer: Sylpheed version 0.9.10 (GTK+ 1.2.10; i386-redhat-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/73 X-Sequence-Number: 9763 On Thu, 6 Jan 2005 09:38:45 -0800 Josh Berkus wrote: > Frank, > > > Now that's rich. I don't think I've ever seen a database perform > > worse after it was normalized. In fact, I can't even think of a > > situation where it could! > > Oh, there are some. For example, Primer's issues around his dating > database; it turned out that a fully normalized design resulted in > very bad select performance because of the number of joins involved. > Of course, the method that did perform well was *not* a simple > denormalization, either. > > The issue with denormalization is, I think, that a lot of developers > cut their teeth on the likes of MS Access, Sybase 2 or Informix 1.0, > where a poor-performing join often didn't complete at all. As a > result, they got into the habit of "preemptive tuning"; that is, doing > things "for performance reasons" when the system was still in the > design phase, before they even know what the performance issues > *were*. > > Not that this was a good practice even then, but the average software > project allocates grossly inadequate time for testing, so you can see > how it became a bad habit. And most younger DBAs learn their skills > on the job from the older DBAs, so the misinformation gets passed > down. Yeah the more I thought about it I had a fraud detection system I built for a phone company years ago that when completely normalized couldn't get the sub-second response the users wanted. It was Oracle and we didn't have the best DBA in the world. I ended up having to push about 20% of the deep call details into flat files and surprisingly enough it was faster to grep the flat files than use the database, because as was previously mentioned all of the joins. --------------------------------- Frank Wiles http://www.wiles.org ---------------------------------