Мой путь к секционированию в PostgreSQL

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

Привет, Хабр! Меня зовут Алмаз и сейчас я хочу поделиться методом, который помог мне реализовать секционирование.

Секционирование в PostgreSql

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

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

Схема секционированной таблицы “sales”

Минусы этого подхода:

— Усложняется структура базы данных. Каждая секция в определениях базы — это таблица, хоть и является частью одной логической сущности.
— Преобразовать существующую таблицу в секционированную и наоборот нельзя.
— Нет полной поддержки в версии Postgres 11.

Плюсы:

+ Быстродействие. В определенных случаях мы можем работать с ограниченным набором секций, не перебирая всю таблицу, даже поиск по индексу для больших таблиц будет медленнее. Повышается доступность данных.
+ Массовая загрузка и удаление данных командами ATTACH/DETACH. Это избавляет нас от накладных расходов в виде VACUUM-а. что позволяет более эффективно сопровождать базу данных.
+ Возможность указать TABLESPACE для секции. Это дает нам возможность выносить данные в другие разделы, но все же мы работаем в рамках одного инстанса и метаданные главного каталога будут содержать информацию о секциях.(не путать с шардингом)

2 пути к реализации секционирования в PostgreSql:

1. Наследование таблиц (INHERITS)
Когда, создавая таблицу, мы говорим «наследуйся от другой (родительской) таблицы». При этом добавляем ограничения для управления данными в таблице. Этим мы поддерживаем логику разбиения данных, но это логически разные таблицы.

Тут нужно отметить расширение разработанное компанией Postgres Professional pg_pathman, которое реализует секционирование, также через наследование таблиц.

 CREATE TABLE orders_y2010 (       CHECK (log_date >= DATE '2010-01-01)     ) INHERITS (orders); 

2. Декларативный подход (PARTITION)

Таблица определяется как секционированная декларативно. Данное решение появилось в 10 версии PostgreSql.

CREATE TABLE orders (log_date date not null, …)  PARTITION BY RANGE(log_date); 

Я выбрал декларативный подход. Это дает большое преимущество — нативность, больше фич поддерживается ядром. Рассмотрим развитие PostgreSQL в данном направлении:

Источник

Но PostgreSql продолжает развиваться, и в 12 версии есть поддержка ссылок на секционированную таблицу. Это большой прорыв.

Мой путь

Учитывая вышесказанное, был написан скрипт на PL/pgSQL, который создает секционированную таблицу на основе существующей и “перекидывает” все ссылки на новую таблицу. Тем самым мы получаем секционированную таблицу на основе существующей и продолжаем работать с ней как с обычной таблицей.
Скрипт не требует дополнительных зависимостей и выполняется в отдельной схеме, которую создает сам. Также записывает логи повтора и отмены действий. Данный скрипт решает две основные задачи: создает секционированную таблицу и реализует внешние ссылки на нее через констрейнт триггеры.

Требование к скрипту: PostgreSql v.:11 и выше.

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

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

 call partition_run(); 

2. Вызов отложенных задач, которые были запланированы во время основной работы:

 call partition_run_jobs(); 

Работа может быть запущена в несколько потоков. Оптимальное количество потоков близка к количеству секционируемых таблиц.

Входные параметры для скрипта (_pt record)

Скрипт изнутри, основные действия:

— Создаем секционированную таблицу

 perform _partition_create_parent_table(_pt); 

— Создаем секции

 perform _partition_create_child_tables(_pt); 

— Копируем данные в секции

 perform _partition_copy_data(_pt); 

— Добавим ограничения (job)

 perform _partition_add_constraints(_pt); 

— Восстановим ссылки на внешние таблицы

 perform _partition_restore_referrences(_pt); 

— Восстановим триггеры

 perform _partition_restore_triggers(_pt); 

— Создаем событийный триггер

 perform _partition_def_tr_on_delete(_pt); 

— Создаем индексы (job)

 perform _partition_create_index(_pt); 

— Заменяем вьюхи, ссылки на секцию (job)

 perform _partition_replace_view(_pt); 

Время работы скрипта зависит от многих факторов, но основные — это размер целевых таблиц, количества отношений, индексы и характеристики сервера. В моем случае таблица 300Gb секционировалась меньше чем за час.

Результат

Что мы получили? Посмотрим на план запроса:

 EXPLAIN ANALYZE  select * from “sales” where dt BETWEEN '01.01.2019'::date and '14.01.2019'::date 

Результат из секционированной таблицы мы получали быстрее и использовали меньше ресурсов нашего сервера по сравнению с запросом к обычной таблице.

В данном примере обычная и секционированные таблицы находятся на одной базе и имеют около 200М записей. Это хороший результат, учитывая то, что мы, не переписывая прикладной код, получили ускорение. Запросы по другим индексам также работают хорошо, но следует помнить: всегда, когда мы можем определить секцию, результат будет в несколько раз быстрее, т.к. PostgreSql умеет отбрасывать лишние секции на этапе планирования запроса (set enable_partition_pruning to on).

Итог

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

PostgreSQL — самая современная в мире реляционная база данных с открытым исходным кодом!

Всем спасибо!

Ссылка на исходник