Thread: question about partitioning
Right now I am in the process of migrating an Oracle DB over to Postgres 8.4.3. The table is partitioned by size. Is there anyway to partition the new postgres table by size? I created some partitions for the new table, but I didn't give postgres any rules to partition by, so I have 250M test records in one table. Any ideas or thoughts on how to build the rules for the table by size would be greatly appreciated. -- Joshua Gooding Software Engineer TTi Technologies Wheeling, WV 26003 w: 304-233-5680 x 308 c: 304-794-8341
On 2010-06-24, Joshua Gooding <JGooding@ttitech.net> wrote: > Right now I am in the process of migrating an Oracle DB over to Postgres > 8.4.3. The table is partitioned by size. Is there anyway to partition > the new postgres table by size? I created some partitions for the new > table, but I didn't give postgres any rules to partition by, so I have > 250M test records in one table. Any ideas or thoughts on how to build > the rules for the table by size would be greatly appreciated. by size of what?
I think I replied to the individual and not to the list before.... As of right now size doesn't matter, I need to partition it via a date. 10 partitions, 10 weeks worth of data. I was thinking of partitioning it off every 32GB of data, but that is not exactly what I am looking to do. Joshua Gooding On 6/24/2010 11:06 AM, Jasen Betts wrote: > On 2010-06-24, Joshua Gooding<JGooding@ttitech.net> wrote: > >> Right now I am in the process of migrating an Oracle DB over to Postgres >> 8.4.3. The table is partitioned by size. Is there anyway to partition >> the new postgres table by size? I created some partitions for the new >> table, but I didn't give postgres any rules to partition by, so I have >> 250M test records in one table. Any ideas or thoughts on how to build >> the rules for the table by size would be greatly appreciated. >> > by size of what? > >
I don't know how you would partition by size. Date is a good candidate, and roughly wouldn't you have the same number of tx's/day You'll only benefit query performance if you include the partitioning column in the where clause. If you have a surrogate pk, you could also use this to partition. Using a range key you would probably get relatively constantpartition size. Doug -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Joshua Gooding Sent: Thursday, June 24, 2010 2:31 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] question about partitioning I think I replied to the individual and not to the list before.... As of right now size doesn't matter, I need to partition it via a date. 10 partitions, 10 weeks worth of data. I was thinking of partitioning it off every 32GB of data, but that is not exactly what I am looking to do. Joshua Gooding On 6/24/2010 11:06 AM, Jasen Betts wrote: > On 2010-06-24, Joshua Gooding<JGooding@ttitech.net> wrote: > >> Right now I am in the process of migrating an Oracle DB over to Postgres >> 8.4.3. The table is partitioned by size. Is there anyway to partition >> the new postgres table by size? I created some partitions for the new >> table, but I didn't give postgres any rules to partition by, so I have >> 250M test records in one table. Any ideas or thoughts on how to build >> the rules for the table by size would be greatly appreciated. >> > by size of what? > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
I'm trying to figure out the logic behind the date parameters though. I don't have to worry at all about partition size. Joshua Gooding On 6/24/2010 3:37 PM, Little, Douglas wrote: > I don't know how you would partition by size. > Date is a good candidate, and roughly wouldn't you have the same number of tx's/day > You'll only benefit query performance if you include the partitioning column in the where clause. > If you have a surrogate pk, you could also use this to partition. Using a range key you would probably get relativelyconstant partition size. > > Doug > > > > -----Original Message----- > From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Joshua Gooding > Sent: Thursday, June 24, 2010 2:31 PM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] question about partitioning > > I think I replied to the individual and not to the list before.... > > As of right now size doesn't matter, I need to partition it via a date. > 10 partitions, 10 weeks worth of data. > > I was thinking of partitioning it off every 32GB of data, but that is > not exactly what I am looking to do. > > Joshua Gooding > > > On 6/24/2010 11:06 AM, Jasen Betts wrote: > >> On 2010-06-24, Joshua Gooding<JGooding@ttitech.net> wrote: >> >> >>> Right now I am in the process of migrating an Oracle DB over to Postgres >>> 8.4.3. The table is partitioned by size. Is there anyway to partition >>> the new postgres table by size? I created some partitions for the new >>> table, but I didn't give postgres any rules to partition by, so I have >>> 250M test records in one table. Any ideas or thoughts on how to build >>> the rules for the table by size would be greatly appreciated. >>> >>> >> by size of what? >> >> >> >
There is no partitioning by size that I know of but at: http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html there is very good documentation on the topic. As of this last weekend I had myself to do some testing with partitioning in Postgres 8.4. I had 7000 items. For each of them I stored 6 variables (smallint) for each hour quarter. Test data was generated for 5 years. That gave me 210336 records per id per 5 years period and a total of 1,472,352,000 records. This is taking about 33MB per partition table and a total of 231GB. Doing something like a grouping by the id and summing up the values of 10 ids takes about 2.5 seconds, which looks to me like a quite a nice performance. Petru Ghita On 24/06/2010 15:05, Joshua Gooding wrote: > rules for