pg_partman: автоматическое секционирование PostgreSQL для тайм‑серий и retention

Тайм‑серии и большие журнальные таблицы в PostgreSQL стареют быстро: свежие данные горячие и активно читаются, старые — накапливаются, фрагментируются, удорожают VACUUM и бэкапы. Декларативное partitioning решает часть задачи, но рутину всё равно надо автоматизировать: заранее создавать будущие партиции, удалять устаревшие (retention), переносить в архивные схемы, контролировать индексы и обслуживание. Здесь на сцену выходит pg_partman — расширение, которое берёт на себя создание/удаление секций и упрощает эксплуатацию больших таблиц без ночных скриптов на коленке. Если база живёт на изолированном сервере, удобно держать её на облачном VDS — так проще управлять кроном и сервисами.

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

Секционирование целесообразно, если:

• В таблице миллионы+ строк, а операции удаления по времени вызывают заметный раздув и долгий VACUUM.
• Нагрузочный профиль типичен для time series: свежие записи — инсерты/апдейты, старые — почти только чтение или периодический drop.
• Нужна простая и прозрачная retention‑политика: хранить N дней/месяцев и аккуратно удалять целыми секциями.

pg_partman поверх нативного partitioning (PostgreSQL 10+) даёт:

• Автоматическое создание будущих партиций по времени/ID с запасом (premake).
• Удаление или перенос старых партиций по retention.
• Шаблонную таблицу для единообразия индексов, storage‑параметров и привилегий у новых партиций.
• Простые процедуры обслуживания через cron (run_maintenance()).

Идея: чем меньше число «живых» строк в каждой партиции и чем реже их трогают, тем быстрее работают Index Only Scan, короче план и быстрее проходят VACUUM.

Подготовка: выбор ключа и границ партиций

Для тайм‑серий почти всегда берут колонку с временной меткой: ts timestamptz или ts bigint (epoch). Дальше — шаг партиционирования:

• hourly — при массивной вставке (телеметрия, логирование высокого трафика).
• daily — баланс скорости и количества таблиц, хороший дефолт для 1–50 млн строк/сутки.
• monthly — аналитика, низкая частота вставки, длинный retention.

Правило: чем «горячее» окно доступа, тем мельче шаг, чтобы высвобождать старые данные целиком (drop) и держать «горячую» партицию небольшой.

Установка и включение расширения

В большинстве дистрибутивов есть пакет pg_partman под конкретную версию сервера. Имена пакетов отличаются, ориентируйтесь на репозитории вашей ОС.

# Debian/Ubuntu (примерное имя пакета, проверяйте версию PG)
sudo apt update
sudo apt install postgresql-16-partman

# RHEL/Rocky/AlmaLinux (примерное имя пакета)
sudo dnf install pg_partman_16

Затем создайте схему для служебных объектов и подключите расширение в кластере:

-- в psql под суперпользователем/владельцем БД
CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

Базовая схема: родительская таблица и шаблон

Предположим, у нас поток событий:

-- Родительская секционированная таблица (декларативная)
CREATE TABLE public.events (
    id           bigint       NOT NULL,
    ts           timestamptz  NOT NULL,
    user_id      bigint,
    payload      jsonb,
    PRIMARY KEY (id, ts)
) PARTITION BY RANGE (ts);

-- Шаблон для новых партиций (наследуется при создании)
CREATE TABLE public.events_template (
    -- можно задать storage‑параметры и индексы
) TABLESPACE pg_default;

-- Индекс, релевантный по времени (для свежей партиции будет очень полезен)
-- Для нативного partitioning: создаем индекс на РОДИТЕЛЕ — это "partitioned index"
CREATE INDEX events_ts_idx ON public.events (ts);

-- Пример селективного индекса для фильтрации
CREATE INDEX events_user_ts_idx ON public.events (user_id, ts);

Важно: уникальные ограничения на секционированных таблицах должны включать ключ секционирования (ts), иначе межпартиционная уникальность не гарантируется. В примере PRIMARY KEY (id, ts).

Создание конфигурации pg_partman

Теперь подключим pg_partman к нашему родителю. Выберем нативное партиционирование (type = 'native') и суточный шаг:

SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control      := 'ts',
    p_type         := 'native',
    p_interval     := 'daily',
    p_template_table := 'public.events_template'
);

-- Сколько будущих партиций держать заранее (чтобы INSERT не упирался в создание)
UPDATE partman.part_config
   SET premake = 7
 WHERE parent_table = 'public.events';

-- При необходимости зафиксируйте первый интервал (опционально)
-- UPDATE partman.part_config SET start_partition = '2025-01-01' WHERE parent_table = 'public.events';

После этого pg_partman создаст нужные партиции на ближайшие дни. Проверить можно обычным запросом к каталогу партиций или по состоянию partman.part_config.

Retention: удаление или архивирование старых данных

Retention‑политика определяется в partman.part_config. Вариантов два:

• Удалять устаревшие партиции целиком — максимально быстро и дешево.
• Переносить старые партиции в отдельную схему (архив) — если удалять нельзя, но доступ нужен редко.

Конфигурация на 90 дней хранения с удалением:

UPDATE partman.part_config
   SET retention = '90 days',
       retention_schema = NULL,
       retention_keep_table = false
 WHERE parent_table = 'public.events';

Альтернатива: перенос в схему archive вместо удаления (доступно для последующего оффлайн‑экспорта):

CREATE SCHEMA IF NOT EXISTS archive;

UPDATE partman.part_config
   SET retention = '12 months',
       retention_schema = 'archive',
       retention_keep_table = true
 WHERE parent_table = 'public.events';

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

Обслуживание: как запускать pg_partman через cron

Две задачи: заранее создавать новые партиции и применять retention. Обе выполняет run_maintenance(). Самый простой способ — крон на узле с БД:

# Каждые 5 минут: создание будущих партиций для всех конфигов
*/5 * * * * psql "dbname=appdb" -c "SELECT partman.run_maintenance();" >/dev/null 2>&1

# Раз в ночь: принудительное обслуживание (retention и уборка опциональных хвостов)
30 2 * * * psql "dbname=appdb" -c "SELECT partman.run_maintenance(p_analyze := false);" >/dev/null 2>&1

Если баз в кластере несколько, выполняйте run_maintenance() в каждой из них. В больших инсталляциях имеет смысл вынести задания в отдельного системного пользователя и добавить мониторинг длительности/ошибок.

Облачный VDS-сервер

Виртуальные серверы с быстрым запуском и гибкой конфигурацией от 390₽ / мес

Доступные локации

Россия Нидерланды Финляндия

Смотреть тарифы

VACUUM/ANALYZE и параметры для шаблона

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

-- Для будущих партиций: настройки на шаблоне
ALTER TABLE public.events_template SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_scale_factor = 0.02,
    toast.autovacuum_vacuum_scale_factor = 0.05
);

-- Для уже созданных партиций массово применим параметры
DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT inhrelid::regclass AS child
             FROM pg_inherits
            WHERE inhparent = 'public.events'::regclass
  LOOP
    EXECUTE format('ALTER TABLE %s SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_analyze_scale_factor = 0.02)', r.child);
  END LOOP;
END$$;

Не забывайте про ANALYZE после наполнения крупных партиций — планировщик будет благодарен. Для борьбы с индексным bloat полезен плановый REINDEX CONCURRENTLY; детали — в статье о снижении bloat в PostgreSQL.

Индексы: на родителе или на партициях

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

• CREATE INDEX CONCURRENTLY на «partitioned index» недоступен, зато доступен на каждой партиции отдельно.
• Если индексы создавались только на шаблоне, перераздать их на существующие партиции можно собственным скриптом.

-- Без простоя (по партициям, последовательно)
DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT inhrelid::regclass AS child
             FROM pg_inherits
            WHERE inhparent = 'public.events'::regclass
  LOOP
    -- пример: индекс по user_id, ts
    EXECUTE format('CREATE INDEX CONCURRENTLY IF NOT EXISTS %I_user_ts_idx ON %s (user_id, ts)',
                   r.child::text, r.child);
  END LOOP;
END$$;

Для «горячих» партиций подумайте о частичных индексах (например, по конкретному статусу) и регулярном REINDEX CONCURRENTLY при растущем bloat больших B‑tree. Подбор стратегий см. также в материале про индексы и обслуживание партиций.

Миграция существующей большой таблицы

Обычный сценарий — у вас уже есть огромная events_big, и вы хотите перейти на партиции без долгого простоя:

1. Создайте родителя и шаблон, как выше.
2. Поднимите pg_partman на родителе.
3. Используйте функции разрезания существующих данных пакетно; для тайм‑серий — по интервалам.
4. Перенесите записи из старой таблицы в новые партиции; чаще всего батчами по диапазонам ts с INSERT ... SELECT ... WHERE ts BETWEEN ....
5. Переключите запись с приложения на нового родителя.

