On 25/08/17 10:28, Chris Travers wrote: > > > On Thu, Aug 24, 2017 at 9:44 PM, Andres Freund <and...@anarazel.de > <mailto:and...@anarazel.de>> wrote: > > 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. >
+1 (or +as much as I am allowed to get away with really ;) ) > > To be clear, this would still require a high-level concept of a global > index and the only question is whether it gets stored as multiple > partitions against partitioned tables vs stored in one giant index, right? > No, just global constraints. For example, if you consider unique index to be implementation detail of a unique constraint, there is nothing stopping us to use multiple such indexes (one per partition) as implementation detail to single global unique constraint. No need for global index at all. -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
