Thread: 1600 Column limit..
Is this set in stone.. or is there somewhere you can change this.. and will changing it cause upgrade problems in the future.. Travis
On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote: > Is this set in stone.. or is there somewhere you can change this.. and will changing it cause upgrade problems in the future.. If you are trying to have 1600 columns in a table, you probably have made a design mistake in your table design. You should take a look at some database design books to see if you can change the structure.
The reason we have that is we have 23 slots in a shelf (of equipment) and we have 23 ports in a slot. We are tracking 3 different items per port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual items to track of which they are all a single digit. You add into that some misc. stuff like shelf name and poll_time and it becomes a mess.. I just split them into 3 seperate tables. Travis -----Original Message----- From: Joe Tomcat [mailto:tomcat@mobile.mp] Sent: Thursday, November 14, 2002 1:35 PM To: Williams, Travis L, NPONS Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] 1600 Column limit.. On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote: > Is this set in stone.. or is there somewhere you can change this.. and will changing it cause upgrade problems in the future.. If you are trying to have 1600 columns in a table, you probably have made a design mistake in your table design. You should take a look at some database design books to see if you can change the structure.
not really knowing the specifics, it seems you would want to describe the table this way instead: table #1 slot_number,shelf_id,port_number,item_type_id table #2 shelf_id shelf_name table #3 item_type_id item_data I mean, what happens if some new shelf comes out with 25 slots and a slot with 30 ports, or a new item per slot is added. You'll be forced to redesign your schema. just my $0.02 On Wed, 2002-11-13 at 18:40, Williams, Travis L, NPONS wrote: > The reason we have that is we have 23 slots in a shelf (of equipment) > and we have 23 ports in a slot. We are tracking 3 different items per > port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual > items to track of which they are all a single digit. You add into that > some misc. stuff like shelf name and poll_time and it becomes a mess.. > I just split them into 3 seperate tables. > > Travis > > -----Original Message----- > From: Joe Tomcat [mailto:tomcat@mobile.mp] > Sent: Thursday, November 14, 2002 1:35 PM > To: Williams, Travis L, NPONS > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] 1600 Column limit.. > > > On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote: > > Is this set in stone.. or is there somewhere you can change this.. and > will changing it cause upgrade problems in the future.. > > If you are trying to have 1600 columns in a table, you probably have > made a design mistake in your table design. You should take a look at > some database design books to see if you can change the structure. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Brian Hirt <bhirt@mobygames.com>
Seems like a more effective layout would be create table tracking ( i_slot int4, i_port int4, i_item int4 ); maybe i'm missing somthing, but a 1600 row table is tiny, a 1600 column table would be very difficult to manage, and poor design. Gavin Williams, Travis L, NPONS wrote: >The reason we have that is we have 23 slots in a shelf (of equipment) >and we have 23 ports in a slot. We are tracking 3 different items per >port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual >items to track of which they are all a single digit. You add into that >some misc. stuff like shelf name and poll_time and it becomes a mess.. >I just split them into 3 seperate tables. > >Travis > >-----Original Message----- >From: Joe Tomcat [mailto:tomcat@mobile.mp] >Sent: Thursday, November 14, 2002 1:35 PM >To: Williams, Travis L, NPONS >Cc: pgsql-general@postgresql.org >Subject: Re: [GENERAL] 1600 Column limit.. > > >On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote: > > >>Is this set in stone.. or is there somewhere you can change this.. and >> >> >will changing it cause upgrade problems in the future.. > >If you are trying to have 1600 columns in a table, you probably have >made a design mistake in your table design. You should take a look at >some database design books to see if you can change the structure. > > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > --------------------------------------------------------- Scanned by Sophos Anti-Virus v3.59TPOS, MIMEDefang v2.19, and Spam Assassin v2.31 on satchel.bteg.net
On Wed, 13 Nov 2002, Williams, Travis L, NPONS wrote: > Is this set in stone.. or is there somewhere you can change this.. and > will changing it cause upgrade problems in the future.. I'm not sure how easy it'd be to get around the particular number limit enforced, but at the very least you'd have to increate the block size from 8k.
we have to have all of that information per shelf.. so we have the columns with the information then each row is a differentshelf.. which to start with there are 400.. which could grow to upwards to any number.. but I did split all of thisup into seperate tables.. Travis -----Original Message----- From: Gavin M. Roy [mailto:gmr@justsportsusa.com] Sent: Wednesday, November 13, 2002 8:19 PM To: Williams, Travis L, NPONS; pgsql-general@postgresql.org Subject: Re: [GENERAL] 1600 Column limit.. Seems like a more effective layout would be create table tracking ( i_slot int4, i_port int4, i_item int4 ); maybe i'm missing somthing, but a 1600 row table is tiny, a 1600 column table would be very difficult to manage, and poor design. Gavin Williams, Travis L, NPONS wrote: >The reason we have that is we have 23 slots in a shelf (of equipment) >and we have 23 ports in a slot. We are tracking 3 different items per >port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual >items to track of which they are all a single digit. You add into that >some misc. stuff like shelf name and poll_time and it becomes a mess.. >I just split them into 3 seperate tables. > >Travis > >-----Original Message----- >From: Joe Tomcat [mailto:tomcat@mobile.mp] >Sent: Thursday, November 14, 2002 1:35 PM >To: Williams, Travis L, NPONS >Cc: pgsql-general@postgresql.org >Subject: Re: [GENERAL] 1600 Column limit.. > > >On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote: > > >>Is this set in stone.. or is there somewhere you can change this.. and >> >> >will changing it cause upgrade problems in the future.. > >If you are trying to have 1600 columns in a table, you probably have >made a design mistake in your table design. You should take a look at >some database design books to see if you can change the structure. > > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > --------------------------------------------------------- Scanned by Sophos Anti-Virus v3.59TPOS, MIMEDefang v2.19, and Spam Assassin v2.31 on satchel.bteg.net
"Williams, Travis L, NPONS" <tlw@att.com> writes:
> Is this set in stone.. or is there somewhere you can change this.. and
> will changing it cause upgrade problems in the future..
It is not easy to make a large increase in that limit. See the comments
in src/include/access/htup.h for some of the reasons why. You'd also
need a significant increase in block size to be able to do anything
useful. (For instance, if all your columns are int4, you surely can't
fit more than 2K columns in 8K blocksize.) Finally, the system is not
very well optimized for large numbers of columns --- even if you could
physically make things fit, there are various places whose runtime is
O(N^2) in the number of columns.
I agree with the nearby comments that say you should consider a database
redesign. If extremely wide tables were a standard design technique,
we'd not have allowed these limitations to accrue...
regards, tom lane
Hi Williams, --On Mittwoch, 13. November 2002 22:27 -0500 "Williams, Travis L, NPONS" <tlw@att.com> wrote: > we have to have all of that information per shelf.. so we have the > columns with the information then each row is a different shelf.. which > to start with there are 400.. which could grow to upwards to any number.. > but I did split all of this up into seperate tables.. Travis Seems you are a bit influenced by Makarenko ;) To your problem: I see you have all these information which appears to be in a row - but whats the use other then the storage? How to find a special Slot and look for its contents? If you would instead have some related tables, the job is easy - all slots could be indexed. Otherwise you could use 1 column and use the array datatype as well. I dont know if there is a limit in the array datatype, but I would not expect one. Regards Tino > -----Original Message----- > From: Gavin M. Roy [mailto:gmr@justsportsusa.com] > Sent: Wednesday, November 13, 2002 8:19 PM > To: Williams, Travis L, NPONS; pgsql-general@postgresql.org > Subject: Re: [GENERAL] 1600 Column limit.. > > > Seems like a more effective layout would be > > create table tracking ( > i_slot int4, > i_port int4, > i_item int4 ); > > maybe i'm missing somthing, but a 1600 row table is tiny, a 1600 column > table would be very difficult to manage, and poor design. > > Gavin > > Williams, Travis L, NPONS wrote: > >> The reason we have that is we have 23 slots in a shelf (of equipment) >> and we have 23 ports in a slot. We are tracking 3 different items per >> port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual >> items to track of which they are all a single digit. You add into that >> some misc. stuff like shelf name and poll_time and it becomes a mess.. >> I just split them into 3 seperate tables. >> >> Travis >> >> -----Original Message----- >> From: Joe Tomcat [mailto:tomcat@mobile.mp] >> Sent: Thursday, November 14, 2002 1:35 PM >> To: Williams, Travis L, NPONS >> Cc: pgsql-general@postgresql.org >> Subject: Re: [GENERAL] 1600 Column limit.. >> >> >> On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote: >> >> >>> Is this set in stone.. or is there somewhere you can change this.. and >>> >>> >> will changing it cause upgrade problems in the future.. >> >> If you are trying to have 1600 columns in a table, you probably have >> made a design mistake in your table design. You should take a look at >> some database design books to see if you can change the structure. >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >> >> > > > > > --------------------------------------------------------- > Scanned by Sophos Anti-Virus v3.59TPOS, MIMEDefang v2.19, > and Spam Assassin v2.31 on satchel.bteg.net > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
1) What is the "array datatype"? I looked for this under data types and didn't see it. 2) We have a huge amount of scripts that basically maintain the our network as a whole. They are wanting to start off by getting out of using a 20 meg flat file because every time they do an update it takes forever.. so we are trying to mimic what they have in the file.. then in the future we can get them to change how their data is structured.. in their datafile they have it set up exactly like the db is.. lotsa columns.. then they look for different trends in the data.. or they look for specific values per shelf.. I understand how this could be done with this information all together.. but getting them to do change thier scripts is something else.. what I did was break it down into 3 seperate tables.. with each piece of information in a different one.. Travis -----Original Message----- From: Tino Wildenhain [mailto:tino@wildenhain.de] Sent: Thursday, November 14, 2002 7:44 AM To: Williams, Travis L, NPONS; Gavin M. Roy; pgsql-general@postgresql.org Subject: Re: [GENERAL] 1600 Column limit.. Hi Williams, --On Mittwoch, 13. November 2002 22:27 -0500 "Williams, Travis L, NPONS" <tlw@att.com> wrote: > we have to have all of that information per shelf.. so we have the > columns with the information then each row is a different shelf.. which > to start with there are 400.. which could grow to upwards to any number.. > but I did split all of this up into seperate tables.. Travis Seems you are a bit influenced by Makarenko ;) To your problem: I see you have all these information which appears to be in a row - but whats the use other then the storage? How to find a special Slot and look for its contents? If you would instead have some related tables, the job is easy - all slots could be indexed. Otherwise you could use 1 column and use the array datatype as well. I dont know if there is a limit in the array datatype, but I would not expect one. Regards Tino > -----Original Message----- > From: Gavin M. Roy [mailto:gmr@justsportsusa.com] > Sent: Wednesday, November 13, 2002 8:19 PM > To: Williams, Travis L, NPONS; pgsql-general@postgresql.org > Subject: Re: [GENERAL] 1600 Column limit.. > > > Seems like a more effective layout would be > > create table tracking ( > i_slot int4, > i_port int4, > i_item int4 ); > > maybe i'm missing somthing, but a 1600 row table is tiny, a 1600 column > table would be very difficult to manage, and poor design. > > Gavin > > Williams, Travis L, NPONS wrote: > >> The reason we have that is we have 23 slots in a shelf (of equipment) >> and we have 23 ports in a slot. We are tracking 3 different items per >> port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual >> items to track of which they are all a single digit. You add into that >> some misc. stuff like shelf name and poll_time and it becomes a mess.. >> I just split them into 3 seperate tables. >> >> Travis >> >> -----Original Message----- >> From: Joe Tomcat [mailto:tomcat@mobile.mp] >> Sent: Thursday, November 14, 2002 1:35 PM >> To: Williams, Travis L, NPONS >> Cc: pgsql-general@postgresql.org >> Subject: Re: [GENERAL] 1600 Column limit.. >> >> >> On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote: >> >> >>> Is this set in stone.. or is there somewhere you can change this.. and >>> >>> >> will changing it cause upgrade problems in the future.. >> >> If you are trying to have 1600 columns in a table, you probably have >> made a design mistake in your table design. You should take a look at >> some database design books to see if you can change the structure. >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >> >> > > > > > --------------------------------------------------------- > Scanned by Sophos Anti-Virus v3.59TPOS, MIMEDefang v2.19, > and Spam Assassin v2.31 on satchel.bteg.net > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Hi, Maybe a view can help you. You can design your database as needed and let your applications see what they want to see. You can write some triggers so that applications can update through the view. A view with 1600 columns is ugly too, but better than a row with 1600 columns. Ok - you will have trouble do 1600 selfjoins in one view :-( Another solution is to offer your applications some stored procedures, which collects the data. In a 1600 column table the database will do a linear search for data in a row. Tommi Am Donnerstag, 14. November 2002 16:14 schrieb Williams, Travis L, NPONS: > 1) What is the "array datatype"? I looked for this under data types and > didn't see it. > > 2) We have a huge amount of scripts that basically maintain the our > network as a whole. They are wanting to start off by getting out of > using a 20 meg flat file because every time they do an update it takes > forever.. so we are trying to mimic what they have in the file.. then > in the future we can get them to change how their data is structured.. > in their datafile they have it set up exactly like the db is.. lotsa > columns.. then they look for different trends in the data.. or they look > for specific values per shelf.. I understand how this could be done with > this information all together.. but getting them to do change thier > scripts is something else.. what I did was break it down into 3 seperate > tables.. with each piece of information in a different one.. > Travis > > -----Original Message----- > From: Tino Wildenhain [mailto:tino@wildenhain.de] > Sent: Thursday, November 14, 2002 7:44 AM > To: Williams, Travis L, NPONS; Gavin M. Roy; > pgsql-general@postgresql.org > Subject: Re: [GENERAL] 1600 Column limit.. > > > Hi Williams, > > --On Mittwoch, 13. November 2002 22:27 -0500 "Williams, Travis L, NPONS" > > <tlw@att.com> wrote: > > we have to have all of that information per shelf.. so we have the > > columns with the information then each row is a different shelf.. > > which > > > to start with there are 400.. which could grow to upwards to any > > number.. > > > but I did split all of this up into seperate tables.. Travis > > Seems you are a bit influenced by Makarenko ;) > To your problem: I see you have all these information > which appears to be in a row - but whats the use other > then the storage? How to find a special Slot and look > for its contents? If you would instead have some related > tables, the job is easy - all slots could be indexed. > Otherwise you could use 1 column and use the array datatype > as well. I dont know if there is a limit in the array datatype, > but I would not expect one. > > Regards > Tino > > > -----Original Message----- > > From: Gavin M. Roy [mailto:gmr@justsportsusa.com] > > Sent: Wednesday, November 13, 2002 8:19 PM > > To: Williams, Travis L, NPONS; pgsql-general@postgresql.org > > Subject: Re: [GENERAL] 1600 Column limit.. > > > > > > Seems like a more effective layout would be > > > > create table tracking ( > > i_slot int4, > > i_port int4, > > i_item int4 ); > > > > maybe i'm missing somthing, but a 1600 row table is tiny, a 1600 > > column > > > table would be very difficult to manage, and poor design. > > > > Gavin > > > > Williams, Travis L, NPONS wrote: > >> The reason we have that is we have 23 slots in a shelf (of equipment) > >> and we have 23 ports in a slot. We are tracking 3 different items > > per > > >> port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual > >> items to track of which they are all a single digit. You add into > > that > > >> some misc. stuff like shelf name and poll_time and it becomes a > > mess.. > > >> I just split them into 3 seperate tables. > >> > >> Travis > >> > >> -----Original Message----- > >> From: Joe Tomcat [mailto:tomcat@mobile.mp] > >> Sent: Thursday, November 14, 2002 1:35 PM > >> To: Williams, Travis L, NPONS > >> Cc: pgsql-general@postgresql.org > >> Subject: Re: [GENERAL] 1600 Column limit.. > >> > >> On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote: > >>> Is this set in stone.. or is there somewhere you can change this.. > > and > > >> will changing it cause upgrade problems in the future.. > >> > >> If you are trying to have 1600 columns in a table, you probably have > >> made a design mistake in your table design. You should take a look > > at > > >> some database design books to see if you can change the structure. > >> > >> > >> > >> ---------------------------(end of > > broadcast)--------------------------- > > >> TIP 2: you can get off all lists at once with the unregister command > >> (send "unregister YourEmailAddressHere" to > > majordomo@postgresql.org) > > > --------------------------------------------------------- > > Scanned by Sophos Anti-Virus v3.59TPOS, MIMEDefang v2.19, > > and Spam Assassin v2.31 on satchel.bteg.net > > > > ---------------------------(end of > > broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Dr. Eckhardt + Partner GmbH http://www.epgmbh.de
Tommi Maekitalo <t.maekitalo@epgmbh.de> writes: > In a 1600 column table the database will do a linear search for data > in a row. Why's that? Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Hi, Am Freitag, 15. November 2002 11:16 schrieb Neil Conway: > Tommi Maekitalo <t.maekitalo@epgmbh.de> writes: > > In a 1600 column table the database will do a linear search for data > > in a row. > > Why's that? > > Cheers, > > Neil one thing is to locate rows in a database. This is optimized and we can see this in the query plan. But what does postgresql do, when he knows the row and he should decide, which columns to send back? I'm not sure, but I would suspect, that he just searches for columns with matching names. This can be done with optimized access of systemtables, but the result are some column-ids, which has to be located in the row. Tommi -- Dr. Eckhardt + Partner GmbH http://www.epgmbh.de