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

Введение

В этой лабораторной работе вы научитесь реализовывать полнотекстовый поиск в PostgreSQL. Полнотекстовый поиск предоставляет возможность искать в документах на естественном языке по конкретным словам или фразам. Вы начнете с создания таблицы articles и добавления образцов данных. Затем вы добавите столбец search_vector типа tsvector, который является специализированным типом данных для хранения обработанного текста. Вы создадите индекс для ускорения поиска и затем выполните запросы с использованием tsquery. Наконец, вы настроите триггер для автоматического обновления данных поиска при изменении исходного текста, чтобы ваш индекс поиска оставался актуальным.

Создание таблицы и вставка примеров данных

На этом этапе вы подключитесь к базе данных PostgreSQL, создадите таблицу articles и вставите некоторые образцы данных. Эта таблица послужит основой для реализации и тестирования полнотекстового поиска.

Сначала откройте терминал и подключитесь к интерактивной оболочке PostgreSQL (psql) от имени пользователя postgres. Все последующие SQL-команды в этой лабораторной работе будут выполняться внутри этой оболочки.

sudo -u postgres psql

Теперь вы увидите приглашение PostgreSQL, которое выглядит как postgres=#.

Далее создайте таблицу articles. Эта таблица будет хранить идентификатор, заголовок и основное содержимое каждой статьи.

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
);

Теперь вставьте три образца статей в таблицу articles. Эти данные будут использоваться для ваших поисковых запросов на следующих этапах.

INSERT INTO articles (title, content) VALUES
('PostgreSQL Tutorial', 'This is a comprehensive tutorial on PostgreSQL.'),
('Full Text Search in PostgreSQL', 'Learn how to implement full text search using TSVECTOR in PostgreSQL.'),
('PostgreSQL Performance Tuning', 'Tips and tricks to improve the performance of your PostgreSQL database.');

Чтобы убедиться, что данные были вставлены правильно, выполните запрос SELECT для просмотра всех записей в таблице.

SELECT * FROM articles;

Вы должны увидеть три только что вставленные строки, аналогично выводу ниже:

 id |             title              |                                       content
----+----------------------------------+-----------------------------------------------------------------------
  1 | PostgreSQL Tutorial            | This is a comprehensive tutorial on PostgreSQL.
  2 | Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
  3 | PostgreSQL Performance Tuning  | Tips and tricks to improve the performance of your PostgreSQL database.
(3 rows)

Добавление столбца TSVECTOR и индекса GIN

Для выполнения полнотекстового поиска вам нужен столбец типа tsvector. Значение tsvector представляет собой отсортированный список уникальных слов (лексем), которые были нормализованы для объединения различных вариантов одного и того же слова. На этом этапе вы добавите столбец tsvector и создадите на нем индекс для ускорения поисковых запросов.

Сначала добавьте новый столбец с именем search_vector типа TSVECTOR в таблицу articles.

ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

Затем заполните этот новый столбец, преобразовав title и content каждой статьи в tsvector. Для этого преобразования используется функция to_tsvector. Она обрабатывает текст, удаляя общие слова (стоп-слова) и приводя слова к их корневой форме (стемминг).

UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || content);

В этой команде 'english' указывает конфигурацию текстового поиска, а || — оператор конкатенации для объединения заголовка и содержимого.

Чтобы значительно ускорить полнотекстовый поиск, создайте GIN (Generalized Inverted Index) индекс на столбце search_vector. GIN-индексы очень эффективны для индексации составных значений, таких как tsvector.

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

Вы можете проверить, что столбец и индекс были добавлены, просмотрев структуру таблицы с помощью команды \d.

\d articles

Вывод теперь должен включать столбец search_vector и GIN-индекс articles_search_idx.

                                     Table "public.articles"
    Column     |          Type          | Collation | Nullable |                 Default
---------------+------------------------+-----------+----------+-----------------------------------------
 id            | integer                |           | not null | nextval('articles_id_seq'::regclass)
 title         | character varying(255) |           | not null |
 content       | text                   |           |          |
 search_vector | tsvector               |           |          |
Indexes:
    "articles_pkey" PRIMARY KEY, btree (id)
    "articles_search_idx" gin (search_vector)

Выполнение полнотекстового поиска

Теперь, когда столбец tsvector и индекс GIN настроены, вы можете выполнять эффективный полнотекстовый поиск. Запросы строятся с использованием типа данных tsquery, который представляет собой текстовый запрос. Оператор @@ используется для сопоставления tsquery с tsvector.

Сначала найдем статьи, содержащие слово search. Функция to_tsquery преобразует строку поиска в tsquery.

SELECT title, content FROM articles WHERE search_vector @@ to_tsquery('english', 'search');

Этот запрос вернет статью о "Full Text Search".

             title              |                                       content
----------------------------------+-----------------------------------------------------------------------
 Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
(1 row)

Вы также можете использовать операторы в своем запросе. Оператор & (AND) находит документы, содержащие все указанные термины. Давайте найдем статьи, содержащие как PostgreSQL, так и performance.

SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & performance');

Это вернет статью о настройке производительности.

             title
-------------------------------
 PostgreSQL Performance Tuning
(1 row)

Оператор | (OR) находит документы, содержащие хотя бы один из указанных терминов. Давайте найдем статьи, содержащие либо tutorial, либо tuning.

SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'tutorial | tuning');

Этот запрос вернет две статьи.

             title
-------------------------------
 PostgreSQL Tutorial
 PostgreSQL Performance Tuning
(2 rows)

Автоматизация обновлений с помощью триггера

В настоящее время столбец search_vector является статическим. Если вы обновите заголовок или содержимое статьи, search_vector не отразит изменение автоматически. Чтобы решить эту проблему, вы можете создать триггер, который автоматически обновляет столбец search_vector перед любой операцией INSERT или UPDATE в таблице articles.

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

CREATE OR REPLACE FUNCTION articles_tsvector_update() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        to_tsvector('pg_catalog.english', NEW.title || ' ' || NEW.content);
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

Переменная NEW в функции ссылается на строку, которая вставляется или обновляется.

Затем создайте сам триггер. Этот триггер будет вызывать функцию articles_tsvector_update перед каждой вставкой или обновлением строки.

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE FUNCTION articles_tsvector_update();

Теперь протестируем триггер. Сначала вставим новую статью о индексировании баз данных.

INSERT INTO articles (title, content) VALUES ('Database Indexing', 'Learn about B-Tree and GIN indexes.');

Благодаря триггеру, search_vector для этой новой статьи был автоматически заполнен. Вы можете проверить это, выполнив поиск по слову indexes.

SELECT title FROM articles WHERE search_vector @@ to_tsquery('indexes');

Новая статья должна появиться в результатах.

        title
---------------------
 Database Indexing
(1 row)

Это подтверждает, что ваш триггер работает правильно, обеспечивая синхронизацию вашего полнотекстового поискового индекса с вашими данными.

Резюме

В этой лабораторной работе вы изучили основы реализации полнотекстового поиска в PostgreSQL. Вы успешно создали таблицу, заполнили ее данными и настроили для полнотекстового поиска, добавив столбец tsvector. Вы узнали, как использовать функцию to_tsvector для обработки текста и индекс GIN для оптимизации производительности поиска. Вы также практиковались в выполнении поисковых запросов с tsquery, используя различные операторы. Наконец, вы реализовали триггер для автоматизации процесса синхронизации поискового индекса с вашими данными, что является критически важным шагом для поддержания надежной системы поиска.