Contents
pg_pathman
Модуль pg_pathman
предоставляет оптимизированный механизм секционирования, а также функции для создания и управления секциями.
Расширение совместимо с PostgreSQL 9.5 (поддержка 9.6 будет добавлена в одном из ближайших обновлений).
Концепция pg_pathman
Секционирование -- это способ разбиения одной большой таблицы на множество меньших по размеру. Для каждой записи можно однозначно определить секцию, в которой она должна храниться посредством вычисления ключа. Секционирование в postgres основано на механизме наследования. Каждому наследнику задается условие CHECK CONSTRAINT. Например:
CREATE TABLE test (id SERIAL PRIMARY KEY, title TEXT);
CREATE TABLE test_1 (CHECK ( id >= 100 AND id < 200 )) INHERITS (test);
CREATE TABLE test_2 (CHECK ( id >= 200 AND id < 300 )) INHERITS (test);
Несмотря на гибкость, этот механизм обладает недостатками. Так при фильтрации данных оптимизатор вынужден перебирать все дочерние секции и сравнивать условие запроса с CHECK CONSTRAINT-ами секции, чтобы определить из каких секций ему следует загружать данные. При большом количестве секций это создает дополнительные накладные расходы, которые могут свести на нет выигрыш в производительности от применения секционирования.
Модуль pg_pathman
предоставляет функции для создания и управления секциями, а также механизм секционирования, оптимизированный с учетом знания о структуре дочерних таблиц. Конфигурация сохраняется таблице pathman_config
, каждая строка которой содержит запись для одной секционированной таблицы (название таблицы, атрибут и тип разбиения). В процессе инициализации pg_pathman
кеширует конфигурацию дочерних таблиц в формате, удобном для быстрого поиска. Получив запрос типа SELECT
к секционированной таблице, pg_pathman
анализирует дерево условий запроса и выделяет из него условия вида:
ПЕРЕМЕННАЯ ОПЕРАТОР КОНСТАНТА
где ПЕРЕМЕННАЯ
-- это атрибут, по которому было выполнено разбиение, ОПЕРАТОР
-- оператор сравнения (поддерживаются =, <, <=, >, >=), КОНСТАНТА
-- скалярное значение. Например:
WHERE id = 150
Затем основываясь на стратегии секционирования и условиях запроса pg_pathman
находит в кеше соответствующие секции и строит план.
В текущей версии pg_pathman
поддерживает следующие типы секционирования:
- RANGE - разбивает таблицу на секции по диапазонам ключевого аттрибута; для оптимизации построения плана используется метод бинарного поиска.
- HASH - данные равномерно распределяются по секциям в соответствии со значениями hash-функции, вычисленными по заданному целочисленному атрибуту.
More interesting features are yet to come. Stay tuned!
Roadmap
- Предоставить возможность установки пользовательских колбеков на создание\уничтожение партиции (issue #22)
- LIST-секционирование;
- Оптимизация hash join для случая, когда обе таблицы секционированы по ключу join’а.
Установка
Для установки pg_pathman выполните в директории модуля команду:
make install USE_PGXS=1
Модифицируйте параметр shared_preload_libraries в конфигурационном файле postgres.conf:
shared_preload_libraries = 'pg_pathman'
Для вступления изменений в силу потребуется перезагрузка сервера PostgreSQL. Затем выполните в psql:
CREATE EXTENSION pg_pathman;
Важно: Если вы хотите собрать
pg_pathman
для работы с кастомной сборкой PostgreSQL, не забудьте установить переменную окруженияPG_CONFIG
равной пути к исполняемому файлу pg_config. Узнать больше о сборке расширений для PostgreSQL можно по ссылке: here.
Функции pg_pathman
Создание секций
plpgsql
create_hash_partitions(relation REGCLASS,
attribute TEXT,
partitions_count INTEGER,
partition_name TEXT DEFAULT NULL)
Выполняет HASH-секционирование таблицы relation
по целочисленному полю attribute
. Параметр partitions_count
определяет, сколько секций будет создано. Если partition_data
установлен в значение true
, то данные из родительской таблицы будут автоматически распределены по секциям. Стоит иметь в виду, что миграция данных может занять некоторое время, а данные заблокированы. Для конкурентной миграции данных см. функцию partition_table_concurrently()
.
```plpgsql create_range_partitions(relation REGCLASS, attribute TEXT, start_value ANYELEMENT, interval ANYELEMENT, count INTEGER DEFAULT NULL partition_data BOOLEAN DEFAULT true)
create_range_partitions(relation REGCLASS,
attribute TEXT,
start_value ANYELEMENT,
interval INTERVAL,
count INTEGER DEFAULT NULL,
partition_data BOOLEAN DEFAULT true)
``
Выполняет RANGE-секционирование таблицы
relationпо полю
attribute. Аргумент
start_valueзадает начальное значение,
interval-- диапазон значений внутри одной секции,
count` -- количество создаваемых секций (если не задано, то pathman попытается определить количество секций на основе значений аттрибута).
```plpgsql create_partitions_from_range(relation REGCLASS, attribute TEXT, start_value ANYELEMENT, end_value ANYELEMENT, interval ANYELEMENT, partition_data BOOLEAN DEFAULT true)
create_partitions_from_range(relation REGCLASS,
attribute TEXT,
start_value ANYELEMENT,
end_value ANYELEMENT,
interval INTERVAL,
partition_data BOOLEAN DEFAULT true)
``
Выполняет RANGE-секционирование для заданного диапазона таблицы
relationпо полю
attribute`.
Миграция данных
plpgsql
partition_table_concurrently(relation REGCLASS)
Запускает новый процесс (background worker) для конкурентного перемещения данных из родительской таблицы в дочерние секции. Рабочий процесс использует короткие транзакции для перемещения небольших объемов данных (порядка 10 тысяч записей) и, таким образом, не оказывает существенного влияния на работу пользователей.
plpgsql
stop_concurrent_part_task(relation REGCLASS)
Останавливает процесс конкурентного партиционирования. Обратите внимание, что процесс завершается не мгновенно, а только по завершении текущей транзакции.
Утилиты
plpgsql
create_hash_update_trigger(parent REGCLASS)
Создает триггер на UPDATE для HASH секций. По-умолчанию триггер на обновление данных не создается, т.к. это создает дополнительные накладные расходы. Триггер полезен только в том случае, когда меняется значение ключевого аттрибута.
plpgsql
create_range_update_trigger(parent REGCLASS)
Аналогично предыдущей, но для RANGE секций.
Управление секциями
plpgsql
split_range_partition(partition REGCLASS,
value ANYELEMENT,
partition_name TEXT DEFAULT NULL,)
Разбивает RANGE секцию partition
на две секции по значению value
.
plpgsql
merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)
Объединяет две смежные RANGE секции. Данные из partition2
копируются в partition1
, после чего секция partition2
удаляется.
plpgsql
append_range_partition(p_relation REGCLASS,
partition_name TEXT DEFAULT NULL)
Добавляет новую RANGE секцию с диапазоном pathman_config.range_interval
в конец списка секций.
plpgsql
prepend_range_partition(p_relation REGCLASS,
partition_name TEXT DEFAULT NULL)
Добавляет новую RANGE секцию с диапазоном pathman_config.range_interval
в начало списка секций.
plpgsql
add_range_partition(relation REGCLASS,
start_value ANYELEMENT,
end_value ANYELEMENT,
partition_name TEXT DEFAULT NULL)
Добавляет новую RANGE секцию с заданным диапазоном к секционированной таблице relation
.
plpgsql
drop_range_partition(partition TEXT)
Удаляет RANGE секцию вместе с содержащимися в ней данными.
plpgsql
attach_range_partition(relation REGCLASS,
partition REGCLASS,
start_value ANYELEMENT,
end_value ANYELEMENT)
Присоединяет существующую таблицу partition
в качестве секции к ранее секционированной таблице relation
. Структура присоединяемой таблицы должна в точности повторять структуру родительской.
plpgsql
detach_range_partition(partition REGCLASS)
Отсоединяет секцию partition
, после чего она становится независимой таблицей.
plpgsql
disable_pathman_for(relation REGCLASS)
Отключает механизм секционирования pg_pathman
для заданной таблицы. При этом созданные ранее секции остаются без изменений.
plpgsql
drop_partitions(parent REGCLASS,
delete_data BOOLEAN DEFAULT FALSE)
Удаляет все секции таблицы parent
. Если параметр delete_data
задан как false
(по-умолчанию false
), то данные из секций копируются в родительскую таблицу.
Дополнительные параметры
plpgsql
enable_parent(relation REGCLASS)
disable_parent(relation REGCLASS)
Включает/исключает родительскую таблицу в план запроса. В оригинальном планировщике PostgreSQL родительская таблица всегда включается в план запроса, даже если она пуста. Это создает дополнительные накладные расходы. Выполните disable_parent()
, если вы не собираетесь хранить какие-либо данные в родительской таблице. Значение по-умолчанию зависит от того, был ли установлен параметр partition_data
при первоначальном разбиении таблицы (см. функции create_range_partitions()
и create_partitions_from_range()
). Если он был установлен в значение true
, то все данные были перемещены в секции, а родительская таблица отключена. В противном случае родительская таблица по-умолчанию влючена.
plpgsql
enable_auto(relation REGCLASS)
disable_auto(relation REGCLASS)
Включает/выключает автоматическое создание секций (только для RANGE секционирования). По-умолчанию включено.
Custom plan nodes
pg_pathman
вводит три новых узла плана (см. custom plan nodes), предназначенных для оптимизации времени выполнения:
RuntimeAppend
(замещает узел типаAppend
)RuntimeMergeAppend
(замещает узел типаMergeAppend
)PartitionFilter
(выполняет работу INSERT-триггера)
PartitionFilter
работает как прокси-узел для INSERT-запросов, распределяя новые записи по соответствующим секциям:
``` 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 (4 rows) ```
Узлы RuntimeAppend
и RuntimeMergeAppend
имеют между собой много общего: они нужны в случает, когда условие WHERE принимает форму:
ПЕРЕМЕННАЯ ОПЕРАТОР ПАРАМЕТР
Подобные выражения не могут быть оптимизированы во время планирования, т.к. значение параметра неизвестно до стадии выполнения. Проблема может быть решена путем встраивания дополнительной процедуры анализа в код Append
узла, таким образом позволяя ему выбирать лишь необходимые субпланы из всего списка дочерних планов.
Есть по меньшей мере несколько ситуаций, которые демонстрируют полезность таких узлов:
``` /* создаем таблицу, которую хотим секционировать */ CREATE TABLE partitioned_table(id INT NOT NULL, payload REAL);
/* заполняем данными */ INSERT INTO partitioned_table SELECT generate_series(1, 1000), random();
/* выполняем секционирование */ SELECT create_hash_partitions('partitioned_table', 'id', 100);
/* создаем обычную таблицу */ CREATE TABLE some_table AS SELECT generate_series(1, 100) AS VAL; ```
id = (select ... limit 1)
``` EXPLAIN (COSTS OFF, ANALYZE) SELECT * FROM partitioned_table WHERE id = (SELECT * FROM some_table LIMIT 1);QUERY PLAN
Custom Scan (RuntimeAppend) (actual time=0.030..0.033 rows=1 loops=1) InitPlan 1 (returns $0) -> Limit (actual time=0.011..0.011 rows=1 loops=1) -> Seq Scan on some_table (actual time=0.010..0.010 rows=1 loops=1) -> Seq Scan on partitioned_table_70 partitioned_table (actual time=0.004..0.006 rows=1 loops=1) Filter: (id = $0) Rows Removed by Filter: 9 Planning time: 1.131 ms Execution time: 0.075 ms (9 rows)
/* выключаем узел RuntimeAppend */ SET pg_pathman.enable_runtimeappend = f;
EXPLAIN (COSTS OFF, ANALYZE) SELECT * FROM partitioned_table WHERE id = (SELECT * FROM some_table LIMIT 1);
QUERY PLAN
Append (actual time=0.196..0.274 rows=1 loops=1) InitPlan 1 (returns $0) -> Limit (actual time=0.005..0.005 rows=1 loops=1) -> Seq Scan on some_table (actual time=0.003..0.003 rows=1 loops=1) -> Seq Scan on partitioned_table_0 (actual time=0.014..0.014 rows=0 loops=1) Filter: (id = $0) Rows Removed by Filter: 6 -> Seq Scan on partitioned_table_1 (actual time=0.003..0.003 rows=0 loops=1) Filter: (id = $0) Rows Removed by Filter: 5 ... /* more plans follow */ Planning time: 1.140 ms Execution time: 0.855 ms (306 rows) ```
id = ANY (select ...)
``` EXPLAIN (COSTS OFF, ANALYZE) SELECT * FROM partitioned_table WHERE id = any (SELECT * FROM some_table limit 4);QUERY PLAN
Nested Loop (actual time=0.025..0.060 rows=4 loops=1) -> Limit (actual time=0.009..0.011 rows=4 loops=1) -> Seq Scan on some_table (actual time=0.008..0.010 rows=4 loops=1) -> Custom Scan (RuntimeAppend) (actual time=0.002..0.004 rows=1 loops=4) -> Seq Scan on partitioned_table_70 partitioned_table (actual time=0.001..0.001 rows=10 loops=1) -> Seq Scan on partitioned_table_26 partitioned_table (actual time=0.002..0.003 rows=9 loops=1) -> Seq Scan on partitioned_table_27 partitioned_table (actual time=0.001..0.002 rows=20 loops=1) -> Seq Scan on partitioned_table_63 partitioned_table (actual time=0.001..0.002 rows=9 loops=1) Planning time: 0.771 ms Execution time: 0.101 ms (10 rows)
/* выключаем узел RuntimeAppend */ SET pg_pathman.enable_runtimeappend = f;
EXPLAIN (COSTS OFF, ANALYZE) SELECT * FROM partitioned_table WHERE id = any (SELECT * FROM some_table limit 4);
QUERY PLAN
Nested Loop Semi Join (actual time=0.531..1.526 rows=4 loops=1) Join Filter: (partitioned_table.id = some_table.val) Rows Removed by Join Filter: 3990 -> Append (actual time=0.190..0.470 rows=1000 loops=1) -> Seq Scan on partitioned_table (actual time=0.187..0.187 rows=0 loops=1) -> Seq Scan on partitioned_table_0 (actual time=0.002..0.004 rows=6 loops=1) -> Seq Scan on partitioned_table_1 (actual time=0.001..0.001 rows=5 loops=1) -> Seq Scan on partitioned_table_2 (actual time=0.002..0.004 rows=14 loops=1) ... /* 96 scans follow */ -> Materialize (actual time=0.000..0.000 rows=4 loops=1000) -> Limit (actual time=0.005..0.006 rows=4 loops=1) -> Seq Scan on some_table (actual time=0.003..0.004 rows=4 loops=1) Planning time: 2.169 ms Execution time: 2.059 ms (110 rows) ```
NestLoop
involving a partitioned table, which is omitted since it's occasionally shown above.
Узнать больше о работе RuntimeAppend можно в блоге Александра Короткова.
Примеры
Common tips
- You can easily add partition column containing the names of the underlying partitions using the system attribute called tableoid:
SELECT tableoid::regclass AS partition, * FROM partitioned_table;
Несмотря на то, что индексы на родительской таблице не очень полезны (т.к. таблица пуста), они тем не менее выполняют роль прототипов для создания индексов в дочерних таблицах:
pg_pathman
автоматически создает аналогичные индексы для каждой новой секции.Получить все текущие процессы конкурентного секционирования можно из представления
pathman_concurrent_part_tasks
:plpgsql SELECT * FROM pathman_concurrent_part_tasks; userid | pid | dbid | relid | processed | status
--------+------+-------+-------+-----------+--------- dmitry | 7367 | 16384 | test | 472000 | working (1 row)
HASH секционирование
Рассмотрим пример секционирования таблицы, используя HASH-стратегию на примере таблицы товаров. ``` CREATE TABLE items ( id SERIAL PRIMARY KEY, name TEXT, code BIGINT);
INSERT INTO items (id, name, code)
SELECT g, md5(g::text), random() * 100000
FROM generate_series(1, 100000) as g;
Если дочерние секции подразумевают наличие индексов, то стоит их создать в родительской таблице до разбиения. Тогда при разбиении pg_pathman автоматически создаст соответствующие индексы в дочерних.таблицах. Разобьем таблицу `hash_rel` на 100 секций по полю `value`:
SELECT create_hash_partitions('items', 'id', 100);
Пример построения плана для запроса с фильтрацией по ключевому полю:
SELECT * FROM items WHERE id = 1234;
id | name | code
------+----------------------------------+------
1234 | 81dc9bdb52d04dc20036dbd8313ed055 | 1855
(1 row)
EXPLAIN SELECT * FROM items WHERE id = 1234;
QUERY PLAN
Append (cost=0.28..8.29 rows=0 width=0)
-> Index Scan using items_34_pkey on items_34 (cost=0.28..8.29 rows=0 width=0)
Index Cond: (id = 1234)
Стоит отметить, что pg_pathman исключает из плана запроса родительскую таблицу, и чтобы получить данные из нее, следует использовать модификатор ONLY:
EXPLAIN SELECT * FROM ONLY items;
QUERY PLAN
Seq Scan on items (cost=0.00..0.00 rows=1 width=45) ```
RANGE секционирование
Рассмотрим пример разбиения таблицы по диапазону дат. Пусть у нас имеется таблица логов: ``` CREATE TABLE journal ( id SERIAL, dt TIMESTAMP NOT NULL, level INTEGER, msg TEXT ); CREATE INDEX ON journal(dt);
INSERT INTO journal (dt, level, msg)
SELECT g, random()*6, md5(g::text)
FROM generate_series('2015-01-01'::date, '2015-12-31'::date, '1 minute') as g;
Разобьем таблицу на 365 секций так, чтобы каждая секция содержала данные за один день:
SELECT create_range_partitions('journal', 'dt', '2015-01-01'::date, '1 day'::interval);
Новые секции добавляются автоматически при вставке новых записей в непокрытую область. Однако есть возможность добавлять секции вручную. Для этого можно воспользоваться следующими функциями:
SELECT add_range_partition('journal', '2016-01-01'::date, '2016-01-07'::date);
SELECT append_range_partition('journal');
```
Первая создает новую секцию с заданным диапазоном. Вторая создает новую секцию с интервалом, заданным при первоначальном разбиении, и добавляет ее в конец списка секций. Также можно присоеднинить существующую таблицу в качестве секции. Например, это может быть таблица с архивными данными, расположенная на другом сервере и подключенная с помощью 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);
Устаревшие секции можно сливать с архивной:
SELECT merge_range_partitions('journal_archive', 'journal_1');
Разделить ранее созданную секцию на две можно с помощью следующей функции, указав точку деления:
SELECT split_range_partition('journal_366', '2016-01-03'::date);
Чтобы отсоединить ранее созданную секцию, воспользуйтесь функцией:
SELECT detach_range_partition('journal_archive');
Пример построения плана для запроса с фильтрацией по ключевому полю: ``` SELECT * FROM journal WHERE dt >= '2015-06-01' AND dt < '2015-06-03'; id | dt | level | msg --------+---------------------+-------+---------------------------------- 217441 | 2015-06-01 00:00:00 | 2 | 15053892d993ce19f580a128f87e3dbf 217442 | 2015-06-01 00:01:00 | 1 | 3a7c46f18a952d62ce5418ac2056010c 217443 | 2015-06-01 00:02:00 | 0 | 92c8de8f82faf0b139a3d99f2792311d ... (2880 rows)
EXPLAIN SELECT * FROM journal WHERE dt >= '2015-06-01' AND dt < '2015-06-03';
QUERY PLAN
Append (cost=0.00..58.80 rows=0 width=0) -> Seq Scan on journal_152 (cost=0.00..29.40 rows=0 width=0) -> Seq Scan on journal_153 (cost=0.00..29.40 rows=0 width=0) (3 rows) ```
Деакцивация pg_pathman
Для включения и отключения модуля pg_pathman
и отдельных его копонентов существует ряд GUC переменных:
pg_pathman.enable
--- полная отключение (или включение) модуляpg_pathman
pg_pathman.enable_runtimeappend
--- включение/отключение функционалаRuntimeAppend
pg_pathman.enable_runtimemergeappend
--- включение/отключение функционалаRuntimeMergeAppend
pg_pathman.enable_partitionfilter
--- включение/отключение функционалаPartitionFilter
Чтобы безвозвратно отключить механизм pg_pathman
для отдельной таблицы, используйте фунцию disable_pathman_for()
. В результате этой операции структура таблиц останется прежней, но для планирования и выполнения запросов будет использоваться стандартный механизм PostgreSQL.
SELECT disable_pathman_for('range_rel');
Обратная связь
Если у вас есть вопросы или предложения, а также если вы обнаружили ошибки, напишите нам в разделе issues.
Авторы
Ильдар Мусин i.musin@postgrespro.ru Postgres Professional, Россия
Александр Коротков a.korotkov@postgrespro.ru Postgres Professional, Россия
Дмитрий Иванов d.ivanov@postgrespro.ru Postgres Professional, Россия