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 E9FC1D1B4F6 for ; Thu, 18 Sep 2003 17:27:24 +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 11045-06 for ; Thu, 18 Sep 2003 14:27:15 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id EB911D1B4F0 for ; Thu, 18 Sep 2003 14:27:13 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3653889; Thu, 18 Sep 2003 10:27:43 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: "Alexander Priem" , Subject: Re: Many fields in one table or many tables? Date: Thu, 18 Sep 2003 10:27:12 -0700 User-Agent: KMail/1.4.3 References: <20399973.1063861169740.JavaMail.root@rowlf.psp.pas.earthlink.net> <008901c37dbc$b3615550$b696a8c0@APR> In-Reply-To: <008901c37dbc$b3615550$b696a8c0@APR> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200309181027.12195.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/269 X-Sequence-Number: 3635 Alexander, > I am in the process of creating a database design in which LOTS of data > need to be modelled. > > For instance, I need to store data about products. Every product has LOTS > of properties, well over a hundred. > Do any of you know if and how PostgreSQL would prefer one approach over the > other? Queston 1: Do all products have all of these properties, or do some/many/most not have some properties? If the answer is the former, then a single table, however broad, is the logical construct. If the latter, than several tables makes more sense: why create NULL columns for stuff you could just leave out? Question 2: Is it true that some properties will be updated *much* (100x) more frequently than others? If so, it would make sense from a performance/postgresql standpoint to isolate those properties to related table(s). Keep in mind that this recommendation is strictly performance related, and is not necessarily the best relational design. Suggestion 3: There was an issue in 7.3 with table rows which are overly broad -- some problems with PSQL, I believe. It would be worth searching for, as I cannot remember what the limit is where problems occurred. -- Josh Berkus Aglio Database Solutions San Francisco