Какие индексы и партиционирование использовать для таблицы заказов с сотнями миллионов записей

Краткое описание задачи и ожидаемые эффекты

При работе с таблицей заказов на сотни миллионов записей ключевая цель — обеспечить приемлемое время отклика для бизнес-запросов (поиск по id, выборки по датам, агрегации по статусам и по пользователю) без неизбежного роста операций ввода-вывода. Неправильные индексы и неадекватное партиционирование приведут к задержкам при вставках, блокировкам и дорогому обслуживанию (vacuum/reindex).

В этом руководстве мы пройдём последовательно: подготовка окружения, анализ запросов, выбор и проектирование индексов, стратегия партиционирования, план миграции без простоя, контрольные точки, нагрузочное тестирование, запуск и пострелизный мониторинг. Каждая часть содержит практические инструкции и конкретные шаги, которые можно применить в PostgreSQL или аналогичных СУБД.

Что подготовить перед изменениями (не пропускайте эти шаги)

Подготовка — это минимум, который избавит от большинства проблем при изменении структуры большой таблицы. 1) Снимите полные бэкапы (физические и/или логические). 2) Проверьте версию СУБД и примените последние патчи безопасности. 3) Убедитесь, что у вас есть доступ к дополнительному дисковому пространству для временных файлов индекса и репликации.

Кроме того, подготовьте метрики и инструменты: включите pg_stat_statements, сохраните текущие планы запросов (EXPLAIN ANALYZE для критичных запросов), зафиксируйте размеры таблиц и индексов, и экспортируйте список всех текущих индексов. Рекомендуется иметь тестовую копию данных — либо снапшот, либо выборочную репликацию на стенд для нагрузочного тестирования.

• Полный бэкап и проверка восстановления
• Список критичных запросов и их планы
• Набор метрик: latency, TPS, io, pg_stat_activity
• Тестовая среда с репликой данных

Анализ паттернов доступа: ключ к правильным индексам

Прежде чем добавлять индексы, ответьте на вопросы: какие запросы самые частые, какие — самые тяжёлые, какие поля участвуют в WHERE, JOIN, ORDER BY, GROUP BY. Используйте pg_stat_statements или аналитику приложения, чтобы ранжировать запросы по частоте и суммарному времени. Важно выделить 2–3 главных сценария: чтение по id, выборки по диапазону дат и агрегаты по статусам/региону.

Далее формализуйте шаблоны запросов и оцените селективность колонок (примерно: одна колонка даёт селективность 1:10, другая 1:1000). На основе этих данных принимайте решение: 1) индекс по первичному ключу обязателен; 2) для частых фильтров с высокой селективностью — отдельный индекс; 3) для запросов, которые используют несколько колонок в фильтре, рассматривайте комбинированные (multicolumn) или покрывающие (covering) индексы.

Выбор типов индексов и когда их применять

Тип индекса выбирается по типу запросов: B-tree — универсальный для =, <, >, ORDER BY; BRIN — подходит для очень больших таблиц с физически локализованными данными (например, по дате) и экономит место; hash — редко нужен, только для очень специфичных хеш-поисков (в PostgreSQL имеет ограничения). Рассмотрите также expression-индексы для вычисляемых полей и partial-индексы, если фильтруемый набор маленький и стабилен (например, active = true).

Практические рекомендации: 1) Оставляйте B-tree для PK и наиболее частых фильтров; 2) Используйте BRIN для больших диапазонных выборок по дате, если строки добавляются примерно упорядоченно; 3) Создавайте частичные индексы вместо полного индекса, когда только малая часть строк участвует в запросах (это уменьшит размер и ускорит обновления). Не создавайте индексы «про запас» — каждый индекс добавляет накладные расходы на вставки и удаление.

• B-tree — основной индекс для ключей и сортировок
• BRIN — экономия места при локализованных по времени данных
• Partial/Expression — уменьшение размера индекса для специфичных фильтров

Стратегии партиционирования для таблицы заказов

Партиционирование позволяет разбить одну логическую таблицу на набор более мелких физически независимых сегментов. Варианты: 1) RANGE (обычно по дате заказа) — удобен для историзации и быстрого удаления старых данных; 2) LIST (по региону/магазину) — когда выборки по конкретным значениям часты; 3) HASH — полезен для равномерного распределения записи при попытке уменьшить контеншн на вставках.

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

