From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Roelant Ossewaarde <miep@belboek.com>,
	Richard Huxton <dev@archonet.archonet.com>
Subject: Re: Use arrays or not?
Date: Thu, 29 Apr 2004 13:47:16 -0700
User-Agent: KMail/1.5.4
Cc: pgsql-sql@postgresql.org
References: <20040429164059.GC24558@belboek.com> <40914CC2.6050909@archonet>
	<20040429202631.GB25328@belboek.com>
In-Reply-To: <20040429202631.GB25328@belboek.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200404291347.16488.josh@agliodbs.com>

Roelant,

> So, let me rephrase my questions:
> 1. When and why would anyone use arrays?

When the data itself is an ordered set of items which is indivisible and lacks 
meaning outside the ordered set.   For example, a set of ordered pairs of 
molecules in a gene snippet.  Or a mathematical matrix.

> 2. When designing the database, is it really true that there is no 
performance
> difference between a table of which the number of tuples grow by a factor 
of,
> say 10, and a table of which the size of the tuples grow by a factor of, say
> 10?

Nobody's tested anything.   I would *tend* to think that PostgreSQL would 
handle more-of-less-wide-rows somewhat better, but that's just a guess.   

Hmmm ... not completely a guess.   Postgres, by default, compresses fields 
over 8K in size (see TOAST in the docs).  This makes those fields somewhat 
slower to update.   So if 1 XML rec < 8k but 4 XML rec > 8k, there could be a 
small-but-noticeable performance loss from going to "broad" rows.

If I had your application, I would not go for the array approach, jjust to 
avoid maintainence headaches.    For example, what happens when the books 
start having a variable number of XML records?   Normalized designs are 
almost always easier to deal with from a perspective of long-term 
maintainence.

The arrays, as far as I can tell, gain you nothing in ethier performance or 
convenience.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco