Como implementar full-text search em português no PostgreSQL

1. Fundamentos do Full-Text Search no PostgreSQL

O PostgreSQL oferece um sistema robusto de busca textual que vai muito além do simples ILIKE. Enquanto ILIKE realiza varreduras sequenciais em busca de padrões, o full-text search trabalha com conceitos linguísticos como lexemas, stop words e stemming.

Os dois pilares desse sistema são:

  • tsvector: Representa um documento processado, contendo lexemas (palavras normalizadas) e suas posições no texto.
  • tsquery: Representa uma consulta processada, também com lexemas, pronta para ser comparada com um tsvector.

O operador fundamental é @@, que verifica se um tsquery corresponde a um tsvector. Exemplo básico:

SELECT to_tsvector('portuguese', 'gatos correm atrás de ratos') @@ to_tsquery('portuguese', 'gato');
-- Resultado: true

Funções importantes incluem plainto_tsquery (converte texto simples em tsquery) e to_tsvector (converte texto em tsvector). Diferentemente do ILIKE, que busca substrings literais, o full-text search entende que "gatos" e "gato" são o mesmo lexema.

2. Configuração de Dicionário e Stemming para Português

O PostgreSQL já inclui suporte nativo ao português. Para verificar se a configuração está disponível:

SELECT cfgname FROM pg_ts_config WHERE cfgname = 'portuguese';

O stemming reduz palavras à sua raiz (ex: "correndo", "correu" → "corr"). As stop words (artigos, preposições, conjunções) são automaticamente removidas.

Para inspecionar o processamento de uma frase:

SELECT ts_debug('portuguese', 'Os desenvolvedores estavam programando sistemas');

A saída mostra cada token, seu tipo de lexema e como foi processado. Se necessário, é possível personalizar a lista de stop words consultando o arquivo de configuração do PostgreSQL, mas para a maioria dos casos a configuração padrão é suficiente.

3. Criação de Índices GIN para Aceleração de Consultas

Sem índice, cada consulta precisa processar toda a tabela. O índice GIN (Generalized Inverted Index) é otimizado para tsvector:

CREATE TABLE artigos (
    id SERIAL PRIMARY KEY,
    titulo TEXT NOT NULL,
    corpo TEXT NOT NULL,
    data_publicacao DATE DEFAULT CURRENT_DATE
);

-- Coluna gerada para armazenar o tsvector automaticamente
ALTER TABLE artigos ADD COLUMN texto_busca TSVECTOR
    GENERATED ALWAYS AS (
        to_tsvector('portuguese', coalesce(titulo, '') || ' ' || coalesce(corpo, ''))
    ) STORED;

-- Índice GIN sobre a coluna gerada
CREATE INDEX idx_artigos_busca ON artigos USING GIN (texto_busca);

Índices GIN são superiores a GiST para full-text search porque ocupam menos espaço e são mais rápidos em consultas de busca, embora a atualização seja ligeiramente mais custosa.

4. Construção de Consultas com Ranking e Relevância

Para ordenar resultados por relevância, usamos ts_rank ou ts_rank_cd:

SELECT titulo, 
       ts_rank(texto_busca, query) AS relevancia
FROM artigos, 
     plainto_tsquery('portuguese', 'desenvolvimento web') AS query
WHERE texto_busca @@ query
ORDER BY relevancia DESC;

Para priorizar campos, use setweight:

ALTER TABLE artigos DROP COLUMN texto_busca;
ALTER TABLE artigos ADD COLUMN texto_busca TSVECTOR
    GENERATED ALWAYS AS (
        setweight(to_tsvector('portuguese', coalesce(titulo, '')), 'A') ||
        setweight(to_tsvector('portuguese', coalesce(corpo, '')), 'B')
    ) STORED;

Com pesos, o título (peso A) tem mais impacto que o corpo (peso B) no ranking. É possível combinar com filtros adicionais:

SELECT titulo, data_publicacao, ts_rank(texto_busca, query) AS relevancia
FROM artigos, 
     plainto_tsquery('portuguese', 'desenvolvimento web') AS query
WHERE texto_busca @@ query
  AND data_publicacao >= '2024-01-01'
