Full-text search no PostgreSQL

1. Introdução à Full-text Search no PostgreSQL

A busca textual é uma necessidade comum em aplicações que lidam com grandes volumes de dados textuais. Tradicionalmente, desenvolvedores recorrem ao operador LIKE ou ILIKE para encontrar padrões em strings, mas essa abordagem possui limitações significativas: não entende variações linguísticas, não ordena resultados por relevância e não escala bem em grandes volumes.

O PostgreSQL oferece um recurso nativo chamado Full-text Search (FTS) que resolve esses problemas. FTS é um sistema de busca textual que realiza três operações fundamentais: tokenização (quebra do texto em tokens), stemming (redução das palavras à sua raiz) e ranking (ordenação por relevância).

Diferente do LIKE, que simplesmente verifica se uma substring existe, o FTS compreende a estrutura linguística do texto. Por exemplo, ao buscar por "correndo", o FTS também encontrará documentos contendo "correr" ou "correu", pois reconhece que pertencem à mesma raiz.

Quando usar FTS no PostgreSQL:
- Aplicações que precisam de busca textual em documentos, artigos, descrições de produtos
- Sistemas que exigem ordenação por relevância dos resultados
- Cenários onde o volume de dados é moderado (até centenas de milhões de registros)

Limitações:
- Não suporta fuzzy search (busca aproximada) nativamente
- Não possui análise de sinônimos embutida
- Para buscas em tempo real com alta concorrência, soluções especializadas como Elasticsearch podem ser mais adequadas

2. Fundamentos: tsvector e tsquery

O coração do FTS no PostgreSQL são dois tipos de dados: tsvector e tsquery.

Criando um tsvector:

O tsvector representa o texto processado, contendo os lexemas (palavras normalizadas) e suas posições no documento.

SELECT to_tsvector('portuguese', 'O gato correu rapidamente pelo telhado');
-- Resultado: 'corr':3 'gato':2 'rapid':4 'telh':6

Observe que artigos e preposições (como "o" e "pelo") foram removidos, e palavras como "correu" foram reduzidas à raiz "corr".

Criando consultas com tsquery:

O tsquery representa a consulta de busca, também processada linguisticamente.

SELECT to_tsquery('portuguese', 'correr & gato');
-- Resultado: 'corr' & 'gato'

A função plainto_tsquery() é mais simples, convertendo texto puro em tsquery:

SELECT plainto_tsquery('portuguese', 'correr gato');
-- Resultado: 'corr' & 'gato'

Operadores de correspondência:

O operador @@ verifica se um tsvector corresponde a um tsquery:

SELECT to_tsvector('portuguese', 'O gato correu') @@ to_tsquery('portuguese', 'correr & gato');
-- Resultado: true

Para combinar consultas, use && (AND) e || (OR):

SELECT to_tsvector('portuguese', 'O gato correu') @@ to_tsquery('portuguese', 'correr | cachorro');
-- Resultado: true (porque "correr" está presente)

3. Configurações de Idioma e Stop Words

O PostgreSQL suporta múltiplos idiomas através de configurações de texto. Cada configuração define regras de tokenização, stemming e lista de stop words.

Configurações comuns:

SELECT to_tsvector('english', 'The cats are running');
-- Resultado: 'cat':2 'run':4

SELECT to_tsvector('portuguese', 'Os gatos estão correndo');
-- Resultado: 'gato':2 'corr':4

Criando uma configuração personalizada:

Para criar uma configuração que, por exemplo, não remova stop words:

CREATE TEXT SEARCH CONFIGURATION public.portuguese_sem_stop (COPY = pg_catalog.portuguese);
ALTER TEXT SEARCH CONFIGURATION public.portuguese_sem_stop ALTER MAPPING FOR word, asciiword WITH portuguese_stem;

Impacto das stop words:

Stop words são palavras comuns (artigos, preposições, conjunções) que geralmente não agregam valor à busca. Por padrão, o PostgreSQL as remove durante a indexação. Isso reduz o tamanho do índice e melhora o desempenho, mas pode ser problemático se a busca depender dessas palavras (ex: busca por "ser" como verbo).

4. Indexação para Full-text Search

Para que o FTS seja eficiente em grandes volumes, é essencial criar índices adequados. O PostgreSQL oferece o índice GIN (Generalized Inverted Index), otimizado para buscas em tsvector.

