Ситуация: у вас PostgreSQL, в котором копятся гигантские таблицы. Вы попытались их разбить по времени или по ID, но все уперлось в рутинный менеджмент: надо создавать новые партиции, чистить старые, не забыть настроить индексы… Короче, превращается это в сериал на сто сезонов. А может, вы используете встроенное декларативное партиционирование, но хочется чего‑то поудобнее? Вот тут хорошо поможет pg_partman. Это расширение — фактически «менеджер по партиционированию», который сделает половину этой рутины за вас.
pg_partman — это расширение к PostgreSQL, которое упрощает декларативное партиционирование больших таблиц по времени или по числовым значениям. Не надо вручную создавать новые партиции, ломать голову над датами, выпиливать старые партиции. pg_partman сам создаст нужные секции вперед, поможет с очисткой старых, подскажет, если данные вдруг залетели в дефолтный партишн.
Основные фичи:
-
Автоматическое создание будущих партиций: задаем премейк партиций (например, 4 вперёд), и pg_partman будет автоматически делать новые партиции по расписанию.
-
Управление retention: хотите хранить данные за последние 90 дней, а все старше — дропать? Легко. Хотите не дропать, а перенести в другую схему? Тоже можно.
-
Проверка и фиксация данных в default партиции: если к вам прилетают данные, которым пока нет нужного раздела, pg_partman поместит их в default‑партицию. Потом можно будет аккуратно разложить их по нужным партициям.
Установка и подготовка
Для начала ставим расширение:
CREATE EXTENSION pg_partman SCHEMA partman;Обычно я ставлю в отдельную схему partman, чтобы было красиво. Далее нужна табличка, которую мы будем партиционировать. Представим себе, что у нас аналитика логов событий. Назовем таблицу event_logs. Пусть ъбудет поле log_time TIMESTAMP WITH TIME ZONE и данные залетают пачками:
CREATE TABLE public.event_logs ( id BIGSERIAL PRIMARY KEY, log_time TIMESTAMPTZ NOT NULL, user_id INT, event_type TEXT, event_data JSONB );Теперь наша задача — разбить ее по дням. Каждый день — своя партиция, хранить будем за последние 90 дней, старые партиции будем дропать. Заранее хотим 4 партиции вперед, чтобы не париться.
Создание родительской таблицы под управлением pg_partman
pg_partman.create_parent() превращает таблицу в родительскую для партиций. Важно: в PostgreSQL 14+ для декларативного партиционирования вы должны сначала вручную объявить таблицу PARTITIONED:
-- Перепишем создание таблицы: DROP TABLE IF EXISTS public.event_logs CASCADE; CREATE TABLE public.event_logs ( id BIGSERIAL, log_time timestamptz NOT NULL, user_id int, event_type text, event_data jsonb ) PARTITION BY RANGE (log_time);Отлично. Теперь вызовем create_parent из pg_partman:
SELECT partman.create_parent( p_parent_table := 'public.event_logs', p_control := 'log_time', p_interval := '1 day', -- каждый день отдельная партиция p_premake := 4, -- держать всегда 4 партиции вперёд p_default_table := true, -- создадим default-партицию p_automatic_maintenance := 'on', -- чтобы run_maintenance() нас обслуживал p_jobmon := true -- включим мониторинг, если pg_jobmon установлен ); pg_partman создал конфигурацию в partman.part_config. Он также создаст template‑таблицу, default‑партицию и нужные партиции вплоть до текущего момента. Если у вас сейчас, допустим, 2024-2-12, то у нас будут партиции для последних нескольких дней и на 4 вперед.
Проверим:
SELECT * FROM partman.show_partitions('public.event_logs');Получим список дочерних таблиц. Вы увидите что‑то вроде: public.event_logs_p20240212, public.event_logs_p20240313, и так далее.
Автоматическая работа и maintenance
Чтобы новые партиции появлялись, нужно периодически дергать partman.run_maintenance(). Это можно делать через cron или использовать встроенный background worker. Самый простой вариант — раз в час:
# cron 0 * * * * psql -d mydb -c "SELECT partman.run_maintenance();"Что произойдет при запуске run_maintenance()?
-
Проверит, хватает ли заранее созданных партиций (параметр premake).
-
Если нет, то создаст новые.
-
Проверит, нет ли старых партиций, которые надо дропать (если retention включен).
-
Обновит статистику и сделает analyze, если надо.
В итоге у вас всегда будет свежая структура, готовая под новые данные.
Retention: автоматический дроп старья
90 дней и все, что старше — на свалку? Настроим retention. Для этого просто в partman.part_config меняем retention:
UPDATE partman.part_config SET retention = '90 days', retention_keep_table = false -- хотим именно дропать старье, а не просто отцепить WHERE parent_table = 'public.event_logs';Теперь при следующем run_maintenance() pg_partman посмотрит на самые древние партиции, которым >90 дней, и их отдропает. Если боитесь сразу дропать, можно сначала их только отцеплять retention_keep_table = true, или переносить в другую схему retention_schema = 'old_data', или вообще делать дамп через dump_partition.py.
Что, если в default залетели данные?
Иногда могут появиться данные с timestamp, который не укладывается ни в одну из существующих партиций. Они попадут в default‑партицию (которую мы создали p_default_table := true). Это спасет от ошибок вставки. Но default — это не место для жизни данных. Нужно периодически проверять:
SELECT * FROM partman.check_default();Если там что‑то есть, можно аккуратно перекинуть их в нужные партиции:
SELECT partman.partition_data_time('public.event_logs');Эта функция сама создаст нужные партиции, если их не хватает, и разложит данные куда нужно. Можно делать это по чуть‑чуть, батчами, используя partition_data_proc, чтобы не зависать транзакцией на долгие часы.
Пример батчевого переноса:
CALL partman.partition_data_proc( p_parent_table := 'public.event_logs', p_interval := '1 day', -- переносим по одному дню за раз p_loop_count := 10, -- за одну процедуру 10 дней перетащим p_quiet := false );После такой процедуры данные будут разложены по нужным партициям.
Индексы, настройки, наследование свойств
Одно из болевых мест — а как же индексы, дефолты, привилегии на дочерних таблицах? pg_partman использует template‑таблицу. То есть можно создать таблицу‑шаблон:
CREATE TABLE partman.mytemplate ( CHECK (log_time IS NOT NULL) ) WITH (fillfactor=90);И потом при create_parent указать p_template_table := 'partman.mytemplate'. Тогда все новые партиции будут иметь настройки и индексы от шаблона.
Пример: если вы хотите, чтобы каждая партиция имела индекс по user_id:
CREATE INDEX ON partman.mytemplate (user_id);После этого при создании новых партиций pg_partman клонирует этот индекс.
Constraint exclusion
Когда существует много партиций, PostgreSQL старается применять constraint exclusion или partition pruning, чтобы не перебирать весь зоопарк таблиц. Но для этого нужны корректные CHECK‑констрейнты. pg_partman сам ставит эти констрейнты для партиций. Более того, можно указать дополнительные колонки для ограничения, чтобы при запросах по non‑partition колонкам тоже был пропуск ненужных партиций.
Например, хотим, чтобы на старых таблицах был CHECK по user_id:
UPDATE partman.part_config SET constraint_cols = '{"user_id"}' WHERE parent_table = 'public.event_logs'; -- Применим новые констрейнты SELECT partman.apply_constraints('public.event_logs');Теперь, если вы сделаете запрос типа SELECT * FROM event_logs WHERE user_id = 123, PostgreSQL сможет пропустить те партиции, у которых по constraint точно нет user_id = 123 (особенно если pg_partman уже зафиксировал мин/макс значения).
Поддержка подуровневого (суб-)партиционирования
Если у нас просто гигантское чудовище и одного уровня партиционирования мало, можно сделать субпартиции. Например, сначала по году, а потом внутри года по дням. Но это уже rocket science. Создаем верхний уровень через create_parent(), потом create_sub_parent() для второго уровня. Предупреждаю: это сложно и нужно реально понимать, что вы делаете. Производительности напрямую субпартиционирование может не дать, если вы не крутите петабайты.
Пример (очень упрощенный):
-- Предположим, event_logs уже партиционирован по месяцу SELECT partman.create_sub_parent( p_top_parent := 'public.event_logs', p_control := 'log_time', p_interval := '1 day', p_declarative_check := 'yes' -- требуется для подтверждения );Сразу предупреждаю: существующие данные будут переразбиваться. Делайте это аккуратно и лучше на пустых таблицах или после резервного копирования.
Undo Partitioning
Когда‑то вы решили разбить таблицу на партиции, а потом передумали. Undo в pg_partman тоже есть:
SELECT partman.undo_partition( p_parent_table := 'public.event_logs', p_target_table := 'public.event_logs_flat' );Это будет понемногу переносить данные из партиций обратно в обычную таблицу. Тоже можно делать батчами и с паузами.
Итог
pg_partman — бомбическая штука для тех, кто устал вручную менеджить партиции. Он сам создает будущие партиции, чистит старые, позволяет перераспределять данные из default‑партиций и делает много вкусных вещей, чтобы вы могли сосредоточиться на аналитике, а не на рутине.
С документацией можно ознакомиться здесь.
А тем, кому интересно узнать о трендах и будущем системного анализа, рекомендую посетить открытый урок, который пройдет 23 декабря в рамках курса «Системный аналитик. Team Lead» в Otus. Записаться можно по ссылке.
ссылка на оригинал статьи https://habr.com/ru/articles/866192/
Добавить комментарий