pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquestion about partitioning
6+ messages / 4 participants
[nested] [flat]
* question about partitioning
@ 2010-06-24 13:05 Joshua Gooding <JGooding@ttitech.net>
0 siblings, 2 replies; 6+ messages in thread
From: Joshua Gooding @ 2010-06-24 13:05 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: question about partitioning
@ 2010-06-24 15:06 Jasen Betts <jasen@xnet.co.nz>
parent: Joshua Gooding <JGooding@ttitech.net>
1 sibling, 1 reply; 6+ messages in thread
From: Jasen Betts @ 2010-06-24 15:06 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: question about partitioning
@ 2010-06-24 19:31 Joshua Gooding <JGooding@ttitech.net>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 1 reply; 6+ messages in thread
From: Joshua Gooding @ 2010-06-24 19:31 UTC (permalink / raw)
To: pgsql-sql
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?
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: question about partitioning
@ 2010-06-24 19:37 Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
parent: Joshua Gooding <JGooding@ttitech.net>
0 siblings, 1 reply; 6+ messages in thread
From: Little, Douglas @ 2010-06-24 19:37 UTC (permalink / raw)
To: Joshua Gooding <JGooding@ttitech.net>; pgsql-sql
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 constant 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?
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: question about partitioning
@ 2010-06-24 19:51 Joshua Gooding <JGooding@ttitech.net>
parent: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
0 siblings, 0 replies; 6+ messages in thread
From: Joshua Gooding @ 2010-06-24 19:51 UTC (permalink / raw)
To: 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 relatively constant 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?
>>
>>
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: question about partitioning
@ 2010-06-24 20:17 Petru Ghita <petrutz@venaver.info>
parent: Joshua Gooding <JGooding@ttitech.net>
1 sibling, 0 replies; 6+ messages in thread
From: Petru Ghita @ 2010-06-24 20:17 UTC (permalink / raw)
To: Joshua Gooding <JGooding@ttitech.net>; pgsql-sql
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
Attachments:
[application/pgp-keys] 0x7CF29D04.asc (2.8K, ../../4C23BD71.40706@venaver.info/2-0x7CF29D04.asc)
download
[application/pgp-signature] signature.asc (258B, ../../4C23BD71.40706@venaver.info/3-signature.asc)
download
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-06-24 20:17 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-24 13:05 question about partitioning Joshua Gooding <JGooding@ttitech.net>
2010-06-24 15:06 ` Jasen Betts <jasen@xnet.co.nz>
2010-06-24 19:31 ` Joshua Gooding <JGooding@ttitech.net>
2010-06-24 19:37 ` Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-06-24 19:51 ` Joshua Gooding <JGooding@ttitech.net>
2010-06-24 20:17 ` Petru Ghita <petrutz@venaver.info>
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