Перейти к основному содержимому

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

Внимание!

Расширение pg_pathman исключено из состава продукта и не поддерживается в версии 7.1.0.

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

В истории развития 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;
-- Возобновить доступ к таблице.