pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLists or external TABLE?
3+ messages / 3 participants
[nested] [flat]
* Lists or external TABLE?
@ 2005-09-30 09:16 xchris <lyralyra@fastmail.fm>
2005-09-30 09:34 ` Re: Lists or external TABLE? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 3+ messages in thread
From: xchris @ 2005-09-30 09:16 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Lists or external TABLE?
2005-09-30 09:16 Lists or external TABLE? xchris <lyralyra@fastmail.fm>
@ 2005-09-30 09:34 ` Richard Huxton <dev@archonet.com>
2005-10-04 21:01 ` Re: Lists or external TABLE? Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Huxton @ 2005-09-30 09:34 UTC (permalink / raw)
To: xchris <lyralyra@fastmail.fm>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Lists or external TABLE?
2005-09-30 09:16 Lists or external TABLE? xchris <lyralyra@fastmail.fm>
2005-09-30 09:34 ` Re: Lists or external TABLE? Richard Huxton <dev@archonet.com>
@ 2005-10-04 21:01 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jim C. Nasby @ 2005-10-04 21:01 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: xchris <lyralyra@fastmail.fm>; pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-10-04 21:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-30 09:16 Lists or external TABLE? xchris <lyralyra@fastmail.fm>
2005-09-30 09:34 ` Richard Huxton <dev@archonet.com>
2005-10-04 21:01 ` Jim C. Nasby <jnasby@pervasive.com>
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