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

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

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

Как отвечать

Перечислите горячие запросы сервиса авторизации и индекс под каждый:

• вход по логину: уникальный индекс по email или username. Почту сравнивают без учёта регистра, поэтому нужен индекс по выражению lower(email) или тип citext в PostgreSQL;
• проверка refresh-токена: уникальный индекс по хэшу токена, сам токен в базе не хранят;
• сессии пользователя: индекс по sessions.user_id для списка сессий и выхода со всех устройств. PostgreSQL не создаёт индекс на внешний ключ сам;
• чистка просроченных сессий: индекс по expires_at для фоновой задачи;
• роли: составной первичный ключ (user_id, role_id) плюс индекс по role_id для обратного запроса;
• OAuth-вход: уникальный индекс по (provider, provider_user_id);
• блокировка после неудачных попыток: (user_id, created_at) в журнале попыток.

Проверка: EXPLAIN (ANALYZE, BUFFERS) на реальных запросах, pg_stat_statements для поиска тяжёлых, pg_stat_user_indexes для неиспользуемых индексов. Индексы в Java-проекте живут в миграциях Flyway или Liquibase, а не в автогенерации схемы Hibernate. На большой таблице CREATE INDEX CONCURRENTLY, чтобы не блокировать запись; такая команда не выполняется внутри транзакции.

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

Дублируют индекс на первичном ключе. Колонка под PRIMARY KEY уже проиндексирована, отдельный CREATE INDEX на ней только замедлит вставки.

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

Индексируют email обычным индексом, а ищут через lower(email). Планировщик такой индекс не возьмёт и уйдёт в последовательное сканирование.