Partitioning больших таблиц в PostgreSQL

Когда таблица растёт до сотен гигабайт и запросы тормозят, разумное разбиение по партициям снижает время отклика и облегчает обслуживание. Я покажу шаги выбора ключа, создание declarative partitions, миграцию данных и типичные ошибки на примерах с реальными командами и цифрами.

Когда таблица растёт до сотен гигабайт и запросы тормозят, разумное разбиение по партициям снижает время отклика и облегчает обслуживание. Я покажу шаги выбора ключа, создание declarative partitions, миграцию данных и типичные ошибки на примерах с реальными командами и цифрами.

Когда нужен partitioning?

Разделение таблицы на партиции становится полезным, когда сопровождаются хотя бы две из трёх проблем: медленные сканирования из-за больших объёмов данных, длительное обслуживание (VACUUM/ANALYZE, reindex) и высокий ввод/вывод при бэкапах или архивации. Конкретные триггеры:

• таблица > 100 ГБ и рост > 10 ГБ в неделю;
• планирование запросов показывает частые seq scan по диапазону дат в последних 30 днях;
• еженедельный VACUUM FULL или reindex занимает > 1 час на таблицу;
• удаление старых данных — регулярная операция (например, rollup логов каждые 30–90 дней).

В крупных проектах 2025–2026 годов практика такова: если ваша OLTP-таблица активна и достигает 200–500 млн строк или 200+ ГБ, partitioning практически всегда даёт эффект. Но есть и случаи, когда он не нужен: если вы делаете редкие аналитические выборки по всей таблице и не можете легко фильтровать по ключу партиционирования.

Шаг 1: выбор ключа

Выбор ключа — самый важный этап. От него зависит эффективность pruning, возможность чистки старых данных и нагрузка на планировщик. Практика показывает три основных подхода: RANGE по дате, LIST по сервису/шарду и HASH для равномерного распределения. Приведу конкретные рекомендации и расчёты.

1. RANGE по дате — когда применять

Подходит для временных рядов, логов и событий. Выгоден, если 70–90% запросов фильтруют по дате или по времени. Пример: таблица access_logs, размер 1 ТБ, средний ежедневный прирост 50 ГБ. Разбиение по месяцу или по неделе уменьшит объем сканирования и упростит удаление старых данных.

CREATE TABLE access_logs (
  id bigint PRIMARY KEY,
  ts timestamptz NOT NULL,
  user_id bigint,
  event jsonb
) PARTITION BY RANGE (ts);

Рекомендации по интервалам: до 10 ГБ/день — партиции по месяцу, 10–100 ГБ/день — партиции по неделе, >100 ГБ/день — партиции по дню. Для 50 ГБ/день (пример выше) лучший интервал — неделя: ~350 ГБ на партицию.

2. LIST по категориальному полю

Используйте, когда у вас небольшое число значений, и запросы часто фильтруют по этому полю. Например, multi-tenant система с 20 инстансами: LIST по tenant_id позволит локализовать запросы и обслуживание.

CREATE TABLE events (
  id serial,
  tenant_id int NOT NULL,
  payload jsonb
) PARTITION BY LIST (tenant_id);

Ограничение: если уникальных значений > 1000, LIST становится неудобным. Тогда лучше HASH или комбинированный ключ.

3. HASH — для равномерного распределения

HASH-партиционирование полезно при высоком разнообразии ключей и при цели равномерно распределить I/O. Пример: таблица sessions с равномерно распределёнными user_id. Недостаток — сложнее удалять по диапазону времени.

CREATE TABLE sessions (
  id bigint,
  user_id bigint,
  created_at timestamptz
) PARTITION BY HASH (user_id);

Практический лимит: используйте 8–256 партиций для HASH. Эксперименты 2025 года на PostgreSQL 15 показали, что >512 партиций увеличивает время планирования на 5–20% в зависимости от сложности запроса.

Комбинированные схемы (composite key)

