Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 958461337BB4
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 15 Mar 2011 07:10:27 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 50349-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 15 Mar 2011 10:10:18 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp1.infomaniak.ch (smtp1.infomaniak.ch [84.16.68.89])
	by mail.postgresql.org (Postfix) with ESMTP id 70D751337BC8
	for <pgsql-performance@postgresql.org>;
	Tue, 15 Mar 2011 07:10:18 -0300 (ADT)
Received: from [192.168.25.106] (reverse.completel.net [92.103.1.80] (may be
	forged)) (authenticated bits=0)
	by smtp1.infomaniak.ch (8.14.2/8.14.2) with ESMTP id p2FAA7Ca025270
	(version=TLSv1/SSLv3 cipher=AES256-SHA bits=256 verify=NO);
	Tue, 15 Mar 2011 11:10:09 +0100
Message-ID: <4D7F3AF9.2030207@intesens.com>
Date: Tue, 15 Mar 2011 11:10:01 +0100
From: Samba GUEYE <samba.gueye@intesens.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; fr;
	rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
To: Robert Haas <robertmhaas@gmail.com>
CC: Jim Nasby <jim@nasby.net>,
	Postgre Performance <pgsql-performance@postgresql.org>, ctw@adverb.ly
Subject: Re: Table partitioning problem
References: <4D764F04.7020400@intesens.com>
	<04931568-A1CA-4B5D-B907-E061713E1B54@nasby.net>
	<4D788AE2.7010004@intesens.com>
	<AANLkTikxt=JTY+dMq769vGOnTYQRmk2O1hJ0YsD5vKLW@mail.gmail.com>
	<4D7E455E.5050908@intesens.com>
	<AANLkTinxQ53kCc+V4XHtWmtHw_utwkbuwFz8qVLPg=Hp@mail.gmail.com>
In-Reply-To: <AANLkTinxQ53kCc+V4XHtWmtHw_utwkbuwFz8qVLPg=Hp@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------090202040701040909090501"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.609 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, MAY_BE_FORGED=0.29,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201103/152
X-Sequence-Number: 42831

This is a multi-part message in MIME format.
--------------090202040701040909090501
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Alright thanks all of you for your answers, but i've got 3 more questions :

   1. Why "... partitionning is not a good idea ..." like you said
      Robert and Conor "... I grant that it would be better to never
      need to do that" ?
   2. Is there another way or strategy to deal with very large tables
      (over 100 000 000 rows per year in one table)  beyond indexing and
      partitionning?
   3. If you had to quantify a limit of numbers of rows per table in a
      single postgresql database server what would you say?

PS: i'm using postgresql since less than 2 month because i thought that 
partitioning was a possible solution  that doesn't offer me Apache Derby 
for my large table problem so if these questions sounds "dummy" for you 
this is a postgresql novice talking to you.

Regards

Le 14/03/2011 20:40, Robert Haas a écrit :
> On Mon, Mar 14, 2011 at 12:42 PM, Samba GUEYE<samba.gueye@intesens.com>  wrote:
>> Yeah but is there a workaround to force the root table to propagate the
>> foreign key to the partitionned table
>> because right now all foreign keys to partitionned table throws constraints
>> violation and it's a big problem for me
> No.  Generally, table partitioning is not a good idea unless you are
> dealing with really large tables, and nearly all of your queries apply
> only to a single partition.  Most likely you are better off not using
> table inheritance in the first place if you need this feature.
>
> It would be nice if we had a way to do this for the rare cases where
> it would be useful, but we don't.
>


--------------090202040701040909090501
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#ffffff">
    Alright thanks all of you for your answers, but i've got 3 more
    questions : <br>
    <ol>
      <li>Why "... partitionning is not a good idea ..." like you said
        Robert and Conor "... I grant that it would be better to never
        need to do that" ?</li>
      <li>Is there another way or strategy to deal with very large
        tables (over 100 000 000 rows per year in one table)&nbsp; beyond
        indexing and partitionning?</li>
      <li>If you had to quantify a limit of numbers of rows per table in
        a single postgresql database server what would you say?<br>
      </li>
    </ol>
    PS: i'm using postgresql since less than 2 month because i thought
    that partitioning was a possible solution&nbsp; that doesn't offer me
    Apache Derby for my large table problem so if these questions sounds
    "dummy" for you this is a postgresql novice talking to you.<br>
    <br>
    Regards<br>
    <br>
    Le 14/03/2011 20:40, Robert Haas a &eacute;crit&nbsp;:
    <blockquote
      cite="mid:AANLkTinxQ53kCc+V4XHtWmtHw_utwkbuwFz8qVLPg=Hp@mail.gmail.com"
      type="cite">
      <pre wrap="">On Mon, Mar 14, 2011 at 12:42 PM, Samba GUEYE <a class="moz-txt-link-rfc2396E" href="mailto:samba.gueye@intesens.com">&lt;samba.gueye@intesens.com&gt;</a> wrote:
</pre>
      <blockquote type="cite">
        <pre wrap="">Yeah but is there a workaround to force the root table to propagate the
foreign key to the partitionned table
because right now all foreign keys to partitionned table throws constraints
violation and it's a big problem for me
</pre>
      </blockquote>
      <pre wrap="">
No.  Generally, table partitioning is not a good idea unless you are
dealing with really large tables, and nearly all of your queries apply
only to a single partition.  Most likely you are better off not using
table inheritance in the first place if you need this feature.

It would be nice if we had a way to do this for the rare cases where
it would be useful, but we don't.

</pre>
    </blockquote>
    <br>
  </body>
</html>

--------------090202040701040909090501--