Thread: Table PARTITION
<p><font face="Verdana" size="2">How can we create oracle's table with partition in Postgres. (How to create Table
partionin postgres)</font><p><font face="Verdana" size="2">CREATE TABLE invoices</font><br /><font face="Verdana"
size="2">(invoice_no NUMBER NOT NULL, </font><br /><font face="Verdana" size="2"> invoice_date DATE NOT
NULL,</font><br/><font face="Verdana" size="2"> comments VARCHAR2(500))</font><p><b><font face="Verdana"
size="2">PARTITIONBY RANGE</font></b> <font face="Verdana" size="2">(invoice_date)</font><br /><font face="Verdana"
size="2">(PARTITIONinvoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users</font><p><font
face="Verdana"size="2">Regards<br /> Dinesh Pandey</font><font face="Times New Roman"><br /><br /><br /></font><b><font
color="#808080"face="Times New Roman"
size="2">----------------------------------------------------------------------------------</font></b><br
/><b></b><b><fontcolor="#808080" face="Verdana" size="2">Dinesh Pandey</font></b><font face="Verdana"
size="2"> </font><fontcolor="#808080" face="Verdana" size="2"></font><br /><font color="#808080" face="Verdana"
size="2">Sr.Software Engineer<br /><br /> Second Foundation (India) Pvt. Ltd.<br /> Plot# 52<br /> Industrial Area,
PhaseII<br /> Chandigarh. (India)<br /> PH: (O) 0172-2639202, Extn: 233</font>
Dinesh Pandey wrote:
> How can we create oracle's table with partition in Postgres. (How to create
> Table partion in postgres)
>
> CREATE TABLE invoices
> (invoice_no NUMBER NOT NULL,
> invoice_date DATE NOT NULL,
> comments VARCHAR2(500))
>
> PARTITION BY RANGE (invoice_date)
> (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
> 'DD/MM/YYYY')) TABLESPACE users
By hand, I'm afraid (although I think people are looking into this
feature for version 8.1)
There's lots of discussion in the mail archives about how people are
handling this. You're basically looking at some combination of - partial/conditional indexes - unions - inheritance
-- Richard Huxton Archonet Ltd
Hi Richard,
I am using postgres 8.0.1. It allows us to create TABLESPACE but I don't
know how to create PARTITION in postgres.
Can you pls tell me little more.
Thanks
Dinesh Pandey
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Thursday, April 07, 2005 5:36 PM
To: dpandey@secf.com
Cc: 'PostgreSQL'
Subject: Re: [SQL] Table PARTITION
Dinesh Pandey wrote:
> How can we create oracle's table with partition in Postgres. (How to
> create Table partion in postgres)
>
> CREATE TABLE invoices
> (invoice_no NUMBER NOT NULL,
> invoice_date DATE NOT NULL,
> comments VARCHAR2(500))
>
> PARTITION BY RANGE (invoice_date)
> (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
> 'DD/MM/YYYY')) TABLESPACE users
By hand, I'm afraid (although I think people are looking into this feature
for version 8.1)
There's lots of discussion in the mail archives about how people are
handling this. You're basically looking at some combination of - partial/conditional indexes - unions - inheritance
-- Richard Huxton Archonet Ltd
This is a totally selfish question, but IF someone has a few minutes,
could he/she explain why table partitioning is such an important tool?
Thanks,
Sean
On Apr 7, 2005, at 8:06 AM, Richard Huxton wrote:
> Dinesh Pandey wrote:
>> How can we create oracle's table with partition in Postgres. (How to
>> create
>> Table partion in postgres)
>> CREATE TABLE invoices
>> (invoice_no NUMBER NOT NULL, invoice_date DATE NOT NULL,
>> comments VARCHAR2(500))
>> PARTITION BY RANGE (invoice_date)
>> (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
>> 'DD/MM/YYYY')) TABLESPACE users
>
> By hand, I'm afraid (although I think people are looking into this
> feature for version 8.1)
>
> There's lots of discussion in the mail archives about how people are
> handling this. You're basically looking at some combination of
> - partial/conditional indexes
> - unions
> - inheritance
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
Dinesh Pandey wrote: > > Hi Richard, > > I am using postgres 8.0.1. It allows us to create TABLESPACE but I don't > know how to create PARTITION in postgres. > > Can you pls tell me little more. You can't I'm afraid. You can use inheritance / a view unioning tables to do something similar though. Check the mailing-list archives for notes on these. -- Richard Huxton Archonet Ltd
Sean Davis wrote: > This is a totally selfish question, but IF someone has a few minutes, > could he/she explain why table partitioning is such an important tool? Say you have a large log-table, you could partition it by month. If most queries only search the last month or two, a lot of your partitioned data could be moved to cheaper/slower disks (via tablespaces). After (say) 24 months, you can start dropping the oldest partitions, and this should take place without affecting any other partitions. How you partition your data depends on how you plan to access it. If you don't query your log-tables by date there's not much point in partitioning by date. -- Richard Huxton Archonet Ltd
* Richard Huxton <dev@archonet.com> wrote: > Sean Davis wrote: > >This is a totally selfish question, but IF someone has a few minutes, > >could he/she explain why table partitioning is such an important tool? > > Say you have a large log-table, you could partition it by month. If most > queries only search the last month or two, a lot of your partitioned > data could be moved to cheaper/slower disks (via tablespaces). You can solve this problem with multiple tables rules quite easily. At this point you can also filter out some unused data (often historical data requires less information than live data, because only the end result of certain finished things is interesting for the future, but many things needed as long as things are open are completely irrelevant for later usage, i.e. an archive of accounting information for webhosters wont require datails of single http requests) Lets give me some examples on one of my customer's projects: At fXignal - an forex market trading platform - we're maintaining an long-time archive of all run orders. An "open" trade (you've bought some position) has one order, while an "closed" trade (things are sold again) has two. I.g we've got two kind of accesses to trade information: a) viewing and manipulating open trades - active trading (must be fast!) b) only viewing closed trades for reports (account report, etc) Also we've got some information which are only interesting for open trades, ie. limits (points where trade should be closed automatically). We've solved this by having two tables: one for open trades and one for archived (closed) trades. When an trade is opened, it goes to the open-trade table and resides there until it goes to closed state (by setting a "closed" flag). Once the trades has reached closed state its copied to the archive table and removed from the open trade table by an rule. (see CREATE RULE). When archived trades get old (3 month) we need less information from that, which has to be kept very long (several years). For that we catch the DELETE on the archive table and copy data to the longtime archive before it gets removed from the archive table. For long time analyses we've got some views which map together interesting information from all tables. Well, this way we've got the same benefits as with partitions, with a little bit more coding work, but then with better control and filtering out unneeded stuff. cu -- ---------------------------------------------------------------------Enrico Weigelt == metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.decellphone: +49 174 7066481 ----------------------------------------------------------------------- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting-- Webshops -- ---------------------------------------------------------------------
Yes, I will do in this way.... Thanks Dinesh Pandey -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Enrico Weigelt Sent: Friday, April 08, 2005 7:18 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Table PARTITION * Richard Huxton <dev@archonet.com> wrote: > Sean Davis wrote: > >This is a totally selfish question, but IF someone has a few minutes, > >could he/she explain why table partitioning is such an important tool? > > Say you have a large log-table, you could partition it by month. If > most queries only search the last month or two, a lot of your > partitioned data could be moved to cheaper/slower disks (via tablespaces). You can solve this problem with multiple tables rules quite easily. At this point you can also filter out some unused data (often historical data requires less information than live data, because only the end result of certain finished things is interesting for the future, but many things needed as long as things are open are completely irrelevant for later usage, i.e. an archive of accounting information for webhosters wont require datails of single http requests) Lets give me some examples on one of my customer's projects: At fXignal - an forex market trading platform - we're maintaining an long-time archive of all run orders. An "open" trade (you've bought some position) has one order, while an "closed" trade (things are sold again) has two. I.g we've got two kind of accesses to trade information: a) viewing and manipulating open trades - active trading (must be fast!) b) only viewing closed trades for reports (account report, etc) Also we've got some information which are only interesting for open trades, ie. limits (points where trade should be closed automatically). We've solved this by having two tables: one for open trades and one for archived (closed) trades. When an trade is opened, it goes to the open-trade table and resides there until it goes to closed state (by setting a "closed" flag). Once the trades has reached closed state its copied to the archive table and removed from the open trade table by an rule. (see CREATE RULE). When archived trades get old (3 month) we need less information from that, which has to be kept very long (several years). For that we catch the DELETE on the archive table and copy data to the longtime archive before it gets removed from the archive table. For long time analyses we've got some views which map together interesting information from all tables. Well, this way we've got the same benefits as with partitions, with a little bit more coding work, but then with better control and filtering out unneeded stuff. cu -- ---------------------------------------------------------------------Enrico Weigelt == metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.decellphone: +49 174 7066481 ----------------------------------------------------------------------- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting-- Webshops -- --------------------------------------------------------------------- ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster