Расширение pg_pathman и переход на декларативное секционирование данных

Секционирование — это способ хранения таблицы, содержащей множество записей. При секционировании записи распределяются между несколькими малыми физическими таблицами-секциями по значениям поля, набора полей или выражения (ключа секционирования). В качестве имени таблицы в словарь данных помещается логическое объединение секций – родительская (секционированная) таблица. Данные физически хранятся в таблицах-секциях, а разработчики в логике запросов обращаются к родительской таблице. Логические запросы к таблице сервер выполняет набором физических операций по секциям таблицы и индексов.

В истории развития PostgreSQL выделяются два подхода к секционированию, которые относятся к двум периодам развития:

• Секционирование с использованием наследования (Partitioning Using Inheritance) – до PostgreSQL 9.6.
• Декларативное секционирование (Native или Declarative Partitioning) – начиная с PostgreSQL 10.

Более подробно варианты описаны в документации PostgreSQL. Секционирование с использованием наследования для совместимости поддерживается в версиях 10 и выше, но рекомендуется постепенно перейти с него на декларативный синтаксис. Физические форматы хранимых данных при использовании разных подходов одинаковы. Различается объединяющая секции логика.

Расширение pg_pathman — надстройка над механизмом секционирования с наследованием, которая автоматизирует поддержку секций, добавляет функции для управления данными, дополняет стандартный планировщик оптимизированными путями доступа к секциям по структуре таблицы.

Примечание:

Начиная с Pangolin 5.1.0, использовать pg_pathman не рекомендуется. Необходимо перейти на доступное во всех версиях СУБД Pangolin декларативное секционирование.

По запросу CRPLTFRM-2624 для декларативного секционирования добавлена возможность автоматического, нативного интервального секционирования, аналогичного PARTITION BY RANGE из Oracle Database 11g+.

Поддержка pg_pathman с версий 6.x.x не осуществляется.

В СУБД Pangolin для облегчения перехода с pg_pathman декларативное секционирование было доработано.

Развитие оригинального pg_pathman с появлением декларативного секционирования было заморожено, последние 2-3 года только добавляется совместимость с очередными ядрами PostgreSQL, а также исправляются ошибки.

Возможности декларативного секционирования

Декларативное секционирование использует иерархию наследования, автоматически ее поддерживая. Декларация состоит из указания метода (стратегии) секционирования и списка полей таблицы или выражений — ключа секционирования, указывается сразу при создании таблицы в разделе PARTITION BY предложения CREATE TABLE.

Чтобы добавить таблицу, необходимо сразу объявить (декларировать) как родительскую и одновременно задать ключ в разделе PARTITION BY описания таблицы:

CREATE TABLE measurement (city_id int not null, logdate date not null, <..> ) PARTITION BY RANGE (logdate);

Поддерживается секционирование по диапазону, хешу, списку.

Исключение таблицы из конфигурации механизма невозможно.

Для переноса данных необходимо создавать новую таблицу

Автоматическое объединение секций при декларативном секционировании не поддерживается.

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

Автоматическое расщепление секций не поддерживается. В транзакции возможно отсоединить секцию, добавить новые пустые и перенести данные в них:

ALTER TABLE "eMAR" Detach PARTITION "eMAR_default";
 
CREATE TABLE "eMAR_inBw" PARTITION OF public."eMAR" FOR VALUES FROM ('2016-04-17 20:00:00') TO ('2016-06-17 20:00:00');
<..>
 
insert into "eMAR_inBw" select * from "eMAR_default" where "MedDateTime" >= '2016-04-17 20:00:00' and "MedDateTime" <= '2016-06-17 20:00:00'
<..>

Для того чтобы подключить готовую таблицу в качестве секции, используется раздел ATTACH PARTITION в ALTER TABLE. Секция должна уже соответствовать родительской таблице по структуре, чего можно добиться использованием CREATE TABLE:

CREATE TABLE measurement_y2008m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-- Загрузить данные
ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );

Механизм по умолчанию будет пересоздавать ограничения и секции индексов (с проверкой данных), переиспользуя готовые соответствующие ограничения и индексы.

Чтобы отсоединить секцию в отдельную таблицу используется встроенная операция ALTER TABLE DETACH PARTITION:

ALTER TABLE measurement DETACH PARTITION measurement_y2015m12;

Поменять местами секцию и готовую таблицу, аналогично операции EXCHANGE PARTITION WITH TABLE в Oracle невозможно. Необходимо использовать по отдельности операции DETACH, RENAME и ATTACH, которые могут выполняться в рамках одной транзакции.

Для декларативного секционирования устранение секций встроено в ядро СУБД и учитывает границы секций в словаре данных, переключаясь параметром enable_partition_pruning, которое по умолчанию включено.

Когда какое-либо из условий в WHERE-разделе затрагивает ключ секционирования и сформулировано так, что по декларации таблицы часть секций заведомо не может содержать искомые данные, планировщик может исключить лишние секции прямо при разборе запроса:

SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-------------------------------------------------------------------​----------------
 Aggregate  (cost=37.75..37.76 rows=1 width=8)
   ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
         Filter: (logdate >= '2008-01-01'::date)

В плане сразу видны только оставшиеся секции.

Когда ключ секционирования участвует в запросе, но заранее его значения предсказать невозможно, ядро изменяет алгоритм выполнения пути Aggregate по ходу планирования или исполнения плана.

Эта возможность, как и RuntimeAppend в pg_pathman, поддерживается:

• в подготовленных предложениях для параметров оператора PREPARE;
• во вложенных циклах (соединение) для значений от внутренней стороны цикла;
• родительских запросах для параметров, получаемых из подзапросов.

В этом случае в плане изменяется:

• если устранение выполнено на PREPARE-фазе - увеличивается свойство Subplans Removed;
• если значения ключа становятся известными только при исполнении запроса - уменьшается свойство loops у подпланов, соответствующих редко используемым секциям.

Параметр-переключатель enable_partition_pruning - общий и для статического, и для динамического устранений секций.

Быстрая вставка в декларативно секционированную таблицу выполняется с распределением записей автоматически, начиная c 10 версии ядра (во всех версиях СУБД Pangolin), дополнительные настройки не нужны.

В стандартном PostgreSQL есть ограничение - секции, соответствующие значениям ключа, должны быть уже созданы – иначе INSERT-операция вернет ошибку: partition of relation <..> found for row.

В Pangolin, начиная с версии 5.2.0 доступна возможность нативного интервального партиционирования – автоматического добавления секций по правилам, вычисляемым от поступающих значений ключа, которые добавлены к описанию таблицы.

С 11 версии ядра (во всех СУБД Pangolin) доступно оптимальное выполнение UPDATE-операций, которые предполагают перемещение записи между секциями (аналогично ENABLE ROW MOVEMENT в Oracle). Дополнительные настройки не нужны, встроенному планировщику для поиска маршрута достаточно декларации таблицы и описания секций в словаре данных.

С 11 ядра (во всех версиях СУБД Pangolin) в декларативно секционированной таблице можно смешивать обычные (локальные) секции со сторонними (внешними), подключенными через "обертку" (wrapper):

CREATE FOREIGN TABLE measurement_y2016m07
    PARTITION OF measurement FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')
    SERVER server_07;

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

INSERT-операция между локальными и сторонними секциями распределяет записи автоматически, UPDATE может перемещать записи только в одну сторону – из локальной секции в стороннюю.

При декларативном секционировании перехват COPY не нужен. Стандартная команда ядра COPY работает над декларативно секционированной таблицей следующим образом:

• COPY TO - копирует из таблицы те же записи, которые выбирает SELECT * FROM ONLY <секция>. Чтобы выбрать записи из всех секций, придется использовать COPY (SELECT * FROM table);
• COPY FROM автоматически распределяет записи по секциям;
• COPY FREEZE в секционированную таблицу не поддерживается.

