pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Many 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