Таблица запроса Postgres по индексу индекса диапазона блоков (BRIN) напрямую

У меня есть N клиентских машин. Я хочу загрузить каждую машину с отдельным разделом индекса BRIN.

Для этого требуется:

создать BRIN с предопределенным количеством разделов - равным количеству клиентских машин
отправлять запросы от клиентов, которые используют WHERE на идентификаторе разделов BRIN вместо фильтра на индексированном столбце

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

В настоящее время я могу добиться этого, сохранив новый столбец, который разбивает мою таблицу на количество ковшей, равную количеству клиентских машин (или используйте row_number() over (order by datetime) % N на лету). Таким образом, это не будет эффективно при синхронизации и памяти, а индекс BRIN выглядит как приятная функция, которая может ускорить такие варианты использования.

Минимальный воспроизводимый пример для 3 клиентских машин:

CREATE TABLE bigtable (datetime TIMESTAMPTZ, value TEXT);
INSERT INTO bigtable VALUES ('2015-12-01 00:00:00+00'::TIMESTAMPTZ, 'txt1');
INSERT INTO bigtable VALUES ('2015-12-01 05:00:00+00'::TIMESTAMPTZ, 'txt2');
INSERT INTO bigtable VALUES ('2015-12-02 02:00:00+00'::TIMESTAMPTZ, 'txt3');
INSERT INTO bigtable VALUES ('2015-12-02 03:00:00+00'::TIMESTAMPTZ, 'txt4');
INSERT INTO bigtable VALUES ('2015-12-02 05:00:00+00'::TIMESTAMPTZ, 'txt5');
INSERT INTO bigtable VALUES ('2015-12-02 16:00:00+00'::TIMESTAMPTZ, 'txt6');
INSERT INTO bigtable VALUES ('2015-12-02 23:00:00+00'::TIMESTAMPTZ, 'txt7');

Ожидаемый результат:

клиент 1

2015-12-01 00:00:00+00, 'txt1'
2015-12-01 05:00:00+00, 'txt2'
2015-12-02 02:00:00+00, 'txt3'

клиент 2

2015-12-02 03:00:00+00, 'txt4'
2015-12-02 05:00:00+00, 'txt5'

клиент 3

2015-12-02 16:00:00+00, 'txt6'
2015-12-02 23:00:00+00, 'txt7'

Вопрос:
Как я могу создать BRIN с предопределенным количеством разделов и запускать запросы, которые фильтруют идентификаторы разделов вместо фильтрации в столбце индекса?
Необязательно любым другим способом, с помощью которого BRIN (или другие плюсы pg) может ускорить задачу параллельной загрузки нескольких клиентов из отдельной таблицы?

Ответ 1

Похоже, вы хотите очертить таблицу на многих машинах и иметь каждую локальную таблицу (один осколок глобальной таблицы) с индексом BRIN с ровно одним ведром. Но это не имеет никакого смысла. Если один индекс диапазона BRIN охватывает всю (локальную) таблицу, то он никогда не может быть очень полезен.

Похоже на то, что вы ищете, это разбиение на разделы с ограничениями CHECK, которые можно использовать для исключения разделов. PostgreSQL поддерживает это в течение длительного времени с наследованием таблицы (хотя и не для каждого раздела, находящегося на отдельной машине). Используя этот метод, диапазон, охватываемый ограничением CHECK, должен быть явно установлен для каждого раздела. Эта способность явно указывать границы звучит так, как будто именно то, что вы ищете, просто используя другую технологию.

Но код ограничения исключения раздела не работает с модулем. Код достаточно умен, чтобы знать, что WHERE id=5 нужно только проверить раздел CHECK (id BETWEEN 1 and 10), потому что он знает, что id = 5 означает, что идентификатор находится между 1 и 10. Точнее, он знает, что это противоречит этому.

Но код никогда не записывался, чтобы знать, что WHERE id=5 подразумевает, что id%10 = 5%10, даже если люди это знают. Поэтому, если вы создаете свои разделы на операторах модуля, например CHECK (id%10=5), а не на диапазонах, вам нужно будет посыпать все ваши запросы с помощью WHERE id = $1 and id % 10= $1 %10, если вы хотите, чтобы он воспользовался ограничениями.

Ответ 2

Следуя вашему описанию и комментариям, я бы сказал, что вы смотрите в неправильном направлении. Вы хотите разбить таблицу вверх, чтобы доступ был быстрым и простым, но без необходимости разделить вещи вверх, потому что для этого вам потребуется знать количество узлов, которые являются вид переменной, если я правильно понимаю. И, несмотря на это, для разделения вещей требуется довольно много обработки.

Если честно, я бы пошла по вашей проблеме по-другому. Вместо того, чтобы присваивать каждую запись ведро, я предпочел бы назначить каждой записи псевдослучайное значение в заданном диапазоне. Я не знаю о Postgres, но в MSSQL я бы использовал BINARY_CHECKSUM(NewID()) вместо Rand(). Основная причина в том, что случайная функция сложнее использовать на ней SET. Вместо этого вы также можете использовать код хеширования, который возвращает разумное рабочее пространство. Во всяком случае, в моей ситуации MSSQL полученное значение тогда будет знаковым целым числом, сидящим где-то в диапазоне от -2 ^ 31 до +2 ^ 31 (дайте или возьмите, проверьте документацию для точных границ!). Таким образом, когда мастер-машина решает назначить n клиентских машин, каждой машине может быть назначен точный диапазон, который - с учетом свойств рандомизатора/хэширования - будет огибать достаточно близкое приближение к рабочей нагрузке, деленной на n. Предполагая, что у вас есть индекс в поле выбора, это должно быть достаточно быстро, независимо от того, решите ли вы разбить таблицу на тысячу или миллион кусков.

PS: помните, что этот подход будет работать только "правильно", если количество строк для обработки (значительно) превосходит число машин, которые будут обрабатывать. С небольшими номерами вы можете видеть, что несколько машин не получают ничего, в то время как другие получают всю работу.

Ответ 3

В принципе, все, что вам нужно знать, это размер отношения после загрузки, а затем параметр pages_per_range хранения должен быть установлен в делитель, который дает вам необходимое количество разделов.

Не нужно вводить искусственный идентификатор раздела, потому что есть поддержка для достаточного количества типов и операторов. Здесь важна физическая компоновка таблицы, поэтому, если вы настаиваете на том, что идентификатор раздела является ключом, и в конечном итоге вводите отображение вне порядка между порядком естественной загрузки и искусственным идентификатором раздела, убедитесь, что вы кластерируете таблицу в этом столбце порядок сортировки перед созданием BRIN.

Однако в то же время помните, что более дискретные значения имеют больше шансов на поражение индекса, чем меньше, поэтому более высокая мощность лучше - искусственный идентификатор раздела будет иметь 1/n мощности натурального ключа, где n равно количество различных значений для каждого раздела.

Подробнее здесь и здесь.