Партиционирование в PostgreSQL: декларативные секции, индексы и обслуживание

Партиционирование в PostgreSQL давно вышло из статуса «хитрого трюка» и стало базовой техникой для высоконагруженных OLTP/OLAP-систем, потоков событий и больших журналов. Декларативные секции (partitioned tables) позволяют прозрачно разносить данные по физическим таблицам, снижая нагрузку на индексы и упрощая обслуживание. В этой статье я собрал практические подходы: как выбрать ключ и тип партиционирования, какие индексы ставить, как устроены первичные и внешние ключи в секциях, как работает pruning и partitionwise-оптимизации, а также как выстроить регулярный maintenance и ретеншн без простоя.

Декларативное партиционирование: основы и типы

Декларативные секции появились в PostgreSQL 10, и это кардинально упростило жизнь по сравнению с ручным наследованием и триггерами. Теперь достаточно объявить родительскую таблицу и правило разбиения: движок сам маршрутизирует вставки и, что важнее, умеет отбрасывать нерелевантные секции на этапе планирования запроса (partition pruning).

Основные типы:

• Range — секции по диапазонам значений: время (day/week/month), числовые интервалы, версии.
• List — дискретные списки значений: страна, регион, тип объекта.
• Hash — равномерное распределение ключей для балансировки горячих вставок/обновлений.

Возможна вложенная схема (subpartitioning), например Range по дате и внутри Hash по идентификатору клиента для устранения горячих точек.

Быстрый пример: помесячные секции по времени события

-- Родитель
CREATE TABLE events (
  id bigserial,
  ts timestamptz NOT NULL,
  user_id bigint NOT NULL,
  payload jsonb NOT NULL,
  PRIMARY KEY (id, ts)
) PARTITION BY RANGE (ts);

-- Секции на три месяца вперёд
CREATE TABLE events_2025_01 PARTITION OF events FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE events_2025_03 PARTITION OF events FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Опционально: дефолтная секция для "заблудившихся" данных
CREATE TABLE events_default PARTITION OF events DEFAULT;

Обратите внимание на первичный ключ: в PostgreSQL уникальные ограничения и PK для партиционированной таблицы должны включать ключ партиционирования. Это логично: без глобального индекса движок не может проверить уникальность «вне» секций.

Выбор ключа и размера секций

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

• Монолитность по времени: если данные идут как бесконечный поток событий, выбирайте Range по времени. Это упрощает ретеншн (DROP/DETACH старых секций) и уменьшает bloat на горячих данных.
• Равномерность нагрузки на вставку: для монолитного времени возникнет «горячая» последняя секция. Если write-heavy и много параллельных вставок, добавляйте вложенный Hash по сущности (например, user_id), чтобы распределить горячие страницы.
• Кардинальность и селективность запросов: если запросы почти всегда фильтруют по дате — хорошо. Если фильтр по стране/типу — возможно LIST по стране, а внутри RANGE по дате.
• Количество секций: у PostgreSQL неплохая масштабируемость, но тысячи секций повышают служебные накладные. Практично держать сотни, иногда — несколько тысяч, но следите за планировщиком и временем EXPLAIN.
• Размер секции: ориентир — такой, чтобы VACUUM/ANALYZE секции был быстрым, а индекс не разрастался до «неподъёмного». Для журналов часто берут день/неделю, для аналитических витрин — месяц/квартал.

Индексы в секционированных таблицах

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

Локальные индексы, уникальность и покрытие

• Глобальных индексов в классическом понимании нет: каждый индекс физически локален секции. Поэтому UNIQUE и PRIMARY KEY должны включать ключ партиционирования.
• Если вы пишете CREATE INDEX ON events(ts, user_id) на родителе, движок создаст такой же индекс в каждой секции. Это удобно для консистентности схемы.
• Режим создания индексов одновременно на всём дереве секций зависит от версии. Часто приходится строить индексы поочерёдно на секциях, используя CONCURRENTLY там, где это поддерживается. Проверяйте конкретный релиз сервера и планируйте окно обслуживания.
• Частичные индексы отлично сочетаются с секциями. Например, на каждой секции можно индексировать только «активные» строки. Так вы сокращаете размер и ускоряете INSERT/UPDATE.

BRIN для временных данных

