X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 4B5DF9DD561 for ; Wed, 7 Dec 2005 05:31:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05461-09-2 for ; Wed, 7 Dec 2005 05:31:08 -0400 (AST) X-Greylist: delayed 03:20:17.742114 by SQLgrey- Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25]) by postgresql.org (Postfix) with ESMTP id 0D1B79DCD35 for ; Wed, 7 Dec 2005 05:31:03 -0400 (AST) Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [216.148.227.85]) by svr2.postgresql.org (Postfix) with ESMTP id 51CBCF0CAE for ; Wed, 7 Dec 2005 03:07:00 +0000 (GMT) Received: from dell8200 (pcp09613107pcs.rte20201.de.comcast.net[68.82.197.68]) by comcast.net (rwcrmhc12) with SMTP id <20051207030657014008o119e>; Wed, 7 Dec 2005 03:06:59 +0000 From: "Rick Schumeyer" To: Subject: table partitioning: effects of many sub-tables (was COPY too slow...) Date: Tue, 6 Dec 2005 22:06:52 -0500 Message-ID: <000101c5fadb$470c7df0$0200a8c0@dell8200> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0002_01C5FAB1.5E3675F0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.333 required=5 tests=[HTML_MESSAGE=0.001, RCVD_IN_BL_SPAMCOP_NET=1.332] X-Spam-Score: 1.333 X-Spam-Level: * X-Archive-Number: 200512/227 X-Sequence-Number: 16048 This is a multi-part message in MIME format. ------=_NextPart_000_0002_01C5FAB1.5E3675F0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Based on a suggestion on the postgis list, I partitioned my 80 million (for now) record table into subtables of about 230k records (the amount of data collected in five minutes). At the moment I have 350 subtables. Everything seems to be great.COPY time is ok, building a geometric index on "only" 230k records is ok, query performance is ok. I'm a little concerned about having so many subtables. 350 tables is not bad, but what happens if the number of subtables grows into the thousands? Is there a practical limit to the effectiveness partitioning? ------=_NextPart_000_0002_01C5FAB1.5E3675F0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Based on a suggestion on the postgis list, I partitioned my 80 = million (for now) record table into

subtables of about 230k records (the amount of data collected in = five minutes).  At the moment

I have 350 subtables.

 

Everything seems to be great…COPY time is ok, building a geometric index on “only” 230k records

is ok, query performance is ok.

 

I’m a little concerned about having so many subtables. =  350 tables is not bad, but what happens if

the number of subtables grows into the thousands?  Is there = a practical limit to the effectiveness

partitioning?

 

------=_NextPart_000_0002_01C5FAB1.5E3675F0--