pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMany fields in one table or many tables?
3+ messages / 3 participants
[nested] [flat]
* Many fields in one table or many tables?
@ 2003-09-18 08:13 Alexander Priem <ap@cict.nl>
2003-09-18 17:27 ` Re: Many fields in one table or many tables? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Alexander Priem @ 2003-09-18 08:13 UTC (permalink / raw)
To: pgsql-performance
Hi guys,
I am in the process of creating a database design in which LOTS of data need
to be modelled.
For instance, I need to store data about products. Every product has LOTS of
properties, well over a hundred.
So I'm wondering. What's the best approach here, performance wise? Just
create one Product table with well over a hundred columns? Or would it be
better to divide this over more tables and link them together via ID's? I
could for instance create tables Product, PriceInfo, Logistics, StorageInfo,
PackagingInfo and link them together via the same ID. This would be easier
to document (try to visualize a 100+ column table in a document!), but would
it impact performance? I tihnk maybe it would impact Select performance, but
Updating of products would maybe speed up a little...
All info about a product is unique for this product so records in PriceInfo,
Logistics, StorageInfo, PackagingInfo tables would map one to one to records
in the Product table.
Do any of you know if and how PostgreSQL would prefer one approach over the
other?
Thanks in advance,
Alexander Priem.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Many fields in one table or many tables?
2003-09-18 08:13 Many fields in one table or many tables? Alexander Priem <ap@cict.nl>
@ 2003-09-18 17:27 ` Josh Berkus <josh@agliodbs.com>
2003-09-18 18:20 ` Re: Many fields in one table or many tables? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2003-09-18 17:27 UTC (permalink / raw)
To: Alexander Priem <ap@cict.nl>; pgsql-performance
Alexander,
> I am in the process of creating a database design in which LOTS of data
> need to be modelled.
>
> For instance, I need to store data about products. Every product has LOTS
> of properties, well over a hundred.
<snip>
> Do any of you know if and how PostgreSQL would prefer one approach over the
> other?
Queston 1: Do all products have all of these properties, or do some/many/most
not have some properties? If the answer is the former, then a single table,
however broad, is the logical construct. If the latter, than several tables
makes more sense: why create NULL columns for stuff you could just leave out?
Question 2: Is it true that some properties will be updated *much* (100x) more
frequently than others? If so, it would make sense from a
performance/postgresql standpoint to isolate those properties to related
table(s). Keep in mind that this recommendation is strictly performance
related, and is not necessarily the best relational design.
Suggestion 3: There was an issue in 7.3 with table rows which are overly broad
-- some problems with PSQL, I believe. It would be worth searching for, as
I cannot remember what the limit is where problems occurred.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Many fields in one table or many tables?
2003-09-18 08:13 Many fields in one table or many tables? Alexander Priem <ap@cict.nl>
2003-09-18 17:27 ` Re: Many fields in one table or many tables? Josh Berkus <josh@agliodbs.com>
@ 2003-09-18 18:20 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-09-18 18:20 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Alexander Priem <ap@cict.nl>; pgsql-performance
Josh Berkus <josh@agliodbs.com> writes:
> Suggestion 3: There was an issue in 7.3 with table rows which are overly broad
> -- some problems with PSQL, I believe.
Not sure about PSQL, but I think there still are some performance issues
in the backend with SELECTs involving more than a couple hundred
targetlist entries. These are probably fixable at not-very-large effort
but we haven't made any consistent push to find and fix the trouble
spots. The issues that I recall are O(N^2) problems (doubly nested
loops) so the performance with ~100 entries is no problem but it gets
rapidly worse above that. You could hit this even with ~100-column
tables if you try to select all columns from a join of two or more.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-09-18 18:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-18 08:13 Many fields in one table or many tables? Alexander Priem <ap@cict.nl>
2003-09-18 17:27 ` Josh Berkus <josh@agliodbs.com>
2003-09-18 18:20 ` Tom Lane <tgl@sss.pgh.pa.us>
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