ORDER BY relevancia DESC, data_publicacao DESC;

5. Tratamento de Busca por Frases e Prefixos

O operador <-> (distância entre palavras) permite busca por frases exatas:

-- Busca por "inteligência artificial" com até 2 palavras de distância
SELECT titulo FROM artigos
WHERE texto_busca @@ phraseto_tsquery('portuguese', 'inteligência artificial');

-- Distância personalizada: <2> significa até 2 palavras entre os termos
SELECT titulo FROM artigos
WHERE texto_busca @@ to_tsquery('portuguese', 'inteligência <2> artificial');

Para autocomplete com prefixo, use :*:

SELECT titulo FROM artigos
WHERE texto_busca @@ to_tsquery('portuguese', 'desenvolv:*');

Isso encontra "desenvolvimento", "desenvolvedor", "desenvolver". Para busca fonética, a extensão pg_trgm com índice GiST pode ajudar em casos de erros ortográficos, mas lembre-se: pg_trgm não entende português, apenas similaridade de trigramas.

6. Manutenção e Atualização de Índices em Produção

Em tabelas grandes, a abordagem de coluna gerada (como usamos) é ideal, pois o PostgreSQL atualiza o tsvector automaticamente a cada INSERT ou UPDATE. Alternativamente, gatilhos podem ser usados para maior controle.

Para reindexação segura em produção:

REINDEX INDEX CONCURRENTLY idx_artigos_busca;

Monitore o uso dos índices:

SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexname = 'idx_artigos_busca';

Execute VACUUM regularmente para manter o desempenho:

VACUUM ANALYZE artigos;

7. Exemplo Prático Completo: Blog em Português

Abaixo, um script completo com explicações:

-- 1. Criação da tabela
CREATE TABLE artigos (
    id SERIAL PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    corpo TEXT NOT NULL,
    data_publicacao DATE DEFAULT CURRENT_DATE
);

-- 2. Inserção de dados de exemplo
INSERT INTO artigos (titulo, corpo, data_publicacao) VALUES
('Introdução ao Desenvolvimento Web', 
 'O desenvolvimento web moderno utiliza frameworks como React e Node.js para criar aplicações robustas.', 
 '2024-01-15'),
('Inteligência Artificial na Prática', 
 'Modelos de machine learning estão transformando a indústria com soluções inovadoras.', 
 '2024-02-20'),
('Banco de Dados Avançados', 
 'PostgreSQL oferece recursos como full-text search e índices GIN para buscas eficientes.', 
 '2024-03-10');

-- 3. Adiciona coluna tsvector com pesos
ALTER TABLE artigos ADD COLUMN texto_busca TSVECTOR
    GENERATED ALWAYS AS (
        setweight(to_tsvector('portuguese', coalesce(titulo, '')), 'A') ||
        setweight(to_tsvector('portuguese', coalesce(corpo, '')), 'B')
    ) STORED;

-- 4. Cria índice GIN
CREATE INDEX idx_artigos_busca ON artigos USING GIN (texto_busca);

-- 5. Consulta final: busca por "desenvolvimento web" ordenada por relevância e data
SELECT titulo, 
       data_publicacao,
       ts_rank(texto_busca, query) AS relevancia
FROM artigos, 
     plainto_tsquery('portuguese', 'desenvolvimento web') AS query
WHERE texto_busca @@ query
ORDER BY relevancia DESC, data_publicacao DESC;

-- Resultado esperado:
-- "Introdução ao Desenvolvimento Web" aparece primeiro (título tem peso A)
-- "Banco de Dados Avançados" aparece depois (apenas no corpo, peso B)

Explicação linha a linha:
- Linhas 1-5: Estrutura básica da tabela com campos relevantes.
- Linhas 7-12: Três artigos de exemplo com dados realistas.
- Linhas 14-18: Coluna gerada que combina título (peso A) e corpo (peso B) em um único tsvector.
- Linha 20: Índice GIN para acelerar consultas.
- Linhas 22-29: Consulta que busca "desenvolvimento web", calcula relevância e ordena resultados.

O resultado prioriza artigos onde o termo aparece no título, seguido por aqueles onde aparece apenas no corpo, tudo ordenado também pela data mais recente.

Referências