Hi,
On 24.08.2017 22:44, Andres Freund 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
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.
The good thing about multiple indexes is that they are more compact and
manageable.
--
Ildar Musin
i.mu...@postgrespro.ru
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
