Re: Use arrays or not?

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