Для append-only журналов BRIN часто даёт лучшее соотношение «размер/скорость». Он хранит статистические сводки по страницам, и если данные приходят по возрастанию времени, запросы с диапазонами по ts работают эффективно при минимальном размере индекса.

-- Лёгкий индекс для сканов по времени
CREATE INDEX events_ts_brin ON events USING brin (ts) WITH (pages_per_range = 128);

Для OLTP-операций, точечных поисков и строгой уникальности всё же пригодится B-Tree по бизнес-ключу. Часто комбинируют: BRIN для аналитики по времени и локальные B-Tree по идентификаторам.

Планировщик: pruning, partitionwise join/aggregate

Главный профит — планировщик отбрасывает нерелевантные секции на этапе планирования. Это называется partition pruning. Чтобы оно работало, выражения фильтра должны быть вычислимы на этапе планирования (например, ts >= date_trunc('month', now()) воспринимается нормально, но в некоторых случаях лучше передавать параметры как $1 и давать стабильные функции).

• Partitionwise aggregate/join: если обе стороны запроса имеют одинаковую схему секционирования по одному ключу, движок может выполнять агрегации/соединения по секциям. Это уменьшает память и ускоряет планы.
• Следите за EXPLAIN (ANALYZE, BUFFERS). Количество «Pruned partitions» — важный индикатор того, что схема секционирования сходится с паттернами запросов.

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

Первичные/внешние ключи и ограничения

Особенности ограничений в секционированных таблицах:

• PRIMARY KEY/UNIQUE: должны включать ключ партиционирования. Иначе движок отклонит определение.
• FOREIGN KEY: ссылки на партиционированные таблицы поддерживаются, но проверка идёт по секциям. Массовые вставки со множеством ссылок могут упираться в стоимость проверок на множестве секций — планируйте индексы на столбцах ссылок в целевых секциях.
• CHECK: на секциях автоматически создаются ограничения, определяющие диапазон или список значений. Они критичны для pruning.

Обслуживание (maintenance) секций

Партиционирование упрощает обслуживание: вместо одного гиганта вы работаете с набором умеренных таблиц. Но рутина должна быть чётко описана.

Ротация, ретеншн и «холодные» секции

Типовой цикл для диапазонных секций по времени:

1. Заранее создать секции на горизонте 1–3 периода (например, на месяц вперёд), чтобы вставки не спотыкались о отсутствие подходящей секции.
2. Еженедельно или ежемесячно отсекать «холодные» секции: DETACH PARTITION для архивирования или сразу DROP по политике хранения.
3. Опционально перемещать старые секции в другой табличный пространств (tablespace) перед архивированием.

-- Создать секцию следующего месяца
CREATE TABLE events_2025_04 PARTITION OF events FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');

-- Отсоединить старую секцию для бэкапа/архива
ALTER TABLE events DETACH PARTITION events_2024_12;
-- После копирования/архива:
DROP TABLE events_2024_12;

Отсоединение быстрее и безопаснее, чем массовое DELETE: не создаёт лавины WAL и не оставляет bloat. Вырезали — и забыли. Для стратегии резервного копирования см. разбор инструментов и практик в материале про резервные копии через pgBackRest: как строить бэкапы и восстановление PostgreSQL с pgBackRest.

VACUUM/ANALYZE и настройки автосервиса

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

ALTER TABLE events_2025_03 SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit = 2000
);

Чтобы не править каждую новую секцию руками, задавайте параметры на родителе — многие параметры наследуются. Там, где нужно тонко настроить «активную» секцию, применяйте ALTER TABLE точечно. Дополнительно рекомендую чек-лист и параметры в отдельной заметке: настройка autovacuum и индексов в PostgreSQL.

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

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

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

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

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

REINDEX, bloat и pg_repack

Преимущество секций в том, что вы можете обслуживать индексы по частям и без ощутимого простоя. На больших нагрузках полезно уметь перестраивать индекс секции по очереди. Для снижения bloat избегайте массовых DELETE на активной секции — используйте ротацию. Если всё же нужен точечный пересчёт, применяйте перестройку индексов секции и анализируйте отчёты по bloat.

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

Паттерны индексации для производительности

Несколько практических схем под разные нагрузки:

• Time-series: BRIN(ts) для длинных сканов по времени + локальные B-Tree по бизнес-ключам (например, (user_id, ts DESC)), возможно с INCLUDE для покрытия выборок.
• OLTP со смешанными запросами: комбинированные B-Tree индексы, отражающие реальные WHERE/ORDER BY. Частичные индексы на «активные» подмножества данных в каждой секции.
• Hash-субсекционирование: уменьшает горячие точки на последней секции и помогает масштабировать запись, но усложняет схему и обслуживание — используйте, если реально упираетесь в контенцию.

-- Покрывающий индекс для выборок по пользователю и свежим событиям
CREATE INDEX ON events (user_id, ts DESC) INCLUDE (id) WHERE ts > now() - interval '7 days';

Миграция существующей таблицы на партиции

Прямого ALTER TABLE ... PARTITION BY для произвольной таблицы в старших ветках нет, поэтому типичная миграция выглядит так:

1. Создаёте новую родительскую таблицу с PARTITION BY и нужными индексами/ограничениями.
2. Создаёте набор секций (пустых).
3. Пакетно переносите данные батчами по ключу секционирования в соответствующие секции. Для минимизации блокировок используйте INSERT ... SELECT малыми порциями.
4. На время миграции можно направлять новые записи в обе таблицы через приложение или временную прослойку и затем выполнить финальную синхронизацию.
5. Меняете имена: старую таблицу переименовываете, новую — в боевое имя. Обновляете внешние ключи и зависимые объекты.

-- Родитель под новую схему
CREATE TABLE events_new (LIKE events INCLUDING ALL) PARTITION BY RANGE (ts);

-- Пример секций
CREATE TABLE events_2025_01 PARTITION OF events_new FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
...

-- Перенос порциями
INSERT INTO events_new
SELECT * FROM events
WHERE ts >= '2025-01-01' AND ts < '2025-02-01' AND id > :last_id
LIMIT 10000;

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

Типичные ошибки и анти-паттерны

• Слишком мелкие секции: тысячи суточных секций за годы без реальной необходимости — тяжёлая номенклатура, долгие планы, медленные DDL.
• Неучтённый ключ сортировки: запросы фильтруют по user_id, а секции — по времени; в результате pruning не помогает, приходится сканировать множество секций.
• Массовые удаления на горячей секции: порождает bloat, тормозит autovacuum. Лучше использовать ротацию секций.
• Уникальные ключи без ключа секционирования: PostgreSQL не даст их создать; планируйте ключи заранее.
• Недокументированный процесс ротации: забыли создать секцию на следующий месяц — потеряли вставки или получили ошибку. Автоматизируйте проверку наличия будущих секций.

Мониторинг и контроль качества

Для партиционированных таблиц добавьте в мониторинг:

• Количество секций и наличие секций на будущие периоды.
• Autovacuum-метрики по активной секции: n_dead_tup, last_vacuum, last_autovacuum.
• Использование индексов на секциях: idx_scan, размер индексов.
• Pruning-фактор: выборочные EXPLAIN на ключевых запросах и число «Pruned partitions».
• Темпы роста и заполнение диска, в том числе на tablespace с архивами.

Чек-лист внедрения

• Определить ключ секционирования, согласованный с основными where/join/aggregate.
• Выбрать тип (Range/List/Hash) и размер секции так, чтобы VACUUM и индексы были управляемыми.
• Спроектировать индексы: минимум для OLTP, BRIN для длинных сканов, частичные индексы там, где это выгодно.
• Продумать ротацию: создание будущих секций, DETACH/DROP старых, архивирование.
• Настроить autovacuum-порог на родителе и при необходимости на активной секции.
• Собрать дашборд мониторинга для pruning, autovacuum и размеров.
• Прописать регламент DDL по секциям: как добавлять индексы, как перестраивать, как обновлять схему.

Итог

Партиционирование в PostgreSQL — это не волшебная палочка, но при корректном выборе ключа и дисциплине обслуживания даёт существенный выигрыш в производительности и управляемости. Декларативные секции избавляют от ручной логики маршрутизации, а локальные индексы позволяют тонко подогнать схему под реальные запросы. Закладывайте политику ретеншна, автоматизируйте создание будущих секций и не забывайте про мониторинг pruning и активности autovacuum — тогда большие объёмы данных перестанут быть проблемой, а станут предсказуемой частью вашего контура.