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

pg_pathman. Оптимизация секционирования больших и распределенных баз данных

Версия: 1.5.12.

В исходном дистрибутиве установлено по умолчанию: нет.

Связанные компоненты: отсутствуют.

Внимание!

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

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

pg_pathman исключен из состава продукта.

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

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

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

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

Возможности pg_pathman:

  • секционирование больших баз данных без прерывания их работы;
  • ускорение запросов с секционированными таблицами;
  • управление существующими секциями и добавление новых;
  • добавление в качестве секций сторонних таблиц;
  • объединение секционированных таблицы в планах запросов для операций чтения и записи.

Доработка​

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

Ограничения​

Ограничения отсутствуют.

Установка​

Необходимо добавить pg_pathman в настроечный параметр shared_preload_libraries в конфигурационном файле PostgreSQL.conf.

Важно:

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

Создайте расширение в БД:

CREATE EXTENSION pg_pathman SCHEMA ext;

Настройка​

Настройка не требуется.

Использование модуля​

Добавление секционированной таблицы​

Расширение поддерживает два типа секционирования: по диапазону ключа и по хешу от него. Секционирование по списку значений не поддерживается. Ключом может быть поле, несколько полей (составной ключ) или детерминированное выражение – функция полей. Расширение работает только с теми таблицами, конфигурация которых уже добавлена во внутренние таблицы расширения (pathman_config, pathman_partition_list). Для этого требуются констрейнты и триггеры с наименованиями по определенным шаблонам, причем расширение автоматически добавляет и удаляет их. Стандартный способ добавления таблицы – секционировать уже существующую обычную таблицу, заполненную данными или пустую:

-- Секционирование по диапазону, ключ -- поле
create table test (id int8 not null);
select create_range_partitions('test', 'id', 1, 10, 1000);
-- Секционирование по хешу, ключ - детерминированное выражение от поля
create table test (data jsonb not null);
select create_hash_partitions('test','(data->>''key'')::int8',100);

Эти функции автоматически создают секции по шаблону, переносят данные (процесс можно отложить и завершить потом – см. ниже), организовывают констрейнты и добавляют таблицу в конфигурацию расширения.

В случае добавления пустой таблицы, заполнить ее данными можно через pg_pathman – с помощью функциональности добавления секций к таблице (ниже).

Есть способ добавить в расширение уже секционированную таблицу – функция add_to_pathman_config(), но все внутренние объекты этой таблицы должны соответствовать ожиданиям pg_pathman. Обычное применение этой функции - восстановить связь с pg_pathman для таблицы, которая ранее была создана pg_pathman, исправляя ситуацию после drop extension pg_pathman; или переноса данных.

Исключение таблицы из конфигурации расширения​

Структура и данные в таблице сохранятся. Будет действовать стандартный механизм секционирования наследованием, но не будут автоматически добавляться секции при добавлении записей и применяться оптимизированные планы:

SELECT disable_pathman_for('range_rel');

Завершение отложенного переноса данных​

Если секционированная таблица пуста, поскольку она объявлена без переноса данных (флаг partition_data установлен в значение false), то можно завершить миграцию данных в фоновом режиме:

-- Запуск фонового переноса данных транзакциями по 1000 записей. В случае блокировки - ожидание 1 с, повторение до 60 попыток.
SELECT partition_table_concurrently('test', 1000, 1.0);
 
-- Просмотр процессов переноса:
SELECT * FROM pathman_concurrent_part_tasks;
userid | pid | dbid | relid | processed | status
--------+------+-------+-------+-----------+---------
user | 7367 | 16384 | test | 472000 | working
(1 row)
 
-- Удаление ненужного процесса:
SELECT stop_concurrent_part_task('test');
 
-- Перенос данных можно перезапускать много раз, повторяя попытки.
-- Когда данные перенесены успешно и полностью, можно исключить бывшую родительскую таблицу из планов, порождаемых pg_pathman:
SELECT set_enable_parent ('test', false);
-- Если ранее при добавлении таблицы partition_data был установлен в true, то полный перенос был выполнен сразу, тогда родительская таблица исключается сразу.

При прерывании процесса текущая транзакция будет отработана полностью, при этом она и все предыдущие транзакции не отменятся.

Объединение секций (range)​

Найти названия секций (физических таблиц) и убедиться, что диапазоны подходящие:

select * from pathman_partition_list where parent = 'test'::regclass and range_max::int <= 101;

Соединить секции, слить вместе данные и поправить ограничения:

