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.
