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

Что такое секционирование и когда оно может пригодиться

Секционирование — это разделение одной логической таблицы на отдельные физические части (секции). Разделение выполняется по строчкам, на основании значений определённого поля или полей в этих строчках — такие поля называются ключом секционирования. 

Допустим, есть таблица city, и мы секционировали её на части city_msk, city_spb, city_nsk. В данном случае колонка city_code — ключ секционирования. 

Причём непосредственно в секционированной таблице city никаких строк находиться не может — они расположены в секциях. В какую секцию попадает строка, зависит от значения поля с кодом города. Если нет подходящей секции, то строка вставляется в секцию DEFAULT. Если её нет, то возвращается ошибка «для строки на найдена секция в отношении».

Секционирование работает прозрачно для приложения. Все обращения — как на чтение, так и на запись — выполняются к таблице city. А вот в какой секции будет найдена строка при чтении или в какую секцию она попадёт при записи, определяется планировщиком СУБД.

Секционирование помогает: 

1. Повышать производительность запросов: в плане запроса не фигурируют лишние секции, где нет строк, подпадающих под условия запроса.

2. При обслуживании больших таблиц укладываться в рамки отведённого технологического окна (ночное время, технические работы и пр.). Перестройка таблицы при помощи VACUUM FULL, когда вам нужно освободить пространство, перестройка индексов через REINDEX, создание резервных копий через pg_dump и другие операции выполнятся над отдельными секциями гораздо быстрее, чем над единой таблицей.

3. Гибко управлять данными: переносить исторические и редко используемые данные на более дешёвые носители. Подробнее о жизненном цикле данных рассказали в этой статье.

Декларировать будете? Разбираемся с декларативным секционированием

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

В команде create table мы указываем метод секционирования, например partition by range, и перечисляем столбцы, которые будут ключом секционирования.

Транзакционность DDL-операций превращает этот механизм в мощный инструмент управления секционированными таблицами и расширяет возможности автоматизации.

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

CREATE TABLE measurement_y2006m02 PARTITION OF measurement 
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'); 
CREATE TABLE measurement_y2006m03 PARTITION OF measurement 
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

Но в декларативном секционировании есть и ряд ограничений:

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

• Ключ секционирования может включать только столбцы первичного ключа. То есть если в таблице есть PRIMARY KEY, секционировать можно только по столбцам, которые он покрывает. 

• Триггеры уровня BEFORE ROW для INSERT не могут менять секцию, в которую попадёт новая строка.

• Нельзя декларативно секционировать существующую таблицу.

Что делать, когда нужно секционировать существующую таблицу 

Для этого есть два способа:

1. Подключить таблицу как историческую секцию к новой секционированной таблице.

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

create table measurement_p (LIKE measurement) partition by range (logdate);

И секцию на будущее:

create table measurements_2025 partition of measurements_p for values from ('2025-01-01') to ('2026-01-01');

Готовим старую таблицу, чтобы избежать длительной блокировки: 

begin;
set local statement_timeout to '1s'; --чтобы не устраивать долгий exclusive-лок живой таблицы, если кто-то работает с ней в данный момент и мешает взять его быстро.
alter table measurements add constraint measurements_partbound_check check (logdate < '2025-01-01' and created_at is not null) not valid; -- чтобы подключить существующую таблицу в качестве секции к секционированной таблице, необходимо указать диапазон значений, который покрывает данная секция. В этот момент будет создано ограничение CHECK, если оно не существовало до этого. Данное ограничение гарантирует, что в таблице (секции) находятся значения только из объявленного диапазона. Создание такого ограничения заранее существенно ускоряет подключение секции. Фраза NOT VALID позволяет не производить сразу валидацию, которая предполагает полное сканирование таблицы.
commit;
alter table measurements validate constraint measurements_partbound_check; -- произвести валидацию ограничения. Данная команда не блокирует таблицу в эксклюзивном режиме и совместима с DML-операторами. 

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