Иногда оптимально комбинировать RANGE и HASH: RANGE по месяцу + HASH внутри месяца для большого потока данных. Пример разбиения для 2026 года: 12 партиций RANGE (по месяцам) и внутри каждой — 16 HASH-партиций по user_id, всего 192 партиции. Это снижает contention на insert и ускоряет pruning.

Шаг 2: declarative partitions

Declarative partitioning поддерживается в PostgreSQL с версии 10, а стабильно используется в продакшене на версиях 12–16. Синтаксис простой: PARTITION BY RANGE/LIST/HASH. Я приведу шаблоны создания и примеры для 2025–2026 практик.

Создание основной таблицы и партиций

-- основная таблица
CREATE TABLE metrics (
  id bigserial,
  metric_date date NOT NULL,
  value double precision,
  tags jsonb
) PARTITION BY RANGE (metric_date);

-- партиция за январь 2026
CREATE TABLE metrics_2026_01 PARTITION OF metrics
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

Автоматическое создание партиций можно делать скриптом в cron/pg_cron: создаём партиции на ближайшие N периодов (обычно N=6–12) заранее, чтобы избежать появляющихся DDL в пиковую нагрузку.

Автоматизация создания партиций — пример скрипта

#!/usr/bin/env bash
# create_monthly_partitions.sh — запускать раз в неделю, создает партиции на 12 месяцев вперед
DB=production
SCHEMA=public
TABLE=metrics
psql "$DB" -At -c "\
DO $$
DECLARE
  start_date date := date_trunc('month', current_date);
  i int;
BEGIN
  FOR i IN 0..11 LOOP
    EXECUTE format('CREATE TABLE IF NOT EXISTS %I.%I_%s PARTITION OF %I.%I FOR VALUES FROM (%L) TO (%L)',
      '$SCHEMA', '$TABLE', to_char(start_date + (i || '' month'')::interval, 'YYYY_MM'),
      '$SCHEMA', '$TABLE', to_char(start_date + i * interval '1 month', 'YYYY-MM-01'), to_char(start_date + (i+1) * interval '1 month', 'YYYY-MM-01'));
  END LOOP;
END$$;"

Этот подход гарантирует, что при появлении новых вставок не будет ошибки «no partition found», и вы избегаете DDL в пиковое время, если скрипт запускается в низкой нагрузке (например, 03:00 по локальному времени).

Ограничения declarative partitions

• нет глобального индекса (по состоянию на 2026); индексы создаются на каждой партиции отдельно;
• FOREIGN KEY между родительской и дочерней таблицами имеет ограничения: FK к партиционированной таблице работает, но конфигурация и производительность остаются чувствительными к числу партиций;
• DDL-операции (CREATE TABLE PARTITION, ATTACH) могут занимать десятки секунд при большом количестве партиций и высоком concurrent workload — планируйте окна обслуживания.

Шаг 3: миграция существующих данных

Миграция — самый рискованный этап. Я опишу несколько подходов с оценкой времени и даю пример бездоуктативной репликации и пошаговой миграции с коротким даунтаймом.

Вариант A — pg_dump/pg_restore (прост, но с даунтаймом)

Подойдёт для баз < 1 ТБ или когда можно допустить даунтайм. Пример оценки: дамп 200 ГБ на NVMe диске — 12–20 минут на чтение и 20–40 минут на восстановление с параллелизмом 8 (pg_restore -j 8). Общий даунтайм для переключения — 5–15 минут на фэйзовку и проверку.

pg_dump -Fc -j 8 -d production -t public.big_table > big_table.dump
-- на новом сервере:
pg_restore -d production -j 8 big_table.dump

После восстановления на новом кластере создаёте таблицу как partitioned, импортируете данные и переключаете DNS/pgpool/HAProxy.

Вариант B — логическая репликация (минимальный даунтайм)

