Партиционирование PostgreSQL по месяцам: настройка

разделение БД postgresql по месяцам

Содержание: показать

Партиционирование PostgreSQL по месяцам: пошаговая настройка секционирования

Быстрый ответ
Партиционирование PostgreSQL по месяцам делается через декларативное партиционирование (PARTITION BY RANGE по дате) плюс расширение pg_partman для автоматизации. Коротко: создаёшь родительскую таблицу с ключом партиционирования, накатываешь pg_partman, задаёшь premake на будущие месяцы, переносишь старые данные батчами через partition_data_proc, проверяешь план запросов на partition pruning. На таблицу в 50-100 млн строк уходит от 2 до 6 часов с учётом переноса данных, без даунтайма продакшна при аккуратной миграции.

1. Диагноз

Поднял таблицу на 40 миллионов строк. VACUUM идёт три часа. Запрос за последний месяц сканирует всю историю с 2019 года. Знакомо?

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

Что получишь на выходе:

  • Родительскую таблицу с автоматически создаваемыми дочерними партициями по месяцам
  • Индексы и constraints, унаследованные каждой партицией
  • Автоматизацию через pg_partman: новые партиции создаются сами, старые сами архивируются или дропаются
  • План миграции существующих данных без блокировки продакшна

Времени на настройку с нуля — час-полтора. На перенос существующих данных — зависит от объёма, считай по 15-20 минут на каждые 5-10 млн строк на среднем железе.

Нужно: PostgreSQL 13+ (лучше 16+, ниже объясню почему), права суперпользователя или CREATE на базу, окно для теста на staging — прогонять миграцию сразу на проде без репетиции не советую никому, кто хочет спать по ночам.

Что будет в статье

  • Причины, почему большая таблица без партиционирования начинает тормозить
  • Рецепт: создание партиционированной таблицы + pg_partman + перенос данных
  • Проверка через EXPLAIN и partition pruning
  • Осложнения — 7 реальных ошибок из продакшна с решениями
  • Альтернативы партиционированию по месяцам
  • Профилактика: мониторинг, бэкап, безопасность, обновление

2. Причины

Разбираем, почему монолитная таблица с историческими данными рано или поздно ломает продакшн.

Причина Почему ломает
Таблица растёт линейно, индексы — нелинейно B-tree индекс на 100 млн строк занимает в разы больше места и глубже по высоте дерева, чем 10 индексов по 10 млн — каждый lookup идёт дольше
VACUUM не успевает за autovacuum_naptime Чем больше таблица, тем дольше проход VACUUM, тем выше риск отставания и раздутия (bloat) — диск съедается мёртвыми строками
Запросы за период сканируют всю историю Без партиционирования планировщик не знает, что 95% строк из прошлых лет ему не нужны — Seq Scan или Index Scan идёт по всей таблице
Удаление старых данных блокирует таблицу DELETE FROM table WHERE date < … на десятках миллионов строк держит блокировку и генерирует горы WAL
Бэкап и восстановление тормозят весь кластер pg_dump одной гигантской таблицы нельзя распараллелить по кускам так же гибко, как набор партиций
Архивация старых данных — боль Без партиций архивировать «данные старше года» — это DELETE с фулскан, а не одна команда DETACH PARTITION
Конкурентные апдейты бьют по одному и тому же файлу Все сессии дерутся за одни и те же страницы буфера — на партициях нагрузка размазывается физически

Ну и запросы у вас — сказала база данных и повисла. Знакомая картина, если таблица росла три года без единого плана.

3. Рецепт

Подготовка

Компонент Требуемая версия
PostgreSQL 13.0+ (рекомендую 16 или 17 — в 16 партиционирование ощутимо ускорили, добавили BEFORE триггеры на партиционированных таблицах)
pg_partman 5.x (актуальна ветка 5.5.x на момент публикации — в ней закрыты CVE по privilege escalation, проверь свежий релиз на github.com/pgpartman/pg_partman перед установкой)
ОС Linux (Ubuntu 22.04/24.04, Debian 12) — без разницы для самого партиционирования, важна только версия PostgreSQL

На момент публикации актуальна ветка PostgreSQL 18 (последний минорный релиз — 18.4), ветка 17 тоже поддерживается и получает патчи (17.10). Перед установкой проверь свежие релизы на postgresql.org/docs.