Пользовательская функция-обработчик создания секций.

Все три стратегии (RANGE, LIST, HASH) могут использоваться во вложенном виде в любой комбинации: LIST-LIST, LIST-RANGE, LIST-HASH, RANGE-RANGE, RANGE-LIST, RANGE-HASH, HASH-HASH, HASH-LIST и HASH-RANGE. Уровней вложенности может быть несколько. Если родительская иерархия имеет тип HASH, то все дочерние секции должны быть созданы заранее, так как сервер будет выбирать целевую секцию не по наличию, а по хешу от ключа.

Начиная с 12 ядра (версии СУБД Pangolin 5.x.x), планировщик может при выполнении GROUP BY и агрегирования сначала выполнять промежуточные, частичные операции по каждой из секций, а затем объединять результаты. Если в GROUP BY встречается ключ секционирования – вычисляется полный агрегат по каждой из секций, а если нет – только частичный. Затем результаты частичных операций объединяются между собой. Такой вариант требует больше времени при разборе (планировке) запроса на просмотр всех секций и границ, но дает возможность экономить время на выполнении. Особенно хорошо механизм работает со сторонними секциями, так как позволяет вытеснить выполнение частичных агрегатов на сторонние серверы. По умолчанию возможность выключена. Включается параметром enable_partitionwise_aggregate = true. Параметр может выставляться без перезапуска, отдельно на сессию.

Также начиная с 12 ядра (версии СУБД Pangolin 5.x.x) планировщик может рассматривать на уровне отдельных секций соединение таблиц. Посекционное объединение осуществляется только для тех секций, в которых потенциально возможно совместное размещение соединяемых полей. Для того чтобы определить такие секции, условия соединения должны включать все ключи секционирования. В 12 версии ядра PostgreSQL границы секций должны совпадать, в рамках 13 версии добавлена возможность работать по частичному набору с одной из сторон.

Из-за накладных расходов на поиск секции при планировании возможность по умолчанию выключена. Включается параметром enable_partitionwise_join = true.

Возможности СУБД Pangolin для секционирования

Нативное интервальное партиционирование

В СУБД Pangolin добавлен механизм, который позволяет автоматически создавать партиции (секции) для таблиц по мере необходимости этих партиций. Необходимость возникает при вставке новых значений ключа или перемещении данных со значениями ключа, не подходящими под описания существующих секций.

Поддерживаются все встроенные стратегии секционирования. Секции добавляются следующим образом:

• LIST – на каждый элемент списка;
• HASH – значение хеш-функции делится на делитель D с остатком R (R = H mod D), новая секция добавляется для каждого значения остатка R;
• RANGE – на каждый диапазон значений ключа, описанный в новом разделе декларации таблицы.

Вложенное секционирование

В СУБД Pangolin поддерживается вложенное секционирование. Блокировка уровня AccessExclusive для родительской таблицы устанавливается только на момент добавления секции.

Примеры:

CREATE TABLE table1( col1 int ) AUTO PARTITION BY RANGE (col1) PERIOD(10); -- секционировать с интервалом в 10 единиц, начиная с 0.
INSERT INTO table1(col1) VALUES (1);  -- автоматически добавить секцию table1_p0 с диапазоном FROM (0) TO (10) и отправить в нее запись
INSERT INTO table1(col1) VALUES (15); -- добавится table1_p1 с диапазоном FROM (10) TO (20)
INSERT INTO table1(col1) VALUES (2);  -- эта запись попадет в существующую секцию table1_p0
 
-- секционировать по остатку от деления значения хеш-функции поля col1 на 16
CREATE TABLE table1( col1 int ) AUTO PARTITION BY HASH (col1) MODULUS (16);
-- добавлять по секции для каждого значения
CREATE TABLE table1( col1 int ) AUTO PARTITION BY LIST (col1);
 