Подходит для таблиц > 500 ГБ при условии, что у вас PostgreSQL >= 10. Создаёте реплику и реорганизуете таблицу на приемной стороне. Этапы и пример:

1. на исходном сервере: CREATE PUBLICATION pub FOR TABLE big_table;
2. на целевом сервере: подготовить partitioned таблицу и пустые партиции (для скорости — можно автоматом);
3. CREATE SUBSCRIPTION sub CONNECTION 'host=src user=replicator dbname=production' PUBLICATION pub;
4. подождать catchup: мониторьте pg_stat_subscription — на 1 ТБ и 500 Мбит/s канал время передачи может быть 4–8 часов;
5. в краткое окно (1–2 минуты) приостанавливаете записи на источник, даёте подписке закрыть транзакции, переключаете записи на новую БД.

-- мониторинг прогресса
SELECT pid, relid::regclass, received_lsn, last_msg_send_time, last_msg_receipt_time
FROM pg_stat_subscription;

Если таблица очень большая, можно делать предварительный bulk-load: копировать старые данные пакетами INSERT ... SELECT по диапазонам (chunking) и только затем включать реплику для текущих изменений.

Вариант C — поэтапная миграция с ATTACH (online, контролируемая)

Этот метод минимизирует даунтайм: создаёте новую partitioned таблицу, переносите исторические партиции как физические (через CREATE TABLE ... PARTITION OF + ALTER TABLE ... ATTACH PARTITION) или логически через INSERT-SELECT по чанкам.

-- шаги (пример для 500M строк, 200 ГБ)
-- 1. создать partitioned структуру
CREATE TABLE new_tbl (...) PARTITION BY RANGE (ts);
-- 2. для каждого диапазона создать временную таблицу и переместить данные:
CREATE TABLE tmp_2025_01 AS SELECT * FROM old_tbl WHERE ts >= '2025-01-01' AND ts < '2025-02-01';
-- 3. убедиться, что индексы и constraints совпадают, затем:
ALTER TABLE tmp_2025_01 ATTACH PARTITION new_tbl FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

ATTACH PARTITION выполняется быстро (обычно < 1 секунда), если данные уже находятся в таблице-партиции и нет конфликтов с constraint exclusion. Реальная работа — создание tmp_2025_01 и загрузка в неё данных. Для 100M строк (~40 ГБ) INSERT ... SELECT с параллельными транзакциями в 10 потоков даёт 10–30 минут в зависимости от CPU и I/O.

Советы по миграции

• всегда делайте dry-run на staging с тем же набором индексов и настроек autovacuum;
• на проде: создавайте партиции заранее, запускайте нагрузки на чтение в периоды 02:00–05:00; в рабочие часы избегайте DDL;
• мониторьте pg_stat_activity и iostat; при spike дискового I/O уменьшайте параллелизм костомных INSERT;
• делайте контрольную сумму результата (row counts, hash) между старой и новой таблицами перед переключением.

Шаг 4: обслуживание и мониторинг

Партиционирование меняет правила обслуживания. Важные задачи — мониторить autovacuum, регулярно добавлять и удалять партиции, планировать реплики и бэкапы. Привожу конкретные метрики и команды.

Мониторинг и метрики

• количество партиций: держите в пределах 200–2000 в зависимости от версии и нагрузки; на PG15 практический порог — ~1000 партиций для OLTP без ощутимого замедления планирования;
• avg planning_time и execution_time из pg_stat_statements: если planning_time увеличился > 20% после добавления партиций — проверьте pruning и индексы;
• autovacuum activity: время последнего vacuum/last_autovacuum в pg_stat_all_tables для каждой партиции; если партиции активно мутируют, увеличьте autovacuum_vacuum_scale_factor для меньше нагруженных партиций.

Удаление старых партиций

Для таблиц по времени удалять партиции проще всего через DROP PARTITION или просто DROP TABLE для партиции. Это гораздо быстрее, чем DELETE по условию. Пример:

