Как реализовать полнотекстовый поиск по никам пользователей в базе данных

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

Понимаете ли вы, что ник это не текст на естественном языке и классический полнотекстовый поиск с морфологией тут почти бесполезен. Следом обычно спрашивают, как искать по подстроке и с опечатками, не читая таблицу целиком.

Как отвечать

Начните с уточнения сценария: поиск по префиксу для автодополнения, по подстроке или нечёткий, с опечатками. От этого зависит индекс. Для PostgreSQL:

• префикс: WHERE lower(nickname) LIKE 'ab%' обслуживает B-tree по выражению lower(nickname) с классом операторов text_pattern_ops. Самый дешёвый вариант;
• подстрока: расширение pg_trgm и индекс GIN (nickname gin_trgm_ops). Он ускоряет ILIKE '%abc%' и регулярные выражения;
• опечатки: тот же pg_trgm, оператор % и функция similarity() с сортировкой по близости. Порог по умолчанию 0.3, его меняют параметром pg_trgm.similarity_threshold;
• регистр: либо индекс по lower(), либо тип citext.

tsvector с конфигурацией english для ников не подходит: стеммер и стоп-слова рассчитаны на человеческий язык, ники из транслита, цифр и подчёркиваний он разбирает непредсказуемо, а искать по середине слова не умеет вовсе. Индекс GIN при этом обновляется сам при каждой записи, «периодически пересобирать» его не нужно.

На стороне Python запрос идёт через SQLAlchemy или asyncpg с параметрами, с LIMIT и минимальной длиной строки поиска. При миллионах пользователей и сложном ранжировании поиск выносят в Elasticsearch или OpenSearch с токенизатором edge_ngram, но это отдельный кластер и синхронизация данных.

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

Берут to_tsquery и передают туда ввод пользователя как есть. Строка с пробелом или скобкой роняет запрос синтаксической ошибкой; для пользовательского ввода есть plainto_tsquery и websearch_to_tsquery, а для ников лучше вообще триграммы.

Собирают шаблон f"%{q}%" и забывают экранировать % и _ из ввода. Ник a_b тогда совпадёт с axb, а запрос из одного % вернёт всю таблицу.

Ждут от триграммного индекса чудес на коротких запросах. Строка из одного или двух символов почти ничего не отсекает, поэтому вводят минимум в три символа или для коротких запросов переключаются на префиксный поиск.