Introdução
Neste laboratório, você aprenderá como implementar a busca de texto completo (full-text search) no PostgreSQL. A busca de texto completo oferece a capacidade de pesquisar documentos em linguagem natural por palavras ou frases específicas. Você começará criando uma tabela articles e adicionando dados de exemplo. Em seguida, adicionará uma coluna search_vector do tipo tsvector, que é um tipo de dado especializado para armazenar texto processado. Você criará um índice para acelerar as buscas e, em seguida, executará consultas usando tsquery. Finalmente, configurará um gatilho (trigger) para atualizar automaticamente os dados de busca sempre que o texto original for modificado, garantindo que seu índice de busca permaneça atualizado.
Criar uma Tabela e Inserir Dados de Exemplo
Nesta etapa, você se conectará ao banco de dados PostgreSQL, criará uma tabela articles e inserirá alguns dados de exemplo. Esta tabela servirá como base para implementar e testar a busca de texto completo.
Primeiro, abra um terminal e conecte-se ao shell interativo do PostgreSQL (psql) como o usuário postgres. Todos os comandos SQL subsequentes neste laboratório serão executados dentro deste shell.
sudo -u postgres psql
Agora você verá o prompt do PostgreSQL, que se parece com postgres=#.
Em seguida, crie a tabela articles. Esta tabela armazenará um ID, um título e o conteúdo principal de cada artigo.
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT
);
Agora, insira três artigos de exemplo na tabela articles. Esses dados serão usados para suas consultas de busca nas etapas seguintes.
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.');
Para confirmar que os dados foram inseridos corretamente, execute uma consulta SELECT para visualizar todos os registros na tabela.
SELECT * FROM articles;
Você deverá ver as três linhas que acabou de inserir, semelhante à saída abaixo:
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)
Adicionar uma Coluna TSVECTOR e Índice GIN
Para realizar a busca de texto completo, você precisa de uma coluna do tipo tsvector. Um valor tsvector é uma lista ordenada de palavras distintas (lexemas) que foram normalizadas para mesclar diferentes variantes da mesma palavra. Nesta etapa, você adicionará uma coluna tsvector e criará um índice sobre ela para acelerar as consultas de busca.
Primeiro, adicione uma nova coluna chamada search_vector do tipo TSVECTOR à tabela articles.
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;
Em seguida, preencha esta nova coluna convertendo o title e o content de cada artigo em um tsvector. A função to_tsvector é usada para essa conversão. Ela processa o texto removendo palavras comuns (stop words) e reduzindo palavras à sua forma raiz (stemming).
UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || content);
Neste comando, 'english' especifica a configuração de busca de texto, e || é o operador de concatenação para combinar o título e o conteúdo.
Para acelerar significativamente as buscas de texto completo, crie um GIN (Generalized Inverted Index) na coluna search_vector. Índices GIN são altamente eficazes para indexar valores compostos como tsvector.
CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);
Você pode verificar se a coluna e o índice foram adicionados inspecionando a estrutura da tabela com o comando \d.
\d articles
A saída agora deve incluir a coluna search_vector e o índice 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)
Realizar Pesquisas de Texto Completo
Com a coluna tsvector e o índice GIN implementados, você agora pode realizar buscas de texto completo eficientes. As consultas são construídas usando o tipo de dado tsquery, que representa uma consulta de texto. O operador @@ é usado para comparar um tsquery com um tsvector.
Primeiro, vamos procurar por artigos que contenham a palavra search. A função to_tsquery converte a string de busca em um tsquery.
SELECT title, content FROM articles WHERE search_vector @@ to_tsquery('english', 'search');
Esta consulta retornará o artigo sobre "Full Text Search".
title | content
----------------------------------+-----------------------------------------------------------------------
Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
(1 row)
Você também pode usar operadores dentro de sua consulta. O operador & (AND) encontra documentos que contêm todos os termos especificados. Vamos procurar por artigos que contenham tanto PostgreSQL quanto performance.
SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & performance');
Isso retorna o artigo sobre ajuste de desempenho.
title
-------------------------------
PostgreSQL Performance Tuning
(1 row)
O operador | (OR) encontra documentos que contêm pelo menos um dos termos especificados. Vamos procurar por artigos que contenham tutorial ou tuning.
SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'tutorial | tuning');
Esta consulta retornará dois artigos.
title
-------------------------------
PostgreSQL Tutorial
PostgreSQL Performance Tuning
(2 rows)
Automatizar Atualizações com um Trigger
Atualmente, a coluna search_vector é estática. Se você atualizar o título ou o conteúdo de um artigo, o search_vector não refletirá automaticamente a mudança. Para resolver isso, você pode criar um trigger que atualiza automaticamente a coluna search_vector antes de qualquer operação de INSERT ou UPDATE na tabela articles.
Primeiro, defina uma função que será executada pelo trigger. Esta função atualiza o search_vector para a linha nova ou atualizada.
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;
A variável NEW na função refere-se à linha que está sendo inserida ou atualizada.
Em seguida, crie o trigger em si. Este trigger chamará a função articles_tsvector_update antes que cada linha seja inserida ou atualizada.
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE FUNCTION articles_tsvector_update();
Agora, vamos testar o trigger. Primeiro, insira um novo artigo sobre indexação de banco de dados.
INSERT INTO articles (title, content) VALUES ('Database Indexing', 'Learn about B-Tree and GIN indexes.');
Por causa do trigger, o search_vector para este novo artigo foi preenchido automaticamente. Você pode verificar isso procurando pela palavra indexes.
SELECT title FROM articles WHERE search_vector @@ to_tsquery('indexes');
O novo artigo deve aparecer nos resultados.
title
---------------------
Database Indexing
(1 row)
Isso confirma que seu trigger está funcionando corretamente, garantindo que seu índice de busca de texto completo permaneça sincronizado com seus dados.
Resumo
Neste laboratório, você aprendeu os fundamentos da implementação de busca de texto completo no PostgreSQL. Você criou com sucesso uma tabela, a preencheu com dados e a configurou para busca de texto completo adicionando uma coluna tsvector. Você aprendeu a usar a função to_tsvector para processar texto e um índice GIN para otimizar o desempenho da busca. Você também praticou a realização de buscas com tsquery usando diferentes operadores. Finalmente, você implementou um trigger para automatizar o processo de manter o índice de busca sincronizado com seus dados, o que é um passo crítico para manter um sistema de busca confiável.
