Re: [GENERAL] How many fields in a table are too many
Hi Mark, actually there is a simple way to determine this. create an entity relationship model, try to build a 3rd normal form, look on what the users/customers are really doing, not only what data they are dealing with and finally break up the 3rd NF again. A table with too many columns is as bad as a database with too many tables. If you can't keep the overview, something went wrong. Sometimes you can tune the database by tuning the datastructure. It's good if you end up with tables that only have a few columns. But why not twenty, if you need them, it's ok. But if you have significantly more than this number, you should have a good reason, than it's ok as well. What I think is more important, is that you have a tight look on the nature of your data. Treat a number like a number and a date like a date. Ciao, Toni MT wrote: Hi, Just a quick question, not unrelated to my previous question, which I don't think will get answered. I have a table with 13 fields. Is that too many fields for one table. Thanks, Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] How many fields in a table are too many
elein wrote: Re: attribute ordering The column presentation issue maybe should be cross referenced with the problem with rowtypes where the referenced table has dropped columns. This is the one where in plpgsql you do a select * into a rowtype of a table with dropped columns you get a mismatch on the types. Implementing the presentation ordering will probably give a solution to the rowtype problem. Recompiling the function should be totally sufficient. That you mention this as a problem indicates that you have self modifying code or are developing on your production system. Which of them is it? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] How many fields in a table are too many
On Thu, Jun 26, 2003 at 01:19:09PM +0530, Shridhar Daithankar wrote: On 26 Jun 2003 at 3:44, [EMAIL PROTECTED] wrote: On Thu, Jun 26, 2003 at 03:17:12AM -0400, [EMAIL PROTECTED] wrote: I have a table with 13 fields. Is that too many fields for one table. Mark Thirteen? No way. I've got you beat with 21: Pfft! Is *that* all? I've got a table with 116 fields. I *knew* a number of these responses would be forthcoming... :) I remember somebody posting a database schema with 500 fields. Don't worry as long as it works. When you have problems, post it on performance. First advice you would get is to normalize it..:-) Bye Shridhar Do normal form classifications go into negative numbers? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] How many fields in a table are too many
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is the issue of many columns in a tuple the same issue as a SELECT having many columns? I believe all the same inefficiencies need to be fixed whichever way you look at it. Probably many columns in SELECT is the more accurate description though. Together with the recent discussions about attribute reordering, it'd make sense, if we have a resentation order different from the actual physical tuple layout, that the table starts with all variable length fields at the end. This would give a better utilization of attribute offset caching. Don't know though, if this counts for much of the suffering. Jan regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How many fields in a table are too many
Is this a TODO? When columns can be reordered, move varlena columns to the end? --- Jan Wieck wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is the issue of many columns in a tuple the same issue as a SELECT having many columns? I believe all the same inefficiencies need to be fixed whichever way you look at it. Probably many columns in SELECT is the more accurate description though. Together with the recent discussions about attribute reordering, it'd make sense, if we have a resentation order different from the actual physical tuple layout, that the table starts with all variable length fields at the end. This would give a better utilization of attribute offset caching. Don't know though, if this counts for much of the suffering. Jan regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How many fields in a table are too many
Bruce Momjian [EMAIL PROTECTED] writes: Is this a TODO? When columns can be reordered, move varlena columns to the end? Yeah, if it's not in there already. I remember seeing some Berkeley-era comments speculating that this would be a good thing to do. Not sure if they're still in the code, or I believe that really what you want is fixed-width NOT NULL columns first, then fixed-width nullable, then var-width. This ordering will improve the odds of actually being able to use the fixed offset for a particular column. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] How many fields in a table are too many
On Sat, Jun 28, 2003 at 01:43:32PM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is this a TODO? When columns can be reordered, move varlena columns to the end? I believe that really what you want is fixed-width NOT NULL columns first, then fixed-width nullable, then var-width. This ordering will improve the odds of actually being able to use the fixed offset for a particular column. This means that when you do ALTER TABLE ... SET/DROP NOT NULL the table may have to be completely rewritten? Altering the physical order is sure to provoke a rewrite, but I think people will expect a change in constraints to be a fast operation. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Essentially, you're proposing Kevlar shoes as a solution for the problem that you want to walk around carrying a loaded gun aimed at your foot. (Tom Lane) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How many fields in a table are too many
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is this a TODO? When columns can be reordered, move varlena columns to the end? Yeah, if it's not in there already. I remember seeing some Berkeley-era comments speculating that this would be a good thing to do. Not sure if they're still in the code, or I believe that really what you want is fixed-width NOT NULL columns first, then fixed-width nullable, then var-width. This ordering will improve the odds of actually being able to use the fixed offset for a particular column. Added to TODO: * Automatically place fixed-width, NOT NULL columns first in a table I like the idea of putting NOT NULL before NULL columns. However, I am not sure how useful NOT NULL is in practice because there are lots of columns that don't specify NOT NULL but have mostly nulls or mostly non-nulls, which kills our caching --- what I was hoping to do some day was to cache the null bitmask and offsets of the previous tuple and use those if the new tuple has the same null bitmask as the previous tuple. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How many fields in a table are too many
Alvaro Herrera wrote: On Sat, Jun 28, 2003 at 01:43:32PM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is this a TODO? When columns can be reordered, move varlena columns to the end? I believe that really what you want is fixed-width NOT NULL columns first, then fixed-width nullable, then var-width. This ordering will improve the odds of actually being able to use the fixed offset for a particular column. This means that when you do ALTER TABLE ... SET/DROP NOT NULL the table may have to be completely rewritten? Altering the physical order is sure to provoke a rewrite, but I think people will expect a change in constraints to be a fast operation. I don't think we would change any physical ordering with ALTER TABLE --- just do it when the table is created, if possible. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] How many fields in a table are too many
Bruce Momjian [EMAIL PROTECTED] writes: Alvaro Herrera wrote: This means that when you do ALTER TABLE ... SET/DROP NOT NULL the table may have to be completely rewritten? I don't think we would change any physical ordering with ALTER TABLE --- just do it when the table is created, if possible. Right, I was just thinking of this as an optimization we'd apply during initial table creation. Renumbering columns later on would be a huge mess, far more work than the optimization is worth. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How many fields in a table are too many
Alvaro Herrera wrote: On Sat, Jun 28, 2003 at 01:43:32PM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is this a TODO? When columns can be reordered, move varlena columns to the end? I believe that really what you want is fixed-width NOT NULL columns first, then fixed-width nullable, then var-width. This ordering will improve the odds of actually being able to use the fixed offset for a particular column. This means that when you do ALTER TABLE ... SET/DROP NOT NULL the table may have to be completely rewritten? Altering the physical order is sure to provoke a rewrite, but I think people will expect a change in constraints to be a fast operation. I said ... table starts with ..., I said nothing about physically reordering an existing table. All we where talking about is uncoupling the order in which attributes of a relation appear if automatically expanded from SELECT * Currently, they are tied to the order of the physical tuple layout, which in turn is the result of the original order at CREATE TABLE, plus added columns at the end and dropped ones taken out. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How many fields in a table are too many
Bruce Momjian [EMAIL PROTECTED] writes: However, I am not sure how useful NOT NULL is in practice because there are lots of columns that don't specify NOT NULL but have mostly nulls or mostly non-nulls, which kills our caching --- what I was hoping to do some day was to cache the null bitmask and offsets of the previous tuple and use those if the new tuple has the same null bitmask as the previous tuple. We already cache fairly effectively in cases where there are no nulls. I'm not sure it's worth trying to do something with the idea that two adjacent tuples might have nulls in the same places. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How many fields in a table are too many
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: However, I am not sure how useful NOT NULL is in practice because there are lots of columns that don't specify NOT NULL but have mostly nulls or mostly non-nulls, which kills our caching --- what I was hoping to do some day was to cache the null bitmask and offsets of the previous tuple and use those if the new tuple has the same null bitmask as the previous tuple. We already cache fairly effectively in cases where there are no nulls. I'm not sure it's worth trying to do something with the idea that two adjacent tuples might have nulls in the same places. I was thinking of trying it and seeing how often it would be a win, because right now, when we hit a null, our cache is dead. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How many fields in a table are too many
On Sat, Jun 28, 2003 at 02:21:57PM -0400, Tom Lane wrote: snip Right, I was just thinking of this as an optimization we'd apply during initial table creation. Renumbering columns later on would be a huge mess, far more work than the optimization is worth. perhaps it could be done on VACUUM ? cu -- - Enrico Weigelt== metux ITS Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr. phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 smsgate: [EMAIL PROTECTED] - Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How many fields in a table are too many
Re: attribute ordering The column presentation issue maybe should be cross referenced with the problem with rowtypes where the referenced table has dropped columns. This is the one where in plpgsql you do a select * into a rowtype of a table with dropped columns you get a mismatch on the types. Implementing the presentation ordering will probably give a solution to the rowtype problem. elein On Saturday 28 June 2003 08:05, Jan Wieck wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is the issue of many columns in a tuple the same issue as a SELECT having many columns? I believe all the same inefficiencies need to be fixed whichever way you look at it. Probably many columns in SELECT is the more accurate description though. Together with the recent discussions about attribute reordering, it'd make sense, if we have a resentation order different from the actual physical tuple layout, that the table starts with all variable length fields at the end. This would give a better utilization of attribute offset caching. Don't know though, if this counts for much of the suffering. Jan regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- = [EMAIL PROTECTED] Database Consulting www.varlena.com PostgreSQL General Bitshttp:/www.varlena.com/GeneralBits/ Free your mind the rest will follow -- en vogue ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How many fields in a table are too many
There is an article on normalisation at IBM developer works for all those who need a primer on table normalisation. I learnt this from, would you believe, dBase Mac by Jim Heid when I was one of the two users in the world running that version in 1988-1989 =:-D I recomend Database Application Programming with Linux Wiley. There are some good table design chapters even for non Linux people. Good database design helps make PostgreSQL run fast. Cheers Tony Grant -- www.tgds.net Library management software toolkit, redhat linux on Sony Vaio C1XD, Dreamweaver MX with Tomcat and PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How many fields in a table are too many
On Thu, Jun 26, 2003 at 03:17:12AM -0400, [EMAIL PROTECTED] wrote: I have a table with 13 fields. Is that too many fields for one table. Mark Thirteen? No way. I've got you beat with 21: Pfft! Is *that* all? I've got a table with 116 fields. Very soon we'll be upgrading to 7.3 and we can them merrily DROP COLUMN half of them. Until then... The original developers didn't really have a concept of storing different info in different tables. -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ the West won the world not by the superiority of its ideas or values or religion but rather by its superiority in applying organized violence. Westerners often forget this fact, non-Westerners never do. - Samuel P. Huntington pgp0.pgp Description: PGP signature
Re: [GENERAL] How many fields in a table are too many
On Thu, Jun 26, 2003 at 03:17:12AM -0400, [EMAIL PROTECTED] wrote: I have a table with 13 fields. Is that too many fields for one table. Mark Thirteen? No way. I've got you beat with 21: Pfft! Is *that* all? I've got a table with 116 fields. I *knew* a number of these responses would be forthcoming... :) ~Berend Tober ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How many fields in a table are too many
On 26 Jun 2003 at 3:44, [EMAIL PROTECTED] wrote: On Thu, Jun 26, 2003 at 03:17:12AM -0400, [EMAIL PROTECTED] wrote: I have a table with 13 fields. Is that too many fields for one table. Mark Thirteen? No way. I've got you beat with 21: Pfft! Is *that* all? I've got a table with 116 fields. I *knew* a number of these responses would be forthcoming... :) I remember somebody posting a database schema with 500 fields. Don't worry as long as it works. When you have problems, post it on performance. First advice you would get is to normalize it..:-) Bye Shridhar -- We have phasers, I vote we blast 'em! -- Bailey, The Corbomite Maneuver, stardate 1514.2 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How many fields in a table are too many
On Thu, Jun 26, 2003 at 01:02:06 -0400, Mike Mascari [EMAIL PROTECTED] wrote: Hi, Just a quick question, not unrelated to my previous question, which I don't think will get answered. I have a table with 13 fields. Is that too many fields for one table. Mathematically, a binary relation represents the relevant cross product of two domains, x and f(x): A binary relation is a subset of the cross product of two sets. This is more general than using x and f(x) since this implies some restrictions on the subset (namely that there is only one tuple with a specific member of x in the x part of the tuple). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] How many fields in a table are too many
The original developers didn't really have a concept of storing different info in different tables. That kind of stuff drives me nuts. Where do people get their CS degrees? It took me less that 2 days to teach our ARTISTS how to construct fully-normalized tables (it's a long story as to why I was doing this, but you get the picture). Sorry for the rant. Sometimes I can't help it. Jon ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How many fields in a table are too many
[EMAIL PROTECTED] writes: As long as we are playing who's is biggest, I have one with 900+ attributes (normalized) but there is a big warning - if you have a query that returns hundreds of columns it will be very, very slow. Is the SELECT * the only circumstance? That is, if you specify a small number of columns, does the response improve even though the table actually has that large number of columns but is only be asked to supply a column-limited result set? IIRC, the worst problems that Steve's profile exposed were associated with large numbers of columns in a SELECT result --- there are some doubly nested loops that take time O(N^2) in the number of columns. But I would not be surprised if some of those loops get invoked on the underlying table, too, depending on what your query looks like exactly. This is all eminently fixable, it's just a matter of someone finding some round tuits ... for most people it doesn't seem like a high-priority problem, since you won't notice it till you get into the hundreds of columns ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] How many fields in a table are too many
Added to TODO: * Improve performance for queries with many columns We already have an item for tables with many columsn. --- Tom Lane wrote: [EMAIL PROTECTED] writes: As long as we are playing who's is biggest, I have one with 900+ attributes (normalized) but there is a big warning - if you have a query that returns hundreds of columns it will be very, very slow. Is the SELECT * the only circumstance? That is, if you specify a small number of columns, does the response improve even though the table actually has that large number of columns but is only be asked to supply a column-limited result set? IIRC, the worst problems that Steve's profile exposed were associated with large numbers of columns in a SELECT result --- there are some doubly nested loops that take time O(N^2) in the number of columns. But I would not be surprised if some of those loops get invoked on the underlying table, too, depending on what your query looks like exactly. This is all eminently fixable, it's just a matter of someone finding some round tuits ... for most people it doesn't seem like a high-priority problem, since you won't notice it till you get into the hundreds of columns ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] How many fields in a table are too many
Bruce Momjian [EMAIL PROTECTED] writes: Added to TODO: * Improve performance for queries with many columns We already have an item for tables with many columsn. That one's a duplicate then. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How many fields in a table are too many
Is the issue of many columns in a tuple the same issue as a SELECT having many columns? --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Added to TODO: * Improve performance for queries with many columns We already have an item for tables with many columsn. That one's a duplicate then. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How many fields in a table are too many
Bruce Momjian [EMAIL PROTECTED] writes: Is the issue of many columns in a tuple the same issue as a SELECT having many columns? I believe all the same inefficiencies need to be fixed whichever way you look at it. Probably many columns in SELECT is the more accurate description though. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
