Re: Declarative partitioning grammar - Mailing list pgsql-hackers

On Jan 14, 2008, at 1:49 AM, Markus Schiltknecht wrote:

> I don't think the separation into list, hash and range partitioning  
> is adequate. What is the system supposed to do, if you try to  
> insert a row which doesn't fit any of the values in your list or  
> doesn't fit any of the ranges you defined?

Hi Markus,

If you don't define a "default" partition to handle outliers,  the  
insert should fail with an error.

> I prefer a partitioning grammar which doesn't interfere with  
> constraints. We all know how to define constraints. Please don't  
> introduce a new, ambiguous way. A partitioning definition should be  
> able to tell the target partition for *every* row which satisfies  
> the constraints (the real ones, not ambiguous ones).
>
> IMO, a single DDL command should only touch a single split point,  
> i.e. split a table into two partitions, move the split point or  
> remove the split point (joining the partitions again). Those are  
> the only basic commands you need to be able to handle partitioning.

I can certainly appreciate the simplicity of this approach.  It lets  
us use a generic check constraint to perform partitioning, so it is  
more general than partitioning using hash, list, and range.  However,  
it achieves this generality at the expense of usability for typical  
customer cases.  For example, let's look at the case of a table of 1  
year of sales data, where we want to create 12 partitions -- one for  
each month.

With the generic approach, you start with a single table, and start  
by splitting it into two six-month partitions:

ALTER TABLE sales  SPLIT where sales_date > date '2007-06-01'   INTO    (     PARTITION first_half     PARTITION
second_half    );
 

We could implement this approach using check constraints and table  
inheritance: the partition second_half is a child table where  
sales_date > date '2007-06-01', and the partition first_half has the  
complementary constraint NOT(sales_date > date '2007-06-01').

Next, you split each partition:

ALTER TABLE sales  SPLIT PARTITION first_half where sales_date > date '2007-03-01'   INTO    (     PARTITION
first_quarter    PARTITION second_quarter     );
 

So now the child table for first_half itself has two children.  As  
you continue this process you construct a binary tree of table  
inheritance using 12 ALTER statements.

In the "long" grammar you can create and partition the table in one  
statement:

CREATE TABLE sales
...
PARTITION BY sales_date
(
start (date '2007-01-01') end (date '2008-01-01')
every (interval '1 month')
);

> Sorry, but for my taste, the proposed grammar is too long per  
> command, not flexible enough and instead ambiguous for split points  
> as well as for constraints. To me it looks like repeating the  
> mistakes of others.

Thanks for your feedback.  Partitioning the table using series of  
splits is a clever solution for situations where the partitioning  
operation cannot be described using simple equality (like list,hash)  
or ordered comparison (range).  But for many common business cases,  
the "long" grammar is easier to specify.

kind regards,

Jeff