X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 59B0452A4F for ; Tue, 22 Mar 2005 08:35:37 +0000 (GMT) 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 89155-08 for ; Tue, 22 Mar 2005 08:35:28 +0000 (GMT) Received: from cmailm2.svr.pol.co.uk (cmailm2.svr.pol.co.uk [195.92.193.210]) by svr1.postgresql.org (Postfix) with ESMTP id D3B5E5650D for ; Tue, 22 Mar 2005 08:35:23 +0000 (GMT) Received: from modem-2299.llama.dialup.pol.co.uk ([217.135.184.251] helo=192.168.0.102) by cmailm2.svr.pol.co.uk with esmtp (Exim 4.41) id 1DDerA-0005g9-LJ; Tue, 22 Mar 2005 08:35:21 +0000 Subject: Re: What needs to be done for real Partitioning? From: Simon Riggs To: Greg Stark Cc: Josh Berkus , pgsql-performance@postgresql.org In-Reply-To: <87br9ehmja.fsf@stark.xeocode.com> References: <200503191202.38089.josh@agliodbs.com> <87br9ehmja.fsf@stark.xeocode.com> Content-Type: text/plain Organization: 2nd Quadrant Date: Tue, 22 Mar 2005 08:34:22 +0000 Message-Id: <1111480462.11750.510.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-3) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.048 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/428 X-Sequence-Number: 11133 On Sun, 2005-03-20 at 01:14 -0500, Greg Stark wrote: > Josh Berkus writes: > > > -- INSERT INTO should automatically create new partitions where necessary > > ---- new tables should automatically inherit all constraints, indexes, > > keys of "parent" table > > I think you're going about this backwards. Certainly, there are two schools of thought here. I have been in two minds about which those two designs previously, and indeed here which one to support. > Phase I should be an entirely manual system where you add and remove > partitions manually and create and drop indexes you want manually. You need > these low level interfaces anyways for a complete system, it doesn't make > sense to have everything automatic and then later try to wedge in a low level > interface. Only once you have that do you then start offering options to do > these things automatically. Maybe its just me, but ISTM that implementing an automatic system is actually easier to begin with. No commands, no syntax etc. You're right, you need the low level interfaces anyway... > From my experience with Oracle I think there's one big concept that makes the > whole system make a lot more sense: individual partitions are really tables. > The partitioned tables themselves are just meta-objects like views. Hmmm. Oracle provides a very DBA-intensive implementation that as Stacy points out, many people still do not understand. It does work, well. And has many of the wrinkles ironed out, even if not all of them are easy to understand why they exist at first glance. I think it most likely that Phase I should be a simplified blend of both ideas, with a clear view towards minimum impact and implementability, otherwise it may not make the cut for 8.1 Best Regards, Simon Riggs