Оптимизация схемы БД под запросы: что проверять на ревью

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

Начните с запроса, а не с таблицы

Предположим, страница заказа показывает последние оплаченные покупки конкретного клиента. Запрос фильтрует по клиенту и статусу, сортирует по дате и возвращает небольшую выборку:

SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

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

В PostgreSQL 16 для измерения подходит EXPLAIN (ANALYZE, BUFFERS) перед SELECT. ANALYZE реально выполняет запрос, поэтому не запускайте его бездумно на тяжёлом запросе в рабочей среде. Смотрите не только на итоговое время, но и на фактическое число строк, количество чтений блоков, сортировку и расхождение фактических строк с оценкой планировщика.

Что проверять в предложенном индексе

Для показанного запроса кандидат может выглядеть так:

CREATE INDEX orders_customer_status_created_idx
ON orders (customer_id, status, created_at DESC)
INCLUDE (id, total);

Сначала идут поля с проверкой на равенство, затем поле сортировки. Такой порядок может помочь PostgreSQL найти строки нужного клиента и статуса уже в нужном порядке. INCLUDE добавляет возвращаемые значения в листовой уровень индекса, но не делает их ключами для поиска или сортировки. Это не обещает index-only scan: результат зависит, в частности, от состояния visibility map и других деталей исполнения.

Порядок колонок нельзя выбирать по правилу «самое селективное поле всегда первым». Здесь важно, какие условия запрос задаёт, какую сортировку требует и можно ли использовать левую часть составного индекса. Если поле иногда отсутствует в фильтре или вместо равенства используется диапазон, план и полезность индекса могут измениться.

Составной или частичный индекс

Если запросы почти всегда выбирают только оплаченные заказы, можно рассмотреть частичный индекс:

CREATE INDEX orders_paid_customer_created_idx
ON orders (customer_id, created_at DESC)
INCLUDE (id, total)
WHERE status = 'paid';

Он хранит записи только для строк, удовлетворяющих предикату. Это может сократить индекс, но пригодится лишь запросам, условие которых планировщик может сопоставить с status = 'paid'. Если статус передают параметром в подготовленный запрос, выбор частичного индекса зависит от того, какой план PostgreSQL строит и может ли он доказать соответствие предикату. Проверяйте именно запрос из приложения, а не только вручную подставленный литерал.

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

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

Как провести ревью и проверить рефакторинг

Сравнивайте не схему «до» и «после» сама по себе, а поведение конкретных запросов. У запроса могут быть разные планы на таблицах разного размера и при разных значениях параметров. В тесте зафиксируйте версию PostgreSQL, объём и распределение данных, настройки, параметры запроса и условия запуска.

1. Назовите целевой запрос. Приложите реальный SQL или безопасно обезличенный вариант, частоту выполнения и сценарий, который его вызывает. Проверка пройдена, если ясно, какой пользовательский путь должен выиграть.
2. Снимите исходный план. Для SELECT используйте EXPLAIN (ANALYZE, BUFFERS) на подходящих данных. Запишите фактические и оценочные строки, узлы плана, сортировку и чтение блоков, а не только время из одного запуска.
3. Сформулируйте гипотезу. Например: индекс позволит применить фильтр по клиенту и статусу до сортировки. Проверьте, соответствует ли порядок колонок условиям и сортировке. Проверка пройдена, если изменение объясняется поведением именно этого запроса.
4. Сравните после изменения. Повторите измерение в сопоставимых условиях и проверьте запросы, которые используют ту же таблицу. Отдельно оцените цену записи и размер индекса: выигрыш чтения не бесплатен.
5. Подготовьте развёртывание и откат. Для большой таблицы оцените влияние построения индекса на рабочую нагрузку. В PostgreSQL создание индекса с CONCURRENTLY не выполняют внутри транзакционного блока; следуйте документации и проверьте результат операции перед переключением трафика.

Замечания, которые полезны автору

На ревью лучше оставлять проверяемые замечания, а не «добавить индекс для скорости». Например:

• «Этот индекс начинается с created_at, но запрос фильтрует по customer_id и status. Покажите план с этим индексом или объясните, какие другие запросы он обслуживает».
• «Почему здесь добавлен total через INCLUDE? Есть ли измерение, что обращения к таблице — существенная часть стоимости запроса?»
• «Условие частичного индекса фиксирует status = 'paid', а в приложении статус передаётся параметром. Проверьте план именно для используемого приложением способа выполнения».
• «План оценивает мало строк, но фактически проходит значительно больше. Проверьте распределение значений и актуальность статистики прежде, чем объяснять проблему отсутствующим индексом».
• «Покажите влияние нового индекса на вставки и обновления этой таблицы, а также сценарий отката миграции».

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

Где рефакторинг ломается

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

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

[1] Листовой уровень — нижняя часть B-tree, где хранятся ключи индекса и ссылки на строки либо дополнительные значения для INCLUDE. Внутреннее устройство индекса важно здесь только в одном: включённые поля помогают покрытию запроса, но не становятся условиями поиска.

Что делать дальше

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