-- удалить партицию 2024-01
ALTER TABLE metrics DETACH PARTITION metrics_2024_01;
DROP TABLE metrics_2024_01;
-- или сразу
DROP TABLE IF EXISTS metrics_2024_01;

Удаление таблицы на 300 ГБ — обычно занимает секунды-минуты (освобождение страниц фоновой задачей), вместо нескольких часов при DELETE.

Шаг 5: тестирование и откат

Перед вводом в продакшн нужно протестировать всё: планирование запросов, latency под нагрузкой, откатные сценарии. Я даю чеклист и пример плана отката.

Чеклист тестирования

• нагрузочный тест с JMeter/pgbench: повышение нагрузки до 1.5x пикового; проверьте p95/ p99 latency;
• проверка планов запросов: EXPLAIN (ANALYZE, BUFFERS) для 20 самых тяжёлых запросов; ожидаемый выигрыш в I/O должен быть >30% для диапазонных запросов;
• тесты параллельных DDL: симулируйте создание партиции при росте нагрузки (время CREATE TABLE/ATTACH не должно превышать 30 секунд в контролируемой среде);
• функциональные тесты: check constraints, foreign keys, triggers — работоспособность не должна поменяться.

План отката (rollback)

Если после переключения возникают проблемы, откат часто быстрее выполнить через переключение endpoint/pgpool на старый реплицированный сервер. Варианты:

1. имейте готовую точную реплику старой базы до переключения и проверяйте синхронизацию перед cutover;
2. держите старую таблицу нетронутой минимум 24–72 часа, чтобы иметь возможность откатиться коммутируя DNS/endpoint;
3. в крайнем случае — использовать logical replication обратно, но это сложнее и требует тщательной проверки целостности.

Какие pitfalls?

Ниже перечислены наиболее частые ошибки, с которыми я сталкивался в проектах 2024–2026 годов, и как их избежать с конкретными параметрами.

1. Слишком много партиций

Проблема: создание >5000 партиций ведёт к большим накладным расходам на планирование. Рекомендация: держите количество партиций в пределах 200–2000 и используйте composite схемы (RANGE+HASH) при больших объёмах. Измерение: на PG14 тест с 5000 партиций увеличил median planning_time с 2 ms до 30–120 ms в зависимости от запроса.

2. Неправильный ключ партиционирования

Если ключ не совпадает с фильтрами запросов, pruning не сработает и вы потеряете выгоду. Решение: проанализировать топ-100 запросов через pg_stat_statements и выбрать ключ, который участвует в 70% выборок по WHERE.

3. Отсутствие индексов на партициях

Ожидание глобального индекса приводит к ошибкам. Создавайте индексы на каждой партиции автоматически с помощью шаблонов или DDL-скриптов при создании партиций: время создания индекса на партицию 50 ГБ с parallel_workers=4 — 3–10 минут.

4. DDL в пиковое время

Создание или attach партиции под нагрузкой может вызвать блокировки. Планируйте DDL в окнах 01:00–05:00 локального времени и делайте CREATE TABLE IF NOT EXISTS заранее.

Как работают индексы?

Индексы на партиционированных таблицах создаются отдельно на каждой дочерней партиции. Это ключевое отличие от некоторых СУБД с глобальными индексами. Разберёмся с последствиями и оптимизациями.

Локальные индексы

При выполнении запроса с условием, которое покрывают индексы, PostgreSQL применит индексы на релевантных партициях. Пример: запрос по диапазону дат и user_id — планировщик делает partition pruning по дате и затем использует индекс по user_id в каждой оставшейся партиции.

Нет глобального индекса (статус на 2026)

На момент 2026 в большинстве мейнстримных сборок PostgreSQL глобальные индексы для declarative partitioning официально не реализованы. Это значит, что операция поиска по колонке без ограничения по партиционирующему ключу может требовать проверки индексов в каждой партиции. Практическое следствие: если запросы часто фильтруют только по non-partition key, подумайте о следующих приёмах:

