Re: Partitioning docs (was Re: Range partitioning and overlap)

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> Looks good to me (just read the patch), Thanks! Just one suggestion toward
> the top:

> ...allows you to (specify declaratively => declare) that a table is divided
> into partitions. (specification becomes declaration further down)

Sure. That's a little further from the previous wording than what
I had, but it's probably better.

> I am curious as to your thoughts on unique indexes and whether/how to
> better incorporate advice regarding the use of ON CONFLICT with
> partitioning [1] vis-a-vis the overview's claim of:
> "The partitioning substitutes for leading columns of indexes, reducing
> index size and making it more likely that the heavily-used parts of the
> indexes fit in memory" [2]

From a semantic standpoint, there's no doubt that requiring unique indexes
to include the partition key is fine and necessary. (I tried to explain
why in this rewrite.) In the case of list partitioning with a single
value per partition, it's conceivable that we could drop the partition key
column from the index implementing the constraint on that partition, but
it'd be a bit of a wart and I'm not sure that it'd be worth the trouble.
Anyplace where a partition can have more than one value of the partition
key column, you still need that column in the index.

I think what the docs are talking about here is indexes that are *not*
declared unique, or that are declared unique but are created per-partition
rather than globally. In those cases you can leave off the partition key
and the index will still do what you need.

Possibly a better way to write that claim is that partitioning can
substitute for the upper levels of a huge index, rather than "leading
columns" per se. That way of looking at it is still sensible when
a partition covers more than one value of the key column.

regards, tom lane