Как выбрать тип индекса в PostgreSQL
Вопрос
Расскажите, какие типы индексов есть в PostgreSQL и как вы выбираете между ними.
Попробуйте сами
Ответьте вслух за 2 минуты, как на собесе. Потом сверьтесь с тезисами: что прозвучало, а что нет.
Показать ответ и разбор
Коротко
Что должно прозвучать в ответе
B-tree по умолчанию: равенство, диапазоны, сортировка, уникальность, `LIKE 'abc%'`
GIN для составных значений: `jsonb`, массивы, полнотекстовый поиск, `pg_trgm` для поиска по подстроке
GiST для диапазонов, геометрии, ближайших соседей и exclusion-ограничений
BRIN для огромных таблиц с корреляцией значения и физического порядка
Частичные, по выражению и покрывающие (`INCLUDE`) индексы как способы сузить и ускорить
Каждый индекс замедляет запись и мешает HOT-обновлениям, неиспользуемые находят по `pg_stat_user_indexes`
Что могут спросить следом
- Почему GIN медленнее на запись и что такое fastupdate?
- Когда hash-индекс оправдан?
- Как найти неиспользуемые индексы?
Разбор
По умолчанию беру B-tree: он покрывает равенство, диапазоны, сортировку, уникальность и префиксный LIKE. Если ищем внутри значения, например элементы массива, ключи jsonb или слова в тексте, нужен GIN: это инвертированный индекс, быстрый на чтение, но дорогой на запись (смягчается fastupdate и списком ожидания). Для подстрок LIKE '%abc%' беру GIN с pg_trgm. GiST для диапазонов и геометрии, поиска ближайших (ORDER BY point <-> ...) и ограничений EXCLUDE. BRIN для многомиллиардных журналов, где время растёт с физическим порядком: он крошечный. Hash нужен редко, только равенство. Дальше уточняю форму: частичный индекс под горячий запрос (WHERE status = 'new'), индекс по выражению (lower(email)), покрывающий через INCLUDE для Index Only Scan. И помню цену: каждый индекс замедляет вставку и обновление и мешает HOT update, поэтому неиспользуемые нахожу по idx_scan = 0 в pg_stat_user_indexes и удаляю.
Ответьте сами, а не прочитайте
В тренажёре вопрос попадёт в повторения и вернётся, когда начнёт забываться. Плюс билеты с зачётом: 20 вопросов, не больше 2 ошибок.
Предыдущий вопрос
GIN для jsonb: jsonb_ops или jsonb_path_ops
Следующий вопрос
Раздутый индекс на проде