Re: Table Partitioning and Rules
От | Girish Bajaj |
---|---|
Тема | Re: Table Partitioning and Rules |
Дата | |
Msg-id | 003b01c34c98$ca9861f0$7764a8c0@tietronix.com обсуждение исходный текст |
Ответ на | Re: Table Partitioning and Rules (Josh Berkus <josh@agliodbs.com>) |
Ответы |
Re: Table Partitioning and Rules
(Josh Berkus <josh@agliodbs.com>)
Re: Table Partitioning and Rules (Steve Crawford <scrawford@pinpointresearch.com>) Re: Table Partitioning and Rules (Dmitry Tkach <dmitry@openratings.com>) Re: Table Partitioning and Rules (Greg Stark <gsstark@mit.edu>) |
Список | pgsql-sql |
The problem is that Im worried about sequential scans. This particular table can have upto 150 cols and 250 million records. Now we have a reporting requirement that someone could select on ANY col and filter on any col as well. Meaning someone could so a SELECT on col number 1,2,310,1000 from contact where col num 75='X' and col num 139 = 'Y'. I cant possibly index all the cols in the table. So I thought Id best manage the data by splitting up the table into multiple partitions and eventually depending on application logic, only scan those tables that are necessary to scan sequentially instead of the whole big table. Im getting a little confused here cause eventually I would want to join in this 250 million gigantic table as well.. and that would be a real big problem causing loads of sequential scans wouldn't it? Thanks, Girish -----Original Message----- From: Josh Berkus [mailto:josh@agliodbs.com] Sent: Thursday, July 17, 2003 2:03 PM To: Richard Huxton; Girish Bajaj; pgsql-sql@postgresql.org Subject: Re: [SQL] Table Partitioning and Rules Girish, > > Essentially Im trying to store a persons information in a table in the > > database. Since we could have millions of people, with duplicates! Ive > > decided we need to partition the table into segments where all people with > > the LastName starting from A to G will be in one table. H-N will be in > > another table and O-Z in the third. Ive created a VIEW that does a UNION on > > all the tables. This sounds hideously inefficient and a management headache besides. I think PostgreSQL will accept up to 2 billion rows in any one table, and splitting stuff into 3 tables will not improve your performance ... quite the opposite. Change your database design. -- -Josh BerkusAglio Database SolutionsSan Francisco
В списке pgsql-sql по дате отправления: