PostgreSQL partitioning: разбить таблицу по дате

Категория: Postgresql · Язык: SQL · Обновлено:

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

Разбить события по месяцам

SQL
CREATE TABLE events (
    id bigserial,
    created_at timestamptz NOT NULL,
    payload jsonb NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_11 PARTITION OF events
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

CREATE TABLE events_2026_12 PARTITION OF events
    FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

INSERT INTO events (created_at, payload)
VALUES ('2026-11-15T10:00:00Z', '{"type": "login"}');

EXPLAIN (COSTS OFF)
SELECT * FROM events
WHERE created_at >= '2026-11-01' AND created_at < '2026-12-01';

Ключ секционирования должен входить в первичный ключ и уникальные ограничения: PostgreSQL не может проверить уникальность только по id, если строки лежат в разных партициях. В примере используется составной ключ (id, created_at). Индекс на родительской таблице создаёт индексы на каждой партиции, но глобальных индексов нет.

Планировщик отбрасывает партиции, которые не подходят под WHERE (partition pruning). Проверяйте план через EXPLAIN; при функциях от ключа или несовпадении типов pruning может не сработать. Слишком много мелких партиций увеличивает время планирования и число файлов, поэтому выбирайте granularity по нагрузке.

Новую партицию создают заранее по расписанию или через pg_partman. Старую можно отсоединить: ALTER TABLE events DETACH PARTITION events_2026_01; (в PostgreSQL 14+ есть CONCURRENTLY). DETACH не удаляет данные; таблица становится обычной и её можно архивировать отдельно.

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

Документация: table partitioning, ALTER TABLE DETACH.