Порт Протокол Назначение Доступен снаружи?
5432 TCP Основной порт PostgreSQL Нет — только localhost или VPN/бастион, наружу не пробрасывай никогда

Проверь версию и доступы:


SELECT version();
SELECT current_user, has_database_privilege(current_user, current_database(), 'CREATE');

Результат: версия PostgreSQL 13+, права CREATE — true. Если false, тут без администратора кластера не обойдёшься.

Шаг 1. Спроектируй ключ партиционирования

Партиционируем по месяцам — значит ключ это дата события: created_at, event_date, order_date. Важно: ключ партиционирования должен входить в PRIMARY KEY, иначе PostgreSQL не даст создать уникальный индекс.


-- Пример: таблица логов событий
CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    event_date date NOT NULL,
    user_id bigint NOT NULL,
    payload jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);

Результат: таблица создана, но без единой партиции она не примет ни одной строки — вставка сразу упадёт с ошибкой «no partition found for row».

Шаг 2. Создай партиции вручную (для понимания механики)


CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

Результат: две партиции появились в списке дочерних таблиц. Проверь:


SELECT inhrelid::regclass AS partition
FROM pg_inherits
WHERE inhparent = 'events'::regclass;

Вручную создавать партиции на каждый месяц вперёд до конца времён — то ещё удовольствие. Дальше автоматизируем через pg_partman, руками это делать не будешь.

Важно
Не создавай партиции руками и через pg_partman одновременно на одной таблице — pg_partman должен управлять диапазонами целиком, иначе получишь конфликт границ и ошибку при следующем maintenance.

Шаг 3. Установи pg_partman


sudo apt update
sudo apt install postgresql-17-partman

Если пакета нет в репозитории твоего дистрибутива — собери из исходников:


git clone https://github.com/pgpartman/pg_partman.git
cd pg_partman
make
sudo make install

Результат: файлы расширения появились в share/extension вашего PostgreSQL.

Шаг 4. Создай расширение и настрой автоматизацию


CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control => 'event_date',
    p_interval => '1 month',
    p_premake => 3
);

p_premake => 3 значит pg_partman заранее создаст партиции на 3 месяца вперёд. Результат: partman сам создал текущую партицию и три будущих, записал конфиг в partman.part_config.

Проверь конфигурацию:


SELECT * FROM partman.part_config WHERE parent_table = 'public.events';

Шаг 5. Настрой автоматическое обслуживание (background worker или cron)

pg_partman 5.x поставляется с фоновым воркером (BGW). Включи его в postgresql.conf:


shared_preload_libraries = 'pg_partman_bgw'
pg_partman_bgw.interval = 3600
pg_partman_bgw.role = 'partman_maintainer'
pg_partman_bgw.dbname = 'your_database'

Перезапусти PostgreSQL — параметр shared_preload_libraries требует полного рестарта, ребилдом конфига не отделаешься:


sudo systemctl restart postgresql

Альтернатива без BGW — обычный cron с вызовом run_maintenance_proc:


# /etc/cron.d/pg_partman_maintenance
0 * * * * postgres psql -d your_database -c "CALL partman.run_maintenance_proc();"

Результат: раз в час partman проверяет все таблицы под управлением и досоздаёт/убирает партиции по правилам конфига.

Шаг 6. Перенос существующих данных в партиционированную структуру

Тут начинается настоящая работа. Если у тебя таблица существующая, а не с нуля — прямого ALTER TABLE … PARTITION BY в PostgreSQL нет. Придётся переносить данные.

Схема миграции:

%%{init: {
  'theme': 'base',
  'themeVariables': {
    'primaryColor': '#f8fafc',
    'primaryTextColor': '#1e293b',
    'primaryBorderColor': '#94a3b8',
    'lineColor': '#64748b',
    'fontSize': '15px',
    'fontFamily': 'ui-sans-serif, system-ui, sans-serif'
  },
  'flowchart': {'curve': 'linear', 'nodeSpacing': 50, 'rankSpacing': 50}
}}%%
flowchart TD
    A["Старая таблица events_old"] --> B["Новая партиционированная events"]
    B --> C["Перенос данных батчами через partition_data_proc"]
    C --> D["Проверка количества строк"]
    D --> E["Удаление или архивация events_old"]
    style A fill:#f8fafc,stroke:#f97316,stroke-width:2px,color:#9a3412
    style B fill:#f8fafc,stroke:#3b82f6,stroke-width:2px,color:#1e40af
    style C fill:#f8fafc,stroke:#3b82f6,stroke-width:2px,color:#1e40af
    style D fill:#f8fafc,stroke:#3b82f6,stroke-width:2px,color:#1e40af
    style E fill:#f8fafc,stroke:#22c55e,stroke-width:2px,color:#15803d

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


ALTER TABLE events RENAME TO events_old;

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    event_date date NOT NULL,
    user_id bigint NOT NULL,
    payload jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);

SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control => 'event_date',
    p_interval => '1 month',
    p_premake => 3
);

Перенеси данные батчами, чтобы не держать одну гигантскую транзакцию и не забить WAL:


CALL partman.partition_data_proc(
    p_parent_table => 'public.events',
    p_source_table => 'public.events_old',
    p_batch_count => 50,
    p_batch_interval => '1 month'
);

Результат: данные переезжают партиями по месяцам, старая таблица постепенно пустеет. Проверь баланс:


SELECT
    (SELECT count(*) FROM events_old) AS old_count,
    (SELECT count(*) FROM events) AS new_count;

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

Шаг 7. Индексы и constraints

Индексы на партиционированной таблице создаются на родителе — PostgreSQL автоматически прокидывает их на все партиции, включая будущие:


CREATE INDEX idx_events_user_id ON events (user_id);
CREATE INDEX idx_events_event_date ON events (event_date);

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

Архитектура решения

%%{init: {
  'theme': 'base',
  'themeVariables': {
    'primaryColor': '#f8fafc',
    'primaryTextColor': '#1e293b',
    'primaryBorderColor': '#94a3b8',
    'lineColor': '#64748b',
    'fontSize': '15px',
    'fontFamily': 'ui-sans-serif, system-ui, sans-serif'
  },
  'flowchart': {'curve': 'linear', 'nodeSpacing': 50, 'rankSpacing': 50}
}}%%
flowchart TD
    A["Клиентский INSERT"] --> B["Родительская таблица events"]
    B --> C["Партиция events_2026_06"]
    B --> D["Партиция events_2026_07"]
    B --> E["Партиция events_2026_08"]
    F["pg_partman BGW раз в час"] --> B
    F --> G["Создание новых партиций"]
    F --> H["Удаление устаревших партиций"]
    style A fill:#f8fafc,stroke:#3b82f6,stroke-width:2px,color:#1e40af
    style B fill:#f8fafc,stroke:#3b82f6,stroke-width:2px,color:#1e40af
    style C fill:#f8fafc,stroke:#22c55e,stroke-width:2px,color:#15803d
    style D fill:#f8fafc,stroke:#22c55e,stroke-width:2px,color:#15803d
    style E fill:#f8fafc,stroke:#22c55e,stroke-width:2px,color:#15803d
    style F fill:#f8fafc,stroke:#f97316,stroke-width:2px,color:#9a3412
    style G fill:#f8fafc,stroke:#22c55e,stroke-width:2px,color:#15803d
    style H fill:#f8fafc,stroke:#ef4444,stroke-width:2px,color:#b91c1c

4. Проверка

Проверь, что партиционирование реально работает, а не просто существует для галочки. Три вещи: partition pruning в плане запроса, список активных партиций, состояние обслуживания.


EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE event_date >= '2026-07-01' AND event_date < '2026-08-01';

Результат: в плане должно быть «Append» только по одной партиции (events_2026_07), остальные должны отсутствовать в выводе — это и есть partition pruning. Если в плане мелькают все партиции подряд — значит planning constraint exclusion не сработал, смотри раздел осложнений ниже.


-- список всех партиций и их размер
SELECT
    child.relname AS partition_name,
    pg_size_pretty(pg_relation_size(child.oid)) AS size
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'events'
ORDER BY child.relname;


-- лог последнего обслуживания pg_partman
SELECT parent_table, last_updated, undo_in_progress
FROM partman.part_config
WHERE parent_table = 'public.events';

Проверь, что фоновый воркер живой:


sudo -u postgres psql -c "SELECT * FROM pg_stat_activity WHERE backend_type = 'pg_partman_bgw';"

5. Осложнения

Ошибка Причина Решение
no partition of relation «events» found for row Вставляемая дата выходит за диапазон существующих партиций (партиция на этот месяц ещё не создана) Увеличь p_premake или запусти run_maintenance вручную 

