Hi Jeff,
Jeff Cohen wrote:
If you don't define a "default" partition to handle outliers, the
insert should fail with an error.
IMO, you should always have a "default" partition, then, so as not to
violate the constraints (by rejecting tuples which are correct according
to the constraints).
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.
<nitpicking>There are just 11 splits between 12 months, otherwise
correct, yes.</nitpicking>
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')
);
To be fair, you should add the 12 partition names here as well.
I can certainly see merit in letting the database system handle the
binary tree.
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.
Easier to specify initially, maybe, yes. But how about managing it
afterwards? Having seen all the different options for merging,
splitting, exchanging, coalescing and adding, all of them with small
little differences for hash, range and list partitioning - let alone
sub-partitioning - with all of that, the proposed grammar doesn't look
particularly easy to me.
Let's at least drop the differences for list, hash and range
partitioning, those are pretty unneeded, IMO.
Regards
Markus
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
