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

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

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

Как отвечать

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

• логин: поиск пользователя по email. Нужен уникальный индекс без учёта регистра, по выражению lower(email) или через тип citext, иначе Ivan@mail.ru и ivan@mail.ru станут двумя аккаунтами;
• сессии и refresh-токены: поиск по хэшу токена, уникальный индекс на token_hash. Плюс индекс на user_id, чтобы «выйти на всех устройствах» не сканировало таблицу;
• внешние ключи: PostgreSQL сам не индексирует ссылающуюся колонку. Без индекса на sessions.user_id удаление пользователя с каскадом превращается в полный проход по сессиям;
• роли: в связующей таблице составной первичный ключ (user_id, role_id) и отдельный индекс на role_id для обратного запроса;
• OAuth: уникальный индекс на (provider, provider_user_id);
• очистка: индекс по expires_at для удаления просроченных токенов или частичный индекс where revoked = false, если запросы смотрят только на активные.

В Python индексы описываются в модели: unique=True, index=True и Index(...) в SQLAlchemy, Meta.indexes и UniqueConstraint в Django, а на прод едут миграцией Alembic или Django. На большой таблице индекс строят через CREATE INDEX CONCURRENTLY: он не блокирует запись, но не работает внутри транзакции, поэтому такую миграцию выносят из общей транзакции. Эффект проверяйте через EXPLAIN (ANALYZE, BUFFERS) и pg_stat_statements.

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

Индексируют всё подряд, включая password_hash и is_active. По первому никогда не ищут, у второго два значения, и планировщик его проигнорирует.

Забывают про регистр email и получают дубли аккаунтов или промах мимо индекса из-за lower() в запросе.

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