Re: [HACKERS] Proposal: global index
| От | Chris Travers |
|---|---|
| Тема | Re: [HACKERS] Proposal: global index |
| Дата | |
| Msg-id | CAN-RpxA=ovSN2JM-JbAH+YAm5nFnzoeq_MQaPvRVcy0Q02pnBQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [HACKERS] Proposal: global index (Petr Jelinek <petr.jelinek@2ndquadrant.com>) |
| Список | pgsql-hackers |
On Fri, Aug 25, 2017 at 12:15 PM, Petr Jelinek <petr.jelinek@2ndquadrant.com> wrote:
On 25/08/17 10:28, Chris Travers wrote:
>
>
> On Thu, Aug 24, 2017 at 9:44 PM, Andres Freund <andres@anarazel.de+1 (or +as much as I am allowed to get away with really ;) )> <mailto:andres@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.
>
>
> 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.
Ok so in this case a global constraint needs to track partitioned indexes, right?
How does this differ, in practice from a "global but partitioned index?" This seems like splitting hairs but I am trying to see if there is disagreement here that goes beyond language.
For example, could I have a global, partial unique constraint the way I can do things with a single table currently (something like create global unique index foo_id_idxuf on foo(Id) where id > 12345)? Is this something the discussion here would foreclose?
It seems to me that you can get both of these (and more) by adding the concept of a global index which means:
1. Index is on parent table
2. Index is inherited to child tables and managed on parent.
3. Writes to children that hit inherited unique index ALSO must check (with exclusion constraints etc) ALL other tables in the inheritance tree of the index.
That would also have a few important side benefits:
1. Easier management of indexes where all partitions (or other children since there are other uses for table inheritance than partitioning) must be indexed
2. Ability to have partial unique indexes enforced consistently across an inheritance tree.
An alternative might be to generalise partial unique indexes into partial unique constraints..... (alter table foo add unique (bar) where id > 12345)
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Best Regards,
Chris Travers
Database Administrator
Saarbrücker Straße 37a, 10405 Berlin
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Vinayak PokaleДата:
Сообщение: Re: [HACKERS] ECPG: WHENEVER statement with DO CONTINUE action