• создание частичных индексов на партициях с условием по датам/статусу;
• создание materialized view с агрегатами на уровне партиций и обновление их периодически (например, каждые 5–15 минут);
• использование вспомогательных таблиц-индексов (денормализация) для ускорения глобальных поисков.

Параллельное создание индексов и reindex

Для партиций с объёмом 50–300 ГБ используйте concurrent indexing и parallel_workers: CREATE INDEX CONCURRENTLY ... WITH (parallel_workers = 4). Время создания индекса зависит от CPU и I/O; ориентировочные цифры: 50 ГБ — 10–30 минут при SSD и 8 vCPU, 200 ГБ — 40–120 минут.

Поддержание статистики

ANALYZE нужно запускать для каждой новой партиции. Если у вас большое число партиций, создайте cron-джоб, который делает ANALYZE на новых партициях в течение 1–2 часов после их появления. Если статистика устарела, планы могут быть неэффективны и pruning хуже снижает нагрузку.

Дополнительные материалы по оптимизации запросов и устройству PostgreSQL доступны в статьях на сайте: PostgreSQL: оптимизация и администрирование и DevOps: эксплуатация баз данных.

Частые вопросы

как выбрать интервал партиционирования?

Выбирайте интервал опираясь на скорость роста таблицы и шаблоны запросов. Если данные растут 5–10 ГБ в день — используйте месячные партиции; при 10–100 ГБ в день — недельные; при >100 ГБ в день — дневные. Проверьте топ-100 запросов через pg_stat_statements: если 70% запросов фильтруют по дате, RANGE по времени даст максимальный выигрыш. Учтите, что чем меньше интервал — тем больше число партиций и накладные расходы на планирование. Практический компромисс: для 50 ГБ/день выбрать недельные партиции (≈350 ГБ/партиция).

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

Создавать индексы нужно на каждой партиции; используйте скрипты или шаблоны для автоматизации. Если у вас 12 партиций в год, создавайте индекс после создания партиции и запускайте его CONCURRENTLY, чтобы не блокировать записи. Для больших партиций (50–200 ГБ) указывайте parallel_workers=4–8. Для сокращения объёма индексов применяйте частичные индексы (WHERE) если запросы используют такие фильтры.

почему запросы не используют partition pruning?

Причины: условия в WHERE не выражены явно в виде констант или оптимизируемых выражений, функции и типы не сопоставляются, либо planner не может вычислить диапазон во время планирования. Проверьте EXPLAIN и убедитесь, что условие над партиционной колонкой не обёрнуто в функции (например, date_trunc) — лучше писать ts >= '2026-03-01' AND ts < '2026-04-01'. Также убедитесь, что statistics_level не занижен и что новые партиции были проанализированы (ANALYZE).

сколько партиций безопасно иметь?

Безопасный диапазон — 200–2000 партиций для большинства версий PostgreSQL в 2025–2026 годах. Конкретный предел зависит от схемы запросов и версии СУБД: чем новее версия (15–16), тем лучше масштабирование, но всё равно чрезмерное число партиций (тысячи) увеличивает время планирования и объём системных каталогов. Перед масштабированием выше 2000 партиций обязательно тестируйте нагрузку на staging.

чем лучше логическая репликация при миграции?

Логическая репликация минимизирует даунтайм: вы синхронизируете изменения в реальном времени и делаете cutover при небольшой задержке. Для таблиц сотни гигабайт это обычно лучший путь, если инфраструктура позволяет. Минусы: надо выделить ресурсы для реплики, убедиться в совместимости схем и обработать DDL-операции аккуратно. При пропуске больших исторических вставок комбинируют bulk-load + logical replication для текущих данных.

Если нужно, подготовлю подробный plan миграции вашего конкретного кейса: укажите объём таблицы, pattern запросов и версию PostgreSQL — составлю скрипты и оценю длительность и риски.