Thread: Table Creation Question.

Hello, I'm wondering if anyone could point me towards performance/space
tradeoffs for large tables in Postgres.  I would like to create a very
large table that has in each tuple a varchar and a list of integers.  What
are the benefits of designing my table as a varchar/array of integers
vs. the more standard relational way of having a separate tuple for each
varchar/integer pair?

I assume using the array type would save me some cost on storage, while
using the varchar/integer pairs would result in faster queries.  Is this
correct?  Thanks for any help or pointers.

postgres@athena.frontlogic.com writes:

> I assume using the array type would save me some cost on storage, while
> using the varchar/integer pairs would result in faster queries.  Is this
> correct?  Thanks for any help or pointers.

As long as you don't want to look up or join against invidual array
members, the array solution is going to be more space and time efficient.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

In comp.databases.postgresql.general Peter Eisentraut <peter_e@gmx.net> wrote:
> postgres@athena.frontlogic.com writes:

>> I assume using the array type would save me some cost on storage, while
>> using the varchar/integer pairs would result in faster queries.  Is this
>> correct?  Thanks for any help or pointers.

> As long as you don't want to look up or join against invidual array
> members, the array solution is going to be more space and time efficient.

Thanks for the reply.  I am going to join on individual members, so I will shy away
from array types.  I'm uncomfortable with array types in general, because of their
deviance from the relational model.  Does anyone have any links to a discussion of
the pros and cons of using array types?