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

Задача: Один наш крупный телеком-клиент уже несколько лет активно занимается проектами импортозамещения. Его основной фокус — перевод биллинговых систем (ключевого цифрового инструмента для управления бизнесом и взаимодействием с клиентами) с Oracle на PostgreSQL, а в качестве импортонезависимой системы управления базами данных (СУБД) выбрана платформа Nexign Nord на базе PostgreSQL от Nexign.

Вызов: Партиционирование и субпартиционирование — это неотъемлемая часть работы с большими объемами информации в продуктах Nexign. Такой подход позволяет разбивать огромные таблицы на более управляемые части, упрощая обслуживание баз данных (БД). В Oracle для этого используется встроенный автоматический механизм создания новых партиций, но в процессе подготовки к миграции на PostgreSQL перед нами встал вопрос сохранения того же уровня удобства и автоматизации.

Решение: Вместе с клиентом мы выстроили поэтапный процесс миграции продуктов: от простых решений к более сложным. Начинали с баз данных объемом в несколько десятков гигабайт, а к концу 2024 года успешно перенесли одну из крупнейших баз в нашем биллинговом решении — 5 ТБ данных с нагрузкой около 20 000 транзакций в секунду (TPS). Всё это удалось благодаря разработке собственного расширения для автоматизации партиционирования в PostgreSQL с поддержкой партиционирования по хэшу.

В этой статье Андрей Дмитриев, руководитель команды сопровождения СУБД в Nexign, поделится опытом создания этого механизма. На примере реальных задач и ограничений, с которыми команда Nexign столкнулась при миграции БД с Oracle, он расскажет, почему стандартных инструментов оказалось недостаточно, какой был использован подход к проектированию решения, и какие преимущества это дало продуктам и команде Nexign.

Как мы пришли к необходимости собственной разработки

Когда мы искали подходящее решение для автоматизации партиционирования, мы начали с изучения pg_partman — пожалуй, самого популярного и проверенного инструмента для выполнения этой задачи в PostgreSQL. Он сразу привлек наше внимание благодаря высокому уровню проработки и гибкости — pg_partman умеет самостоятельно создавать новые партиции по заданному расписанию, удалять устаревшие данные и даже перераспределять записи между секциями, если это необходимо. Однако, погрузившись в работу расширения, мы заметили, что ему не хватает одной важной для нас функции — поддержки партиционирования по хэшу. Оно используется во многих наших продуктах: основную партицию мы «режем» по дням, а внутри каждого дня создаем несколько субпартиций по хэшу. Задача на разработку такой фичи четвертый год лежит в бэклоге, и на текущий момент не понятно, что будет с ее реализацией.

В связи с этим мы решили двигаться в сторону разработки собственного инструмента. Мы планировали реализовать его не в качестве внешнего модуля, а в виде изменений в ядре Nexign Nord. Главная задумка была в том, чтобы управление партициями стало частью привычного рабочего процесса: мы хотели интегрировать его непосредственно в синтаксис команд CREATE TABLE и ALTER TABLE. Это позволило бы разработчикам и DBA декларативно задавать таблицы с автоматическим партиционированием, указывая все нужные параметры — от интервалов до типов секционирования — прямо в процессе их создания или изменения. Такой подход упрощает настройку и делает саму концепцию партиционирования более естественной частью работы с PostgreSQL — словно она изначально была встроена в систему.

Что реализовано сегодня

На текущий момент Nexign Nord поддерживает:

• автоматическое партиционирование по RANGE и HASH;

• многоуровневое автоматическое партиционирование;

• добавление параметров автоматического партиционирования к уже существующим партиционированным таблицам.

Автопартиционирование в Nexign Nord работает следующим образом: система обнаруживает отсутствие партиций и создает новые, но только для того периода или диапазона, который требуется для входящей записи. Его включение не требует пересоздания таблицы, даже если она уже содержит данные. Индексы, созданные на старых партициях, автоматически строятся и для новых секций. Все параметры партиционирования отображаются в описании таблицы и доступны через стандартные команды, например:

Как задать синтаксис автоматического партиционирования

