re:re:actable
Урок 063 минуты чтения

Как искать текст в SQL

На этой странице
Кратко

Для текстового поиска в PostgreSQL есть три уровня. LIKE/ILIKE — поиск по шаблону: просто, но '%слово%' без специального индекса сканирует всю таблицу и не понимает словоформ. Trigram-поиск (pg_trgm) разбивает строки на тройки символов: с GIN/GiST-индексом ускоряет ILIKE '%x%' и умеет находить похожие строки с опечатками (similarity, оператор %) — хорош для автодополнения по названиям и именам. Полнотекстовый поиск (tsvector, tsquery) работает со словами: нормализует их по словарю языка ('russian': «индексы» → «индекс»), убирает стоп-слова, поддерживает логические операторы, фразы и ранжирование (ts_rank), веса для полей. Для сложного поиска (фасеты, синонимы, опечатки на миллионах документов, многоязычность) используют внешние движки — Elasticsearch/OpenSearch, Meilisearch, Typesense.

Вопрос

Как реализовать поиск по тексту в PostgreSQL?

LIKE / ILIKE

SELECT * FROM products WHERE title ILIKE '%наушники%';

Подходит для маленьких таблиц и админок. Детали — LIKE и ILIKE.

Trigram

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_title_trgm ON products USING gin (title gin_trgm_ops);
 
-- подстрока через индекс
SELECT * FROM products WHERE title ILIKE '%sony%';
 
-- похожие строки (запрос с опечаткой «наушнки»)
SELECT title, similarity(title, 'наушнки') AS sim
FROM products
WHERE title % 'наушнки'               -- similarity выше порога pg_trgm.similarity_threshold (0.3)
ORDER BY sim DESC
LIMIT 10;

Полнотекстовый поиск

-- генерируемый столбец с весами: заголовок важнее текста
ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('russian', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('russian', coalesce(body, '')),  'B')
  ) STORED;
 
CREATE INDEX articles_search_gin ON articles USING gin (search);
 
SELECT id, title, ts_rank(search, q) AS rank,
       ts_headline('russian', body, q, 'MaxWords=20') AS snippet
FROM articles, websearch_to_tsquery('russian', 'индексы postgres -mysql') AS q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;
SELECT to_tsvector('russian', 'Быстрые индексы в PostgreSQL');
-- 'быстр':1 'индекс':2 'postgresql':4
ФункцияНазначение
to_tsvector(config, text)Документ → набор нормализованных лексем
websearch_to_tsqueryЗапрос в стиле поисковиков: "фраза", -исключить, or
plainto_tsqueryПростой текст → AND всех слов
@@Совпадение документа и запроса
ts_rank, ts_rank_cdРелевантность
ts_headlineСниппет с подсветкой

Сравнение подходов

ILIKEpg_trgmFull-textВнешний движок
ПодстрокаДаДаНет (слова)Да
ОпечаткиНетДаНетДа
СловоформыНетНетДаДа
РанжированиеНетПо похожестиДаПродвинутое
Инфраструктура—РасширениеВстроеноОтдельный сервис + синхронизация

Автодополнение

-- префиксный поиск по словам
SELECT title FROM products
WHERE search @@ to_tsquery('russian', 'науш:*')
LIMIT 10;

Для «как вы печатаете» обычно комбинируют префиксный FTS или trigram с сортировкой по популярности.

Частые ошибки

  • ILIKE '%x%' по большой таблице без trigram-индекса.
  • to_tsvector без указания конфигурации — используется default_text_search_config, и индекс по выражению не совпадёт с запросом.
  • Конкатенация полей без coalesce — NULL обнуляет весь документ.
  • Внешний поисковик «по умолчанию», когда хватило бы встроенного FTS.

Что важно помнить

  • LIKE — шаблоны, pg_trgm — подстроки и опечатки, FTS — слова и релевантность.
  • Каждый подход требует своего GIN-индекса.
  • Внешние движки — для сложного поиска на больших объёмах.

Мини-шпаргалка

  • USING gin (col gin_trgm_ops), similarity, %.
  • to_tsvector('russian', ...), websearch_to_tsquery.
  • setweight, ts_rank, ts_headline.
  • GENERATED ALWAYS AS (...) STORED + GIN.

Документация

PostgreSQL: Full Text Search, PostgreSQL: pg_trgm.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам