X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 22EF4D1B4EA for ; Thu, 9 Oct 2003 02:21:35 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 97971-04 for ; Wed, 8 Oct 2003 23:20:49 -0300 (ADT) Received: from news.hub.org (unknown [64.117.224.194]) by svr1.postgresql.org (Postfix) with ESMTP id 4B9F5D1B4E5 for ; Wed, 8 Oct 2003 23:20:45 -0300 (ADT) Received: from news.hub.org (host-64-117-224-194.altec1.com [64.117.224.194] (may be forged)) by news.hub.org (8.12.9/8.12.9) with ESMTP id h992KjQh064818 for ; Thu, 9 Oct 2003 02:20:45 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h992CI4X063933 for pgsql-performance@postgresql.org; Thu, 9 Oct 2003 02:12:18 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Compare rows Date: Wed, 08 Oct 2003 22:07:46 -0400 Organization: cbbrowne Computing Inc Lines: 49 Message-ID: References: <3F841A28.8060504@cranel.com> <200310081010.26576.josh@agliodbs.com> <3F84613D.8040207@cranel.com> <200310081611.59179.josh@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1003 (Gnus v5.10.3) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:Y8GqMk3RazDLQoI7X5cNifUzmBU= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/255 X-Sequence-Number: 4003 In an attempt to throw the authorities off his trail, josh@agliodbs.com (Josh Berkus) transmitted: > child table > id value_type value > 3 uptime 0.3 > 3 speed 11.2 > 3 memory 37 > 3 tty 6 > 7 uptime 1.1 > 7 memory 15 > 9 uptime 0.1 > 9 memory 94 > 9 tty 2 > > As you can see, the NULLs are not stored, making this system much more > efficient on storage space. Wow, that takes me back to a paper I have been looking for for _years_. Some time in the late '80s, probably '88 or '89, there was a paper presented in Communications of the ACM that proposed using this sort of "hypernormalized" schema as a way of having _really_ narrow schemas that would be exceedingly expressive. They illustrated an example of an address table that could hold full addresses with a schema with only about half a dozen columns, the idea being that you'd have several rows linked together. The methodology was _heavy_ on metadata, though not so much so that there were no columns left over for "real" data. The entertaining claim was that they felt they could model the complexities of the operations of any sort of company using not more than 50 tables. It seemed somewhat interesting, at the time; it truly resonated as Really Interesting when I saw SAP R/3, with its bloat of 1500-odd tables. (I seem to remember the authors being Boston-based, and they indicated that they had implemented this "on VMS," which would more than likely imply RDB; somehow I doubt that'll be the set of detail that makes someone remember it...) The need to do a lot of joins would likely hurt performance somewhat, as well as the way that it greatly increases the number of rows. Although you could always split it into several tables, one for each "value_type", and UNION them into a view... -- wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org'). http://cbbrowne.com/info/unix.html You shouldn't anthropomorphize computers; they don't like it.