Thread: Lists or external TABLE?

Hi list,
i'm a postgres noob and before designing a new Database Schema i would
like to ask you a simple question because preformance will be a critical
point.

My question regards performance comparison between a Table which
includes a list or a Table without list and with an external table.

1 - TABLE A has several fields (most of which are types defined by me)
for a total of 30-40 field (counting fields in my new types) and could
contain 5000 rows.

2 - TABLE A will be accessed several times with several views or query.
    (many SELECT,few UPDATE)


Let's suppose i need to add an info about addresses (which includes
country,city,cap....etc etc).
Addresses can vary from 1 to 20 entries..

Talking about performance is it better to include a list of addresses in
TABLE A or is it better to create an external TABLE B?

I'm afraid that including those addresses will decrease performance in
doing many SELECT.

On the other side i'm afraid of doing JOIN with TABLE B.

What do you think?
Thank you!

xchris

xchris wrote:
>
> Let's suppose i need to add an info about addresses (which includes
> country,city,cap....etc etc).
> Addresses can vary from 1 to 20 entries..
>
> Talking about performance is it better to include a list of addresses in
> TABLE A or is it better to create an external TABLE B?

Don't optimise before you have to.

Do the addresses belong in "A"? If so, put them there. On the other
hand, if you want items in "A" to have more than one address, or to
share addresses then clearly you will want a separate address table.
It's difficult to say more without a clear example of your requirements.

Even if you choose to alter your design for performance reasons, you
should make sure you run tests with realistic workloads and hardware.
But first, trust PG to do its job and design your database according to
the problem requirements.

--
   Richard Huxton
   Archonet Ltd

On Fri, Sep 30, 2005 at 10:34:35AM +0100, Richard Huxton wrote:
> xchris wrote:
> >
> >Let's suppose i need to add an info about addresses (which includes
> >country,city,cap....etc etc).
> >Addresses can vary from 1 to 20 entries..
> >
> >Talking about performance is it better to include a list of addresses in
> >TABLE A or is it better to create an external TABLE B?
>
> Don't optimise before you have to.
>
> Do the addresses belong in "A"? If so, put them there. On the other
> hand, if you want items in "A" to have more than one address, or to
> share addresses then clearly you will want a separate address table.
> It's difficult to say more without a clear example of your requirements.
>
> Even if you choose to alter your design for performance reasons, you
> should make sure you run tests with realistic workloads and hardware.
> But first, trust PG to do its job and design your database according to
> the problem requirements.

On top of what Richard said, 5000 rows is pretty tiny. Even if each row
was 1K wide, that's still only 5MB.

Also, if from a data-model standpoint it doesn't matter which way you
go, I suggest looking at what it will take to write queries against both
versions before deciding. I tend to stay away from arrays because
they tend to be harder to query against.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461