From dpandey@secf.com Thu Apr  7 11:41:46 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 7AA0753ABB
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  7 Apr 2005 12:41:46 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 38107-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  7 Apr 2005 11:41:37 +0000 (GMT)
Received: from secf.com (209-128-104-244.bayarea.net [209.128.104.244])
	by svr1.postgresql.org (Postfix) with ESMTP id 17B7E53AA5
	for <pgsql-sql@postgresql.org>; Thu,  7 Apr 2005 12:41:36 +0100 (BST)
Received: from dinesh by secf.com (MDaemon.PRO.v7.2.2.R)
	with ESMTP id md50000590308.msg
	for <pgsql-sql@postgresql.org>; Thu, 07 Apr 2005 04:41:56 -0700
Reply-To: <dpandey@secf.com>
From: "Dinesh Pandey" <dpandey@secf.com>
To: "'PostgreSQL'" <pgsql-sql@postgresql.org>
Subject: Table PARTITION
Date: Thu, 7 Apr 2005 17:08:54 +0530
Organization: Second Foundation India P Ltd.
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0003_01C53B94.8A40AC60"
X-Mailer: Microsoft Office Outlook, Build 11.0.5510
Thread-Index: AcU7Y1MglmkdaBPOSe2W4GvFHS3sUA==
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Authenticated-Sender: dpandey@secf.com
X-Spam-Processed: mail.secf.com, Thu, 07 Apr 2005 04:41:56 -0700
	(not processed: message from valid local sender)
X-MDRemoteIP: 220.227.32.11
X-Return-Path: dpandey@secf.com
X-MDaemon-Deliver-To: pgsql-sql@postgresql.org
X-MDAV-Processed: mail.secf.com, Thu, 07 Apr 2005 04:42:00 -0700
Message-Id: <20050407114136.17B7E53AA5@svr1.postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.536 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, HTML_60_70, HTML_MESSAGE, MSGID_FROM_MTA_ID,
	UPPERCASE_25_50
X-Spam-Level: *
X-Archive-Number: 200504/71
X-Sequence-Number: 20964

This is a multi-part message in MIME format.


------=_NextPart_000_0003_01C53B94.8A40AC60
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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

Regards
Dinesh Pandey 


----------------------------------------------------------------------------
------
Dinesh Pandey  
Sr. Software Engineer 

Second Foundation (India) Pvt. Ltd. 
Plot# 52 
Industrial Area, Phase II 
Chandigarh. (India)
PH: (O) 0172-2639202, Extn: 233


------=_NextPart_000_0003_01C53B94.8A40AC60
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7036.0">
<TITLE>Table PARTITION </TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->

<P><FONT SIZE=3D2 FACE=3D"Verdana">How can we create oracle's table with =
partition in Postgres. (How to create Table partion in postgres)</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Verdana">CREATE TABLE invoices</FONT>

<BR><FONT SIZE=3D2 FACE=3D"Verdana">(invoice_no&nbsp;&nbsp;&nbsp; NUMBER =
NOT NULL, </FONT>

<BR><FONT SIZE=3D2 FACE=3D"Verdana">&nbsp;invoice_date&nbsp; =
DATE&nbsp;&nbsp; NOT NULL,</FONT>

<BR><FONT SIZE=3D2 =
FACE=3D"Verdana">&nbsp;comments&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
VARCHAR2(500))</FONT>
</P>

<P><B><FONT SIZE=3D2 FACE=3D"Verdana">PARTITION BY RANGE</FONT></B> =
<FONT SIZE=3D2 FACE=3D"Verdana">(invoice_date)</FONT>

