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
somevery  
> 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