-- Пример батч‑миграции по датам (минимальный простой)
INSERT INTO public.events (id, ts, user_id, payload)
SELECT id, ts, user_id, payload
  FROM public.events_big
 WHERE ts >= date_trunc('day', now()) - interval '180 days';

-- Повторяйте батчи в окно, следя за нагрузкой и планами (ANALYZE между шагами)

Если нужен абсолютно «живой» переход, продумайте двойную запись (в старую и новую) на короткий период и финальный дельта‑добор с последующей валидацией количества строк.

Выбор интервала и premake: практические пресеты

• До ~5 млн строк в сутки и активные запросы к последним 7–14 дням: daily, premake = 7..14, retention = '60..180 days'.
• Сотни миллионов в сутки и короткий SLA по вставке: hourly, premake = 48..72, retention = '7..30 days'.
• Редкие вставки, длинный архив: monthly, premake = 3..6, retention = '12..36 months'.

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

План запросов и «pruning»

Преимущество partitioning раскрывается, когда планировщик умеет отсеивать нерелевантные партиции (partition pruning). Для этого:

• Пишите запросы с предикатами по ts (например, WHERE ts >= now() - interval '7 days').
• Следите, чтобы индексы совпадали с ведущими условиями и сортировками (ORDER BY ts DESC).
• В аналитике разбирайте агрегаты по недавним периодам на уровне приложения, а не сканируйте весь исторический хвост.

Обслуживание без сюрпризов: VACUUM, статистика, бэкапы

С секционированием у вас меньше блокирующих операций над «монолитом», но больше мелких объектов:

• VACUUM и ANALYZE идут по партициям; настраивайте агрессивнее для «горячих» секций и мягче для старых.
• Бэкапы инкрементальными инструментами работают быстрее, если старые партиции неизменяемы (WAL‑шум ниже).
• REINDEX планируйте по расписанию для больших партиций с высокой скоростью вставки.

Отладка и проверка состояния pg_partman

Полезные проверки, когда что‑то идет не так:

• Есть ли партиция для текущего времени? Посмотрите список дочерних таблиц у родителя.
• Достаточен ли premake? Если крон задержался — пополните вручную, вызвав run_maintenance().
• Правильно ли задан retention? Уточните формат и что именно вы хотите делать со старыми партициями: удалять или переносить.
• Не забыли ли вы индексы на родителе и подходящие параметры на шаблоне?

-- Быстрый список партиций родителя
SELECT c.relname AS child
  FROM pg_inherits i
  JOIN pg_class   c ON c.oid = i.inhrelid
 WHERE i.inhparent = 'public.events'::regclass
 ORDER BY c.relname;

-- Текущая конфигурация partman по родителю
SELECT * FROM partman.part_config WHERE parent_table = 'public.events';

Частые грабли и как их обойти

• Уникальность без ключа секционирования. Межпартиционная уникальность невозможна без включения колонки ts в ключ.
• FOREIGN KEY на родителя. С внешними ключами к секционированным таблицам есть ограничения; продумывайте схему или отказывайтесь от FK в пользу логической валидации.
• Слишком крупный интервал. Месячные партиции при больших объемах вставки ведут к раздутым индексам и долгим VACUUM. Делите чаще.
• Пропущенный cron. Без обслуживания не создадутся новые партиции и не сработает retention. Дублируйте задания, ставьте алерты.
• Индексы только на шаблоне. Для нативного partitioning создавайте индексы на родителе, чтобы они были «partitioned». Шаблон — для параметров хранения и специфичных объектов.

Мини‑чеклист внедрения

1. Определите ключ и шаг партиционирования по профилю нагрузки.
2. Создайте родителя и шаблон, настройте параметры autovacuum на шаблоне.
3. Запустите partman.create_parent(...), проверьте premake.
4. Задайте retention (drop или архивная схема).
5. Настройте cron для run_maintenance() и мониторинг.
6. Создайте нужные индексы на родителе; для существующих партиций — CONCURRENTLY по очереди.
7. Обновите планы VACUUM/ANALYZE, учитывая новые партиции.

Итоги

pg_partman закрывает практическую сторону partitioning: ротация секций по времени, предсоздание, единообразие новых партиций и простая эксплуатация через cron. Для time series вы получаете предсказуемую retention, более короткие планы, быстрый drop старых данных и управляемый VACUUM. Внедряйте с шаблоном, создавайте индексы на родителе, не забывайте о мониторинге обслуживания — и PostgreSQL отблагодарит вас стабильной латентностью и экономией места.