<BR><FONT SIZE=3D2 FACE=3D"Verdana">(PARTITION invoices_q1 VALUES LESS =
THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Verdana">Regards<BR>
Dinesh Pandey</FONT><FONT FACE=3D"Times New Roman"><BR>
<BR>
<BR>
</FONT><B><FONT COLOR=3D"#808080" SIZE=3D2 FACE=3D"Times New =
Roman">------------------------------------------------------------------=
----------------</FONT></B><BR>
<B></B><B><FONT COLOR=3D"#808080" SIZE=3D2 FACE=3D"Verdana">Dinesh =
Pandey</FONT></B><FONT SIZE=3D2 FACE=3D"Verdana">&nbsp;</FONT><FONT =
COLOR=3D"#808080" SIZE=3D2 FACE=3D"Verdana"></FONT><BR>
<FONT COLOR=3D"#808080" SIZE=3D2 FACE=3D"Verdana">Sr. Software =
Engineer<BR>
<BR>
Second Foundation (India) Pvt. Ltd.<BR>
Plot# 52<BR>
Industrial Area, Phase II<BR>
Chandigarh. (India)<BR>
PH: (O) 0172-2639202, Extn: 233</FONT>
</P>

</BODY>
</HTML>
------=_NextPart_000_0003_01C53B94.8A40AC60--




From dev@archonet.com Thu Apr  7 12:06:37 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id C205B53205
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  7 Apr 2005 13:06:37 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 45095-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  7 Apr 2005 12:06:29 +0000 (GMT)
Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net
	[194.217.242.88])
	by svr1.postgresql.org (Postfix) with ESMTP id D489B53A91
	for <pgsql-sql@postgresql.org>; Thu,  7 Apr 2005 13:06:27 +0100 (BST)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-30.mail.demon.net with esmtp (Exim 4.42)
	id 1DJVmI-000NFa-0q; Thu, 07 Apr 2005 12:06:30 +0000
Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id BD30716AC6; Thu,  7 Apr 2005 13:06:27 +0100 (BST)
Message-ID: <42552244.5050603@archonet.com>
Date: Thu, 07 Apr 2005 13:06:28 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: dpandey@secf.com
Cc: 'PostgreSQL' <pgsql-sql@postgresql.org>
Subject: Re: Table PARTITION
References: <20050407114136.17B7E53AA5@svr1.postgresql.org>
In-Reply-To: <20050407114136.17B7E53AA5@svr1.postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.059 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200504/72
X-Sequence-Number: 20965

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



From dpandey@secf.com Thu Apr  7 12:13:12 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 5ED8553823
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  7 Apr 2005 13:13:12 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 46656-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  7 Apr 2005 12:13:02 +0000 (GMT)
Received: from secf.com (209-128-104-244.bayarea.net [209.128.104.244])
	by svr1.postgresql.org (Postfix) with ESMTP id 7328453821
	for <pgsql-sql@postgresql.org>; Thu,  7 Apr 2005 13:13:01 +0100 (BST)
Received: from dinesh by secf.com (MDaemon.PRO.v7.2.2.R)
	with ESMTP id md50000590413.msg
	for <pgsql-sql@postgresql.org>; Thu, 07 Apr 2005 05:12:20 -0700
Reply-To: <dpandey@secf.com>
From: "Dinesh Pandey" <dpandey@secf.com>
To: "'Richard Huxton'" <dev@archonet.com>
Cc: "'PostgreSQL'" <pgsql-sql@postgresql.org>
Subject: Re: Table PARTITION
Date: Thu, 7 Apr 2005 17:39:39 +0530
Organization: Second Foundation India P Ltd.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook, Build 11.0.5510
Thread-Index: AcU7aky9eK57pC5EQ06X9bLl2ZOAhAAACZOg
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
In-Reply-To: <42552244.5050603@archonet.com>
X-Authenticated-Sender: dpandey@secf.com
X-Spam-Processed: mail.secf.com, Thu, 07 Apr 2005 05:12:20 -0700
	(not processed: message from valid local sender)
X-MDRemoteIP: 220.227.32.11
X-Return-Path: dpandey@secf.com
X-MDaemon-Deliver-To: pgsql-sql@postgresql.org
X-MDAV-Processed: mail.secf.com, Thu, 07 Apr 2005 05:12:22 -0700
Message-Id: <20050407121301.7328453821@svr1.postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.42 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, MSGID_FROM_MTA_ID
X-Spam-Level: *
X-Archive-Number: 200504/73
X-Sequence-Number: 20966

 
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





From sdavis2@mail.nih.gov Thu Apr  7 12:17:07 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 76DB052A84
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  7 Apr 2005 13:17:07 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 49082-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  7 Apr 2005 12:16:56 +0000 (GMT)
Received: from mx5.net.nih.gov (mailfwd.nih.gov [165.112.130.36])
	by svr1.postgresql.org (Postfix) with ESMTP id C0FF453AB1
	for <pgsql-sql@postgresql.org>; Thu,  7 Apr 2005 13:16:54 +0100 (BST)
Received: from mx5.net.nih.gov (localhost [127.0.0.1])
	by mx5.net.nih.gov (8.12.10/8.11.6) with ESMTP id j37CGvas019186
	for <pgsql-sql@postgresql.org>; Thu, 7 Apr 2005 08:16:57 -0400
Received: from [128.231.145.14] (holmes.nhgri.nih.gov [128.231.145.14])
	by mx5.net.nih.gov (8.12.10/8.11.6) with ESMTP id j37CGvKd019183;
	Thu, 7 Apr 2005 08:16:57 -0400
In-Reply-To: <42552244.5050603@archonet.com>
References: <20050407114136.17B7E53AA5@svr1.postgresql.org>
	<42552244.5050603@archonet.com>
Mime-Version: 1.0 (Apple Message framework v619.2)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <e5f9c6fea13bb95521003dbdb65ad5f2@mail.nih.gov>
Content-Transfer-Encoding: 7bit
Cc: "'PostgreSQL'" <pgsql-sql@postgresql.org>, dpandey@secf.com
From: Sean Davis <sdavis2@mail.nih.gov>
Subject: Re: Table PARTITION
Date: Thu, 7 Apr 2005 08:16:16 -0400
To: Richard Huxton <dev@archonet.com>
X-Mailer: Apple Mail (2.619.2)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200504/74
X-Sequence-Number: 20967

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




From dev@archonet.com Thu Apr  7 12:33:01 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id F3D6D52962
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  7 Apr 2005 13:33:01 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 54226-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  7 Apr 2005 12:32:53 +0000 (GMT)
Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net
	[194.217.242.92])
	by svr1.postgresql.org (Postfix) with ESMTP id 4DA7852808
	for <pgsql-sql@postgresql.org>; Thu,  7 Apr 2005 13:32:52 +0100 (BST)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-34.mail.demon.net with esmtp (Exim 4.42)
	id 1DJWBr-000D4M-FA; Thu, 07 Apr 2005 12:32:55 +0000
Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id A190317C9D; Thu,  7 Apr 2005 13:32:52 +0100 (BST)
Message-ID: <42552875.6040203@archonet.com>
Date: Thu, 07 Apr 2005 13:32:53 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: dpandey@secf.com
Cc: 'PostgreSQL' <pgsql-sql@postgresql.org>
Subject: Re: Table PARTITION
References: <200504071213.j37CD6821117@s1.uklinux.net>
In-Reply-To: <200504071213.j37CD6821117@s1.uklinux.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.059 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200504/76
X-Sequence-Number: 20969

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