Параметры задаются следующим образом:

• EVERY (<интервал>)

• PARTITION BY {RANGE|HASH}(<column>) (параметры автоматического партиционирования)

Для RANGE-партиционирования с типами столбцов TIME, DATE, DATETIME, DATETIMETZ интервал задается как выражение INTERVAL, например: EVERY (INTERVAL '1 hour'). В остальных случаях, если столбец числовой, интервал указывается простым числом, например: EVERY (123).

Для HASH-партиционирования используется выражение MODULUS, например: EVERY (MODULUS 4). Параметры поддерживают вложенность, что позволяет задавать несколько уровней субпартиций через дополнительный PARTITION BY.

Пример 1: одноуровневое партиционирование

Пример 2: двухуровневое партиционирование

Как управлять параметрами автоматического партиционирования

Ниже приведены основные команды для управления партиционированием в Nexign Nord.

Добавление параметров:

Удаление параметров:

Временное включение и отключение работы автоматического партиционирования:

Как работает наше изменение

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

Создание новой партиции происходит на этапе поиска нужной секции при выполнении запроса. Процесс можно условно разделить на два этапа:

• планирование (отсечение секций);

• исполнение (поиск конкретной секции).

На этапе планирования БД не выдаст ошибку, если в INSERT указаны значения, для которых отсутствует нужная партиция. Механизм отсечения секций (Partition Pruning) определит, что ни одна из существующих партиций не подходит для нового значения, поскольку оно выходит за пределы всех диапазонов, но все равно передаст запрос на дальнейшую обработку.

На этапе исполнения в PostgreSQL должна быть окончательно определена нужная партиция. В стандартной версии PostgreSQL такой запрос завершился бы ошибкой ERROR: no partition of relation “XXX” found for row. В Nexign Nord же включается дополнительная логика: перед тем как вернуть ошибку, мы проверяем настройки в pg_autopart. Если для таблицы настроено автопартиционирование, запускается обработчик, который создает новую партицию, подходящую для вставляемых значений, строго по заданным правилам.

При создании новой партиции мы устанавливаем эксклюзивную блокировку. Это позволяет автоматически разрешать возможные конфликты, если несколько сессий попытаются создать партицию одновременно. Для вставки значений в партицию мы используем штатную функцию PostgreSQL поиска нужных секций. Она определяет ключ партиционирования, значения и стратегию нарезки. На основе этих данных функция возвращает указатель на конкретную секцию, куда следует отправить INSERT. Такой подход позволяет нам защититься от непредвиденных ошибок и использовать стандартные механизмы работы с партициями.

Как правильно называть партиции

Автоматические партиции именуются по правилу Relname$r[h]_part, где:

• Relname – название таблицы

• $ – разделитель уровней вложенности (их может быть несколько в случае использования сабпартиционирования)

• r[h]start__end – описание самой партиции: r — для RANGE, h — для хэш-партиционирования, а start и end — границы диапазона или модуль с остатком для хэша, разделенные двойным подчеркиванием.

Что с производительностью

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

Для моделирования многопользовательского доступа мы создадим таблицу big_table с партиционированием по месяцам и субпартиционированием по хэшу (EVERY (INTERVAL '1 mon') PARTITION BY HASH(user_id) (EVERY (MODULUS 4))).

Наполним ее данными за 2025-2028 гг. и подготовим два скрипта. Первый будет моделировать многопользовательское чтение:

Второй будет моделировать многопользовательскую вставку в уже созданные партиции в 2025-2028 гг.:

Запустим каждый из них через pgbench в 10 потоков на 5 минут:

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

Путем вставки данных создадим новую партицию вне нашего подготовленного диапазона 2025-2028 гг. Для примера сделаем вставку данных за 2125 г., а затем в созданной секции сделаем еще одну субпартицию:

Как видно из результатов эксперимента, первый INSERT с созданием новой партиции по RANGE отработал за 60 мс, а создание новой субпартиции в ней произошло уже быстрее — за 16 мс. После создания секций все остальные INSERT на вставку отрабатывают за стабильные 2 мс.

Что дальше?

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

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

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