Se você já tentou buscar texto com LIKE '%palavra%' em PostgreSQL, sabe que o desempenho despenca conforme a tabela cresce. A busca full-text nativa resolve isso com tsvector e tsquery, além de oferecer ranking por relevância. O resultado esperado: consultas rápidas mesmo em milhões de registros, com suporte a stemming, stop words e operadores booleanos. Antes de começar, você precisa de um banco PostgreSQL 8.3 ou superior e uma tabela com pelo menos uma coluna de texto.
Passo 1: Crie uma coluna tsvector
O primeiro passo é gerar um vetor de busca a partir do texto original. Use a função to_tsvector() com o idioma adequado. Para português brasileiro, o dicionário padrão já cobre radicais e stop words.
ALTER TABLE artigos ADD COLUMN busca tsvector GENERATED ALWAYS AS (to_tsvector('portuguese', titulo || ' ' || corpo)) STORED;
Dica: use coluna gerada (GENERATED ALWAYS AS ... STORED) para que o PostgreSQL mantenha o vetor atualizado automaticamente em inserts e updates. Evite triggers manuais, que são mais propensas a erros.
Erro comum: esquecer de incluir todas as colunas relevantes na concatenação. Se o título e o corpo estão separados, busque nos dois. Se pular uma, o conteúdo fica invisível para a busca.
Passo 2: Crie um índice GIN
Sem índice, cada consulta fará uma varredura sequencial (seq scan). O índice GIN (Generalized Inverted Index) é o recomendado para tsvector.
CREATE INDEX idx_busca_gin ON artigos USING GIN (busca);
Dica: o índice GIN é mais lento para escrever, mas muito mais rápido para ler. Se sua tabela sofre muitas inserções em lote, considere criar o índice após o carregamento dos dados.
Erro comum: usar índice GiST no lugar de GIN. GiST é mais leve em escrita, mas até 3x mais lento em consultas de texto. A documentação oficial do PostgreSQL recomenda GIN para full-text search na maioria dos casos.
Passo 3: Faça a consulta com tsquery
A função to_tsquery() converte uma string de busca em um formato que o PostgreSQL entende. Use operadores como & (E), | (OU) e ! (NÃO).
SELECT titulo, ts_rank(busca, query) AS relevancia FROM artigos, to_tsquery('portuguese', 'cachorro & gato') AS query WHERE busca @@ query ORDER BY relevancia DESC;
Dica: use ts_rank() ou ts_rank_cd() para ordenar resultados por relevância. A primeira considera densidade de termos; a segunda, cobertura de documento. Teste ambas no seu cenário.
Erro comum: não usar o operador @@ e tentar comparar tsvector com string diretamente. O PostgreSQL retorna erro de tipo. Sempre converta a string com to_tsquery() antes.
Passo 4: Trate a entrada do usuário
Nunca passe a string digitada pelo usuário diretamente para to_tsquery(). Um caractere especial como : ou ! pode quebrar a consulta ou pior, permitir injeção. Use plainto_tsquery() para converter texto simples sem operadores.
SELECT * FROM artigos WHERE busca @@ plainto_tsquery('portuguese', 'cachorro gato');
Dica: plainto_tsquery() insere & entre as palavras automaticamente. Para busca mais flexível (com operadores), use to_tsquery() após sanitizar a entrada com regexp_replace().
Erro comum: ignorar acentos e caracteres especiais. O dicionário de português do PostgreSQL já lida com acentos, mas se você precisa de busca fonética ou tolerante a erros de digitação, considere extensões como pg_trgm.
Passo 5: Ajuste o ranking com pesos
Nem todo campo tem a mesma importância. O título deve pesar mais que o corpo. Use setweight() para atribuir pesos A, B, C ou D.
ALTER TABLE artigos ADD COLUMN busca_pesada tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('portuguese', titulo), 'A') || setweight(to_tsvector('portuguese', corpo), 'B') ) STORED;
Dica: pesos são aplicados na função ts_rank(). Você pode ajustar a importância relativa com o quarto parâmetro da função, um array de pesos (ex: {0.1, 0.2, 0.4, 1.0} para D, C, B, A).
Erro comum: inverter a ordem dos pesos no array. A ordem é D, C, B, A. Se colocar {1.0, 0.4, 0.2, 0.1}, o peso A valerá 0.1, o oposto do desejado.
Passo 6: Monitore e otimize
Após implementar, verifique se o índice está sendo usado. Use EXPLAIN ANALYZE na consulta.
EXPLAIN ANALYZE SELECT titulo FROM artigos WHERE busca @@ to_tsquery('portuguese', 'exemplo');
Dica: se o plano mostrar Seq Scan, o índice não está sendo usado. As causas comuns são: coluna não indexada, idioma diferente no índice e na consulta, ou tabela pequena demais (o PostgreSQL pode preferir seq scan).
Erro comum: esquecer de rodar ANALYZE após criar o índice. O otimizador precisa de estatísticas atualizadas para decidir pelo índice.
Checklist do que foi feito
- [ ] Coluna tsvector criada como gerada (STORED)
- [ ] Índice GIN criado na coluna tsvector
- [ ] Consultas usando
@@eto_tsquery()ouplainto_tsquery() - [ ] Entrada do usuário sanitizada
- [ ] Pesos aplicados para ranking por relevância
- [ ]
EXPLAIN ANALYZEconfirmou uso do índice
Perguntas frequentes
Qual a diferença entre tsvector e tsquery?
tsvector é o vetor de termos extraídos do documento (com stemming e remoção de stop words). tsquery é a expressão de busca do usuário, também processada pelo mesmo dicionário. A comparação entre os dois usa o operador @@.
Preciso instalar alguma extensão?
Não. A busca full-text é nativa do PostgreSQL desde a versão 8.3. Nenhuma extensão extra é necessária, a menos que você queira funcionalidades como busca fonética (pg_trgm) ou dicionários customizados.
Como lidar com busca em múltiplos idiomas?
Crie colunas tsvector separadas para cada idioma ou use um dicionário que suporte múltiplos idiomas. Na consulta, especifique o idioma em to_tsquery('english'...). O índice GIN funciona com qualquer combinação.
O índice GIN deixa a inserção muito lenta?
Sim, o GIN tem custo maior de escrita que uma árvore B-tree. Para tabelas com muitas inserções, use o parâmetro gin_pending_list_limit para controlar o buffer de escritas pendentes. Em cenários de alta carga, considere índices parciais.
Como fazer busca por frase exata?
Use o operador <-> (distância) no tsquery. Por exemplo, 'cachorro <-> gato' busca as palavras nessa ordem com distância de até 1 termo. Para distância maior, use <N>, como 'cachorro <3> gato'.
O que fazer se a busca não retorna resultados esperados?
Verifique se o dicionário de idioma está correto. Teste com ts_debug('portuguese', 'sua frase') para ver como o texto é tokenizado e quais stop words são removidas. Ajuste o dicionário ou use simple se precisar de busca literal.