From dev@archonet.com Thu Apr  7 12:37:56 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 865B653838
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  7 Apr 2005 13:37:56 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 53570-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  7 Apr 2005 12:37:48 +0000 (GMT)
Received: from anchor-post-36.mail.demon.net (anchor-post-36.mail.demon.net
	[194.217.242.86])
	by svr1.postgresql.org (Postfix) with ESMTP id C8DD553821
	for <pgsql-sql@postgresql.org>; Thu,  7 Apr 2005 13:37:47 +0100 (BST)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-36.mail.demon.net with esmtp (Exim 4.42)
	id 1DJWGb-000Pn8-Lg; Thu, 07 Apr 2005 12:37:49 +0000
Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 98A6E17C06; Thu,  7 Apr 2005 13:37:46 +0100 (BST)
Message-ID: <4255299B.3080101@archonet.com>
Date: Thu, 07 Apr 2005 13:37:47 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Sean Davis <sdavis2@mail.nih.gov>
Cc: 'PostgreSQL' <pgsql-sql@postgresql.org>, dpandey@secf.com
Subject: Re: Table PARTITION
References: <20050407114136.17B7E53AA5@svr1.postgresql.org>
	<42552244.5050603@archonet.com>
	<e5f9c6fea13bb95521003dbdb65ad5f2@mail.nih.gov>
In-Reply-To: <e5f9c6fea13bb95521003dbdb65ad5f2@mail.nih.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.059 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200504/77
X-Sequence-Number: 20970

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



From weigelt@metux.de Fri Apr  8 01:47:47 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 4F22553869
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  8 Apr 2005 02:47:47 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 84278-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  8 Apr 2005 01:47:44 +0000 (GMT)
Received: from metux.de (seven.metux.de [193.16.1.1])
	by svr1.postgresql.org (Postfix) with ESMTP id 784D55382A
	for <pgsql-sql@postgresql.org>; Fri,  8 Apr 2005 02:47:42 +0100 (BST)
Received: (from weigelt@localhost)
	by metux.de (8.12.10/8.12.10) id j381ljEi020624
	for pgsql-sql@postgresql.org; Fri, 8 Apr 2005 03:47:45 +0200
Date: Fri, 8 Apr 2005 03:47:45 +0200
From: Enrico Weigelt <weigelt@metux.de>
To: pgsql-sql@postgresql.org
Subject: Re: Table PARTITION
Message-ID: <20050408014744.GB18450@nibiru.borg.metux.de>
Reply-To: weigelt@metux.de
References: <20050407114136.17B7E53AA5@svr1.postgresql.org>
	<42552244.5050603@archonet.com>
	<e5f9c6fea13bb95521003dbdb65ad5f2@mail.nih.gov>
	<4255299B.3080101@archonet.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4255299B.3080101@archonet.com>
User-Agent: Mutt/1.4.1i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200504/83
X-Sequence-Number: 20976

* 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.de
  cellphone: +49 174 7066481
---------------------------------------------------------------------
 -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------



From dpandey@secf.com Fri Apr  8 04:55:32 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 2B1AE537AF
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  8 Apr 2005 05:55:32 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 71137-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  8 Apr 2005 04:55:22 +0000 (GMT)
Received: from secf.com (209-128-104-244.BAYAREA.NET [209.128.104.244])
	by svr1.postgresql.org (Postfix) with ESMTP id DEAEB53C77
	for <pgsql-sql@postgresql.org>; Fri,  8 Apr 2005 05:55:21 +0100 (BST)
Received: from dinesh by secf.com (MDaemon.PRO.v7.2.2.R)
	with ESMTP id md50000593627.msg
	for <pgsql-sql@postgresql.org>; Thu, 07 Apr 2005 21:54:52 -0700
Reply-To: <dpandey@secf.com>
From: "Dinesh Pandey" <dpandey@secf.com>
To: <weigelt@metux.de>, <pgsql-sql@postgresql.org>
Subject: Re: Table PARTITION
Date: Fri, 8 Apr 2005 10:22:14 +0530
Organization: Second Foundation India P Ltd.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook, Build 11.0.5510
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
Thread-Index: AcU73sEyKLQ3jSsmRKSrpF/nZaSylgAF+i/w
In-Reply-To: <20050408014744.GB18450@nibiru.borg.metux.de>
X-Authenticated-Sender: dpandey@secf.com
X-Spam-Processed: mail.secf.com, Thu, 07 Apr 2005 21:54:52 -0700
	(not processed: message from valid local sender)
