Thread: Table PARTITION

Table PARTITION

From
"Dinesh Pandey"
Date:
<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> 

Re: Table PARTITION

From
Richard Huxton
Date:
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


Re: Table PARTITION

From
"Dinesh Pandey"
Date:
 
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




Re: Table PARTITION

From
Sean Davis
Date:
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



Re: Table PARTITION

From
Richard Huxton
Date:
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


Re: Table PARTITION

From
Richard Huxton
Date:
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


Re: Table PARTITION

From
Enrico Weigelt
Date:
* 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 --
 
---------------------------------------------------------------------


Re: Table PARTITION

From
"Dinesh Pandey"
Date:
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