begin; 
set statement_timeout to '1s'; -- задаём длительность ожидания получения блокировки, чтобы не блокировать работу пользователей с таблицей, если её не получится захватить мгновенно.
alter table measurements rename to measurements_history; 
alter table measurements_p rename to measurements; 
alter table measurements attach partition measurements_archive for values from (MINVALUE) to ('2025-01-01'); -- поскольку constraint уже создан и валидирован, данная операция обрабатывается почти моментально.
commit;

Таким образом секционирование выполняется за две блокировки длительностью менее секунды. Данные до 1 января 2025 года будут записываться в старую таблицу, а с 1 января — в новую. 

2. Выполнить честное секционирование существующей таблицы.

Если нужно именно «разрезать» таблицу, применяется другой подход. Для этого создаём новую секционированную таблицу с другим именем и готовим необходимое количество секций под данные.

Копируем данные из старой таблицы: 

INSERT INTO measurement_p SELECT * FROM measurement 

или:

COPY TO measurement_p COPY FROM measurement

Поскольку перенос данных может идти долго, нам бы не хотелось на всё время блокировать работу с этой таблицей. Чтобы не было рассогласованности между таблицами — исходной и той, в которую переносятся данные — необходимо убедиться, что вы меняете данные в старой и новой таблицах одновременно. Для этого можно создать триггер на INSERT, UPDATE, DELETE: он будет дублировать все вставляемые и обновляемые строки в новую таблицу, пока выполняется синхронизация. После переноса данных останется переименовать секционированную таблицу так, как называлась старая.

Секционируем по хэшу, списку и диапазонам

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

• по хэшу

• по списку значений

• по диапазонам 

В работе с секционированными таблицами существует такая оптимизация, как partition pruning, или исключение секций. Для исключения секций планировщику нужно дать контекст о том, как секционирована таблица. В системном каталоге записано, сколько в таблице секций, какие диапазоны они покрывают и пр. Запрос, поступающий в СУБД, имеет предикат (условие, которое написано после WHERE). Планировщику можно разрешить использовать метаинформацию о секционированности, чтобы исключить из плана лишние секции, которые не подпадают под условие запроса. Для этого параметр enable_partition_pruning должен быть в значении on.

Разберёмся подробнее в методах секционирования.

1. По хэшу

CREATE TABLE orders_1 PARTITION OF orders FOR VALUES WITH (MODULUS 3, REMAINDER 0); 
CREATE TABLE orders_2 PARTITION OF orders FOR VALUES WITH (MODULUS 3, REMAINDER 1); 
CREATE TABLE orders_3 PARTITION OF orders FOR VALUES WITH (MODULUS 3, REMAINDER 2);

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

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

Операции сравнения, такие как больше/меньше, для хэш-значений не применимы, поэтому partition pruning будет работать только в эквисоединениях и запросах с условием строгого равенства. 

Количество секций объявляется сразу. Далее нельзя менять общее количество, но можно выполнять ATTACH и DETACH — подключение и отключение секций.

2. По списку значений

