From: Josh Berkus <josh@agliodbs.com>
To: Roelant Ossewaarde <miep@belboek.com>
To: Richard Huxton <dev@archonet.archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Use arrays or not?
Date: Thu, 29 Apr 2004 13:47:16 -0700
Message-ID: <200404291347.16488.josh@agliodbs.com> (raw)
In-Reply-To: <20040429202631.GB25328@belboek.com>
References: <20040429164059.GC24558@belboek.com>
<40914CC2.6050909@archonet>
<20040429202631.GB25328@belboek.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, miep@belboek.com, dev@archonet.archonet.com
Subject: Re: Use arrays or not?
In-Reply-To: <200404291347.16488.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox