X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id F2AA111F6C81 for ; Fri, 31 Mar 2006 18:35:28 -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 50043-10 for ; Fri, 31 Mar 2006 18:35:28 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 34D6011F6C7E for ; Fri, 31 Mar 2006 18:35:25 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id F35B956427; Fri, 31 Mar 2006 16:35:25 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Fri, 31 Mar 2006 16:35:25 -0600 Date: Fri, 31 Mar 2006 16:35:25 -0600 From: "Jim C. Nasby" To: Mark Liberman Cc: pgsql-admin@postgresql.org Subject: Re: Release plans for improvements to partitioning Message-ID: <20060331223525.GW49405@pervasive.com> References: <9D938282F8C6EE43B748B910386DE93E0138B420@srvgpimail1.GPI.local> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <9D938282F8C6EE43B748B910386DE93E0138B420@srvgpimail1.GPI.local> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060331:mliberman@mixedsignals.com::Aq2Bh6tbKS2sR6wI:00000000000 0000000000000000000000000LDN X-Hashcash: 1:20:060331:pgsql-admin@postgresql.org::oGOjBossgIi7FGt1:00000000000 0000000000000000000000001MMH X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.108] X-Spam-Score: 0.108 X-Spam-Level: X-Archive-Number: 200603/407 X-Sequence-Number: 21164 On Thu, Mar 30, 2006 at 04:04:04PM -0800, Mark Liberman wrote: > Re-sending: for some reason original post was truncated and did not have carriage returns. If you're going to hard-code CR/LF in, you should do it at the traditional width of 72 characters. Your second email looks way worse on my client than your first. Anyway, someone (Simon maybe?) recently mentioned on -hackers that they're intending to submit a patch for 8.2 with partitioning syntax, which would presumably handle all the mundane stuff you need to do to setup partitioning. > ---------------------------------------- > > I was wondering if anyone has any insight into if/when future improvements to the 8.1 partitioning capabilities are planned. > > The current implementation of partitioning in postgres 8.1 appears to be just a first step. While it would provide some very > nice benefits (such as the absence of vacuum processes after dropping partitions), the additional burden it places on > administrative DDL whenever you add a partition might be a little too daunting to make it a viable solution for us. > > Currently, a single partition, of which we would like to create one per table per day, involves the following definitions: > > 1) Create table definition. This is rather straight-forward ... e.g. CREATE TABLE programs_20060101 INHERITS (programs_1min) > > 2) Check condition. This tells postgres which range of data resides in this partition ... > e.g. CHECK ( end_time >= '2006-01-01' AND end_time < '2006-01-02' ). > > Note: there are no checks to guarantee that these are non-overlapping. > > 3) Insert rule. This tells postgres which partition to insert into for inserts into the master ... e.g. > > CREATE RULE pgm_1min_insert_20060101 AS ON INSERT TO programs_1min > WHERE ( end_time >= '2006-01-01' AND end_time < '2006-01-02' ) > DO INSTEAD INSERT INTO programs_1min_20060101 VALUES ( NEW.programs_id, NEW.begin_time, NEW.end_time); > > Again, there are no guarantees that these are non-overlapping. > > 4) All Indexes. Indexes must be created seperately on each partition. The existance of an index on the master table > does not imply such an index on the underlying partitions. > > This is the major area that would involve too much effort. Whenever a new index is added we would need to write a script > that dynamically added that new index to all partitions of a given table. While this is certainly achievable with scripting, > it simply adds too much margin for error if we are trying to build an automated solution for all of our customers. > > From my understanding, there are other limitations as well, such as: > > 1) No delete rules for deleting across partitions > 2) No update rules for updating across partitions > > In an ideal solution the addition of a new partition would be a single (ALTER TABLE ADD PARTITION) statement > (much like Oracle). This should take care of the table definition, the acceptable ranges (which cannot be > overlapping) and any insert rules. Additionally, the index definition that applies to the table should apply to > all underlying partitions so that any create index statement on the master table would be sufficient to index each > underlying partition. Once created, the partitions should then be "invisible" to the sql writer, such that all > inserts, updates, and deletes appropriately hit only the required partitions. > > Hopefully, some/much of this is underway, but any insight would be appreciated because we need to determine whether we > want to go down the path of implementing a solution based on the current partitioning, or waiting for the next generation > of PG partitioning. > > Thanks, > > Mark > > > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461