Re: On partitioning - Mailing list pgsql-hackers

On 08/31/2014 10:03 PM, Tom Lane wrote:
> Another thought about this general topic:
>
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>> ...
>> Allowed actions on a RELKIND_PARTITION:
>> * CREATE INDEX .. ON PARTITION <n> ON TABLE <xyz>
>> ...
>> Still To Be Designed
>> --------------------
>> * Are indexes/constraints inherited from the parent rel?
> I think one of the key design decisions we have to make is whether
> partitions are all constrained to have exactly the same set of indexes.
> If we don't insist on that it will greatly complicate planning compared
> to what we'll get if we do insist on it, because then the planner will
> need to generate a separate customized plan subtree for each partition.
> Aside from costing planning time, most likely that would forever prevent
> us from pushing some types of intelligence about partitioning into the
> executor.
>
> Now, in the current model, it's up to the user what indexes to create
> on each partition, and sometimes one might feel that maintaining a
> particular index is unnecessary in some partitions.  But the flip side
> of that is it's awfully easy to screw yourself by forgetting to add
> some index when you add a new partition.  
The "forgetting" part is easy to solve by inheriting all indexes from
parent (or template) partition unless explicitly told not to.

One other thing that has been bothering me about this proposal
is the ability to take partitions offline for maintenance or to load
them offline ant then switch in.

In current scheme we do this using ALTER TABLE ... [NO] INHERIT ...

If we also want to have this with the not-directly-accessible partitions
then perhaps it could be done by having a possibility to move
a partition between two tables with exactly the same structure ?

> So I'm not real sure which
> approach is superior from a purely user-oriented perspective.
What we currently have is a very flexible scheme which has a few
drawbacks

1) unnecessarily complex for simple case
2) easy to shoot yourself in the foot by forgetting something
3) can be hard on planner, especially with huge number of partitions

An alternative way of solving these problems is adding some
(meta-)constraints to current way of doing things and some more
automation

CREATE TABLE FOR PARTITIONMASTER   WITH (ALL_INDEXES_SAME=ON,             SAME_STRUCTURE_ALWAYS=ON,
SINGLE_INHERITANCE_ONLY=ON,            NESTED_INHERITS=OFF,
PARTITION_FUNCTION=default_range_partitioning(int)
);

and then force these when adding inherited tables (in this case
partition tables)
either via CREATE TABLE or ALTER TABLE

Best Regards

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