Re: Proposal: global index

On Fri, Aug 25, 2017 at 6:52 AM, Ildar Musin <i(dot)musin(at)postgrespro(dot)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-e936a35f0cfd@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