On Fri, Aug 25, 2017 at 6:52 AM, Ildar Musin <i.mu...@postgrespro.ru> wrote: > I agree with you that garbage collection after partitions drop could be a > major downside of single index scheme. On the other hand not all > partitioning use-cases imply dropping partitions. What worries me about > global unique index built on multiple local indexes is the need to lookup > (almost) every index for every insert/update/FK check. In some cases we can > reduce the number of the indexes to be checked (e.g. by storing min/max > values in metapage), but it will not be possible if key values are spread > across indexes evenly. And it can get quite expensive as partition count > grows.
+1. I think that in the end we probably need both things for different use cases. Some people are going to want 1000 partitions (or, if they can get away with it, 100,000 partitions) and be able to do lookups on a secondary key without searching O(n) indexes. Other people are going to want partitioned indexes so that they can drop them quickly, vacuum them quickly, etc. I don't see anything wrong with eventually offering both things. I do think that it might be premature to work on this without solving some of the other problems in this area first. I think a good first step would be to solve all the problems with declaring an index on a parent table and having it cascade down to all children - i.e. a partitioned index - cf. https://www.postgresql.org/message-id/c8fe4f6b-ff46-aae0-89e3-e936a35f0...@postgrespro.ru - and then work on the problems associated with defining foreign keys reference such an index (e.g. in the case where the index matches the partitioning key, or using the technique Andres describes) - and only then do what you're proposing here, once all of those preliminaries have been sorted out. Otherwise, I fear that this patch will get tangled up in a lot of issues that are really separate concerns. JD is quite right that there are a lot of things about partitioning that need to be improved from where we are today, but I think it's important that we're a bit methodical about how we do that so that we don't end up with a mess. We're not going accept quick hacks in related areas just to get global indexes; all of the issues about how global indexes interact with the SQL syntax, foreign key constraints, partitioned indexes, etc. need to be well-sorted out before we accept a patch for global indexes. It will be easiest, I think, to sort those things out first and add this at the end. That doesn't mean that development can't be done concurrently, but I think what you're likely to find is that getting the actual index machinery to do what you want is a job and a half by itself without burdening the same patch with anything additional. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers