F.45. pg_pathman — оптимизированное решение для секционирования больших и распределённых баз данных #
Важно
Начиная с Postgres Pro 12, использовать pg_pathman не рекомендуется. Применяйте вместо него реализованное в ванильной версии декларативное секционирование, описанное в Разделе 5.11.
pg_pathman — это расширение Postgres Pro, реализующее оптимизированное решение для секционирования больших и распределённых баз данных. Используя pg_pathman, вы можете:
Секционировать большие базы данных, не прерывая их работу.
Ускорять выполнение запросов с секционированными таблицами.
Управлять существующими и добавлять новые секции на лету.
Добавлять в качестве секций сторонние таблицы.
Соединять секционированные таблицы для операций чтения и записи.
Это расширение совместимо с Postgres Pro 9.5 и новее.
F.45.1. Установка и настройка #
Расширение pg_pathman включено в состав Postgres Pro. Установив Postgres Pro, выполните следующие действия, чтобы подготовить pg_pathman к работе:
Добавьте
pg_pathmanв переменнуюshared_preload_librariesв файлеpostgresql.conf:shared_preload_libraries = 'pg_pathman'
Важно
pg_pathmanможет конфликтовать с другими расширениями, использующими те же функции для перехвата управления. Например, возможен конфликтpg_pathmanсpg_stat_statements, так как оба эти расширения используют функциюProcessUtility_hook. Во избежание подобных проблемpg_pathmanдолжен быть всегда последним в списке библиотек:shared_preload_libraries = 'pg_stat_statements, pg_pathman'Перезапустите Postgres Pro, чтобы изменения вступили в силу.
Создайте расширение
pg_pathmanследующим образом:CREATE SCHEMA pathman; GRANT USAGE ON SCHEMA pathman TO PUBLIC; CREATE EXTENSION pg_pathman WITH SCHEMA pathman;
Важно
Чтобы ваши обращения к функциям
pg_pathmanбыли защищены от атак с подменойsearch_path(см. CREATE EXTENSION), устанавливайте это расширение только в чистую схему, где никто, кроме суперпользователей, не имеет праваCREATEдля создания объектов базы данных.
После того как расширение pg_pathman будет создано, вы можете приступить к секционированию таблиц.
Примечание
Во время установки pg_pathman создаёт несколько политик RLS для ограничения доступа к собственным таблицам. Однако ядро Postgres Pro не поддерживает в полной мере выгрузку/восстановление дампов с расширениями, использующими в своих скриптах операторы CREATE POLICY. В связи с этим при восстановлении дампа базы, в которой установлено расширение pg_pathman, вы получите сообщения об ошибках вида:
ERROR: policy "allow_select" for table "pathman_config" already exists
(ОШИБКА: политика "allow_select" для таблицы "pathman_config" уже существует) Их следует игнорировать, так как на полноту восстанавливаемых данных эти ошибки не влияют.
Подсказка
Вы также можете скомпилировать pg_pathman из исходного кода, выполнив следующую команду в каталоге pg_pathman:
make install USE_PGXS=1
Завершив эту операцию, выполните следующие действия для окончания установки.
Также не забудьте дополнительно установить переменную PG_CONFIG, если вы хотите испытать pg_pathman в нестандартной сборке Postgres Pro. Подробнее об этом вы можете прочитать здесь.
Включать/отключать pg_pathman или его определённые узлы можно с помощью переменных GUC. За подробностями обратитесь к Подразделу F.45.5.1.
Если вы хотите полностью отключить pg_pathman для ранее секционированной таблицы, воспользуйтесь функцией disable_pathman_for():
SELECT disable_pathman_for('range_rel');Все секции и данные останутся неизменными и будут обрабатываться стандартным механизмом наследования Postgres Pro.
F.45.1.1. Обновление расширения pg_pathman #
Если у вас уже была установлена предыдущая версия pg_pathman, выполните следующие действия для установки новой версии:
Установите Postgres Pro.
Перезапустите кластер Postgres Pro.
Если у вас уже была установлена предыдущая основная версия
pg_pathman(в её номере другая вторая цифра), выполните следующие действия для установки новой версии:ALTER EXTENSION pg_pathman UPDATE TO
версия; SET pg_pathman.enable = t;Здесь
версия— это номер основной версииpg_pathman, например, 1.5.Узнать текущую версию
pg_pathmanможно, воспользовавшись функциейpathman_version().
F.45.2. Использование #
Выбор стратегии секционирования
Осуществление неблокирующего переноса данных
Секционирование по одному выражению
Секционирование по составному ключу
Реализация многоуровневого секционирования
Использование декларативного синтаксиса
По мере увеличения базы данных механизмы индексирования могут становиться неэффективными и запросы могут выполняться намного медленнее. Для повышения производительности, улучшения масштабируемости и оптимизации процессов администрирования вы можете использовать секционирование — разделение большой таблицы на множество меньших по размеру, когда все строки размещаются в секциях согласно ключу секционирования.
Исторически Postgres Pro поддерживал секционирование через механизм наследования, когда каждая секция создавалась в виде дочерней таблицы с ограничением-проверкой. В Postgres Pro 10 появилась поддержка декларативного секционирования, которая также полагается на наследование. При таком подходе планировщик запросов должен выполнить полный перебор и проверку условий ограничений для каждой секции, чтобы построить план выполнения запроса, что влечёт замедление запросов к таблицам с большим количеством секций. Расширение pg_pathman использует оптимизированные алгоритмы планирования и функции секционирования, учитывающие внутреннюю структуру секционированных таблиц, что позволяет добиться лучшей производительности. Более подробно детали реализации pg_pathman описаны в Подразделе F.45.4.
F.45.2.1. Выбор стратегии секционирования #
Расширение pg_pathman поддерживает следующие стратегии секционирования:
По хешу — строки сопоставляются с секциями с использованием универсальной функции хеширования. Выберите эту стратегию, если в большинстве запросов будет выполняться поиск точного соответствия.
По диапазонам — строки сопоставляются с секциями по диапазонам ключа секционирования, назначаемым каждой секции. Выберите эту стратегию, если ваша база данных содержит числовые данные, которые, скорее всего, будут представлять интерес как значения в интервалах. Например, вас могут интересовать исторические данные по годам или результаты экспериментов в определённых числовых диапазонах. Для получения выигрыша в производительности
pg_pathmanиспользует алгоритм бинарного поиска.
По умолчанию pg_pathman переносит все данные из родительской таблицы в создаваемые секции сразу (производится блокирующее секционирование). При таком подходе вы можете изменить структуру таблицы в одной транзакции, но если объём данных велик, это может привести к приостановке работы. Если важно, чтобы работа не прерывалась, вы можете выполнить параллельное секционирование. В этом случае pg_pathman записывает все новые данные в созданные секции, но сохраняет исходные данные в родительской таблице, пока вы явно не перенесёте их. Это позволяет секционировать большие базы данных, не прерывая работу, так как вы можете выбрать удобное время для переноса данных и переносить их небольшими порциями, не блокируя другие транзакции. Подробнее параллельное секционирование описано в Подразделе F.45.2.2.
F.45.2.1.1. Организация секционирования по хешу #
Чтобы выполнить секционирование по хешу с применением pg_pathman, воспользуйтесь функцией create_hash_partitions():
create_hash_partitions(parent_relid REGCLASS,
expression TEXT,
partitions_count INTEGER,
partition_data BOOLEAN DEFAULT TRUE,
partition_names TEXT[] DEFAULT NULL,
tablespaces TEXT[] DEFAULT NULL)Модуль pg_pathman создаёт указанное число секций, используя хеш-функцию. Вы можете также указать имена секций и табличных пространств, задав параметры partition_names и tablespaces, соответственно.
После того как таблица разделена на секции, удалять или добавлять секции в ней нельзя. Однако при необходимости определённую секцию можно заменить другой таблицей:
replace_hash_partition(old_partition REGCLASS,
new_partition REGCLASS,
lock_parent BOOL DEFAULT TRUE);Если параметр lock_parent равен true, никакие запросы INSERT/UPDATE/ALTER TABLE в родительской таблице не разрешаются.
Если вы опустите необязательный параметр partition_data или зададите для него значение true, все данные из родительской таблицы будут перенесены в секции. Модуль pg_pathman заблокирует эту таблицу для других транзакций до завершения переноса данных. Чтобы избежать приостановки работы, вы можете передать в параметре partition_data значение false и затем вызвать функцию partition_table_concurrently() для переноса данных без блокирования других запросов. За подробностями обратитесь к Подразделу F.45.2.2.
F.45.2.1.2. Организация секционирования по диапазонам #
Модуль pg_pathman предоставляет функцию create_range_partitions() для секционирования по диапазонам. Эта функция создаёт секции, исходя из заданного интервала и начального значения ключа секционирования. Новые секции будут создаваться автоматически при добавлении данных, не попадающих в ранее охваченный интервал.
create_range_partitions(parent_relid REGCLASS,
expression TEXT,
start_value ANYELEMENT,
p_interval ANYELEMENT | INTERVAL,
p_count INTEGER DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE)Модуль pg_pathman создаёт секции в зависимости от переданных параметров. Если необязательный параметр p_count опущен, pg_pathman вычисляет требуемое число секций, исходя из заданного начального значения и интервала. Если затем будут добавляться данные вне ранее заданного диапазона, pg_pathman создаст новые секции автоматически, сохраняя указанный интервал. При таком подходе все секции оказываются одного размера, что может способствовать ускорению запросов и упрощению управления данными.
Также вы можете задать массив, определяющий границы создаваемых секций, в параметре bounds:
create_range_partitions(parent_relid REGCLASS,
expression TEXT,
bounds ANYARRAY,
partition_names TEXT[] DEFAULT NULL,
tablespaces TEXT[] DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE)Если требуется, вы можете также использовать функции управления секциями и добавлять секции вручную. Например, если между созданными секциями образовался промежуток, pg_pathman не сможет заполнить его новой секцией в автоматическом режиме.
По умолчанию все данные из родительской таблицы будут перенесены в указанное количество секций. Модуль pg_pathman заблокирует эту таблицу для других транзакций до завершения переноса данных. Чтобы избежать приостановки работы, вы можете передать в параметре partition_data значение false и затем вызвать функцию partition_table_concurrently() для переноса данных без блокирования других запросов. За подробностями обратитесь к Подразделу F.45.2.2.
F.45.2.2. Осуществление неблокирующего переноса данных #
Если важно не допустить прерывания работы, вы можете произвести секционирование в параллельном режиме, установив для параметра partition_data значение false. В этом случае pg_pathman создаст пустые секции и оставит все исходные данные в родительской таблице. При этом все новые записи будут попадать в созданные секции. Позднее вы сможете переместить все начальные данные в соответствующие секции, не блокируя другие запросы, воспользовавшись функцией partition_table_concurrently():
partition_table_concurrently(relation REGCLASS,
batch_size INTEGER DEFAULT 1000,
sleep_time FLOAT8 DEFAULT 1.0)Здесь:
relation— родительская таблица.batch_size— количество строк, которое должно копироваться из родительской таблицы в секции за один раз. Этот параметр может принимать любое целое значение от 1 до 10000.sleep_time— интервал времени между попытками переноса данных, в секундах.
Модуль pg_pathman запускает фоновый рабочий процесс для переноса данных из родительской таблицы в секции маленькими порциями, размер которых задаётся параметром batch_size. Если одна или несколько строк в порции оказались заблокированы другими запросами, pg_pathman ждёт заданное время (sleep_time) и повторяет попытку (до 60 раз). За процессом переноса данных можно наблюдать в представлении pathman_concurrent_part_tasks, показывающем количество строк, обработанных на данный момент:
[user]postgres: select * from pathman_concurrent_part_tasks ; userid | pid | dbid | relid | processed | status --------+-------+-------+-------+-----------+--------- user | 20012 | 12413 | test | 334000 | working (1 row)
Если потребуется остановить перенос данных, вы можете в любое время выполнить функцию stop_concurrent_part_task():
SELECT stop_concurrent_part_task(relation REGCLASS);
pg_pathman завершит перенос текущей порции и прекратит процесс переноса.
Подсказка
Когда pg_pathman перенесёт все данные из родительской таблицы, вы можете исключить её из плана запроса. За подробностями обратитесь к описанию функции set_enable_parent().
F.45.2.3. Секционирование по одному выражению #
Для стратегий секционирования по диапазону и хешу pg_pathman поддерживает секционирование по выражению, возвращающему одно скалярное значение. Выражением секционирования может быть ссылка на столбец таблицы или вычисление ключа секционирования с использованием значений одного или нескольких столбцов.
Подсказка
Если вас интересует секционирование таблицы по значению кортежа, обратитесь к Подразделу F.45.2.4.
Для секционирования таблицы по выражению используйте функции секционирования pg_pathman. Выражение секционирования должно соответствовать следующим условиям:
В выражении должен фигурировать минимум один столбец секционируемой таблицы.
Все фигурирующие в нём столбцы должны иметь свойство
NOT NULL.В выражении нельзя обращаться к системным атрибутам, таким как
oid,xmin,xmaxи т. д.В выражение нельзя включать подзапросы.
Все функции, используемые выражением, должны быть помечены как
IMMUTABLE.
Так как выражение может возвращать значение практически любого типа, возвращаемое значение нужно привести к типу, для которого выполняется секционирование.
Для обращения к секции необходимо использовать в точности то выражение, по которому выполнено секционирование. В противном случае pg_pathman не сможет оптимизировать запрос. Просмотреть выражения секционирования для всех секционированных таблиц можно в таблице pathman_config.
F.45.2.3.1. Примеры #
Предположим, что у вас есть таблица test, содержащая некоторые данные jsonb:
CREATE TABLE test(col jsonb NOT NULL);
INSERT INTO test
SELECT format('{"key": %s, "date": "%s", "value": "%s"}',
i, current_date, md5(i::text))::jsonb
FROM generate_series(1, 10000 * 10) as g(i);Для секционирования этой таблицы по диапазонам значения key, вам нужно извлечь это значение из объекта jsonb и преобразовать его в числовой тип, например, в bigint:
SELECT create_range_partitions('test', '(col->>''key'')::bigint', 1, 10000, 10);В результате pg_pathman разделит родительскую таблицу на десять секций и поместит в каждую 10000 строк:
SELECT * FROM pathman_partition_list; parent | partition | parttype | expr | range_min | range_max --------+-----------+----------+---------------------------------+-----------+----------- test | test_1 | 2 | ((col ->> 'key'::text))::bigint | 1 | 10001 test | test_2 | 2 | ((col ->> 'key'::text))::bigint | 10001 | 20001 test | test_3 | 2 | ((col ->> 'key'::text))::bigint | 20001 | 30001 test | test_4 | 2 | ((col ->> 'key'::text))::bigint | 30001 | 40001 test | test_5 | 2 | ((col ->> 'key'::text))::bigint | 40001 | 50001 test | test_6 | 2 | ((col ->> 'key'::text))::bigint | 50001 | 60001 test | test_7 | 2 | ((col ->> 'key'::text))::bigint | 60001 | 70001 test | test_8 | 2 | ((col ->> 'key'::text))::bigint | 70001 | 80001 test | test_9 | 2 | ((col ->> 'key'::text))::bigint | 80001 | 90001 test | test_10 | 2 | ((col ->> 'key'::text))::bigint | 90001 | 100001 (10 rows)
F.45.2.4. Секционирование по составному ключу #
Используя pg_pathman, вы также можете реализовать диапазонное секционирование по составному ключу. Составной ключ образуется из двух или нескольких разделённых запятыми элементов, которыми могут быть ссылки на столбцы или выражения, извлекающие значения из таблицы. Выражения, определяющие составной ключ, должны удовлетворять условиям, перечисленным в Подразделе F.45.2.3.
Хотя pg_pathman не поддерживает автоматическое создание секций с составным ключом, вы можете добавлять секции, используя функцию add_range_partition(). Обычно это происходит так:
Включите автоматическое наименование секций для вашей таблицы, вызвав функцию
create_naming_sequence().Создайте составной ключ секционирования.
Зарегистрируйте таблицу, которую вы будете секционировать с помощью
pg_pathman, воспользовавшись функциейadd_to_pathman_config().Добавьте секции на основе составного ключа секционирования, вызвав функцию
add_range_partition().
F.45.2.4.1. Примеры #
Предположим, что у вас есть таблица test, содержащая некоторые данные с датами:
CREATE TABLE test (logdate date NOT NULL, comment text);
Для секционирования таких данных по месяцам и годам вам нужно создать составной ключ:
CREATE TYPE test_key AS (year float8, month float8);
Для включения автоматического именования секций выполните функцию create_naming_sequence(), передав в качестве аргумента имя таблицы:
SELECT create_naming_sequence('test');Зарегистрируйте таблицу test в pg_pathman, указав ключ секционирования, который вы намерены использовать:
SELECT add_to_pathman_config('test',
'( extract(year from logdate),
extract(month from logdate) )::test_key',
NULL);Создайте секцию, включающую все данные в интервале десяти лет, начиная с января текущего кода:
SELECT add_range_partition('test',
(extract(year from current_date), 1)::test_key,
(extract(year from current_date + '10 years'::interval), 1)::test_key);F.45.2.5. Реализация многоуровневого секционирования #
pg_pathman поддерживает многоуровневое секционирование для стратегий секционирования и по хешу, и по диапазонам. Вы можете использовать эти стратегии в любом сочетании: таблица, секционирования по хешам или диапазонам, затем может быть секционирована и по диапазонам, и по хешу.
Чтобы разбить существующую секцию на несколько дочерних, используйте обычные функции секционирования pg_pathman, как описано в Подразделе F.45.2.1, передавая имя данной секции в параметре parent_relid. Точные имена секций вы можете узнать из представления pathman_partition_list.
При реализации диапазонного секционирования с внутренним диапазонным секционированием можно выбрать для внутреннего секционирования либо другое выражение, либо то же, что задано для родительской таблицы. В первом случае, если выбранный диапазон больше диапазона родительской таблицы, будут использоваться только те дочерние секции, диапазон которых пересекается с родительским. Другие дочерние секции останутся пустыми, если только не произойдёт объединение их родительской секции с соседними, в результате которого хотя бы частично будет охвачен их диапазон.
F.45.2.5.1. Примеры #
Предположим, что у вас есть таблица journal, секционированная по месяцам:
-- создание пустой таблицы
CREATE TABLE journal (
id SERIAL,
dt TIMESTAMP NOT NULL,
level INTEGER,
msg TEXT);
-- добавление в таблицу некоторых данных
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;
-- секционирование таблицы по диапазонам
SELECT create_range_partitions('journal', 'dt', '2015-01-01'::date, '1 month'::interval);Если в какой-то момент становится выгоднее иметь меньшие секции, вы можете дополнительно разбить существующие по диапазонам или по секциям. Например, чтобы разбить секцию journal_1 на меньшие по дням, выполните:
SELECT create_range_partitions('journal_1', 'dt', '2015-01-01'::date, '1 day'::interval);Подобным образом вы можете создать вложенные секции с секционированием по хешу. Например, так можно разбить секцию journal_2 на пять секций, используя столбец id в качестве ключа секционирования:
SELECT create_hash_partitions('journal_2', 'id', '5');F.45.2.6. Использование декларативного синтаксиса #
Декларативный синтаксис секционирования позволяет определить стратегию секционирования создаваемой таблицы, а также секционировать существующие таблицы и управлять секциями с помощью команд SQL. Postgres Pro Enterprise предоставляет два варианта декларативного синтаксиса:
Функциональность ядра Postgres Pro; она подробно описывается в Подразделе 5.11.2.
Реализация в расширении pg_pathman.
В зависимости от выбранного варианта будут отличаться доступные формы SQL-команд, освещённые в описаниях команд CREATE TABLE и ALTER TABLE.
По умолчанию для декларативного секционирования используется функциональность ядра Postgres Pro. Чтобы включить декларативное секционирование, которое реализует pg_pathman, установите в partition_backend значение pg_pathman. В этом случае вы сможете использовать стратегии декларативного секционирования по диапазонам и по хешу, описанные ниже. Для команды CREATE TABLE вы можете также переопределить значение partition_backend, воспользовавшись предложением USING . Не путайте это предложение с механизм_секционированияUSING — второе нельзя использовать при создании секционированных таблиц.метод
Выполняя команду CREATE TABLE, вы можете добавить предложение PARTITION BY, чтобы создаваемая таблица разбивалась на секции по диапазонам или по хешу.
Чтобы создать таблицу, секционируемую по диапазонам, укажите имена секций, диапазон включаемых в каждую секцию значений и, если требуется, табличные пространства. Например:
CREATE TABLE abc(id serial NOT NULL)
PARTITION BY RANGE(id)
(
PARTITION abc_100 VALUES LESS THAN (100) TABLESPACE ts1,
PARTITION abc_200 VALUES LESS THAN (200) TABLESPACE ts2
);Создавая таблицу, секционированную по хешу, вы можете либо указать, сколько секций создавать, либо явно задать секции и табличные пространства. Например, чтобы создать таблицу abc с тремя секциями, выполните:
CREATE TABLE abc(id serial NOT NULL)
PARTITION BY HASH (id) PARTITIONS (3);Чтобы явно определить создаваемые секции, выполните следующий оператор:
CREATE TABLE abc(id serial NOT NULL)
PARTITION BY HASH (id)
(
PARTITION abc_first TABLESPACE ts1,
PARTITION abc_second TABLESPACE ts2
);Чтобы секционировать уже существующую таблицу, можно воспользоваться командой ALTER TABLE с предложением PARTITION BY. Например, чтобы разбить таблицу abc по хешу на три секции, выполните:
ALTER TABLE abc PARTITION BY HASH (id) PARTITIONS (3);
При секционировании по диапазонам уже существующей таблицы необходимо указать нижнюю границу первой секции, которая должна быть меньше минимального значения в ключе секционирования, и задать интервал секционирования, определяющий диапазон значений, включаемых в одну секцию:
ALTER TABLE abc PARTITION BY RANGE (id) START FROM (0) INTERVAL (2000);
Если таблица, подлежащая секционированию, содержит большой объём данных и важно не допустить остановки работы, вы можете использовать необязательный параметр CONCURRENTLY. В этом случае pg_pathman создаст пустые секции, а затем будет переносить в них данные по 1000 строк. Это предложение может использоваться при секционировании и по хешу, и по диапазонам. Например:
ALTER TABLE abc PARTITION BY RANGE (id) START FROM (0) INTERVAL (50000) CONCURRENTLY; ALTER TABLE abc PARTITION BY HASH (id) PARTITIONS (3) CONCURRENTLY;
Декларативный синтаксис pg_pathman также поддерживает многоуровневое секционирование. Имея уже секционированную таблицу, можно выполнить команду ALTER TABLE с предложением PARTITION BY для той секции, которую требуется разделить дополнительно. Рассмотрите следующий пример с секционированием по хешу:
CREATE TABLE test(a int NOT NULL, b int NOT NULL)
PARTITION BY by hash(a) PARTITIONS (8);
ALTER TABLE test_1
PARTITION BY hash(b) PARTITIONS (10); После этих команд таблица test будет разбита по хешу на 8 секций, а её секция test_1 будет дополнительно разбита на 10 секций по другому ключу.
С помощью команды ALTER TABLE также можно добавлять, изменять или удалять секции, как описано в ALTER TABLE. Эти действия можно производить с таблицами, секционированными с использованием pg_pathman, вне зависимости от выбранного значения partition_backend.
F.45.2.7. Управление секциями #
pg_pathman предоставляет набор функций для простого управления секциями. За подробностями обратитесь к Подразделу F.45.5.3.4.
F.45.3. Примеры #
F.45.3.1. Общие рекомендации #
Так можно получить столбец
partition, содержащий имена нижележащих секций, воспользовавшись системным атрибутомtableoid:SELECT tableoid::regclass AS partition, * FROM partitioned_table;
Несмотря на то, что индексы в родительской таблице не очень полезны (так как предполагается, что она пуста), они выполняют роль прототипов для создания индексов в секциях. Для каждого индекса в родительской таблице
pg_pathmanсоздаёт подобный индекс в каждой секции.Получить список всех текущих задач параллельного секционирования можно в представлении
pathman_concurrent_part_tasks:SELECT * FROM pathman_concurrent_part_tasks; userid | pid | dbid | relid | processed | status --------+------+-------+-------+-----------+--------- user | 7367 | 16384 | test | 472000 | working (1 row)
Представление
pathman_partition_listв сочетании сdrop_range_partition()может использоваться для удаления диапазонных секций более гибким образом по сравнению с обычнымDROP TABLE:SELECT drop_range_partition(partition, false) /* перенос данных в родительскую таблицу */ FROM pathman_partition_list WHERE parent = 'part_test'::regclass AND range_min::int < 500; NOTICE: 1 rows copied from part_test_11 NOTICE: 100 rows copied from part_test_1 NOTICE: 100 rows copied from part_test_2 drop_range_partition ---------------------- dummy_test_11 dummy_test_1 dummy_test_2 (3 rows)
Вы можете сделать сторонние таблицы секциями с помощью функции
attach_range_partition(). В результате строки, добавляемые в родительскую таблицу, будут перенаправляться в эти сторонние секции механизмомPartitionFilter. По умолчанию добавление строк в такие секции разрешается только при использовании обёртки сторонних данныхpostgres_fdw. Это поведение управляется переменнойpg_pathman.insert_into_fdw. Для её изменения необходимо иметь права суперпользователя.
F.45.3.2. Секционирование по хешу #
Рассмотрим пример секционирования таблицы по хешу. Для начала создадим таблицу с целочисленным столбцом:
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;
Теперь выполним функцию create_hash_partitions() с подходящими аргументами:
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)
Заметьте, узел Append содержит только одно дочернее сканирование, соответствующее предложению WHERE.
Важно
Обратите внимание на тот факт, что pg_pathman исключает родительскую таблицу из плана запроса.
Чтобы обратиться к родительской таблице, используйте модификатор ONLY:
EXPLAIN SELECT * FROM ONLY items; QUERY PLAN ------------------------------------------------------ Seq Scan on items (cost=0.00..0.00 rows=1 width=45)
F.45.3.3. Секционирование по диапазонам #
Рассмотрим пример секционирования по диапазонам. Давайте создадим таблицу, содержащую сообщения журнала:
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;Выполним функцию create_range_partitions(), чтобы создать секции, которые будут содержать данные за один день:
SELECT create_range_partitions('journal', 'dt', '2015-01-01'::date, '1 day'::interval);Этот запрос создаст 364 секции и переместит в них данные из родительской таблицы.
Новые секции добавляются автоматически триггером INSERT, но это можно сделать и вручную, с помощью следующих функций:
-- добавление новой секции с заданным диапазоном
SELECT add_range_partition('journal', '2016-01-01'::date, '2016-01-07'::date);
-- добавление новой секции с диапазоном по умолчанию
SELECT append_range_partition('journal');Первая создаёт новую секцию с заданным диапазоном. Вторая создаёт новую секцию с диапазоном, заданным при первоначальном секционировании, и добавляет её в конец списка секций. Также можно присоединить существующую таблицу в качестве секции. Например, мы можем подключить таблицу архива (это может быть даже сторонняя таблица с другого сервера) с историческими данными:
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);Важно
Присоединённая таблица должна содержать такие же столбцы, что и секционируемая, за исключением удалённых. Эти столбцы должны иметь те же типы, правила сортировки и характеристики NOT NULL, что и исходные.
Для слияния двух соседних секций используйте функцию merge_range_partitions():
SELECT merge_range_partitions('journal_archive', 'journal_1');Чтобы разделить секцию по значению, воспользуйтесь функцией split_range_partition():
SELECT split_range_partition('journal_366', '2016-01-03'::date);Чтобы отсоединить секцию, воспользуйтесь функцией detach_range_partition():
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)
F.45.4. Внутреннее устройство #
Расширение pg_pathman сохраняет конфигурацию секционирования в таблице pathman_config; каждая её строка содержит запись для одной секционированной таблицы (название отношения, столбец секционирования и тип секционирования). На этапе инициализации модуль pg_pathman кеширует некоторую информацию дочерних секций в общей памяти, а затем она может использоваться при построении плана. Когда начинает выполняться запрос SELECT, pg_pathman проходит по дереву условий в поиске выражений вида:
VARIABLE OP CONST
где VARIABLE — это ключ секционирования, OP — оператор сравнения (поддерживаются =, <, <=, >, >=), CONST — скалярное значение. Например:
WHERE id = 150
Затем, учитывая стратегию секционирования и оператор условия, pg_pathman ищет соответствующие секции и строит план.
F.45.4.1. Дополнительные узлы плана #
pg_pathman предоставляет несколько нестандартных узлов плана, позволяющих сократить время выполнения, а именно:
RuntimeAppend(переопределяет узел планаAppend)RuntimeMergeAppend(переопределяет узел планаMergeAppend)PartitionFilter(выполняет роль триггеров INSERT)PartitionRouterреализует межсекционные UPDATE вместо триггеров
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)Узел PartitionRouter представляет собой ещё один промежуточный узел, используемый вместе с PartitionFilter для выполнения межсекционных операций UPDATE, имеющих место, например, при изменении одного из столбцов ключа секционирования.
Важно
Узел PartitionRouter преобразует межсекционные команды UPDATE в DELETE + INSERT. В Postgres Pro до версии 11 эта операция небезопасна, так как pg_pathman не имеет возможности определить, была ли изменённая строка удалена или перемещена в другую секцию.
По умолчанию узел PartitionRouter отключён во избежание нежелательных побочных эффектов. Чтобы включить его, задайте для параметра pg_pathman.enable_partitionrouter значение on.
EXPLAIN (COSTS OFF)
UPDATE partitioned_table
SET value = value + 1 WHERE value = 2;
QUERY PLAN
---------------------------------------------------
Update on partitioned_table_0
-> Custom Scan (PartitionRouter)
-> Custom Scan (PartitionFilter)
-> Seq Scan on partitioned_table_0
Filter: (value = 2)
(5 rows)RuntimeAppend и RuntimeMergeAppend имеют много общего: они оказываются полезными, когда условие WHERE принимает вид:
VARIABLE OP PARAM
Подобные выражения уже не могут быть оптимизированы во время планирования, так как значение параметра оказывается неизвестным до стадии выполнения. Решить эту проблему можно, включив процедуру анализа условия 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(вложенный цикл) с секционированной таблицей, которая опущена здесь, так как была показана выше.
Узнать больше о нестандартных узлах плана вы можете в блоге Александра Короткова.
F.45.5. Справка #
F.45.5.1. Переменные GUC #
Для включения/отключения модуля pg_pathman и отдельных узлов плана предназначены несколько переменных GUC:
pg_pathman.enable— включает (отключает) модульpg_pathman.По умолчанию:
on(вкл.)pg_pathman.enable_runtimeappend— включает нестандартный узелRuntimeAppend.По умолчанию:
on(вкл.)pg_pathman.enable_runtimemergeappend— включает нестандартный узелRuntimeMergeAppend.По умолчанию:
on(вкл.)pg_pathman.enable_partitionfilter— включает нестандартный узелPartitionFilter, выполняющий межсекционные операцииINSERT.По умолчанию:
on(вкл.)pg_pathman.enable_partitionrouter— включает нестандартный узелPartitionRouter, выполняющий межсекционные операцииUPDATE.По умолчанию:
offpg_pathman.enable_auto_partition— включает автоматическое создание секций (в рамках сеанса).По умолчанию:
on(вкл.)pg_pathman.enable_bounds_cache— включает/отключает кеш границ.По умолчанию:
on(вкл.)pg_pathman.insert_into_fdw— разрешает использовать операцииINSERTс различными обёртками сторонних данных. Возможные значения:disabled(такое использование запрещено),postgres(разрешено для обёртки postgres) иany_fdw(разрешено для любых обёрток).По умолчанию:
postgrespg_pathman.override_copy— включает/отключает перехват оператораCOPY.По умолчанию:
on(вкл.)
F.45.5.2. Представления и таблицы #
F.45.5.2.1. pathman_config #
В этой таблице хранится список секционированных таблиц. Это основное хранилище конфигурации.
CREATE TABLE IF NOT EXISTS pathman_config (
partrel REGCLASS NOT NULL PRIMARY KEY,
attname TEXT NOT NULL,
parttype INTEGER NOT NULL,
range_interval TEXT);F.45.5.2.2. pathman_config_params #
В этой таблице хранятся дополнительные параметры, переопределяющие стандартное поведение pg_pathman.
CREATE TABLE IF NOT EXISTS pathman_config_params (
partrel REGCLASS NOT NULL PRIMARY KEY,
enable_parent BOOLEAN NOT NULL DEFAULT TRUE,
auto BOOLEAN NOT NULL DEFAULT TRUE,
init_callback REGPROCEDURE NOT NULL DEFAULT 0,
spawn_using_bgw BOOLEAN NOT NULL DEFAULT FALSE);F.45.5.2.3. pathman_concurrent_part_tasks #
В этом представлении показываются все работающие в данный момент задачи параллельного секционирования.
-- вспомогательная функция, возвращающая множество
CREATE OR REPLACE FUNCTION show_concurrent_part_tasks()
RETURNS TABLE (
userid REGROLE,
pid INT,
dbid OID,
relid REGCLASS,
processed INT,
status TEXT)
AS 'pg_pathman', 'show_concurrent_part_tasks_internal'
LANGUAGE C STRICT;
CREATE OR REPLACE VIEW pathman_concurrent_part_tasks
AS SELECT * FROM show_concurrent_part_tasks();F.45.5.2.4. pathman_partition_list #
В этом представлении показываются все существующие разделы, а также их родители и границы диапазонов (NULL для хеш-секций).
-- вспомогательная функция, возвращающая множество
CREATE OR REPLACE FUNCTION show_partition_list()
RETURNS TABLE (
parent REGCLASS,
partition REGCLASS,
parttype INT4,
expr TEXT,
range_min TEXT,
range_max TEXT)
AS 'pg_pathman', 'show_partition_list_internal'
LANGUAGE C STRICT;
CREATE OR REPLACE VIEW pathman_partition_list
AS SELECT * FROM show_partition_list();F.45.5.3. Функции #
F.45.5.3.1. Функции для создания секций #
create_hash_partitions(parent_relid REGCLASS,
expression TEXT,
partitions_count INTEGER,
partition_data BOOLEAN DEFAULT TRUE,
partition_names TEXT[] DEFAULT NULL,
tablespaces TEXT[] DEFAULT NULL)Выполняет секционирование по хешу для таблицы relation по целочисленному ключу expression. Параметр partitions_count задаёт число создаваемых секций; он не может быть изменён впоследствии. Если параметр partition_data равен true, все данные будут автоматически переноситься из родительской таблицы в секции. Заметьте, что перенос данных может занять некоторое время, и таблица будет заблокирована до завершения транзакции. Если вам нужно перенести данные без блокировки, воспользуйтесь функцией partition_table_concurrently(). Для каждой секции вызывается обработчик создания секции, если он был установлен заранее (см. set_init_callback()).
create_range_partitions(relation REGCLASS,
expression TEXT,
start_value ANYELEMENT,
p_interval ANYELEMENT,
p_count INTEGER DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE)
create_range_partitions(relation REGCLASS,
expression TEXT,
start_value ANYELEMENT,
p_interval INTERVAL,
p_count INTEGER DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE)
create_range_partitions(relation REGCLASS,
expression TEXT,
bounds ANYARRAY,
partition_names TEXT[] DEFAULT NULL,
tablespaces TEXT[] DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE)Выполняет секционирование по диапазонам таблицы relation по ключу секционирования expression. Аргумент start_value задаёт начальное значение, p_interval задаёт диапазон по умолчанию для автоматически создаваемых секций или секций, создаваемых функциями append_range_partition() или prepend_range_partition(). Если в p_interval передаётся NULL, автоматическое создание секций отключается. В p_count задаётся число заранее создаваемых секций (если p_count не задано, pg_pathman пытается определить число секций по значению expression). Массив bounds определяет границы секций, которые должны быть созданы. Этот массив можно построить, воспользовавшись функцией generate_range_bounds(). Для каждой секции вызывается обработчик создания секции, если он был установлен заранее.
F.45.5.3.2. Функции для переноса данных #
partition_table_concurrently(relation REGCLASS,
batch_size INTEGER DEFAULT 1000,
sleep_time FLOAT8 DEFAULT 1.0)Запускает фоновый рабочий процесс для переноса данных из родительской таблицы в секции. Этот рабочий процесс копирует данные в коротких транзакциях небольшими блоками (до 10000 строк в транзакции) и поэтому не оказывает значительного влияния на работу пользователей.
stop_concurrent_part_task(relation REGCLASS)
Останавливает фоновый рабочий процесс, выполняющий задачу параллельного секционирования. Замечание: рабочий процесс завершается после того, как заканчивает перемещение текущей порции данных.
F.45.5.3.3. Триггеры #
Для операций INSERT и межсекционных UPDATE триггеры более не требуются. При этом пользовательские триггеры поддерживаются:
Каждое добавление строки приводит к выполнению триггерных функций BEFORE/AFTER INSERT для соответствующей секции.
Каждое изменение строки приводит к выполнению триггерных функций BEFORE/AFTER UPDATE для соответствующей секции.
Каждое перемещение строки (межсекционное изменение) приводит к выполнению триггерных функций BEFORE UPDATE + BEFORE/AFTER DELETE + BEFORE/AFTER INSERT для соответствующих секций.
F.45.5.3.4. Функции для управления секциями #
replace_hash_partition(old_partition REGCLASS,
new_partition REGCLASS,
lock_parent BOOLEAN DEFAULT TRUE)Заменяет заданную секцию таблицы, секционированной по хешу, другой таблицей. Если lock_parent имеет значение true, операции INSERT/UPDATE/ALTER TABLE в родительской таблице не допускаются.
split_range_partition(partition_relid REGCLASS,
split_value ANYELEMENT,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)Разбивает диапазонную секцию partition на две по значению value. Для создаваемой секции вызывается обработчик создания секции, если он задан.
merge_range_partitions(variadic partitions REGCLASS[])
Объединяет несколько соседних секций при секционировании по диапазонам. Секции автоматически упорядочиваются по расположению диапазонов. Все данные будут собраны в первой секции, а остальные секции будут удалены. Если остающаяся секция содержит дочерние секции, для добавляемых при объединении данных могут создаваться новые дочерние секции согласно существующему выражению секционирования.
append_range_partition(parent_relid REGCLASS,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)Добавляет новую диапазонную секцию с интервалом pathman_config.range_interval в конец списка секций.
prepend_range_partition(parent_relid REGCLASS,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)Добавляет новую диапазонную секцию с интервалом pathman_config.range_interval в начало списка секций.
add_range_partition(parent_relid REGCLASS,
start_value ANYELEMENT,
end_value ANYELEMENT,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)Добавляет новую диапазонную секцию для таблицы relation с заданными границами диапазона. Если в качестве start_value или end_value передаётся NULL, соответствующая граница диапазона будет бесконечной.
drop_range_partition(partition_relid TEXT, delete_data BOOLEAN DEFAULT TRUE)
Удаляет диапазонную секцию, а также все содержащиеся в ней данные, если установлен флаг delete_data.
attach_range_partition(parent_relid REGCLASS,
partition_relid REGCLASS,
start_value ANYELEMENT,
end_value ANYELEMENT)Присоединяет секцию к существующему отношению с секционированием по диапазонам. Структура присоединяемой таблицы должна в точности повторять структуру родительской, включая удалённые столбцы. Если установлен, вызывается обработчик создания секции (см. Подраздел F.45.5.2.2).
detach_range_partition(partition_relid REGCLASS)
Отсоединяет секцию от существующего отношения с секционированием по диапазонам.
disable_pathman_for(parent_relid REGCLASS)
Полностью отключает механизм секционирования pg_pathman для заданной родительской таблицы и удаляет триггер на добавление, если он существует. При этом все секции и данные остаются без изменений.
drop_partitions(parent_relid REGCLASS,
delete_data BOOLEAN DEFAULT FALSE)Удаляет секции родительской таблицы (как сторонние, так и локальные). Если параметр delete_data равен false (это значение по умолчанию), данные сначала копируются в родительскую таблицу.
F.45.5.3.5. Дополнительные функции #
pathman_version()
Возвращает номер версии pg_pathman.
set_interval(relation REGCLASS, value ANYELEMENT)
Изменяет интервал для таблицы, секционированной по диапазонам. Заметьте, что этот интервал должен быть неотрицательными и он не должен быть пустым, то есть его значение должно быть больше нуля для числовых типов, не меньше 1 микросекунды для типа timestamp и не меньше 1 дня для типа date.
set_enable_parent(relation REGCLASS, value BOOLEAN)
Включает/исключает родительскую таблицу в план запроса. В оригинальном планировщике Postgres Pro родительская таблица всегда включается в план запроса, даже если она пуста, что может повлечь дополнительные издержки. Вы можете исключить родительскую таблицу из рассмотрения, если не собираетесь никогда хранить в ней какие-либо данные. Значение по умолчанию зависит от параметра partition_data, заданного при изначальном создании секций функцией create_range_partitions(). Если параметр partition_data имел значение true, значит все данные уже были перенесены в секции, и родительская таблица отключается. В противном случае она включена.
set_auto(relation REGCLASS, value BOOLEAN)
Включает/отключает автоматическое создание секций (только для секционирования по диапазонам). По умолчанию этот режим включён.
set_init_callback(relation REGCLASS, callback REGPROCEDURE DEFAULT 0)
Устанавливает обработчик создания секции, который будет вызываться для каждой присоединяемой или создаваемой секции (по диапазонам или по хешу). Если обработчик имеет характеристику SECURITY INVOKER, он выполняется от имени пользователя, который выполнял оператор, требующий создания новой секции. Например:
INSERT INTO partitioned_table VALUES (-5)
Обработчик должен иметь следующую сигнатуру: part_init_callback(args JSONB) RETURNS VOID. В параметре arg передаются несколько полей, зависящих от типа секционирования:
/* Таблица abc с секционированием по диапазонам (потомок abc_4) */
{
"parent": "abc",
"parttype": "2",
"partition": "abc_4",
"range_max": "401",
"range_min": "301"
}
/* Таблица abc с секционированием по хешу (потомок abc_0) */
{
"parent": "abc",
"parttype": "1",
"partition": "abc_0"
}
set_spawn_using_bgw(relation REGCLASS, value BOOLEAN)
Включает использование SpawnPartitionsWorker для создания новых секций в отдельной транзакции в случае добавления данных за пределами диапазона секционирования.
create_naming_sequence(parent_relid REGCLASS)
Включает автоматическое именование секций для заданной таблицы relation. Эту функцию необходимо использовать при секционировании таблицы по составному ключу.
add_to_pathman_config(parent_relid REGCLASS,
expression TEXT,
range_interval TEXT)
add_to_pathman_config(parent_relid REGCLASS,
expression TEXT)
Регистрирует указанную таблицу relation в pg_pathman для выполнения секционирования по заданному выражению (expression). Для секционирования по диапазонам аргумент range_interval является обязательным. Вы можете передать в нём NULL, если планируете добавлять секции вручную.
generate_range_bounds(p_start ANYELEMENT,
p_interval INTERVAL,
p_count INTEGER)
generate_range_bounds(p_start ANYELEMENT,
p_interval ANYELEMENT,
p_count INTEGER)Строит массив bounds с границами секций, которые должны быть созданы. Этот массив можно передать в качестве аргумента функции create_range_partitions().
F.45.6. Авторы #
Ильдар Мусин
Александр Коротков
Дмитрий Иванов
F.45. pg_pathman — an optimized partitioning solution for large and distributed databases #
Important
Starting from Postgres Pro 12, using pg_pathman is not recommended. Use vanilla declarative partitioning instead, as described in Section 5.11.
The pg_pathman is a Postgres Pro extension that provides an optimized partitioning solution for large and distributed databases. Using pg_pathman, you can:
Partition large databases without downtime.
Speed up query execution for partitioned tables.
Manage existing partitions and add new partitions on the fly.
Add foreign tables as partitions.
Join partitioned tables for read and write operations.
The extension is compatible with Postgres Pro 9.5 or higher.
F.45.1. Installation and Setup #
The pg_pathman extension is included into the Postgres Pro. Once you have Postgres Pro installed, complete the following steps to enable pg_pathman:
Add
pg_pathmanto theshared_preload_librariesvariable in thepostgresql.conffile:shared_preload_libraries = 'pg_pathman'
Important
pg_pathmanmay have conflicts with other extensions that use the same hook functions. For example,pg_pathmanmay interfere with thepg_stat_statementsextension as they both useProcessUtility_hook. To avoid such issues,pg_pathmanmust always be the last in the list of libraries:shared_preload_libraries = 'pg_stat_statements, pg_pathman'Restart the Postgres Pro instance for the settings to take effect.
Create the
pg_pathmanextension as follows:CREATE SCHEMA pathman; GRANT USAGE ON SCHEMA pathman TO PUBLIC; CREATE EXTENSION pg_pathman WITH SCHEMA pathman;
Important
To ensure that your calls to
pg_pathman's functions are always secure againstsearch_path-based attacks (see CREATE EXTENSION for details), install it only into a clean schema where nobody except superusers has theCREATEprivilege for database objects.
Once pg_pathman is enabled, you can start partitioning tables.
Note
During installation, pg_pathman creates a few RLS policies to restrict access to its own tables. Postgres Pro core, however, does not support dump/restore of databases where extensions issuing CREATE POLICY statements are installed. Therefore, when restoring a dump of a database where pg_pathman is installed, you will get error messages such as:
ERROR: policy "allow_select" for table "pathman_config" already exists
Ignore them since they do not affect whether the data being restored is complete.
Tip
You can also build pg_pathman from source code by executing the following command in the pg_pathman directory:
make install USE_PGXS=1
When this operation is complete, follow the steps described above to complete the setup.
In addition, do not forget to set the PG_CONFIG variable if you want to test pg_pathman on a custom build of Postgres Pro. For details, see Building and Installing PostgreSQL Extension Modules.
You can toggle pg_pathman or its specific custom nodes on and off using GUC variables. For details, see Section F.45.5.1.
If you want to permanently disable pg_pathman for a previously partitioned table, use the disable_pathman_for() function:
SELECT disable_pathman_for('range_rel');
All sections and data will remain unchanged and will be handled by the standard Postgres Pro inheritance mechanism.
F.45.1.1. Updating pg_pathman #
If you already have a previous version of pg_pathman installed, complete the following steps to upgrade to a newer version:
Install Postgres Pro.
Restart your Postgres Pro cluster.
If you are running a previous major version of
pg_pathman(the second digit in the version number is different), complete the update as follows:ALTER EXTENSION pg_pathman UPDATE TO
version; SET pg_pathman.enable = t;where
versionis thepg_pathmanmajor version number, such as 1.5.You can check the current
pg_pathmanversion by running thepathman_version()function.
F.45.2. Usage #
Choosing Partitioning Strategies
Running Non-Blocking Data Migration
Partitioning by a Single Expression
Running Multilevel Partitioning
As your database grows, indexing mechanisms may become inefficient and cause high latency as you run queries. To improve performance, ensure scalability, and optimize database administration processes you can use partitioning — splitting a large table into smaller pieces, with each row moved to a single partition according to the partitioning key.
Traditionally, Postgres Pro has supported partitioning via table inheritance, with each partition created as a child table with a CHECK constraint. In Postgres Pro 10, support for declarative partitioning was added, which also relies on inheritance. With these approaches, the query planner has to perform an exhaustive search and check constraints on each partition to build a query plan, which may slow down queries for tables with a large number of partitions. The pg_pathman extension uses an optimized planning algorithms and partitioning functions based on the internal structure of the partitioned tables, which allows to achieve better performance results. For details on pg_pathman implementation specifics, see Section F.45.4.
F.45.2.1. Choosing Partitioning Strategies #
The pg_pathman extension supports the following partitioning strategies:
Hash — maps rows to partitions using a generic hash function. Choose this strategy if most of your queries will be of the exact-match type.
Range — maps rows to partitions based on partitioning key ranges assigned to each partition. Choose this strategy if your database contains numeric data that you are likely to query or manage by ranges. For example, you may want to query historical data by years, or review experiment results by specific numeric ranges. To achieve performance gains,
pg_pathmanuses the binary search algorithm.
By default, pg_pathman migrates all data from the parent table to the newly created partitions at once (blocking partitioning). This approach enables you to restructure the table in a single transaction, but may cause downtime if you have a lot of data. If it is critical to avoid downtime, you can use concurrent partitioning. In this case, pg_pathman writes all the updates to the newly created partitions, but keeps the original data in the parent table until you explicitly migrate it. This enables you to partition large databases without downtime, as you can choose convenient time for migration and copy data in small batches without blocking other transactions. For details on concurrent partitioning, see Section F.45.2.2.
F.45.2.1.1. Setting up Hash Partitioning #
To perform hash partitioning with pg_pathman, run the create_hash_partitions() function:
create_hash_partitions(parent_relid REGCLASS,
expression TEXT,
partitions_count INTEGER,
partition_data BOOLEAN DEFAULT TRUE,
partition_names TEXT[] DEFAULT NULL,
tablespaces TEXT[] DEFAULT NULL)
The pg_pathman module creates the specified number of partitions based on the hash function. Optionally, you can specify partition names and tablespaces by setting partition_names and tablespaces options, respectively.
You cannot add or remove partitions after the parent table is split. If required, you can replace the specified partition with another table:
replace_hash_partition(old_partition REGCLASS,
new_partition REGCLASS,
lock_parent BOOL DEFAULT TRUE);
When set to true, lock_parent parameter will prevent any INSERT/UPDATE/ALTER TABLE queries to parent table.
If you omit the optional partition_data parameter or set it to true, all the data from the parent table gets migrated to partitions. The pg_pathman module blocks the table for other transactions until data migration completes. To avoid downtime, you can set the partition_data parameter to false and later use the partition_table_concurrently() function to migrate your data to partitions without blocking other queries. For details, see the Section F.45.2.2.
F.45.2.1.2. Setting up Range Partitioning #
The pg_pathman module provides the create_range_partitions() for range partitioning. This function creates partitions based on the specified interval and the initial partitioning key value. New partitions are created automatically when you insert data outside of the already covered range.
create_range_partitions(parent_relid REGCLASS,
expression TEXT,
start_value ANYELEMENT,
p_interval ANYELEMENT | INTERVAL,
p_count INTEGER DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE)
The pg_pathman module creates partitions based on the specified parameters. If you omit the optional p_count parameter, pg_pathman calculates the required number of partitions based on the specified start value and interval. If you insert new data outside of the existing partition range, pg_pathman creates new partitions automatically, keeping the specified interval. This approach ensures that all partitions are of the same size, which can improve query performance and facilitate database management.
Alternatively, you can specify an array defining the bounds of partitions to be created using the bounds parameter:
create_range_partitions(parent_relid REGCLASS,
expression TEXT,
bounds ANYARRAY,
partition_names TEXT[] DEFAULT NULL,
tablespaces TEXT[] DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE)
If required, you can also use partition management functions to add partitions manually. For example, if there is a gap between the created partitions, pg_pathman cannot fill it with a new partition in an automated mode.
By default, all the data from the parent table gets migrated to the specified number of partitions. The pg_pathman module blocks the table for other transactions until data migration completes. To avoid downtime, you can set the partition_data parameter to false and later use the partition_table_concurrently() function to migrate your data to partitions without blocking other queries. For details, see the Section F.45.2.2.
F.45.2.2. Running Non-Blocking Data Migration #
If it is critical to avoid downtime, you can perform concurrent partitioning by setting the partition_data parameter of the partitioning function to false. In this case, pg_pathman creates empty partitions, keeping all the original data in the parent table. At the same time, all the database updates are written to the newly created partitions. You can later migrate the original data to partitions without blocking other queries using the partition_table_concurrently() function:
partition_table_concurrently(relation REGCLASS,
batch_size INTEGER DEFAULT 1000,
sleep_time FLOAT8 DEFAULT 1.0)
where:
relationis the parent table.batch_sizeis the number of rows to copy from the parent table to partitions at a time. You can set this parameter to any integer value from 1 to 10000.sleep_timeis the time interval between migration attempts, in seconds.
The pg_pathman module starts a background worker to move the data from the parent table to partitions in small batches of the specified batch_size. If one or more rows in the batch are locked by other queries, pg_pathman waits for the specified sleep_time and tries again, up to 60 times. You can monitor the migration process in the pathman_concurrent_part_tasks view that shows the number of rows migrated so far:
[user]postgres: select * from pathman_concurrent_part_tasks ; userid | pid | dbid | relid | processed | status --------+-------+-------+-------+-----------+--------- user | 20012 | 12413 | test | 334000 | working (1 row)
If you need to stop data migration, run the stop_concurrent_part_task() function at any time:
SELECT stop_concurrent_part_task(relation REGCLASS);
pg_pathman completes the migration of the current batch and terminates the migration process.
Tip
When pg_pathman migrates all the data from the parent table, you can exclude the parent table from the query plan. See the set_enable_parent() function description for details.
F.45.2.3. Partitioning by a Single Expression #
For both range and hash partitioning strategies, pg_pathman supports partitioning by expression that returns a single scalar value. The partitioning expression can reference a table column, as well as calculate the partitioning key based on one or more column values.
Tip
If you would like to partition a table by a tuple, see Section F.45.2.4.
To partition a table by expression, use pg_pathman partitioning functions. The partitioning expression must satisfy the following conditions:
Expression must reference at least one column of the partitioned table.
All referenced columns must be marked as
NOT NULL.Expression cannot reference system attributes, such as
oid,xmin,xmax, etc.Expression cannot include subqueries.
All functions used by expression must be marked as
IMMUTABLE.
As the expression can return a value of virtually any type, make sure to convert it to the type you need for partitioning.
To access a partition, you must use the exact expression used for partitioning. Otherwise, pg_pathman cannot optimize the query. You can view the partitioning expression for each partitioned table in the pathman_config table.
F.45.2.3.1. Examples #
Suppose you have the test table that stores some jsonb data:
CREATE TABLE test(col jsonb NOT NULL);
INSERT INTO test
SELECT format('{"key": %s, "date": "%s", "value": "%s"}',
i, current_date, md5(i::text))::jsonb
FROM generate_series(1, 10000 * 10) as g(i);
To partition this data by range of the key value, you need to extract this value from the jsonb object and convert it to a numeric type, such as bigint:
SELECT create_range_partitions('test', '(col->>''key'')::bigint', 1, 10000, 10);
pg_pathman splits the parent table into ten partitions, with each partition storing 10000 rows:
SELECT * FROM pathman_partition_list; parent | partition | parttype | expr | range_min | range_max --------+-----------+----------+---------------------------------+-----------+----------- test | test_1 | 2 | ((col ->> 'key'::text))::bigint | 1 | 10001 test | test_2 | 2 | ((col ->> 'key'::text))::bigint | 10001 | 20001 test | test_3 | 2 | ((col ->> 'key'::text))::bigint | 20001 | 30001 test | test_4 | 2 | ((col ->> 'key'::text))::bigint | 30001 | 40001 test | test_5 | 2 | ((col ->> 'key'::text))::bigint | 40001 | 50001 test | test_6 | 2 | ((col ->> 'key'::text))::bigint | 50001 | 60001 test | test_7 | 2 | ((col ->> 'key'::text))::bigint | 60001 | 70001 test | test_8 | 2 | ((col ->> 'key'::text))::bigint | 70001 | 80001 test | test_9 | 2 | ((col ->> 'key'::text))::bigint | 80001 | 90001 test | test_10 | 2 | ((col ->> 'key'::text))::bigint | 90001 | 100001 (10 rows)
F.45.2.4. Partitioning by Composite Key #
Using pg_pathman, you can also perform range partitioning by composite key. A composite key consists of two or more comma-separated values, which can be columns or expressions extracting the values from the table. The expressions defining the composite key must satisfy the conditions described in Section F.45.2.3.
Although pg_pathman does not support automatic partition creation by composite key, you can add partitions using the add_range_partition() function. A typical workflow is as follows:
Enable automatic partition naming for your table by running the
create_naming_sequence()function.Create a composite partitioning key.
Register a table you are going to partition with
pg_pathmanusing theadd_to_pathman_config()function.Add a partition based on the defined composite partitioning key using the
add_range_partition()function.
F.45.2.4.1. Examples #
Suppose you have the test table that stores some temporal data:
CREATE TABLE test (logdate date NOT NULL, comment text);
To partition this data by month and year, you have to create a composite key:
CREATE TYPE test_key AS (year float8, month float8);
To enable automatic partition naming, run the create_naming_sequence() function passing the table name as an argument:
SELECT create_naming_sequence('test');
Register the test table with pg_pathman, specifying the partitioning key you are going to use:
SELECT add_to_pathman_config('test',
'( extract(year from logdate),
extract(month from logdate) )::test_key',
NULL);
Create a partition that includes all the data in the range of ten years, starting from January of the current year:
SELECT add_range_partition('test',
(extract(year from current_date), 1)::test_key,
(extract(year from current_date + '10 years'::interval), 1)::test_key);
F.45.2.5. Running Multilevel Partitioning #
pg_pathman supports multilevel partitioning for both hash and range partitioning strategies. You can use partitioning strategies in any combination: a hash- or range-partitioned table can be further partitioned by both hash or range.
To split an existing partition into several child ones, use the regular pg_pathman partitioning functions as explained in Section F.45.2.1, passing the name of the partition to be split as the parent_relid parameter. You can check the exact partition names in the pathman_partition_list view.
When opting for the range-range partitioning combination, you can either choose a different partitioning expression, or use the same expression as for the parent table. In the latter case, if the selected range is larger than that of the parent partition, only those child partitions that intersect with the parent range will be in use. Other child partitions will remain empty unless their parent is merged with an adjacent partition that covers at least a part of their range.
F.45.2.5.1. Examples #
Suppose you have the journal table with some logs, which is partitioned by month:
-- create an empty table
CREATE TABLE journal (
id SERIAL,
dt TIMESTAMP NOT NULL,
level INTEGER,
msg TEXT);
-- generate some log data into the table
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;
-- partition the table by range
SELECT create_range_partitions('journal', 'dt', '2015-01-01'::date, '1 month'::interval);
If having smaller partitions makes more sense at some point, you can further split the partitions by hash or range. For example, to split the journal_1 partition into subpartitions by day, run:
SELECT create_range_partitions('journal_1', 'dt', '2015-01-01'::date, '1 day'::interval);
Similarly, you can use hash partitioning to create child partitions. For example, split the journal_2 partition into five partitions by hash using the id column as the partitioning key:
SELECT create_hash_partitions('journal_2', 'id', '5');
F.45.2.6. Using Declarative Syntax #
Declarative syntax for partitioning enables you to define the partitioning strategy when creating a table, as well as partition existing tables and manage table partitions using SQL commands. Postgres Pro Enterprise offers the following implementations of declarative syntax:
Postgres Pro core functionality, described in detail in Section 5.11.2.
pg_pathman implementation of declarative syntax.
Depending on the chosen implementation, the available SQL command forms will differ, as explained in CREATE TABLE and ALTER TABLE descriptions.
By default, Postgres Pro core functionality is used for declarative partitioning. To enable declarative partitioning provided by pg_pathman, set the partition_backend parameter to the pg_pathman value. In this case, you can use declarative range and hash partitioning strategies as explained below. For the CREATE TABLE command, you can also override the partition_backend setting by specifying the USING clause. Do not confuse this clause with partition_backendUSING , which cannot be used when creating partitioned tables. method
When running the CREATE TABLE command, you can use the PARTITION BY clause to split the resulting table into partitions by range or hash.
To create a table partitioned by range, specify the partition names, the range of values to include into each partition and, optionally, a tablespace. For example:
CREATE TABLE abc(id serial NOT NULL)
PARTITION BY RANGE(id)
(
PARTITION abc_100 VALUES LESS THAN (100) TABLESPACE ts1,
PARTITION abc_200 VALUES LESS THAN (200) TABLESPACE ts2
);
When creating a table partitioned by hash, you can either specify the number of partitions to create, or the exact partitions and tablespaces. For example, to create the abc table with three partitions, run:
CREATE TABLE abc(id serial NOT NULL)
PARTITION BY HASH (id) PARTITIONS (3);
To define the exact partitions to create, use the following statement:
CREATE TABLE abc(id serial NOT NULL)
PARTITION BY HASH (id)
(
PARTITION abc_first TABLESPACE ts1,
PARTITION abc_second TABLESPACE ts2
);
To partition an already created table, you can use the ALTER TABLE command with the PARTITION BY clause. For example, to split the abc table into three hash partitions, run:
ALTER TABLE abc PARTITION BY HASH (id) PARTITIONS (3);
When performing range partitioning of an already created table, you have to specify the lower bound of the first partition, which must not be greater than the smallest value in the partition key column, and provide the partitioning interval that defines the range of values to include into a single partition:
ALTER TABLE abc PARTITION BY RANGE (id) START FROM (0) INTERVAL (2000);
If the table to partition contains a lot of data and it is critical to avoid downtime, consider using the optional CONCURRENTLY clause. In this case, pg_pathman first creates empty partitions, and then migrates the data in batches of 1000 rows. This clause can be used for both hash and range partitioning. For example:
ALTER TABLE abc PARTITION BY RANGE (id) START FROM (0) INTERVAL (50000) CONCURRENTLY; ALTER TABLE abc PARTITION BY HASH (id) PARTITIONS (3) CONCURRENTLY;
pg_pathman declarative syntax also supports multilevel partitioning. Once the table is partitioned, you can run the ALTER TABLE command with the PARTITION BY clause on the partition that you would like to split. Consider the following example with hash partitioning:
CREATE TABLE test(a int NOT NULL, b int NOT NULL)
PARTITION BY by hash(a) PARTITIONS (8);
ALTER TABLE test_1
PARTITION BY hash(b) PARTITIONS (10);
As a result, the test table is split into eight hash partitions, and its test_1 partition is further split into ten partitions by another key.
The ALTER TABLE command can also be run with partition management clauses to add, remove, or modify partitions, as explained in ALTER TABLE. You can perform these actions on tables partitioned with pg_pathman regardless of the partition_backend setting.
F.45.2.7. Managing Partitions #
pg_pathman provides multiple functions for easy partition management. For details, see Section F.45.5.3.4.
F.45.3. Examples #
F.45.3.1. Common Tips #
You can add
partitioncolumn containing the names of the underlying partitions using the system attribute calledtableoid:SELECT tableoid::regclass AS partition, * FROM partitioned_table;
Though indices on a parent table are not particularly useful (since the parent table is supposed to be empty), they act as prototypes for indices on partitions. For each index on the parent table,
pg_pathmancreates a similar index on each partition.All running concurrent partitioning tasks can be listed using the
pathman_concurrent_part_tasksview:SELECT * FROM pathman_concurrent_part_tasks; userid | pid | dbid | relid | processed | status --------+------+-------+-------+-----------+--------- user | 7367 | 16384 | test | 472000 | working (1 row)
The
pathman_partition_listin conjunction withdrop_range_partition()can be used to drop range partitions in a more flexible way compared toDROP TABLE:SELECT drop_range_partition(partition, false) /* move data to parent */ FROM pathman_partition_list WHERE parent = 'part_test'::regclass AND range_min::int < 500; NOTICE: 1 rows copied from part_test_11 NOTICE: 100 rows copied from part_test_1 NOTICE: 100 rows copied from part_test_2 drop_range_partition ---------------------- dummy_test_11 dummy_test_1 dummy_test_2 (3 rows)
You can turn foreign tables into partitions using the
attach_range_partition()function. Rows that were meant to be inserted into the parent will be redirected to foreign partitions usingPartitionFilter. By default, it is only allowed to insert rows into partitions provided bypostgres_fdw. This setting is controlled by thepg_pathman.insert_into_fdwvariable. You must have superuser rights to change this setting.
F.45.3.2. Hash Partitioning #
Consider an example of hash partitioning. First create a table with an integer column:
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;
Now run the create_hash_partitions() function with appropriate arguments:
SELECT create_hash_partitions('items', 'id', 100);
This will create new partitions and move the data from the parent table to partitions.
Here is an example of the query performing filtering by partitioning key:
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)
Notice that the Append node contains only one child scan, which corresponds to the WHERE clause.
Important
Pay attention to the fact that pg_pathman excludes the parent table from the query plan.
To access the parent table, use the ONLY modifier:
EXPLAIN SELECT * FROM ONLY items; QUERY PLAN ------------------------------------------------------ Seq Scan on items (cost=0.00..0.00 rows=1 width=45)
F.45.3.3. Range Partitioning #
Consider an example of range partitioning. Let's create a table containing some dummy logs:
CREATE TABLE journal (
id SERIAL,
dt TIMESTAMP NOT NULL,
level INTEGER,
msg TEXT);
-- similar index will also be created for each partition
CREATE INDEX ON journal(dt);
-- generate some data
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;
Run the create_range_partitions() function to create partitions so that each partition would contain the data for one day:
SELECT create_range_partitions('journal', 'dt', '2015-01-01'::date, '1 day'::interval);
It will create 364 partitions and move the data from the parent table to partitions.
New partitions are appended automatically by insert trigger, but it can be done manually with the following functions:
-- add new partition with specified range
SELECT add_range_partition('journal', '2016-01-01'::date, '2016-01-07'::date);
-- append new partition with default range
SELECT append_range_partition('journal');
The first one creates a partition with specified range. The second one creates a partition with default interval and appends it to the partition list. It is also possible to attach an existing table as partition. For example, we may want to attach an archive table (or even foreign table from another server) for some outdated data:
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);
Important
The attached table must have the same columns as the partitioned table, except for the dropped columns. The attached columns must have the same type, collation, and NOT NULL settings as the original columns.
To merge two adjacent partitions, use the merge_range_partitions() function:
SELECT merge_range_partitions('journal_archive', 'journal_1');
To split partition by value, use the split_range_partition() function:
SELECT split_range_partition('journal_366', '2016-01-03'::date);
To detach partition, use the detach_range_partition() function:
SELECT detach_range_partition('journal_archive');
Here is an example of the query performing filtering by partitioning key:
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)
F.45.4. Internals #
pg_pathman stores partitioning configuration in the pathman_config table; each row contains a single entry for a partitioned table (relation name, partitioning column and its type). During the initialization stage the pg_pathman module caches some information about child partitions in the shared memory, which is used later for plan construction. Before a SELECT query is executed, pg_pathman traverses the condition tree in search of expressions like:
VARIABLE OP CONST
where VARIABLE is a partitioning key, OP is a comparison operator (supported operators are =, <, <=, >, >=), CONST is a scalar value. For example:
WHERE id = 150
Based on the partitioning type and condition's operator, pg_pathman searches for the corresponding partitions and builds the plan.
F.45.4.1. Custom Plan Nodes #
pg_pathman provides a couple of custom plan nodes which aim to reduce execution time, namely:
RuntimeAppend(overridesAppendplan node)RuntimeMergeAppend(overridesMergeAppendplan node)PartitionFilter(drop-in replacement for INSERT triggers)PartitionRouterfor cross-partition UPDATE queries instead of triggers
PartitionFilter acts as a proxy node for INSERT's child scan, which means it can redirect output tuples to the corresponding partition:
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)
PartitionRouter is another proxy node used in conjunction with PartitionFilter to enable cross-partition UPDATE operations, for example, when you update any column of a partitioning key.
Important
The PartitionRouter node transforms cross-partition UPDATE commands into DELETE + INSERT. On Postgres Pro versions prior to 11, this operation is unsafe as pg_pathman cannot determine whether the updated row has been deleted or moved to another partition.
By default, PartitionRouter is disabled to avoid undesirable side effects. To enable this node, set the pg_pathman.enable_partitionrouter to on.
EXPLAIN (COSTS OFF)
UPDATE partitioned_table
SET value = value + 1 WHERE value = 2;
QUERY PLAN
---------------------------------------------------
Update on partitioned_table_0
-> Custom Scan (PartitionRouter)
-> Custom Scan (PartitionFilter)
-> Seq Scan on partitioned_table_0
Filter: (value = 2)
(5 rows)
RuntimeAppend and RuntimeMergeAppend have much in common: they come in handy in a case when WHERE condition takes form of:
VARIABLE OP PARAM
This kind of expressions can no longer be optimized at planning time since the parameter's value is not known until the execution stage takes place. The problem can be solved by embedding the WHERE condition analysis routine into the original Append's code, thus making it pick only required scans out of a whole bunch of planned partition scans. This effectively boils down to creation of a custom node capable of performing such a check.
There are at least several cases that demonstrate usefulness of these nodes:
/* create table we're going to partition */
CREATE TABLE partitioned_table(id INT NOT NULL, payload REAL);
/* insert some data */
INSERT INTO partitioned_table
SELECT generate_series(1, 1000), random();
/* perform partitioning */
SELECT create_hash_partitions('partitioned_table', 'id', 100);
/* create ordinary table */
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) /* disable RuntimeAppend node */ 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) /* disable RuntimeAppend node */ 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)NestLoopinvolving a partitioned table, which is omitted since it's occasionally shown above.
To learn more about custom nodes, see Alexander Korotkov's blog.
F.45.5. Reference #
F.45.5.1. GUC Variables #
There are several user-accessible GUC variables designed to toggle pg_pathman or its specific custom nodes on and off.
pg_pathman.enable— enable/disable thepg_pathmanmodule.Default:
onpg_pathman.enable_runtimeappend— toggle theRuntimeAppendcustom node on/off.Default:
onpg_pathman.enable_runtimemergeappend— toggle theRuntimeMergeAppendcustom node on/off.Default:
onpg_pathman.enable_partitionfilter— toggle thePartitionFiltercustom node on/off to enable/disable cross-partitionINSERToperations.Default:
onpg_pathman.enable_partitionrouter— toggle thePartitionRoutercustom node on/off to enable/disable cross-partitionUPDATEoperations.Default:
offpg_pathman.enable_auto_partition— toggle automatic partition creation on/off (per session).Default:
onpg_pathman.enable_bounds_cache— toggle bounds cache on/off.Default:
onpg_pathman.insert_into_fdw— allowINSERToperations into various foreign-data wrappers. Possible values:disabled,postgres, andany_fdw.Default:
postgrespg_pathman.override_copy— toggleCOPYstatement hooking on/off.Default:
on
F.45.5.2. Views and Tables #
F.45.5.2.1. pathman_config #
This table stores the list of partitioned tables. This is the main configuration storage.
CREATE TABLE IF NOT EXISTS pathman_config (
partrel REGCLASS NOT NULL PRIMARY KEY,
attname TEXT NOT NULL,
parttype INTEGER NOT NULL,
range_interval TEXT);
F.45.5.2.2. pathman_config_params #
This table stores optional parameters that override standard pg_pathman behavior.
CREATE TABLE IF NOT EXISTS pathman_config_params (
partrel REGCLASS NOT NULL PRIMARY KEY,
enable_parent BOOLEAN NOT NULL DEFAULT TRUE,
auto BOOLEAN NOT NULL DEFAULT TRUE,
init_callback REGPROCEDURE NOT NULL DEFAULT 0,
spawn_using_bgw BOOLEAN NOT NULL DEFAULT FALSE);
F.45.5.2.3. pathman_concurrent_part_tasks #
This view lists all currently running concurrent partitioning tasks.
-- helper SRF function
CREATE OR REPLACE FUNCTION show_concurrent_part_tasks()
RETURNS TABLE (
userid REGROLE,
pid INT,
dbid OID,
relid REGCLASS,
processed INT,
status TEXT)
AS 'pg_pathman', 'show_concurrent_part_tasks_internal'
LANGUAGE C STRICT;
CREATE OR REPLACE VIEW pathman_concurrent_part_tasks
AS SELECT * FROM show_concurrent_part_tasks();
F.45.5.2.4. pathman_partition_list #
This view lists all existing partitions, as well as their parents and range boundaries (NULL for hash partitions).
-- helper SRF function
CREATE OR REPLACE FUNCTION show_partition_list()
RETURNS TABLE (
parent REGCLASS,
partition REGCLASS,
parttype INT4,
expr TEXT,
range_min TEXT,
range_max TEXT)
AS 'pg_pathman', 'show_partition_list_internal'
LANGUAGE C STRICT;
CREATE OR REPLACE VIEW pathman_partition_list
AS SELECT * FROM show_partition_list();
F.45.5.3. Functions #
F.45.5.3.1. Partitioning Functions #
create_hash_partitions(parent_relid REGCLASS,
expression TEXT,
partitions_count INTEGER,
partition_data BOOLEAN DEFAULT TRUE,
partition_names TEXT[] DEFAULT NULL,
tablespaces TEXT[] DEFAULT NULL)
Performs hash partitioning for relation by integer key expression. The partitions_count parameter specifies the number of partitions to create; it cannot be changed afterwards. If partition_data is true, all the data will be automatically migrated from the parent table to partitions. Note that data migration may take a while to finish and the table will be locked until transaction commits. See partition_table_concurrently() for a lock-free way to migrate data. Partition creation callback is invoked for each partition if set beforehand (see set_init_callback()).
create_range_partitions(relation REGCLASS,
expression TEXT,
start_value ANYELEMENT,
p_interval ANYELEMENT,
p_count INTEGER DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE)
create_range_partitions(relation REGCLASS,
expression TEXT,
start_value ANYELEMENT,
p_interval INTERVAL,
p_count INTEGER DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE)
create_range_partitions(relation REGCLASS,
expression TEXT,
bounds ANYARRAY,
partition_names TEXT[] DEFAULT NULL,
tablespaces TEXT[] DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE)
Performs range partitioning for relation by partitioning key defined by expression. The start_value argument specifies the initial value, p_interval sets the default range for automatically created partitions or partitions created with append_range_partition() or prepend_range_partition(). If p_interval is set to NULL, automatic partition creation is disabled. p_count is the number of premade partitions. If p_count is not set, than pg_pathman tries to determine the number of partitions based on the expression value. The bounds array defines the bounds for partitions to be created. You can build this array using the generate_range_bounds() function. Partition creation callback is invoked for each partition if set beforehand.
F.45.5.3.2. Data Migration Functions #
partition_table_concurrently(relation REGCLASS,
batch_size INTEGER DEFAULT 1000,
sleep_time FLOAT8 DEFAULT 1.0)
Starts a background worker to move data from parent table to partitions. The worker utilizes short transactions to copy small batches of data (up to 10K rows per transaction) and thus doesn't significantly interfere with user's activity.
stop_concurrent_part_task(relation REGCLASS)
Stops a background worker performing a concurrent partitioning task. Note: worker will exit after it finishes relocating a current batch.
F.45.5.3.3. Triggers #
Triggers are no longer required for INSERT and cross-partition UPDATE operations. However, user-supplied triggers are supported:
Each inserted row results in execution of BEFORE/AFTER INSERT trigger functions of a corresponding partition.
Each updated row results in execution of BEFORE/AFTER UPDATE trigger functions of a corresponding partition.
Each moved row (cross-partition update) results in execution of BEFORE UPDATE + BEFORE/AFTER DELETE + BEFORE/AFTER INSERT trigger functions of corresponding partitions.
F.45.5.3.4. Partition Management Functions #
replace_hash_partition(old_partition REGCLASS,
new_partition REGCLASS,
lock_parent BOOLEAN DEFAULT TRUE)
Replaces the specified partition of hash-partitioned table with another table. When set to true, the lock_parent parameter prevents any INSERT/UPDATE/ALTER TABLE queries to the parent table.
split_range_partition(partition_relid REGCLASS,
split_value ANYELEMENT,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)
Split range partition in two by value, with the specified value included into the second partition. Partition creation callback is invoked for a new partition if available.
merge_range_partitions(variadic partitions REGCLASS[])
Merge several adjacent range partitions. Partitions are automatically ordered by increasing bounds. All the data will be accumulated in the first partition, while other merged partitions are removed. If the remaining partition has any child partitions, new child partitions for the merged data will be created as required using the same partitioning expression.
append_range_partition(parent_relid REGCLASS,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)
Append new range partition with pathman_config.range_interval as interval.
prepend_range_partition(parent_relid REGCLASS,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)
Prepend new range partition with pathman_config.range_interval as interval.
add_range_partition(parent_relid REGCLASS,
start_value ANYELEMENT,
end_value ANYELEMENT,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)
Create a new range partition for relation with the specified range bounds. If the start_value or the end_value is NULL, the corresponding range bound will be infinite.
drop_range_partition(partition_relid TEXT, delete_data BOOLEAN DEFAULT TRUE)
Drop range partition and all of its data if delete_data is true.
attach_range_partition(parent_relid REGCLASS,
partition_relid REGCLASS,
start_value ANYELEMENT,
end_value ANYELEMENT)
Attach partition to the existing range-partitioned relation. The attached table must have exactly the same structure as the parent table, including the dropped columns. Partition creation callback is invoked if set (see Section F.45.5.2.2).
detach_range_partition(partition_relid REGCLASS)
Detach partition from the existing range-partitioned relation.
disable_pathman_for(parent_relid REGCLASS)
Permanently disable pg_pathman partitioning mechanism for the specified parent table and remove the insert trigger if it exists. All partitions and data remain unchanged.
drop_partitions(parent_relid REGCLASS,
delete_data BOOLEAN DEFAULT FALSE)
Drop partitions of the parent table (both foreign and local relations). If delete_data is false, the data is copied to the parent table first. Default is false.
F.45.5.3.5. Additional Functions #
pathman_version()
Returns the pg_pathman version number.
set_interval(relation REGCLASS, value ANYELEMENT)
Update range-partitioned table interval. Note that interval must not be negative and it must not be trivial, i.e. its value should be greater than zero for numeric types, at least 1 microsecond for timestamp and at least 1 day for date.
set_enable_parent(relation REGCLASS, value BOOLEAN)
Include/exclude parent table into/from query plan. In original Postgres Pro planner parent table is always included into query plan even if it's empty, which can lead to additional overhead. You can use disable_parent() if you are never going to use parent table as a storage. Default value depends on the partition_data parameter specified during initial partitioning with the create_range_partitions() function. If the partition_data parameter was true, then all data have already been migrated to partitions and the parent table is disabled. Otherwise, it is enabled.
set_auto(relation REGCLASS, value BOOLEAN)
Enable/disable auto partition propagation (only for range partitioning). It is enabled by default.
set_init_callback(relation REGCLASS, callback REGPROCEDURE DEFAULT 0)
Set partition creation callback to be invoked for each attached or created partition (both hash and range). If callback is marked with SECURITY INVOKER, it is executed with the privileges of the user who produced a statement that has led to creation of a new partition. For example:
INSERT INTO partitioned_table VALUES (-5)
The callback must have the following signature: part_init_callback(args JSONB) RETURNS VOID. Parameter arg consists of several fields whose presence depends on partitioning type:
/* Range-partitioned table abc (child abc_4) */
{
"parent": "abc",
"parttype": "2",
"partition": "abc_4",
"range_max": "401",
"range_min": "301"
}
/* Hash-partitioned table abc (child abc_0) */
{
"parent": "abc",
"parttype": "1",
"partition": "abc_0"
}
set_spawn_using_bgw(relation REGCLASS, value BOOLEAN)
When inserting new data beyond the partitioning range, use SpawnPartitionsWorker to create new partitions in a separate transaction.
create_naming_sequence(parent_relid REGCLASS)
Enable automatic partition naming for the specified relation table. You must run this function when partitioning this table by composite key.
add_to_pathman_config(parent_relid REGCLASS,
expression TEXT,
range_interval TEXT)
add_to_pathman_config(parent_relid REGCLASS,
expression TEXT)
Register the specified relation table with pg_pathman to enable partitioning by the provided expression. For range partitioning, the range_interval argument is mandatory. You can set it to NULL if you are going to add partition manually.
generate_range_bounds(p_start ANYELEMENT,
p_interval INTERVAL,
p_count INTEGER)
generate_range_bounds(p_start ANYELEMENT,
p_interval ANYELEMENT,
p_count INTEGER)
Build the bounds array that defines the bounds for partitions to be created. You can pass this array as an argument to the create_range_partitions() function.
F.45.6. Authors #
Ildar Musin
Alexander Korotkov
Dmitry Ivanov
