Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1

On Thu, Nov 27, 2008 at 8:31 AM, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
>> CREATE PARTITION transaction_2008_11 ON transaction WHERE record_date
>> BETWEEN '2008-11-01' AND '2008-11-30';
>
> I think the main advantage to a better partitioning method would be teaching
> Postgres about the partition key. Instead of a collection of different
> constraints Postgres would know that "record_date" is *always* the partition
> key. So it wouldn't have to be specified every time you declare a partition.

Hmm... I thought the main advantage would be that you wouldn't have
to manually add constraints to all of the child tables, and you
wouldn't have to manually add rules/triggers to the parent table to
redirect DML operations.

What do you see as the advantage of pre-declaring record_date as the
partition key? The major advantage I can think of is that it should
simplify constraint exclusion calculations considerably. Also, you
can easily enforce that partitions are non-overlapping. The
disadvantage is that you can't support more complex partitioning
schemes that can't be expressed in terms of ranges on a single key (an
obvious case is when you want to partition by date AND transaction
type, though that could probably be made to work if you allow
specifying multiple partition keys; less tractable cases are
imaginable).

I guess we could decide we don't care about the more complex
scenarios. Or we could offer:

CREATE TABLE (...) WITH PARTITIONING; -- ad-hoc partitioning
CREATE TABLE (...) WITH PARTITIONING ON (...); -- partition keys must
be non-overlapping slices based only on the given columns

> *But*... in practice I would suggest that cross-table indexes are actually
> very rarely useful. Having them defeats much of the advantage of partitioning
> in the first place. Suddenly you would not be able to instantly drop and load
> whole partitions. They're a big check-list item that people want to have
> before they partition in case they need them but then they find out that the
> down-sides of actually using them makes them quite useless.

That's possible. My every attempt to use inheritance has been stymied
by lack of this feature, but my attempts may not be representative.
In any case, the projects are severable.

> Postgres's current architecture actually has a big advantage over more
> methodical partitioning methods in this case. You can always add additional
> constraints on other columns even if they aren't the "real" partitioning key.
> So for example if you partition the invoice table by month once you close the
> books for a previous month you can add a constraint WHERE invoice_id < 'xxx'.

That's cool.

...Robert