Re: Declarative partitioning

On 18 August 2015 at 18:31, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> > 2. Creating a partition of a partitioned table
> >
> > CREATE TABLE table_name
> > PARTITION OF partitioned_table_name
> > FOR VALUES values_spec;
> >
> > Where values_spec is:
> >
> > listvalues: [IN] (val1, ...)
> >
> > rangevalues: START (col1min, ... ) END (col1max, ... )
> > | START (col1min, ... )
> > | END (col1max, ... )
>
> So, one thing I missed in here is anything about automated partitioning
> of tables; that is, creating new partitions based on incoming data or a
> simple statement which doesn't require knowledge of the partitioning
> scheme. It's possible (and entirely accceptable) that you're
> considering automated partition creation outside of the scope of this
> patch.

I would like to make automatic partitioning outside the scope of this first
patch.

However, for range partitions, it would be *really* useful to
> have this syntax:
>
> CREATE NEXT PARTITION ON parent_table;
>
> Which would just create the "next" partition based on whatever the range
> partitoning scheme is, instead of requiring the user to calculate start
> and end values which might or might not match the parent partitioning
> scheme, and might leave gaps. Also this would be useful for range
> partitions:
>
> CREATE PARTITION ON parent_table USING ( start_value );
>
> ... where start_value is the start range of the new partition. Again,
> easier for users to get correct.
>
> Both of these require the idea of regular intervals for range
> partitions, that is, on a table partitioned by month on a timestamptz
> column, each partition will have the range [ month:1, nextmonth:1 ).
> This is the most common use-case for range partitions (like, 95% of all
> partitioning cases I've seen), so a new partitioning scheme ought to
> address it.
>
> While there are certainly users who desire the ability to define
> arbitrary ranges for each range partition, these are by far the minority
> and could be accomodated by a different path with more complex syntax.
> Further, I'd wager that most users who want to define arbitrary ranges
> for range partitions aren't going to be satisfied with the other
> restrictions on declarative partitioning (e.g. same constraints, columns
> for all partitions) and are going to use inheritance partitioning anyway.

I like the idea of a regular partitioning step because it is how you design
such tables - "lets use monthly partitions".

This gives sanely terse syntax, rather than specifying pages and pages of
exact values in DDL....

PARTITION BY RANGE ON (columns) INCREMENT BY (INTERVAL '1 month' ) START
WITH value;

borrowing the same concepts from sequence syntax.

> Creating index on parent is not allowed. They should be defined on (leaf)

> > partitions. Because of this limitation, primary keys are not allowed on a
> > partitioned table. Perhaps, we should be able to just create a dummy
> > entry somewhere to represent an index on parent (which every partition
> > then copies.)
>
> This would be preferable, yes. Making users remember to manually create
> indexes on each partition is undesirable.

I think it is useful to allow additional indexes on partitions, if desired,
but we should always automatically build the indexes that are defined on
the master when we create a new partition.

Presumably unique indexes will be allowed on partitions. So if the
partition key is unique, we can say the whole partitioned table is unique
and call that a Primary Key.

I would want individual partitions to be placed on separate tablespaces,
but not by default.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services