X-MDRemoteIP: 220.227.32.11
X-Return-Path: dpandey@secf.com
X-MDaemon-Deliver-To: pgsql-sql@postgresql.org
X-MDAV-Processed: mail.secf.com, Thu, 07 Apr 2005 21:54:55 -0700
Message-Id: <20050408045521.DEAEB53C77@svr1.postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.414 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, MSGID_FROM_MTA_ID
X-Spam-Level: *
X-Archive-Number: 200504/84
X-Sequence-Number: 20977

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.de
  cellphone: +49 174 7066481
---------------------------------------------------------------------
 -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster





From nevin.alex@hotmail.com Tue Feb  1 13:36:30 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 54BEB1337B80
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  1 Feb 2011 09:36:30 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 77201-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  1 Feb 2011 13:36:19 +0000 (UTC)
X-Greylist: delayed 00:05:09.674874 by SQLgrey-1.7.6
Received: from snt0-omc2-s50.snt0.hotmail.com (snt0-omc2-s50.snt0.hotmail.com
	[65.54.61.101])
	by mail.postgresql.org (Postfix) with ESMTP id BA55613364DE
	for <pgsql-sql@postgresql.org>; Tue,  1 Feb 2011 09:36:18 -0400 (AST)
Received: from SNT104-W7 ([65.55.90.72]) by snt0-omc2-s50.snt0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.4675);
	Tue, 1 Feb 2011 05:31:08 -0800
Message-ID: <SNT104-W7CDE3E5FD5456E3F651358BE50@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_45959291-c2f6-4b5e-adb9-cce94b1190d4_"
X-Originating-IP: [202.164.140.105]
From: NEVIN ALEX <nevin.alex@hotmail.com>
To: <pgsql-sql@postgresql.org>
Subject: TABLE PARTITION
Date: Tue, 1 Feb 2011 19:01:08 +0530
Importance: Normal
MIME-Version: 1.0
X-OriginalArrivalTime: 01 Feb 2011 13:31:08.0514 (UTC)
	FILETIME=[48B4E420:01CBC214]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.401 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001,
	RCVD_IN_DNSWL_NONE=-0.0001,
	RFC_ABUSE_POST=0.001, SUBJ_ALL_CAPS=1.506, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201102/2
X-Sequence-Number: 35078

--_45959291-c2f6-4b5e-adb9-cce94b1190d4_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


Hi=2C
      I am Nevin Alex and I am using postgresql database for a year . But I=
 have=92nt used table partitions: Please help me to do it in a better way. =
I got it from the documentation that it is an inheritance capability and th=
e Trigger working but for dynamic data how can I implement that.
=20
Thanks in advance 		 	   		  =

--_45959291-c2f6-4b5e-adb9-cce94b1190d4_
Content-Type: text/html; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style><!--
.hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
font-size: 10pt=3B
font-family:Tahoma
}
--></style>
</head>
<body class=3D'hmmessage'>
<P style=3D"MARGIN: 0in 0in 0pt" class=3DMsoNormal><FONT size=3D3><FONT fac=
e=3DCalibri>Hi=2C<?xml:namespace prefix =3D o ns =3D "urn:schemas-microsoft=
-com:office:office" /><o:p></o:p></FONT></FONT></P>
<P style=3D"MARGIN: 0in 0in 0pt" class=3DMsoNormal><FONT size=3D3><FONT fac=
e=3DCalibri>&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B&nbsp=3B I am Nevin Alex and I =
am using postgresql database for a year . But I have=92nt used table partit=
ions: Please help me to do it in a better way. I got it from the documentat=
ion that it is an inheritance capability and the Trigger working but for dy=
namic data how can I implement that.<o:p></o:p></FONT></FONT></P>
<P style=3D"MARGIN: 0in 0in 0pt" class=3DMsoNormal><o:p><FONT size=3D3 face=
=3DCalibri>&nbsp=3B</FONT></o:p></P>
<P style=3D"MARGIN: 0in 0in 0pt" class=3DMsoNormal><FONT size=3D3><FONT fac=
e=3DCalibri>Thanks in advance<o:p></o:p></FONT></FONT></P> 		 	   		  </bod=
y>
</html>=

