Re: On partitioning

On 09/01/2014 04:03 AM, Tom Lane wrote:

> 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.

... and a lot of that comes down to what use cases the partitioning is
meant to handle, and what people are expected to continue to DIY with
inheritance.

Simple range and hash partitioning are the main things being discussed.

Other moderately common partitioning uses seem to be hot/cold
partitioning, usually on unequal ranges, and closely related live/dead
partitioning for apps that soft-delete data.

In both those you may well want to suppress indexes on the cold/dead
portion, much like we currently have partial indexes.

In fact, how different is an index that's present on only a subset of
partitions to a partial index, in planning terms? We know the partitions
it is/isn't on, after all, and can form an expression that finds just
those partitions.

(I guess the answer there is that partial index planning is probably not
smart enough to be useful for this).

> 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.

Seems to be like a "make room to support it in future, but don't do it
now" thing.

Partitioning schemes like:

[prior years]
[last year]
[this year]
[this month]
[this week]

could benefit from it, but they also need things like online
repartitioning, updates to move tuples across partitions, etc.

So it's all in the "let's not lock it out for the future, but lets not
tackle it now either" box.

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