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

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

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

Как отвечать

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

• поиск пользователя при входе: уникальный индекс по email. Если сравниваете без учёта регистра, нужен индекс по выражению lower(email) или колонка типа citext, иначе запрос с lower() пойдёт мимо;
• проверка refresh-токена или сессии: уникальный индекс по хэшу токена. Сам токен в базе не хранят, хранят SHA-256 от него;
• отзыв всех сессий пользователя: индекс по user_id в таблице сессий, а если чаще ищут активные, частичный с WHERE revoked_at IS NULL;
• очистка протухших записей: индекс по expires_at, чтобы фоновая задача удаляла пачками, а не сканировала таблицу;
• вход через внешних провайдеров: уникальный составной (provider, provider_user_id);
• роли: первичный ключ (user_id, role_id) закрывает поиск ролей пользователя, для обратного поиска нужен отдельный индекс по role_id.

Проверяйте на реальных объёмах: EXPLAIN (ANALYZE, BUFFERS), топ запросов из pg_stat_statements, неиспользуемые индексы по нулевому idx_scan в pg_stat_user_indexes. На проде индекс создают через CREATE INDEX CONCURRENTLY. Внутри транзакции он не работает, поэтому в goose такую миграцию помечают -- +goose NO TRANSACTION. Проверять стоит ровно тот запрос, что шлёт pgx, а не упрощённую копию.

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

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

Создают индекс по email, а в запросе пишут WHERE lower(email) = $1. Обычный индекс по колонке такой запрос не использует.

Делают первичным ключом случайный UUIDv4 в таблице с интенсивной вставкой. Новые ключи разбрасываются по всему B-tree, индекс раздувается, кеш работает хуже. UUIDv7 или bigint из последовательности ведут себя заметно спокойнее.