--_45959291-c2f6-4b5e-adb9-cce94b1190d4_--



From vibhor.aim@gmail.com Sun Feb  6 12:11:10 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id BCBDB1337B49
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun,  6 Feb 2011 08:11:10 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 02408-03 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun,  6 Feb 2011 12:11:04 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pw0-f46.google.com (mail-pw0-f46.google.com
	[209.85.160.46])
	by mail.postgresql.org (Postfix) with ESMTP id 7FD75133616B
	for <pgsql-sql@postgresql.org>; Sun,  6 Feb 2011 08:11:03 -0400 (AST)
Received: by pwj3 with SMTP id 3so747329pwj.19
	for <pgsql-sql@postgresql.org>; Sun, 06 Feb 2011 04:11:03 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:subject:mime-version:content-type:from
	:in-reply-to:date:cc:content-transfer-encoding:message-id:references
	:to:x-mailer; bh=rY/HPdnBkXmoTiHEytLoapyYt/lqDSDCycKnhDJ0phs=;
	b=eWuc9wVS6gejojNBxjFJWvurinBlh4tK3c+PszdsE0OqSgf2Ivo9ENyFa7j7Lqz+V1
	XSuQW/Oy5d2xWJotp6CoMS3G7k/BwNA9xK1HH2JcuMnymcp+DTicw/E0fTw99euphWaN
	1YBwjAGs1n6JGrKXimJuyIfUfM1OSoPRQUFBA=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=subject:mime-version:content-type:from:in-reply-to:date:cc
	:content-transfer-encoding:message-id:references:to:x-mailer;
	b=KkdsaEM8t/htOrTg+ile/jVm959qMwi0LmsVlAvYz9OI0vYgOaDYGgd2fWTg12Q7p0
	6iEzNO+rjRnaYuXGFGhscUDtftfzCVBQbyW7y+ttG53xX2n2NCSAhsdXSkHxoznjf+Pl
	b0i8z209Ya3JYTQU+ju9LglYlJuXv5nJXwmkk=
Received: by 10.142.221.7 with SMTP id t7mr14061384wfg.440.1296994263377;
	Sun, 06 Feb 2011 04:11:03 -0800 (PST)
Received: from [211.177.245.79] ([211.177.245.79])
	by mx.google.com with ESMTPS id c3sm4208538wfa.14.2011.02.06.04.10.59
	(version=TLSv1/SSLv3 cipher=RC4-MD5);
	Sun, 06 Feb 2011 04:11:02 -0800 (PST)
Subject: Re: TABLE PARTITION
Mime-Version: 1.0 (Apple Message framework v1082)
Content-Type: text/plain; charset=windows-1252
From: Vibhor Kumar <vibhor.aim@gmail.com>
In-Reply-To: <SNT104-W7CDE3E5FD5456E3F651358BE50@phx.gbl>
Date: Sun, 6 Feb 2011 21:10:54 +0900
Cc: <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <6BC58448-5C8D-4823-95AA-4C813E5BB1D2@gmail.com>
References: <SNT104-W7CDE3E5FD5456E3F651358BE50@phx.gbl>
To: NEVIN ALEX <nevin.alex@hotmail.com>
X-Mailer: Apple Mail (2.1082)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_LOW=-0.7
X-Spam-Level: 
X-Archive-Number: 201102/20
X-Sequence-Number: 35096


On Feb 1, 2011, at 10:31 PM, NEVIN ALEX wrote:

> Hi,
>       I am Nevin Alex and I am using postgresql database for a year . =
But I have=92nt used table partitions: Please help me to do it in a =
better way. I got it from the documentation that it is an inheritance =
capability and the Trigger working but for dynamic data how can I =
implement that.
> =20

Please elaborate more about the data which you are talking about.


> Thanks in advance