В чем разница между партиционированием и индексами с точки зрения производительности

Что проверяют

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

Как отвечать

• индекс: отдельная структура (чаще B-tree), которая ведёт от значения к строкам. Выигрыш при высокой селективности: одна строка из ста миллионов за несколько чтений страниц. Цена: каждая вставка и обновление поддерживают все индексы таблицы;
• партиционирование: таблица делится на части по ключу, в PostgreSQL декларативно через PARTITION BY RANGE, LIST или HASH. Выигрыш даёт отсечение партиций (partition pruning): если в WHERE есть условие по ключу, лишние партиции не читаются вовсе;
• обслуживание: сильная сторона партиций не в чтении, а в жизненном цикле данных. Удалить месяц логов это DETACH PARTITION и DROP TABLE за секунды вместо DELETE миллионов строк с раздуванием таблицы и работой для VACUUM;
• сочетание: их используют вместе. Индекс на партиционированной таблице создаётся на каждой партиции, и каждый из них меньше и лучше помещается в память.

Для Java-разработчика важно, что Hibernate и Spring Data про партиции не знают: таблица для них одна, и выигрыш появляется, только если сам запрос содержит условие по ключу. Проверяют это через EXPLAIN (ANALYZE) на том SQL, который реально генерирует ORM.

Где спотыкаются

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

Выбирают ключ, которого нет в типичных запросах. Тогда каждый запрос идёт по всем партициям, и это медленнее, чем одна таблица с индексом.

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