Re: Release plans for improvements to partitioning

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