Re: Proposal: global index

Hi,

On 2017-08-18 12:12:58 +0300, Ildar Musin wrote:
> While we've been developing pg_pathman extension one of the most frequent
> questions we got from our users was about global index support. We cannot
> provide it within an extension. And I couldn't find any recent discussion
> about someone implementing it. So I'm thinking about giving it a shot and
> start working on a patch for postgres.

FWIW, I personally think for constraints the better approach is to make
the constraint checking code cope with having to check multiple
indexes. Initially by just checking all indexes, over the longer term
perhaps pruning the set of to-be-checked indexes based on the values in
the partition key if applicable. The problem with creating huge global
indexes is that you give away some the major advantages of partitioning:
- dropping partitions now is slow / leaves a lof of garbage again
- there's no way you can do this with individual partitions being remote
or such
- there's a good chunk of locality loss in global indexes

The logic we have for exclusion constraints checking can essentially be
extended to do uniqueness checking over multiple partitions. Depending
on the desired deadlock behaviour one might end up doing speculative
insertions in addition. The foreign key constraint checking is fairly
simple, essentially one "just" need to remove the ONLY from the
generated check query.

Greetings,

Andres Freund