Re: newbie table design question

On Mon, Jun 1, 2009 at 8:41 AM, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
> On Mon, Jun 01, 2009 at 06:53:30AM -0500, Chris Spotts wrote:
>> I just finished doing something very close to this - not quite once per
>> minute, but close.  I started off with an array of integers and after about
>> a month of it, I'm having to redesign my way out of it.
>
> I've had to go the other way before; started with a nicely normalized
> design but the performance overhead (and query complexity to a lesser
> extent) made the design unworkable.  As always the design depends on the
> specifics of the problem, but as the OP said he was new to PG I thought
> it good to point out its support for arrays.

+1

_IF_ arrays fit your requirements, they are an extremely compact and
efficient way to store your data. The requirements are exact..you
can't update or extract a single piece of data out of a block
efficiently. While indexing techniques are possible with gist, they
are moderate help at best. But if arrays fit, your table will be a
fraction of size of the 'row per datum' approach, at the cost of
flexibility. arrays are an enormous footgun when used in tables, but
if used properly can work well.

merlin