Re: On partitioning

On 2014-08-31 16:03:30 -0400, Tom Lane wrote:
> Another thought about this general topic:
>
> Alvaro Herrera <alvherre(at)2ndquadrant(dot)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. So I'm not real sure which
> approach is superior from a purely user-oriented perspective.

I think we're likely to end up with both. In many cases it'll be far
superior from a usability and planning perspective to have indices on
the 'toplevel table' (do we have a good name for that?).

But on the flip side, one of the significant use cases for partitioning
is dealing with historical data. In many cases old data has to be saved
for years but is barely ever queried. It'd be a shame to inflict all
indexes on all partitions for that kind of data. It'd surely be a useful
step to add sane partitioning without that capability, but we shouldn't
base the design on that decision.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services