-- диапазоны с интервалом в 3 единицы, со смещением 2: (-1) TO (2), (2)  TO (5), (5)  TO (8)
CREATE TABLE table1( col1 INTEGER ) AUTO PARTITION BY RANGE (col1) PERIOD(3) OFFSET(2);
-- секции с интервалом в 1 месяц начиная с 5-го числа
CREATE TABLE table1( f DATE ) AUTO PARTITION BY RANGE (f) PERIOD ( INTERVAL '1 month' ) OFFSET ( INTERVAL '4 days' );
-- составной ключ: интервал 10 для поля col1 и 25 для поля col2, со смещением 5 для поля col2
CREATE TABLE table1( col1 int, col2 int ) AUTO PARTITION BY RANGE (col1, col2) PERIOD(10, 25) OFFSET(0, 5);
-- вложенное секционирования (sub-partitioning)
-- родительская таблица секционирована с интервалом в 10 по col1, при этом каждая из секций -- таблица, секционированная по остатку от деления хеша col2 на 16
CREATE TABLE table1( col1 INTEGER , col2 INTEGER ) AUTO PARTITION BY RANGE (col1) PERIOD(10) AUTO PARTITION BY HASH (col2) MODULUS (16);

Примечание:

Изменить стандартную стратегию секционирования на автоматическое секционирование и обратно, а также сменить существующую стратегию автосекционирования невозможно. Необходимо пересоздание таблицы с использованием AUTO PARTITION.

CREATE INDEX и REINDEX CONCURRENTLY

В СУБД Pangolin можно создавать индексы над секционированными таблицами в CONCURRENTLY-режиме.

Миграция с pg_pathman на автоматическое секционирование СУБД Pangolin

Первый из способов перехода с pg_pathman на автосекционирование включает в себя следующие шаги:

• анализ внутренних таблиц расширения (pathman_config, pathman_partition_list);
• создание автоматически секционированной таблицы с тем же методом секционирования, ключом и диапазоном;
• остановка доступа к таблице;
• загрузка в нее данных при помощи COPY FROM (SELECT <..> FROM table);;
• перестраивание индексов при необходимости;
• восстановление доступа.

Примечание:

Существенный объем данных (терабайты) и большое количество индексов увеличивает время приостановки доступа на время переноса данных.

Второй способ применим только для секционирования по диапазону (RANGE) и использует особенности реализации декларативного секционирования и автосекционирования, а именно:

• форматы данных при наследовании и в случае декларативного подхода (в том числе автоматического) секционирования одинаковы, изменяется только связывающая секции логика;
• алгоритм ALTER TABLE <..> ATTACH PARTITION учитывает существующее ограничение на диапазон ключа при секции таблицы. Если ограничение совпадает по описанию с требуемым для секции – данные заново не проверяются;
• CREATE INDEX подключает существующие при секции индексы к описанию локально секционированного индекса в качестве готовых секций, не перестраивая их.

Пример скрипта для осуществления миграции:

-- Приостановить доступ к таблице
BEGIN TRANSACTION;
  CREATE TABLE measurement_part (<..>) AUTO PARTITION BY RANGE (logdate) PERIOD ( INTERVAL '1 month' ) OFFSET( '2023-12-01 00:00:00' );
  select detach_range_partition('measurement_y2006m02'); -- повторить для всех секций
  ALTER TABLE measurement_part ATTACH PARTITION measurement_y2006m02 FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'); -- повторить для всех секций
  DROP TABLE measurement CASCADE;
  ALTER TABLE measurement_part RENAME TO measurement;
  ALTER TABLE measurement_y2006m06 DROP CONSTRAINT measurement_y2006m06_logdate_check; -- ограничения были нужны только для наследования и pg_pathman, повторить для всех секций
  CREATE INDEX measurement_logdate ON measurement(logdate); -- будут использованы существующие секции индекса
COMMIT;
-- Возобновить доступ к таблице.