Как искать текст в 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 | Сниппет с подсветкой |
Сравнение подходов
ILIKE | pg_trgm | Full-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.