CALL partman.run_maintenance_proc(p_parent_table => 'public.events');
Ошибка Причина Решение
Планировщик сканирует все партиции вместо одной constraint_exclusion выключен или запрос использует функцию поверх колонки (например date_trunc(event_date)) Убедись что constraint_exclusion = partition (по умолчанию так), перепиши условие на прямое сравнение с датой 

SHOW constraint_exclusion;
-- должно быть: partition
Ошибка Причина Решение
duplicate key value violates unique constraint при вставке PRIMARY KEY не включает ключ партиционирования — PostgreSQL требует, чтобы уникальный индекс покрывал колонку RANGE Пересоздай PK как составной: PRIMARY KEY (id, event_date)
ERROR: cannot attach foreign key constraint on partitioned table Внешние ключи на партиционированные таблицы поддерживаются только с версии PostgreSQL 12 и с ограничениями по составным ключам Используй составной FK, включающий колонку партиционирования, либо проверяй целостность на уровне приложения
pg_partman_bgw не запускается после restart Параметр shared_preload_libraries не применился или роль partman_maintainer не создана Проверь postgresql.conf и создай роль: CREATE ROLE partman_maintainer LOGIN;
Миграция partition_data_proc виснет на несколько часов Слишком большой p_batch_count при отсутствии индекса на колонке партиционирования в исходной таблице Создай индекс на event_date в events_old до запуска миграции, уменьши размер батча
Диск неожиданно заполнился во время миграции Данные временно дублируются — старая таблица ещё не очищена, новая уже растёт Планируй запас диска х2 от объёма таблицы на время миграции, чисти events_old партиями сразу после переноса

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

6. Альтернативы

Партиционирование по месяцам — не единственный вариант, но для логов, событий, метрик и заказов с явной временной осью это чаще всего оптимальный выбор.

  • Партиционирование по неделям или дням — даёт более гранулярный контроль, но плодит партиции быстрее: за год набежит 52 или 365 таблиц вместо 12. Подходит для очень высоконагруженных систем с телеметрией.
  • Партиционирование по HASH — если ключевая ось не время, а, например, tenant_id в multi-tenant системе. Не решает проблему архивации старых данных, зато размазывает нагрузку по tenants.
  • Партиционирование по LIST — когда данные естественно делятся по категориям (регион, статус), а не по диапазону дат.
  • TimescaleDB — расширение поверх PostgreSQL, специализированное под time-series, с автоматическим сжатием старых чанков. Избыточно, если тебе нужно просто резать таблицу по месяцам без специфики метрик.
  • Архивация в отдельную БД или Parquet-файлы — вариант для данных, которые почти никогда не читаются, но юридически обязаны храниться. Партиционирование внутри PostgreSQL проще в поддержке, если данные иногда всё же нужны в запросах.

Для типичного случая — таблица логов/событий/заказов с постоянным ростом и запросами преимущественно за последние недели — партиционирование по месяцам через pg_partman остаётся балансом между простотой и контролем.

7. Профилактика

Мониторинг


-- партиции без свежих данных дольше 40 дней — сигнал что maintenance не отработал
SELECT parent_table, last_updated
FROM partman.part_config
WHERE last_updated < now() - interval '2 days';

Заведи алерт в Zabbix или Prometheus (postgres_exporter) на размер таблицы events_old и на возраст самой свежей партиции.

Резервное копирование

  • Что бэкапить: конфигурацию partman.part_config отдельно от данных — при восстановлении на новый сервер конфиг важно восстановить первым
  • Как часто: полный pg_dump/pg_basebackup по расписанию, для больших партиционированных баз — WAL-архивирование через pgBackRest или Barman для point-in-time recovery
  • Где хранить: минимум в двух местах — локально и в объектном хранилище (S3-совместимое), 3-2-1 никто не отменял
  • Как восстановить: pg_restore для логического дампа, либо разворачивание из базового бэкапа плюс накат WAL до нужной точки — обязательно протестируй восстановление на staging хотя бы раз в квартал

Безопасность

  • pg_partman с версии 5.5 не требует superuser — создай отдельную роль partman_maintainer с точечными правами вместо суперпользователя
  • Ограничь доступ к порту 5432 через firewall (ufw allow from конкретных IP, не 0.0.0.0/0)
  • fail2ban на попытки подбора пароля в pg_hba.conf при methode md5/scram
  • Отдельный пользователь БД для приложения с правами только на нужные таблицы, не на весь кластер
  • Row Level Security, если несколько ролей работают с одной и той же таблицей конфигурации partman — актуально в multi-tenant сценариях

