Перейти к основному содержимому

Практика: Полнотекстовый поиск и текстовые данные


Онлайн редактор кода для PostgreSQL

Здесь вы можете попробовать свои силы в программировании на языке SQL для работы с PostgreSQL. Для этого мы предоставляем вам онлайн редактор кода, в котором вы можете написать свой код и запустить его.


❗️ При обновлении страницы код пропадёт, по этому, сохраните свой код куда-нибудь, если он важный.

Задание 1 — Базовый полнотекстовый поиск​

Задание 1: Простой поиск + tsquery + разные операторы

⏱️ Примерное время: 15-25 минут

Создай таблицу статей/постов и напиши поисковые запросы с разными операторами.


Требования:

  • создай таблицу articles с полем search_vector tsvector
  • хотя бы один триггер или функция для автоматического обновления tsvector
  • минимум 4 разных варианта запроса (с &, |, !, <->, <N>)
  • используй to_tsquery, plainto_tsquery, phraseto_tsquery, websearch_to_tsquery

Пример:

SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('russian', 'postgresql & оптимизация') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

Задание 2 — Ранжирование и подсветка​

Задание 2: Ранжирование + ts_headline + веса

⏱️ Примерное время: 20-35 минут

Добавь ранжирование и красивую подсветку результатов.


Требования:

  • используй ts_rank, ts_rank_cd
  • хотя бы один запрос с setweight (A/B/C)
  • минимум 2 варианта ts_headline с разными настройками
  • красивое форматирование результата

Пример:

SELECT
    title,
    ts_headline('russian', content, query, 'StartSel=<mark>, StopSel=</mark>, MaxFragments=2') AS snippet,
    ts_rank(search_vector, query, 1) AS rank
FROM articles, websearch_to_tsquery('russian', 'postgresql индекс') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 5;

Задание 3 — Нечёткий поиск + триграммы + Levenshtein​

Задание 3: Поиск с опечатками (pg_trgm, fuzzystrmatch)

⏱️ Примерное время: 25-40 минут

Реализуй поиск, который прощает опечатки и находит похожие строки.


Требования:

  • установи расширения pg_trgm и fuzzystrmatch
  • создай GIN-индексы с gin_trgm_ops
  • хотя бы один запрос с similarity(), levenshtein(), %
  • комбинированный поиск (FTS + fuzzy)

Пример:

SELECT title, similarity(title, 'Оптимизация PostgreSQL') AS sim
FROM articles
WHERE title % 'Оптимизация PostgreSQL'
   OR search_vector @@ to_tsquery('russian', 'оптимизация')
ORDER BY sim DESC
LIMIT 5;

Задание 4 — Продвинутые сценарии (автодополнение, многоязычность, логи)​

Задание 4: Автодополнение, многоязычный поиск, поиск по логам

⏱️ Примерное время: 40-70 минут

Реализуй реальные поисковые фичи.


Требования:

  • функция поиска с параметрами (query, limit, offset, filters…)
  • минимум один сценарий с автодополнением
  • хотя бы один многоязычный пример или синонимы
  • красивая функция + комментарии

Пример структуры функции:

CREATE FUNCTION search_articles(
    q TEXT,
    lim INT DEFAULT 10,
    offs INT DEFAULT 0
) RETURNS TABLE(...) AS $$
...
$$ LANGUAGE sql STABLE;

Задание 5 — Итоговый поисковый движок​

Задание 5: Полноценная поисковая система (итоговое)

⏱️ Примерное время: 90-180 минут

Создай мини-поисковик уровня блога / магазина / документации.


Требования:

  • полноценная таблица + tsvector + GIN-индекс
  • триггер для обновления search_vector
  • функция поиска с ранжированием, подсветкой, пагинацией
  • поддержка весов, синонимов, опечаток, фраз
  • логирование запросов + автодополнение (бонус)
  • красивая выдача: заголовок, сниппет, релевантность, метаданные

Это задание — отличный проект для портфолио или собеседования на позицию SQL-разработчика / аналитика / бэкендера.

подсказка

Полнотекстовый поиск — это когда ты перестаёшь писать 15 условий LIKE и начинаешь думать как поисковая система.
Главные правила успеха:

  1. Отдельная колонка tsvector + GIN-индекс — обязательно
  2. websearch_to_tsquery — лучший друг пользовательского ввода
  3. ts_headline — делает результаты красивыми и понятными
  4. ts_rank + веса + нормализация = релевантные результаты
  5. pg_trgm + levenshtein = прощение опечаток