Какие задачи решали с индексами и миграциями в PostgreSQL

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

Работали ли вы с живой базой под нагрузкой, а не только с ddl-auto=update. Интервьюер хочет услышать, как вы находили медленный запрос, выбирали индекс и выкатывали изменение схемы без простоя. Следом часто спрашивают, как добавить индекс на большую таблицу в проде.

Как отвечать

Расскажите один или два реальных случая по схеме: симптом, диагностика, решение, проверка. Цифры свои.

По индексам:

• поиск медленного: pg_stat_statements по суммарному времени, затем EXPLAIN (ANALYZE, BUFFERS) по конкретному запросу. Заодно всплывает N+1 от Hibernate, которое индексом не лечится;
• выбор типа: B-tree по умолчанию, составной с учётом порядка колонок, частичный под WHERE status = 'ACTIVE', GIN для jsonb и полнотекстового поиска, BRIN для огромных таблиц, растущих по времени;
• чистка: неиспользуемые индексы по idx_scan = 0 в pg_stat_user_indexes, каждый из них тормозит запись.

По миграциям:

• инструмент: Flyway или Liquibase в Spring Boot, версионированные файлы в репозитории, а Hibernate в проде только validate или none;
• индекс без блокировки: CREATE INDEX CONCURRENTLY не останавливает запись, но не работает внутри транзакции. В Liquibase у changeSet ставят runInTransaction="false", во Flyway такой оператор выносят в отдельную миграцию;
• блокировки: SET lock_timeout перед ALTER TABLE, чтобы миграция не повисла в очереди и не заблокировала всех за собой;
• совместимость: переименование колонки через expand и contract, потому что при раскатке старая и новая версии приложения работают одновременно;
• большие обновления: заполнение данных пачками, а не одним UPDATE.

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

Делают CREATE INDEX без CONCURRENTLY на таблице в десятки миллионов строк, и запись встаёт до конца построения. Упавший CONCURRENTLY оставляет невалидный индекс, его надо удалить.

Ставят B-tree на email и ждут ускорения от lower(email) = ? или LIKE '%...%'. Нужен индекс по выражению или pg_trgm.

Рассчитывают на откат миграции. Удалённую колонку с данными откат не вернёт, поэтому на проде чаще чинят новой миграцией вперёд.