• RANGE по дате — упрощённая архивация и быстрый DROP/ATTACH
• LIST по региону — быстрые выборки по географии
• HASH — равномерное распределение для параллельных вставок

Пошаговый план миграции (создание индексов и партиций без простоя)

Миграция должна минимизировать блокировки и нагрузку. Универсальная последовательность: 1) На тестовой базе создать партиции и индексы, прогнать тесты; 2) На проде подготовить пустые партиции (ATTACH ещё пустых сегментов допустим), 3) Использовать CREATE INDEX CONCURRENTLY для индексов на больших таблицах, чтобы не блокировать INSERT/SELECT; 4) По возможности применять pg_repack или logical replication для реорганизации данных без долгих блокировок.

Конкретные шаги с минимальными потерями: 1) Создайте новую партицированную структуру (структуру таблиц и схему партиций). 2) Для существующих данных — выполняйте миграцию по партийно: копируйте данные партиями (например, по месяцам) с CHECKPOINT и валидацией каждой партии. 3) После копирования — переключите записи (можно сделать RENAMING: временная таблица -> основная). 4) Удалите старые индексы только после проверки статистики и плана запросов.

Контрольные точки (список обязательных проверок перед каждым этапом)

Контрольные точки помогают не пропустить критичные моменты. Всегда проверяйте: 1) Наличие актуального бэкапа и тест восстановления; 2) Нагрузочные метрики: IOPS, latency, CPU; 3) Достаточность места для временных индексов (index build); 4) Артефакты плана выполнения (EXPLAIN) для сравнения до/после.

Пошаговый чек-лист перед запуском на проде: 1) Тест миграции пройден на стенде; 2) Созданы и проверены индексы на маленьком куске данных; 3) Настроены алерты на рост времени выполнения и задержки ввода-вывода; 4) План отката — как быстро вернуть прежнюю структуру (например, переименование таблиц или переключение реплики).

• Проверка бэкапа и восстановления
• EXPLAIN/ANALYZE основных запросов
• Наличие свободного места для временных индексов
• Алерты и план отката

Нагрузочное тестирование и валидация индексов

Перед переключением на новую схему прогоните сценарии, приближённые к реальным: массовые вставки, обновления статусов, выборки диапазонов, агрегации. Используйте реальные распределения по времени и ключам — это позволит увидеть hotspots и степень использования каждого индекса. Сравните latency и throughput до и после, фиксируя планы выполнения.

Используйте инструменты: pgbench для массовых операций, JMeter/Locust для API-слоя, и собирайте метрики из pg_stat_activity и pg_stat_io. Особое внимание уделите конкурентным вставкам: индексы могут увеличивать время INSERT из-за обновлений структуры индекса; проверьте fillfactor, autovacuum и maintenance_work_mem, чтобы найти баланс между скоростью вставки и эффективностью чтения.

Запуск в продакшн и что проверять после старта

При запуске сначала включите постепенное переключение трафика (canary/blue-green), контролируя ключевые метрики: среднее время ответа для критичных запросов, задержки на вставки, количество блокировок. В первые часы/дни наблюдайте за ростом размера индексов и показателями autovacuum — возможно потребуется скорректировать пороги или расписание maintenance.

После запуска выполните регламент: 1) Через сутки — собрать статистику pg_stat_all_indexes для выявления неиспользуемых индексов и планов с Seq Scan; 2) Через неделю — проверить фрагментацию/бloat и, при необходимости, запланировать reindex/pg_repack для отдельных партиций; 3) Поддерживайте документацию по партициям и процедурам удаления старых партиций (DROP/ATTACH).

Типичные ошибки и как их избежать

Частые ошибки — создание слишком большого числа индексов «на всякий случай», партиционирование без учёта реальных паттернов доступа, и попытки делать крупные DDL-операции без тестовой реплики. Эти ошибки приводят к росту времени вставок, неправильным планам запросов и высоким затратам на обслуживание.

Как избежать: 1) Перед созданием индекса убедитесь в его пользе — используйте EXPLAIN и статистику использования; 2) Стартуйте с минимального набора индексов и добавляйте по мере необходимости; 3) Делайте миграции партиями и автоматизируйте мониторинг, чтобы быстро вернуть предыдущую конфигурацию в случае деградации.

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