Обновление

  • Как обновлять безопасно: ALTER EXTENSION pg_partman UPDATE TO ‘x.y.z’ — сначала на staging, читай CHANGELOG на предмет breaking changes между мажорными версиями (4.x → 5.x менял механику фонового обслуживания)
  • Что проверить до обновления: текущую версию расширения (SELECT extversion FROM pg_extension WHERE extname = ‘pg_partman’), совместимость с версией PostgreSQL
  • Как откатиться: держи бэкап конфигурации part_config перед обновлением, у pg_partman нет автоматического downgrade — откат только восстановлением из бэкапа

8. FAQ

Почему партиционирование PostgreSQL не работает после настройки?

Чаще всего дело в constraint_exclusion или в том, что запрос использует функцию поверх колонки партиционирования вместо прямого сравнения. Проверь EXPLAIN — если планировщик сканирует все партиции вместо одной, перепиши условие WHERE на прямое сравнение с датой без обёрток вроде date_trunc().

Как проверить что секционирование работает правильно?

Запусти EXPLAIN (ANALYZE, BUFFERS) на типовой запрос за конкретный месяц. В плане должна быть только одна партиция в Append, а не все сразу. Дополнительно сверь размер каждой партиции через pg_relation_size — они должны расти постепенно, а не оставаться пустыми.

Что делать если partition_data_proc завис при переносе данных?

Проверь, есть ли индекс на колонке партиционирования в исходной таблице — без него каждый батч идёт полным сканом. Уменьши p_batch_count, если сервер под нагрузкой, и следи за pg_stat_activity на предмет блокировок.

Чем pg_partman отличается от встроенного декларативного партиционирования?

Декларативное партиционирование — это механизм самого PostgreSQL для создания и хранения партиций. pg_partman — надстройка над ним, которая автоматизирует рутину: создание новых партиций по расписанию, удаление устаревших, перенос данных из немонолитной таблицы. Без pg_partman всё то же самое придётся делать вручную или писать свои cron-скрипты.

Сколько партиций можно создать в PostgreSQL без потери производительности?

Формальных ограничений нет, но на практике при количестве партиций больше нескольких тысяч планировщик начинает тратить заметное время на само планирование запроса. Для месячного партиционирования это не проблема — за 10 лет набежит 120 партиций, комфортный объём. Для дневного партиционирования на долгий срок стоит заранее продумать retention policy и дроп старых партиций.

9. Прогноз

Настроили партиционирование по месяцам, накатили pg_partman на автомате, перенесли историю без блокировки продакшна. Теперь запросы за текущий месяц бьют в одну партицию вместо полного скана всей истории, VACUUM обрабатывает партиции по отдельности и укладывается в разумное время, а архивация старых данных — это DETACH PARTITION вместо часового DELETE с блокировками.

Дальше — дело техники: подключи мониторинг на возраст партиций, протестируй восстановление из бэкапа хотя бы раз, и раз в квартал сверяйся с CHANGELOG pg_partman на новые версии. Если после настройки что-то не заработало как надо — пиши в комментарии, разберёмся.

Полезные ссылки:
Официальная документация PostgreSQL по партиционированию
pg_partman на GitHub

Андрей А.
Author: Андрей А.

Руководитель ИТ / Кризис-менеджер 25 лет в IT: от инженера в МегаФоне до руководителя отдела. Знаю, как выглядит бардак: нестабильные сети, устаревшая инфраструктура, конфликты в команде, раздутые сроки. Помогаю бизнесу выходить из кризиса: навожу порядок в легаси, стабилизирую то, что разваливается, выстраиваю прогнозируемые процессы. Не раз возвращал к жизни ИТ-структуры — знаю цену хаосу. 📍 Ищу проект для полной реорганизации / стабилизации. 📬 Telegram: @over_dude ✉️ mail@it-apteka.com

Оставайтесь на связи

Рецепты от IT-боли. Без воды, без рекламы, без маркетинговой шелухи.

Подписаться на IT-Аптеку →

Мы ВКонтакте

IT-Аптека — советы, новости и помощь рядом.

Вступить в группу ВКонтакте →
Поделитесь:

Связанные записи

Оставьте комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Прокрутить вверх