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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
