Re: partitions vs indexes

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: partitions vs indexes
Дата
Msg-id CAHOFxGqoB2PryV7P_UUyj159MmEgSYf9ZzOLPwbZ+bceTfp9aQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: partitions vs indexes  ("Enrico Thierbach" <eno@open-lab.org>)
Ответы Re: partitions vs indexes  ("Enrico Thierbach" <eno@open-lab.org>)
Список pgsql-general
"I would like to convert a table with a primary key into a partitioned setup by a column which is not part of the primary key"

That isn't possible. The partition key must be contained by the primary key. That is, the primary key could be site_id, id and you can create hash partition on id or site_id but not created_on.

You could drop primary key and foreign keys and implement them via trigger functions as described in this blog series, but it seems questionable-

I do not assume the restriction would be dropped in future releases. I don't know that scanning all the partitions to figure out whether the primary key is violated would be advisable. Which is what the trigger functions described in the blog post has to do, right?

It might be noteworthy that partitioning with more than 10-100 partitions is MUCH faster in PG12 than PG11 (up to 4-8 thousand partitions) from testing shared by those working on that code.

В списке pgsql-general по дате отправления:

Предыдущее
От: "Enrico Thierbach"
Дата:
Сообщение: Re: partitions vs indexes
Следующее
От: Aleš Zelený
Дата:
Сообщение: Wall shiping replica failed to recover database with error: invalidcontrecord length 1956 at FED/38FFE208