select merge_range_partitions('test_1', 'test_2');

Расщепление секции на несколько (range)​

Найти название секции и убедиться, что диапазон подходит:

select * from pathman_partition_list where parent = 'test'::regclass and range_max::int <= 101;

Расщепить секцию по заданному значению ключа:

select split_range_partition('test_1', 95);

Подключение готовой таблицы в качестве секции (range)​

Для того, чтобы подключить готовую таблицу в качестве секции, выполните:

create table test_attach (like abc);
insert into test_attach values (-100), (-50);
 
select attach_range_partition('abc', 'abc_attach', -100, -10);
 
select * from pathman_partition_list where parent = 'abc'::regclass

Отсоединение секции в отдельную таблицу (range)​

Чтобы выделить секцию как отдельную таблицу, выполните:

select * from pathman_partition_list where parent = 'abc'::regclass and range_min::int <= 0;
 
select detach_range_partition('abc_attach');

Перемена мест для секции и готовой таблицы (hash)​

Чтобы поменять местами секцию и готовую таблицу:

create table test_attach (like abc);
insert into test_attach values (-100), (-50);
 
select attach_range_partition('abc', 'abc_attach', -100, -10);
 
replace_hash_partition('test_1', 'test_attach', true);

Использование устранения секций в плане выборки​

Ранние версии расширения получили название по сокращению от "path manager" – диспетчер путей. Имелись в виду пути плана для планировщика – одиночные варианты операций доступа к таблице.

Стандартные версии операций выборки по ключу и присоединения секционированной таблицы вложенным циклом для наследования выполняли полный поиск значений ключа по всему списку секций.

pg_pathman улучшает в планировщике работу стандартного пути Append и добавляет новый специализированный путь RuntimeAppend. Во время планирования пути Append-расширение убирает обработку лишних секций, если требуемые значения/диапазоны ключа очевидны из предикатов в разделе WHERE-запроса. Если же значения ключа во время планирования не предсказать, потому что они поступят только при выполнении, то применяется новый путь RuntimeAppend – он может изменять список перебираемых секций динамически.

Расширение по управляемым им таблицам хранит в своей конфигурации и памяти дополнительную информацию о границах секций. Именно эти подсказки делают устранение секций возможным.

Статическое устранение секций - улучшенный Append​

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

SET pg_pathman.enable = 'on';

explain (costs off) select * from my_table where a >= 450 and a < 550;

QUERY PLAN
------------------------------
Append
-> Seq Scan on my_table_5
Filter: (a >= 450)
-> Seq Scan on my_table_6
Filter: (a < 550)
(5 rows)

Динамическое устранение секций - путь RuntimeAppend​

Пример:

SET pg_pathman.enable_runtimeappend = 'on';
 
explain (analyze, costs off, timing off) select * from abc join dummy using (val);
 
QUERY PLAN
-----------------------------------------------------------
Nested Loop (actual rows=0 loops=1)
-> Seq Scan on dummy (actual rows=5 loops=1)
-> Custom Scan (RuntimeAppend) (actual rows=0 loops=5)
Prune by: (dummy.val = abc.val)
-> Seq Scan on abc_1 (actual rows=0 loops=4)
Filter: (dummy.val = val)
-> Seq Scan on abc_2 (actual rows=0 loops=1)
Filter: (dummy.val = val)
Planning time: 6.259 ms
Execution time: 0.535 ms
(10 rows)
 
prepare q(int, int, int) as select * from abc where val in ($1, $2, $3);
 
explain (analyze, costs off, timing off) execute q(1, 200, 100);
 
QUERY PLAN
-----------------------------------------------------
Custom Scan (RuntimeAppend) (actual rows=0 loops=1)
Prune by: (abc.val = ANY (ARRAY[$1, $2, $3]))
-> Seq Scan on abc_1 (actual rows=0 loops=1)
Filter: (val = ANY (ARRAY[$1, $2, $3]))
-> Seq Scan on abc_2 (actual rows=0 loops=1)
Filter: (val = ANY (ARRAY[$1, $2, $3]))
Planning time: 0.365 ms
Execution time: 0.203 ms
(8 rows)

Узел поддерживается:

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

Соблюдено соответствие возможности Oracle Dynamic Pruning.

Вставка между секциями (INSERT). Путь PartitionFilter​

Стандартное секционирование наследованием вообще не подразумевает возможности выполнять вставку (INSERT) в родительскую (секционированную) таблицу. Такая возможность добавляется триггером или набором правил, в котором отдельная логика определяет каждую поступившую запись в соответствующую секцию. Записи обрабатываются каждая по отдельности, триггер вызывается каждый раз.

В pg_pathman INSERT-операция в секционированную таблицу разрешается в плане запроса, куда входит путь PartitionFilter:

SET pg_pathman.enable_partitionfilter = 'on';
 
EXPLAIN (COSTS OFF) INSERT INTO partitioned_table SELECT generate_series(1, 10), random();
 
QUERY PLAN
-----------------------------------------
 Insert on partitioned_table
  -> Custom Scan (PartitionFilter)
   -> Subquery Scan on “*SELECT*”
    -> Result

PartitionFilter, работая вместо триггера как шаг плана, автоматически распределяет между секциями сразу множество записей. В отличие от стандартного решения, поддерживает:

  • раздел RETURNING;
  • счетчик добавленных записей;
  • дополнительные триггеры BEFORE / AFTER / FOR EACH ROW.

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

-- на уровне расширения - параметр pg_pathman.enable_auto_partition
SET pg_pathman.enable_auto_partition = 'on';
 
-- точная настройка на уровне таблицы
SELECT set_auto('my_table', true);

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

Чтобы не удерживать добавляемую секцию до завершения транзакции исключительным образом, специально для добавления секции порождается фоновый процесс. Он создает секцию, фиксирует только свою транзакцию и закрывается. Секция сразу же становится видимой остальным сессиям. Теперь остальные сессии могут в эту секцию осуществлять вставку, не блокируя друг друга в ожидании фиксации DDL.

Межсекционные обновления - пути PartitionRouter и PartitionOverseer​

В секционированной таблице обновление полей ключа секционирования может привести к неоходимости физически перенести запись в другую секцию. Если в результате обновления изменяется диапазон, в который входило значение ключа, то управляемые расширением ограничения не разрешат обновленной записи остаться в прежней секции. Придется прозрачно транслировать операцию UPDATE в две операции: вставку новой секции в сочетании удалением старой.

Именно этим автоматически занимаются пути PartitionOverseer (ведомый) и PartitionRouter (ведущий путь плана). Функциональность по умолчанию выключена, чтобы включить ее, выполните команду:

SET pg_pathman.enable_partitionrouter = 'on';
 
EXPLAIN (COSTS OFF) UPDATE partitioned_table SET value = value + 1 WHERE value = 2;
                       QUERY PLAN
---------------------------------------------------------
 Custom Scan (PartitionOverseer)
   ->  Update on partitioned_table_2
         ->  Custom Scan (PartitionFilter)
               ->  Custom Scan (PartitionRouter)
                     ->  Seq Scan on partitioned_table_2
                           Filter: (value = 2)
(6 rows)

Соответствует возможности Oracle ENABLE ROW MOVEMENT.

Шардирование​

Расширение позволяет подключать в роли секции внешнюю таблицу стороннего сервера, используя механизм FDW:

CREATE FOREIGN TABLE journal_archive (
    id      INTEGER NOT NULL,
    dt      TIMESTAMP NOT NULL,
    level   INTEGER,
    msg     TEXT)
SERVER archive_server;
 
SELECT attach_range_partition('journal', 'journal_archive', '2014-01-01'::date, '2015-01-01'::date);

Параметр pg_pathman.insert_into_fdw в этом случае регулирует поведение INSERT-фильтрации в пути PartitionFilter.

По умолчанию его значение - postgres (автоматически направлять записи в стороннюю секцию только тогда, когда тип обертки - postgres). Возможны еще значения disabled (отключить автоматическую отправку записей в сторонние секции) и any_fdw (автоматически отправлять записи при любом типе обертки).

Перехват копирования​

Расширение может перехватывать серверный вариант команды COPY в адрес секционированной таблицы и выполнять вместо нее свою реализацию – PATHMAN COPY. Алгоритм повторяет PartitionFilter: берет данные из stdin или файла и прозрачно распределяет их по секциям, при этом также может автоматически добавлять новые секции.

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

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

CREATE OR REPLACE FUNCTION my_callback(params jsonb)
RETURNS VOID AS
$$
<..>
$$ LANGUAGE plpgsql;
 
-- TODO
SELECT set_init_callback('my_table', 'my_callback(jsonb)');

Иерархическое секционирование​

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

Декларативное секционирование вместо pg_pathman​

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

Во всех версиях СУБД Pangolin (с 10 версии ядра PostgreSQL) для секционирования лучше применять механизм, специально разработанный только для этой цели: декларативное (declarative) секционирование. Иногда его называют естественным, встроенным (native).

Декларативное секционирование основано на иерархии наследования. Эта иерархия по указаниям в декларации поддерживается постоянно, автоматически и прозрачно для приложений. Декларация состоит из указания метода (стратегии) секционирования и списка полей таблицы или выражений - ключа секционирования. Полную декларацию требуется указать сразу при создании таблицы в команде 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, при этом секция должна уже соответствовать родительской таблице по структуре, чего можно добиться использованием ключа LIKE в 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' );

ATTACH PARTITION обходится на родительской таблице блокировкой SHARE UPDATE EXCLUSIVE, требуя ACCESS EXCLUSIVE только на секцию. CREATE TABLE ... PARTITION OF требует ACCESS EXCLUSIVE на родительской таблице.

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

Оптимизация подключения секции​

Если не подготовить секцию заранее, то во время присоединения секции произойдет:

  1. Создание UNIQUE / PRIMARY KEY и секций прочих индексов, определенных на родительской таблице.
  2. Создание триггеров уровня записи по родительской таблице.
  3. Полное сканирование присоединяемой таблицы для подтверждения того, что фактические значения ключа не выходят за границы в декларации.
  4. Если есть секция DEFAULT, то произойдет сдвиг ее границы с полным сканированием DEFAULT для подтверждения того, что в DEFAULT не было значений ключа, относящихся по декларации к новой секции. На DEFAULT будет наложена блокировка ACCESS EXCLUSIVE на время сканирования.

Избежать выполнения этих долгих операций под блокировкой можно следующим образом:

  1. Заранее создать на присоединяемой таблице индексы, соответствующие по составу и порядку полей декларациям ключа и других индексов родительской таблицы:
CREATE INDEX CONCURRENTLY measurement_usls_200602_idx ON measurement_y2006m02 (unitsales);
ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);

Секции индексов будут подключены к индексам родительской таблицы, как если бы была выполнена команда ALTER INDEX ATTACH PARTITION. 2. Заранее добавить ограничение на границы ключа:

ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02 CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
  1. Добавить или изменить ограничение на значения ключа в DEFAULT так, чтобы оно исключало появление в DEFAULT значений ключа новой секции (всех новых секций, если планируется добавить их несколько).

Отсоединение секции в отдельную таблицы​

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

ALTER TABLE measurement DETACH PARTITION measurement_y2015m12;

Перестановка секции и готовой таблицы​

Операции, похожей на EXCHANGE PARTITION WITH TABLE из Oracle, не предусмотрено. Объединить ATTACH PARTITION и DETACH PARTITION в одном предложении ALTER невозможно. Необходимо по отдельности выполнить DETACH, RENAME и ATTACH. Рекомендуется собрать эти операции в одной транзакции.

Устранение секций в плане выборки (улучшенный Append)​

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

В плане при срабатывании возможности изменяется вид или алгоритм стандартного Aggregate-узла.

Статическое устранение секций​

Когда какое-либо из условий в разделе 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 у подпланов, соответствующих редко используемым секциям, вплоть "never executed" - секция не использовалась. Здесь параметр-переключатель enable_partition_pruning - общий и для статического, и для динамического устранения секций.

Быстрая вставка между секциями​

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

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

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

Межсекционные обновления​

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

Дополнительные настройки не нужны, встроенному планировщику для поиска маршрута достаточно декларации таблицы и описания секций в словаре данных.

Работа со сторонними секциями​

В декларативно секционированной таблице можно смешивать обычные (локальные) секции со сторонними (foreign), подключенными через "обертку" (wrapper):

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

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

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

Перехват копирования​

Перехват копирования не нужен. Стандартная команда ядра 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, то все дочерние секции должны быть созданы заранее, так как сервер будет выбирать целевую секцию не по наличию, а по хешу от ключа.

Partition-wise Aggregates​

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

По умолчанию возможность выключена. Включается параметром:

enable_partitionwise_aggregate = true

Параметр может выставляться без перезапуска, отдельно на сессию.

Partition-wise Joins​

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

В плане вместо ведущих узлов Append и Join по ним появятся ведущие Join под Append, которые будут вызываться много раз.

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

enable_partitionwise_join = true

Примечание:

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

Новые возможности Pangolin для секционирования​

Нативное интервальное секционирование​

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

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

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

Поддерживается вложенное секционирование.

Блокировка уровня 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 CONCURRENTLY и REINDEX CONCURRENTLY​

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

Миграция с pg_pathman на автоматическое секционирование Pangolin​

Первый из способов перехода с pg_pathman на автосекционирование:

  1. Осмотрете внутренние таблицы расширения (pathman_config, pathman_partition_list).
  2. Создайте автоматически секционированную таблицу с тем же методом секционирования, ключом и диапазоном. Если индексов на таблице много, а данных мало - можно сразу добавить индексы.
  3. Остановите доступ к таблице.
  4. Загрузите в нее данные при помощи INSERT INTO table_new (SELECT * FROM table).
  5. Если на втором шаге индексы не добавлялись, то перестройте индексы.
  6. Возобновите доступ.

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

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

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

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

Пример миграции​

Пример приводится для таблицы среднего размера (1.2 ГБ), которая была получена при помощи встроенного генератора pgbench. Таблица pgbench_accounts, множитель генератора - 100. Для примера добавим индекс по неключевому полю.

Генерация таблицы и ее перевод под управление pg_pathman:

-- Создаем плоскую таблицу с индексом
psql -c "CREATE DATABASE bench"
pgbench -d bench -i -s 100
psql -d bench -c "CREATE INDEX IF NOT EXISTS idx_pgbench_accounts_bid ON pgbench_accounts(bid)"
 
-- Переносим таблицу pgbench_accounts под pg_pathman
 
psql -d bench -c "CREATE EXTENSION pg_pathman"
 
psql -d bench
  SHOW pg_pathman.enable;
  -- должно быть on
  -- счета с aid от 1 до 10 млн, 20 секций по 50 тыс. счетов
  SELECT create_range_partitions('pgbench_accounts', 'aid', 1, 500000);
  -- После создания иерархии наследования и переноса данных старые индексы остаются в качестве шаблонов для добавления индексов к новым автосекциям
  -- Их можно перестроить по опустевшей таблице, освобождая дисковое пространство
  REINDEX (VERBOSE) TABLE pgbench_accounts;
  VACUUM VERBOSE ANALYZE; -- это необязательно, но здесь лучше не ждать autovacuum
  EXPLAIN SELECT * from pgbench_accounts WHERE aid BETWEEN 5000000 AND 5000020;
  -- используется улучшенный Append, усечение Append до двух секций
  EXPLAIN ANALYZE SELECT * FROM pgbench_accounts WHERE (aid BETWEEN 5000000 AND 5000020) AND bid=51;
  -- улучшенный Append с доступом через индекс по bid к одной из секций
  \q

Пример миграции с переносом данных:

psql -c "DROP DATABASE IF EXISTS bench (FORCE)"
-- Делаем подготовку (I)
 
psql -d bench
  -- Формируем из pathman_config предложение для создания новой таблицы
  SELECT 'CREATE TABLE '||partrel||'_new (LIKE '||partrel||' INCLUDING DEFAULTS) AUTO PARTITION BY RANGE ('||expr||') PERIOD ('||range_interval||') OFFSET (1);' l
    FROM pathman_config WHERE partrel = 'pgbench_accounts'::regclass;
  -- И выполняем его
  CREATE TABLE pgbench_accounts_new (LIKE pgbench_accounts INCLUDING DEFAULTS) AUTO PARTITION BY RANGE (aid) PERIOD (500000) OFFSET (1);
  -- Данных много, более 1 ГБ -- выгоднее загружать без индексов, добавить индексы после загрузки
  BEGIN;
    INSERT INTO pgbench_accounts_new (SELECT * FROM pgbench_accounts);
    ALTER TABLE pgbench_accounts_new ADD PRIMARY KEY (aid);
    CREATE INDEX IF NOT EXISTS idx_pgbench_accounts_new_bid ON pgbench_accounts_new(bid);
    ALTER TABLE pgbench_accounts RENAME TO pgbench_accounts_old;
    ALTER TABLE pgbench_accounts_new RENAME TO pgbench_accounts;
  COMMIT;
  VACUUM VERBOSE ANALYZE; -- необязательно, но лучше не ждать autovacuum
  \d+ pgbench_accounts
  \dPn+ pgbench_accounts
  SHOW enable_partition_pruning;
  EXPLAIN SELECT * from pgbench_accounts WHERE aid BETWEEN 5000000 AND 5000020;
  EXPLAIN ANALYZE SELECT * FROM pgbench_accounts WHERE (aid BETWEEN 5000000 AND 5000020) AND bid=51;
  -- Pruning работает, данные перенесены. Можно убрать старую таблицу.
  DROP TABLE pgbench_accounts_old CASCADE;
  \q

