synchronous partitioning of referenced tables

[parihaaraka]

The very typical case is several big tables with foreign keys (e.g. transactions <- posts). We could (have to) add the same field to split on, but foreign key constraints and referenced tables are out of scope of pg_pathman. If inherited tables can't share pk of their parent, pg_pathman should help to solve the fk problem (OR there is a way to apply a single pk on all child tables in case of being referenced by equally partitioned table?).

[yazun]

Yes, this would be very desirable for us too. The main complication with partitioning in PG is lack of FK support (also in the planner). This plus composite keys partitioning would cover most of our requirements.

[funbringer]

To @parihaaraka:

Sorry, it looks like I don't get your idea: do you intend to refence a non-partitioned table from a partitioned one using foreign keys, or vice versa?

Also, by

pg_pathman should help to solve the fk problem

you state that you would like to have this solved by pg_pathman, right?

[parihaaraka]

Еще раз другими словами :)
Пример: есть две большие таблицы, причем одна ссылается на другую. Типовая ситуация: таблица с некими операциями и ссылающаяся на нее таблица с бухгалтерскими проводками, которые провели по каждой из операций. Если мы захотим поделить таблицы, то под раздачу в любом случае попадают обе (или, в моем случае, 3 штуки), чтобы сохранить поддержку ссылочной целостности средствами СУБД. Добавляем, предположим, поле с годом транзакции в каждую из обсуждаемых таблиц в расчете на разбивку по годам. В итоге, с одной стороны, нам придется обслуживать разбивку и внешние ключи руками (это про "synchronous partitioning of referenced tables" и "pg_pathman should help to solve the fk problem"), где апи pg_pathman можно было бы адаптировать на получение не одной таблицы, а массива, чтобы у всех провести одинаковое разбиение и поднять между результирующими разделами ссылки по аналогии с теми, что есть на родительских таблицах. С другой стороны, начинает вырисовываться картина (понятно, что это, может быть, когда-нибудь потом), где можно было бы обеспечить ссылочную целостность между одинаково разбитыми таблицами мЕньшими усилиями, учитывая критерий разбивки при контроле целостности и в join-ах автоматически.

* Молодцы, хорошее дело делаете. Спасибо.
** Когда в планах следующий стабильный релиз?

[funbringer]

To @parihaaraka:

Релиз-кандидат планируется выпустить на этой неделе, нам все еще нужно удовлетворить внутренние запросы со стороны наших коллег.

Что касается любых вопросов, связанных с foreign key, то с ними не все так просто, потому что для безболезненной реализации referential integrity нам сильно не хватает глобальных индексов по нескольким таблицам. В противном случае на нас ложится бремя реализации некоего механизма с туманным исходом. Я думаю, мы что-нибудь придумаем, но обещать конкретную реализацию не могу.

To @yazun:

Composite partitioning key is an interesting idea, but I doubt it will be implemented any time soon. However, we aim to implement partitioning by expressions which could actually reference several columns.

[akorotkov]

Я правильно понял, что вы хотите, чтобы несколько таблиц были секционированы по одному и тому же ключу и тогда FK можно будет реализовать между отдельными секциями (а не между секционированными таблицами в целом)?

[yazun]

The idea is that when a table is partitioned with FKs pointing to it - all dependent tables should be partitioned as well using exactly the same partitioning scheme (range, hash on the same FKed columns). So it would be propagating partitioning down dependent tables on the FKs.
Global index would be a separate issue - each partitions should derive PKs, indicies etc ideally.

i.e.

A (id)
<---referenced by B (parent_id)
<---referenced by C (parent_id) <--- referenced by D (dparent_id)

and when A is partitioned by (id): B,C,D should be partitioned by referencing keys too.

and partition prunning should be done when joins are executed on such tables with a single table
having partitioning condition included.

So individual partitions would be referenced as:
A1 (id)
<---referenced by B1 (parent_id)
<---referenced by C1 (parent_id) <--- referenced by D1 (dparent_id)
....
Ak (id)
<---referenced by Bk (parent_id)
<---referenced by Ck (parent_id) <--- referenced by Dk (dparent_id)

[parihaaraka]

To @akorotkov:
Да, всё верно. На данный момент это единственный способ разбить таблицы с сохранением внешних ключей, и можно было бы в минимуме автоматизировать разбивку связанных таблиц и создание fk между секциями заменой аргумента relation text на relation text[] (или перегруженными функциями). Если же рассматривать такую ситуацию более детально (абсолютно идентичное разбиение таблиц из-за ссылок между ними с их фактическим наличием), то наверняка ее можно использовать более глубоко в процессе выполнения запроса.

[funbringer]

To @parihaaraka , @yazun:

Ok, now I understand. I really dislike the idea of automatic propagation, which means that this feature would indeed require REGCLASS\TEXT parameter type to be replaced with a corresponding array type.

Actually, it would complicate things even further: in this case all partitioning-related operations (e.g. appending partition) must be propagated to the bound partitioned tables, which results in a certain amount of coding to be done.

[1803]

My two cents:
I had a somewhat similar problem that I solved by adding a so-called shadow table - a table that consists only of the ID column of the parent table. This solution allowed me to somewhat overcome the update/delete issues regarding referential integrity that are mentioned above.
Although I want to admit that I am not happy with it in any way, but sometimes you have to do what you have to do. I described it in a bit more detail in SE question that was really popular and got a lot of attention.
I can describe the solution above more thoroughly and show some pl/pgsql code if necessary.

Если что-то из вышесказанного или содержимое ссылки имеет смысл продублировать на русском языке, дайте знать.

[prawn-cake]

Sounds like having common field in the tables which can become a "partition key" solves the issue

Given the following schema

-- NOTE: pseudo code --
users
- id
- name

comments
- user_id (fk to users.id)
- text

and choosing partition key as a hash(users.id) and hash(comments.user_id) solves the issue, your data for both tables will end up in the same section

[funbringer]

I guess the discussion is over.