Criando um índice GIN em uma coluna tsvector:

Primeiro, crie uma coluna do tipo tsvector:

ALTER TABLE artigos ADD COLUMN texto_busca tsvector 
    GENERATED ALWAYS AS (to_tsvector('portuguese', conteudo)) STORED;

Depois, crie o índice GIN:

CREATE INDEX idx_artigos_texto_busca ON artigos USING GIN (texto_busca);

Índices funcionais:

Se não quiser criar uma coluna extra, use um índice funcional diretamente:

CREATE INDEX idx_artigos_fts ON artigos USING GIN (to_tsvector('portuguese', conteudo));

A consulta deve usar a mesma expressão:

SELECT * FROM artigos 
WHERE to_tsvector('portuguese', conteudo) @@ to_tsquery('portuguese', 'correr');

5. Ranking e Relevância de Resultados

A função ts_rank() calcula a relevância de um documento para uma consulta, baseada na frequência e posição dos termos.

Ordenando por relevância:

SELECT titulo, ts_rank(texto_busca, to_tsquery('portuguese', 'correr & gato')) AS relevancia
FROM artigos
WHERE texto_busca @@ to_tsquery('portuguese', 'correr & gato')
ORDER BY relevancia DESC
LIMIT 10;

Destacando termos com headline():

A função headline() retorna um trecho do texto com os termos encontrados destacados (por padrão, entre <b> tags):

SELECT titulo, 
       ts_headline('portuguese', conteudo, to_tsquery('portuguese', 'correr & gato'),
                   'MaxWords=50, MinWords=20, StartSel=<mark>, StopSel=</mark>') AS trecho
FROM artigos
WHERE texto_busca @@ to_tsquery('portuguese', 'correr & gato')
ORDER BY ts_rank(texto_busca, to_tsquery('portuguese', 'correr & gato')) DESC
LIMIT 5;

Usando ts_rank_cd():

A variante ts_rank_cd() (coverage density) considera a densidade de cobertura, sendo útil para documentos longos:

SELECT titulo, ts_rank_cd(texto_busca, to_tsquery('portuguese', 'correr')) AS relevancia
FROM artigos
WHERE texto_busca @@ to_tsquery('portuguese', 'correr')
ORDER BY relevancia DESC;

6. Boas Práticas e Casos de Uso Avançados

Colunas geradas tsvector para desempenho:

Usar colunas geradas (GENERATED ALWAYS AS ... STORED) é a abordagem recomendada, pois o tsvector é atualizado automaticamente quando o texto muda, sem necessidade de triggers manuais.

FTS em múltiplas colunas com setweight():

Para buscar em várias colunas (ex: título e corpo) dando pesos diferentes:

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

Agora, termos no título (peso A) têm mais relevância que no corpo (peso B).

Combinando FTS com filtros adicionais:

SELECT a.titulo, a.data_publicacao, u.nome AS autor
FROM artigos a
JOIN usuarios u ON a.autor_id = u.id
WHERE a.texto_busca @@ to_tsquery('portuguese', 'correr')
  AND a.data_publicacao >= '2024-01-01'
  AND a.categoria_id = 5
ORDER BY ts_rank(a.texto_busca, to_tsquery('portuguese', 'correr')) DESC
LIMIT 20;

7. Comparação com Alternativas e Limitações

FTS no PostgreSQL vs. Elasticsearch/Solr:

Característica PostgreSQL FTS Elasticsearch
Configuração Nativo no banco Serviço externo
Escalabilidade Vertical (até centenas de milhões) Horizontal (bilhões)
Fuzzy search Não nativo Nativo
Sinônimos Requer extensões Nativo
Tempo real Transacional Quase tempo real

Limitações do FTS nativo:

  • Fuzzy search: O FTS padrão não encontra "correr" se o usuário digitar "corer". Para isso, use a extensão pg_trgm com índice GIN trigram.
  • Sinônimos: Não há suporte nativo. É necessário criar dicionários personalizados.
  • Acentuação: Depende da configuração de idioma. Em português, "coração" e "coracao" são tratados como diferentes.

Extensão pg_trgm para busca aproximada:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_artigos_trgm ON artigos USING GIN (conteudo gin_trgm_ops);

SELECT * FROM artigos 
WHERE conteudo % 'corer';  -- encontra "correr" com similaridade

Referências