Полнотекстовый поиск из 07-05 силён в морфологии, но беспомощен перед опечаткой: посетитель набирает в поиске меню «capucino» (или «капучіно»), и FTS, который ищет по нормализованным лексемам, не находит ничего — такой лексемы в индексе нет. А пользователь хотел «Cappuccino». Нужен поиск, прощающий ошибки и работающий по похожести написания, а не по словам, — и в Postgres он есть в расширении pg_trgm.

Цель юнита — нечёткий поиск на триграммах: функция similarity (насколько строки похожи), оператор % (похожи ли выше порога — готовый «возможно, вы имели в виду») и ускорение LIKE/ILIKE с подстрокой в середине через GIN-индекс. И в конце — матрица выбора: когда FTS, когда pg_trgm, а когда пора во внешний движок.

Триграммы и similarity

pg_trgm режет строку на триграммы — тройки подряд идущих символов (Cappuccino → cap, app, ppu, …). Схожесть двух строк — это доля общих триграмм: similarity(a, b) возвращает число от 0 (ничего общего) до 1 (идентичны). Опечатка меняет лишь несколько триграмм, поэтому similarity('Cappuccino', 'capucino') остаётся высокой, а у непохожих названий падает почти в ноль. Это принципиально иной поиск, чем FTS: он не знает слов и морфологии — он сравнивает написание посимвольно, и именно поэтому ловит опечатки.

Слово в триграммах

Слово режется на тройки подряд идущих символов (плюс добивка пробелами по краям), и схожесть — это доля общих троек:

  Cappuccino → cap app ppu puc ucc cci cin ino
  capucino   → cap apu puc uci cin ino
 
  общие тройки: cap, puc, cin, ino → similarity('Cappuccino','capucino') = 0.538
  у непохожих слов общих троек почти нет → similarity ≈ 0

Именно поэтому trgm ловит опечатки, а FTS — нет: сравнение идёт по написанию, а не по словарным лексемам.

Оператор % и порог

Считать similarity ко всему и сортировать — можно, но для фильтра «похоже / не похоже» есть оператор %: name % 'capucino' истинно, когда схожесть выше параметра pg_trgm.similarity_threshold (по умолчанию 0.3). Это и есть готовый «did-you-mean»: оставляем только то, что выше порога, сортируем по similarity DESC — и показываем «возможно, вы имели в виду…». Порог настраивается под задачу: ниже — больше кандидатов (и шума), выше — строже.

Ускоренный LIKE и матрица выбора

Бонус pg_trgm — индекс под подстроку. name LIKE '%presso%' обычный B-tree не ускоряет (шаблон начинается с % — искать не от чего), а GIN с классом gin_trgm_ops индексирует триграммы и поэтому ускоряет и %-схожесть, и ILIKE '%...%' (06-05). Где что применять:

Что показывает наш код

Лабораторный стол menu_search_lab (позиции меню) + расширение pg_trgm и trgm-GIN. Три запроса вокруг опечатки capucino:

SELECT name, similarity(name, 'capucino') FROM menu_search_lab ORDER BY similarity DESC;  -- SimilarityScores
SELECT name FROM menu_search_lab WHERE name % 'capucino' ORDER BY similarity DESC;         -- DidYouMean (порог 0.3)
SELECT name FROM menu_search_lab WHERE name ILIKE '%presso%';                              -- AcceleratedLike

similarity возвращает real; округляем round(...::numeric, 3)::text для стабильного вывода. Имена английские: схожесть — это сравнение наборов триграмм, оно детерминировано и от локали не зависит. Юнит добавляет своё расширение и стол → make db-reset накатывает их через brew.Apply.

Запуск

docker compose up -d
make lecture L=07-jsonb-arrays-and-search/07-06-pg-trgm-fuzzy T=db-reset
make lecture L=07-jsonb-arrays-and-search/07-06-pg-trgm-fuzzy

Вывод:

1) similarity(name, 'capucino') — схожесть по триграммам (опечатка в Cappuccino):
НАЗВАНИЕ    SIMILARITY
Cappuccino  0.538
Cold Brew   0.056
Americano   0.056
Espresso    0.000
Latte       0.000
Flat White  0.000
Macchiato   0.000
 
2) name % 'capucino' — выше порога 0.3 (did-you-mean):
   Cappuccino (similarity 0.538)
 
3) name ILIKE '%presso%' — подстрока в середине, ускоряется trgm-GIN:
   Espresso

similarity к опечатке capucino высока только у Cappuccino (0.538), у остальных — почти ноль: общих триграмм мало. Поэтому оператор % (порог 0.3) оставил единственного кандидата — готовый «возможно, вы имели в виду Cappuccino». А ILIKE '%presso%' нашёл Espresso по подстроке в середине — тот случай, где обычный индекс бессилен, а trgm-GIN выручает.

Заборчик

pg_trgm — точечный инструмент, а не замена поиску:

• similarity ничего не знает о смысле: Latte и Matte для неё похожи, хотя это разные вещи; на коротких строках шум выше;
• порог % калибруют под данные — слишком низкий завалит выдачу мусором;
• trgm-GIN на больших таблицах заметно тяжелее обычного индекса по записи и размеру; вешать его «на всякий случай» не стоит;
• pg_trgm дополняет FTS, а не отменяет: типичная связка — «FTS по словам + trgm для опечаток/ILIKE».

Когда нужны морфология десятка языков, синонимы, обучаемая релевантность или поиск по терабайтам — это уже не «поиск в БД», а внешний движок (эстафета в Elasticsearch соседнего kafka-cookbook); твой DBA подскажет, где граница.

Что забрать с собой

• pg_trgm ищет по схожести написания (триграммы), а не по словам: similarity(a,b) ∈ [0,1] ловит опечатки, чего FTS и LIKE не умеют.
• Оператор % — «схоже выше порога» (pg_trgm.similarity_threshold, default 0.3) — готовый «did-you-mean».
• GIN gin_trgm_ops ускоряет и %, и ILIKE '%подстрока%' (где обычный B-tree бесполезен).
• Матрица: слова/морфология → FTS; опечатки/ILIKE → trgm; точное членство → массив/junction; масштаб/синонимы/ML → внешний движок.

На этом модуль 07 закрыт: от доступа к jsonb и его границ — через SQL/JSON path, массивы-vs-junction, полнотекстовый и нечёткий поиск. Дальше — модуль 08 «Аналитика, оконные функции и LATERAL»: считаем running total, ранжируем top-N на группу, строим day-over-day и скользящие средние, ходим по дереву категорий рекурсивным CTE и убиваем N+1 через LATERAL — аналитика прямо в SQL, без выгрузки в приложение.