CREATE TABLE part_1 PARTITION OF city FOR VALUES IN ('MSK'); 
CREATE TABLE part_2 PARTITION OF city FOR VALUES IN ('NSK'); 
CREATE TABLE part_3 PARTITION OF city FOR VALUES IN ('SPB’);

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

3. По диапазонам

CREATE TABLE part_1 PARTITION OF sales FOR VALUES FROM ('2006-01-01') TO ('2010-01-01'); 

CREATE TABLE part_2 PARTITION OF sales FOR VALUES FROM ('2013-01-01') TO ('2016-01-01'); 

CREATE TABLE part_3 PARTITION OF sales FOR VALUES FROM ('2016-01-01') TO ('2020-01-01’); 

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

При создании секции задаётся два значения для указания интервала: «от» и «до». Причём первое значение указывается включительно, а второе нет. Поэтому если требуется создать две секции с интервалами без разрыва, то значение конца первого интервала нужно указать как начальное значение следующего интервала. 

Ванильные методы работы с секциями

Среди ванильных методов рассмотрим ATTACH, DETACH, DEFAULT и многоуровневое секционирование.

ATTACH и DETACH

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

При присоединении таблицы в качестве секции необходимо указать, какие значения ключа секционирования она покрывает, в соответствии с методом секционирования. Для range — интервал, для list — значение, для hash — остаток от деления. 

ALTER TABLE measurement ATTACH PARTITION measurement_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01' );

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

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

ALTER TABLE measurement DETACH PARTITION measurement_2023; 

DEFAULT

В секцию по умолчанию попадают строки, которые по ключу секционирования не попадают ни в одну другую секцию.  

CREATE TABLE … PARTITION OF … DEFAULT ;

Многоуровневое секционирование

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

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

Реплицируем секционированные таблицы

Если физическая репликация создаёт полную копию базы данных на STAND BY-сервере, то с логической репликацией чуть сложнее. Она предполагает, что мы создаём публикацию на некоторые таблицы на одном сервере и подписываемся на неё с других серверов.

Обычно логическая репликация применяется в двух случаях: 

1. Когда нужно консолидировать данные. Допустим, есть центральный сервер и несколько региональных серверов. На каждом региональном создана публикация, а на центральном — подписка на эти публикации. Когда в любой из таблиц на региональных серверах появляются новые данные, они по подписке попадают на центральный сервер и там консолидируются. 

2. Когда нужно оперативно обновлять справочники НСИ. Например, на центральном сервере хранится таблица с нормативно-справочной информацией. Чтобы отправить её на региональные серверы, нужно создать публикацию на центральном и подписаться на неё с остальных серверов.

CREATE PUBLICATION имя [ WITH ( publish_via_partition_root = on|off [, ... ] ) ]

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

Рассмотрим вариант с выключенной опцией: publish_via_partition_root = off. В данном случае команды INSERT, UPDATE, DELETE и TRUNCATE будут приходить на подписчиков с названием той секции, к которой относилась эта строка. Например, если на публикаторе выполнялась вставка строки в таблицу city с кодом города МСК, то на подписчика будет транслироваться команда вставки сразу в секцию insert into city_msk, как если бы публикации были созданы на каждую секцию в отдельности. Для этого на подписчике должны быть созданы соответствующие таблицы, но они не обязаны быть секциями некой секционированной таблицы city — они могут быть и самостоятельными таблицами.

Когда опция включена (publish_via_partition_root = on), при репликации данных все изменения передаются через основную (родительскую) таблицу, а не через её отдельные партиции.

Например, если у вас есть секционированная таблица city, и вы добавляете в неё новые данные с помощью команды INSERT, то эти изменения будут отправлены подписчикам как операции над таблицей city, а не над конкретными её партициями.

Это означает, что на стороне подписчика таблица city может быть секционирована по-другому или вообще не быть секционирована. Подписчик сможет принимать и применять изменения, даже если его структура партиций отличается от структуры публикатора.

Расширенные методы работы с секциями в Postgres Pr

Рассмотрим, какие операции можно выполнять с секциями в Postgres Pro. 

1. SPLIT позволяет разрезать секцию на более мелкие. 

ALTER TABLE measurement SPLIT PARTITION имя_секции INTO (PARTITION имя_секции1 { FOR VALUES указание_границ_секции | DEFAULT }, PARTITION имя_секции2 { FOR VALUES указание_границ_секции | DEFAULT } [, ...])

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

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

• Если в секционированной таблице нет секции DEFAULT, то сумма интервалов полученных секций должна покрывать интервал изначальной секции.

• Если разделяется секция DEFAULT, то среди новых также должна быть секция DEFAULT. 

2. MERGE объединяет несколько секций в одну. 

ALTER TABLE measurement MERGE PARTITIONS (имя_секции1, имя_секции2 [, ...]) INTO имя_секции

3. Расширение pgpro_autopart — автосекционирование по требованию. 

Секционирование — крайне полезный инструмент, но, чтобы с ним работать, необходимо заранее подготавливать секции, создавая их вручную. Это добавляет большой объём работы для DBA, учитывая, что обычно секции исчисляются сотнями. Pgpro_autopart позволяет создавать секции динамически, когда выполняется вставка строки, а подходящей секции нет. 

В расширении есть функция ap_enable_automatic_partition_creation, в которую нужно передать название таблицы (tablename), интервал (interval) и начальное значение (first_value) — точку отсчёта, от которой будут откладываться интервалы.

pgpro_autopart работает только с новыми таблицами, так как не может отслеживать секции, которые были созданы ранее.

Представление ap_tables_view позволяет отслеживать таблицы с включённым автосекционированием.

Расширение доступно с 17-й версии Postgres Pro и закрывает функциональность более не используемого pg_pathman.

Схема работы расширения pgpro_autopart

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

Шаг 1 Создаём пустую таблицу measurement с интервальным секционированием по ID:

CREATE TABLE measurement (id bigint, s text) PARTITION BY RANGE (id); 

Шаг 2 Вызываем функцию из состава расширения, указываем название таблицы, интервал и начальное значение: 

 SELECT ap_enable_automatic_partition_creation(‘measurement', 10, 100);

где 10 — интервал, 100 — начальное значение.

1. В этот момент таблица автоматически переименовывается, добавляется префикс real.

Cоздаётся представление, которое называется именем старой таблицы — measurement. Представление — это запрос SELECT * FROM real_measurement. Оно нужно для того, чтобы в триггере для этого представления проверить, существует ли подходящая секция — так как в триггере непосредственно на таблицу такую логику реализовать нельзя. Важно отметить, что всё это происходит прозрачно для приложения, просто теперь приложение будет вызывать представление.

2. Автоматически создаётся триггер INSTEAD OF INSERT, UPDATE, в котором автоматически создаются секции, когда для вставляемой строчки подходящей секции не нашлось.

Шаг 3 Вставляем первую строчку с ID 111: 

INSERT INTO measurement VALUES (111, ‘text’ ); 

NOTICE: New partition "public"."real_measurement_110_120" created 

При этом автоматически создаётся первая секция. В её названии указываются границы интервала, который она покрывает: 

Шаг 4 Если мы выполняем UPDATE и меняем значение ключа секционирования со 111 на −55, то под неё автоматически создастся новая секция.

UPDATE measurement SET id = -55 WHERE id = 111 RETURNING *; 

NOTICE: New partition "public"."real_measurement_-60_-50" created 

Ситуации с UPDATE, INSERT, DELETE с фразой RETURNING тоже корректно обрабатываются. 

Чего ждать дальше

Уже в I квартале 2025 года мы добавим глобальные индексы. Это единые индексы для всех секций секционированной таблицы, поддерживающие уникальность. 

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

В I квартале 2026 года появится референсное (ссылочное) секционирование, когда создаваемая таблица секционируется не явно, а через ссылку на родительскую таблицу. То есть не по своему PRIMARY KEY, а по FOREIGN KEY, с теми же диапазонами ключа, что и в родительской таблице. Так секции родительской и дочерней таблицы получаются связанными, и при планировании запроса, определив одну секцию, мы можем определить связанную с ней без сканирования иерархии.

При подключении секции (ATTACH) к родительской таблице, к дочерней секция тоже подключается, при отключении (DETACH) — отключается.

Пример референсного секционирования

Допустим, есть таблица ORDERS, секционированная по интервалам (месяцам). При создании новой таблицы мы можем сослаться на родительскую таблицу как на референс, и дочерняя секционированная таблица будет разделена на те же интервалы. В ней будет столько же секций, сколько и в родительской. А при добавлении новых секций в родительскую таблицу они будут отображаться и в дочерней.

На этом всё. Пишите в комментариях, если остались вопросы.