Пример миграции без переноса данных:

psql -c "DROP DATABASE IF EXISTS bench (FORCE)"
-- Делаем подготовку (I)
 
psql -d bench
  -- Забираем параметры для секционирования из pg_pathman: поле, интервал. Сразу в форме SQL для создания пустой таблицы.
  SELECT 'CREATE TABLE '||partrel||'_new (LIKE '||partrel||' INCLUDING DEFAULTS INCLUDING INDEXES) AUTO PARTITION BY RANGE ('||expr||') PERIOD ('||range_interval||') OFFSET (1);' l
    FROM pathman_config WHERE partrel = 'pgbench_accounts'::regclass;
  -- Сохраняем полученный SQL, пусть называется "CREATE SQL".
 
  SELECT 'ALTER TABLE '||partition||' NO INHERIT '||parent||';' l FROM pathman_partition_list WHERE parent = 'pgbench_accounts'::regclass;
  -- Сохраняем этот текст, назовем его  "I"
  SELECT 'ALTER TABLE '||parent||'_new ATTACH PARTITION '||partition||' FOR VALUES FROM ('||range_min||') TO ('||range_max||');'
    FROM pathman_partition_list WHERE parent = 'pgbench_accounts'::regclass;
  --  И этот текст сохраняем, пусть это будет "A".
  SELECT 'ALTER TABLE '||conrelid::regclass||' DROP CONSTRAINT '||conname||';' l from pg_constraint where conname like '%pgbench_accounts%_check';
  -- Этот текст тоже сохраняем, назовем его "D".
 
  -- Миграцию с переключением секций будем делать в транзакции
  BEGIN;
    -- Выполняем CREATE SQL:
    CREATE TABLE pgbench_accounts_new (LIKE pgbench_accounts INCLUDING DEFAULTS INCLUDING INDEXES) AUTO PARTITION BY RANGE (aid) PERIOD (500000) OFFSET (1);
 
    -- Выключаем pg_pathman на старой таблице и выводим ее из конфигурации расширения:
    SELECT disable_pathman_for('pgbench_accounts');
    -- Расширение оставляет за собой внутреннюю поледовательность номеров секций, убираем ее:
    DROP SEQUENCE IF EXISTS pgbench_accounts_seq;
    
    -- Выполняем сохраненный текст I
       ALTER TABLE pgbench_accounts_1 NO INHERIT pgbench_accounts;
       <..>
       ALTER TABLE pgbench_accounts_20 NO INHERIT pgbench_accounts;
 
    -- Выполняем сохраненный текст A
       ALTER TABLE pgbench_accounts_new ATTACH PARTITION pgbench_accounts_1 FOR VALUES FROM (1) TO (500001);
       <..>
       ALTER TABLE pgbench_accounts_new ATTACH PARTITION pgbench_accounts_20 FOR VALUES FROM (9500001) TO (10000001);
 
    -- Старая таблица пуста, можно ее удалить и заменить новой таблицей:
    DROP TABLE pgbench_accounts;
    ALTER TABLE pgbench_accounts_new RENAME TO pgbench_accounts;
    
    -- Новый механизм секционирования не пользуется констрейнтами на секциях постоянно.
    -- Они требовалиись только для быстрого подключения секций, больше они не нужны.
    -- Выполняем сохраненный скрипт D
       ALTER TABLE pgbench_accounts_1 DROP CONSTRAINT pathman_pgbench_accounts_1_check;
       <..>
       ALTER TABLE pgbench_accounts_20 DROP CONSTRAINT pathman_pgbench_accounts_20_check;
 
    \dPn
    -- Параметры автосекционирования на месте, два индекса есть.
    \d+ pgbench_accounts
    -- Секции подключены
    \d+ pgbench_accounts_1
    -- На первой секции указаны границы и есть секции обоих индексов.
  COMMIT;
  SHOW enable_partition_pruning;
  EXPLAIN SELECT * from pgbench_accounts WHERE aid BETWEEN 5000000 AND 5000020;
  -- используется новый улучшенный Append, похожий на старый из pg_pathman - такое же усечение до двух секций
  EXPLAIN ANALYZE SELECT * FROM pgbench_accounts WHERE (aid BETWEEN 5000000 AND 5000020) AND bid=51;
  -- тоже улучшенный Append с доступом через индекс по bid к одной из секций
  \q