Busca Full-Text com PostgreSQL e tsvector

A maioria dos projetos que precisa de busca textual começa com LIKE '%termo%'. Funciona por duas semanas. Depois alguém pede busca por sinônimos, ranking por relevância, busca ignorando acentos, e o LIKE vira um gargalo que faz full table scan em toda query.

A reação comum é subir um Elasticsearch. Mas se o seu dataset tem menos de 10 milhões de registros e você não precisa de busca facetada complexa, o PostgreSQL resolve com tsvector e tsquery sem adicionar infraestrutura extra. Você mantém busca e dados no mesmo lugar, com transações ACID, sem sincronização entre serviços.

O que são tsvector e tsquery

O PostgreSQL tem um motor de busca textual embutido desde a versão 8.3. Dois tipos de dados sustentam esse motor:

• tsvector: representação normalizada de um documento. Cada palavra vira um lexema (forma raiz), com posição no texto original.
• tsquery: representação de uma consulta de busca, com operadores lógicos (&, |, !) e busca por prefixo (:*).

A normalização transforma "implementações rápidas" em lexemas como implement e rapid, removendo stopwords e aplicando stemming de acordo com o dicionário configurado.

-- Visualizando como o PostgreSQL normaliza texto em português
SELECT to_tsvector('portuguese', 'As implementações rápidas nem sempre funcionam bem');
-- Resultado: 'bem':6 'funcion':5 'implement':2 'rápid':3
-- "As", "nem", "sempre" foram removidas (stopwords)
-- "implementações" virou "implement" (stemming)

-- tsquery converte a busca do usuário no mesmo formato normalizado
SELECT to_tsquery('portuguese', 'implementação & rápida');
-- Resultado: 'implement' & 'rápid'

O operador @@ faz o match entre os dois:

SELECT to_tsvector('portuguese', 'As implementações rápidas nem sempre funcionam bem')
       @@ to_tsquery('portuguese', 'implementação & rápida');
-- Resultado: true

Modelando a coluna de busca

Existem duas abordagens para armazenar o tsvector: coluna computada em tempo de query ou coluna materializada com trigger. A diferença é direta.

Para a maioria dos casos reais, a coluna materializada com trigger é a escolha certa. A coluna gerada funciona bem quando o vetor depende de campos da mesma linha.

-- Criando a tabela com coluna de busca materializada
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    author_name TEXT NOT NULL,
    search_vector tsvector,
    created_at TIMESTAMPTZ DEFAULT now()
);
 
-- Índice GIN: estrutura invertida que mapeia cada lexema às linhas que o contêm
-- GIN é melhor que GiST para busca textual porque tem precisão exata (sem falsos positivos)
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

Trigger para manter o vetor sincronizado

O PostgreSQL oferece a função tsvector_update_trigger, mas ela não permite pesos diferentes por campo. Para dar mais relevância ao título do que ao corpo, use um trigger customizado:

-- Função que combina campos com pesos diferentes
-- 'A' = maior relevância (título), 'B' = média (autor), 'C' = menor (corpo)
CREATE OR REPLACE FUNCTION articles_search_vector_update() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('portuguese', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('portuguese', COALESCE(NEW.author_name, '')), 'B') ||
        setweight(to_tsvector('portuguese', COALESCE(NEW.body, '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_articles_search_vector
    BEFORE INSERT OR UPDATE OF title, body, author_name
    ON articles
    FOR EACH ROW
    EXECUTE FUNCTION articles_search_vector_update();

O COALESCE evita que um campo NULL quebre a concatenação. O BEFORE INSERT OR UPDATE OF garante que o trigger só dispara quando os campos relevantes mudam, não em qualquer UPDATE na linha.

Queries de busca com ranking

A busca simples usa @@. A busca com ranking usa ts_rank ou ts_rank_cd:

-- Busca com ranking ponderado por campo
-- O array {0.1, 0.2, 0.4, 1.0} define pesos para D, C, B, A respectivamente
SELECT
    id,
    title,
    ts_rank(
        search_vector,
        plainto_tsquery('portuguese', 'busca textual'),
        32  -- flag 32: normaliza pelo tamanho do documento
    ) AS rank
FROM articles
WHERE search_vector @@ plainto_tsquery('portuguese', 'busca textual')
ORDER BY rank DESC
LIMIT 20;

A diferença entre to_tsquery e plainto_tsquery importa: to_tsquery exige sintaxe com operadores (&, |), enquanto plainto_tsquery aceita texto livre e conecta os termos com & automaticamente. Para input de usuário, plainto_tsquery é mais seguro. Para busca avançada com operadores, websearch_to_tsquery (PostgreSQL 11+) aceita sintaxe parecida com Google:

-- websearch_to_tsquery aceita aspas para frase exata e "-" para exclusão
SELECT * FROM articles
WHERE search_vector @@ websearch_to_tsquery('portuguese', '"busca textual" -elasticsearch')
ORDER BY ts_rank(search_vector, websearch_to_tsquery('portuguese', '"busca textual" -elasticsearch')) DESC;

Highlight de resultados com ts_headline

O ts_headline gera snippets com os termos encontrados marcados em HTML:

SELECT
    title,
    ts_headline(
        'portuguese',
        body,
        plainto_tsquery('portuguese', 'busca textual'),
        'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15, MaxFragments=2'
    ) AS snippet
FROM articles
WHERE search_vector @@ plainto_tsquery('portuguese', 'busca textual')
LIMIT 10;

Um detalhe que a documentação menciona mas pouca gente absorve: ts_headline roda sobre o texto original (não sobre o tsvector), então ele é computacionalmente caro. Aplique ts_headline só nas linhas já filtradas pelo WHERE, nunca em toda a tabela. O LIMIT antes do ts_headline não ajuda aqui porque o PostgreSQL precisa calcular o headline para ordenar. Se performance for problema, calcule o headline na aplicação.

Integração com Node.js e Prisma

Se o projeto usa Prisma para migrations, a coluna tsvector e o trigger precisam de uma migration SQL raw, já que o Prisma não tem suporte nativo a tsvector:

// prisma/migrations/20240101_add_search_vector/migration.sql
// Execute via prisma migrate com arquivo SQL customizado
 
import { PrismaClient } from '@prisma/client';
 
const prisma = new PrismaClient();
 
// Busca full-text usando $queryRawUnsafe com parametrização
// NUNCA concatene input do usuário direto na query
async function searchArticles(userQuery: string, page: number = 1, pageSize: number = 20) {
  const offset = (page - 1) * pageSize;
 
  const results = await prisma.$queryRaw<
    Array<{ id: number; title: string; snippet: string; rank: number }>
  >`
    SELECT
      id,
      title,
      ts_headline(
        'portuguese',
        body,
        websearch_to_tsquery('portuguese', ${userQuery}),
        'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15'
      ) AS snippet,
      ts_rank(search_vector, websearch_to_tsquery('portuguese', ${userQuery}), 32) AS rank
    FROM articles
    WHERE search_vector @@ websearch_to_tsquery('portuguese', ${userQuery})
    ORDER BY rank DESC
    LIMIT ${pageSize}
    OFFSET ${offset}
  `;
 
  return results;
}

O tagged template literal do Prisma ($queryRaw com backticks) parametriza automaticamente, protegendo contra SQL injection. Se estiver usando o driver pg diretamente, use $1, $2:

import { Pool } from 'pg';
 
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
 
async function searchArticles(userQuery: string, limit: number = 20) {
  // $1 é parametrizado pelo driver, sem risco de injection
  const { rows } = await pool.query(
    `SELECT id, title,
       ts_rank(search_vector, websearch_to_tsquery('portuguese', $1), 32) AS rank
     FROM articles
     WHERE search_vector @@ websearch_to_tsquery('portuguese', $1)
     ORDER BY rank DESC
     LIMIT $2`,
    [userQuery, limit]
  );
  return rows;
}

Busca com unaccent para ignorar acentos

O dicionário portuguese do PostgreSQL faz stemming, mas não remove acentos por padrão. "código" e "codigo" não casam. Para resolver, instale a extensão unaccent e crie um dicionário customizado:

-- Extensão precisa de superuser ou permissão CREATE EXTENSION
CREATE EXTENSION IF NOT EXISTS unaccent;
 
-- Dicionário que aplica unaccent antes do stemming em português
CREATE TEXT SEARCH CONFIGURATION portuguese_unaccent (COPY = portuguese);
 
ALTER TEXT SEARCH CONFIGURATION portuguese_unaccent
    ALTER MAPPING FOR hword, hword_part, word
    WITH unaccent, portuguese_stem;

Depois, use portuguese_unaccent em vez de portuguese no trigger e nas queries. Lembre de recriar o trigger e reindexar:

-- Atualiza todos os registros existentes com a nova configuração
UPDATE articles SET
    search_vector =
        setweight(to_tsvector('portuguese_unaccent', COALESCE(title, '')), 'A') ||
        setweight(to_tsvector('portuguese_unaccent', COALESCE(author_name, '')), 'B') ||
        setweight(to_tsvector('portuguese_unaccent', COALESCE(body, '')), 'C');

Medindo de verdade: 200 mil linhas, EXPLAIN ANALYZE e uma surpresa

Tudo nesta seção roda num PostgreSQL 16 descartável, sem depender de dados seus:

docker run -d --name pg-fts -e POSTGRES_PASSWORD=demo -e POSTGRES_DB=demo -p 5433:5432 postgres:16-alpine

O script cria uma tabela com coluna tsvector gerada (GENERATED ALWAYS AS ... STORED) na configuração portuguese, insere 200.000 linhas com generate_series, mede a busca antes e depois do índice GIN e testa unaccent. Salve como demo.sql e rode com psql -h localhost -p 5433 -U postgres -d demo -f demo.sql.

CREATE EXTENSION IF NOT EXISTS unaccent;
 
CREATE TABLE artigos (
  id serial PRIMARY KEY,
  titulo text NOT NULL,
  corpo text NOT NULL,
  busca tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('portuguese', coalesce(titulo, '')), 'A') ||
    setweight(to_tsvector('portuguese', coalesce(corpo, '')), 'B')
  ) STORED
);
 
INSERT INTO artigos (titulo, corpo)
SELECT
  'Artigo ' || g || CASE WHEN g % 97 = 0 THEN ' sobre migrações seguras em produção'
    ELSE ' sobre ' || (ARRAY['cache','filas','autenticação','observabilidade','deploy'])[1 + g % 5] END,
  'Conteúdo gerado ' || g || '. ' || CASE WHEN g % 97 = 0
    THEN 'A migração do banco de dados precisa de rollback planejado e zero downtime.'
    ELSE 'Texto de exemplo com termos variados sobre engenharia de software e produção.' END
FROM generate_series(1, 200000) AS g;
ANALYZE artigos;
 
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)
SELECT id, titulo, ts_rank_cd(busca, q) AS rank
FROM artigos, websearch_to_tsquery('portuguese', 'migração segura') AS q
WHERE busca @@ q
ORDER BY rank DESC
LIMIT 10;
 
CREATE INDEX artigos_busca_gin ON artigos USING gin (busca);
ANALYZE artigos;
 
-- repita o EXPLAIN acima

O que o stemmer português faz com o seu texto

Antes dos planos, o detalhe que mais gera bug em produção. Com a configuração portuguese, singular e plural de "migração" viram tokens diferentes:

SELECT to_tsvector('portuguese', 'As migrações do banco precisam de rollback planejado');
 'banc':4 'migraçõ':2 'planej':8 'precis':5 'rollback':7
 
SELECT plainto_tsquery('portuguese', 'migrações seguras'), websearch_to_tsquery('portuguese', 'migração -cache');
 'migraçõ' & 'segur' | 'migraçã' & !'cach'

migrações vira migraçõ e migração vira migraçã. Uma busca por "migrações" não encontra um texto que só diz "migração", e vice-versa. As linhas do exemplo casam porque o título tem o plural e o corpo tem o singular. Se o seu domínio depende disso, indexe o campo duas vezes (com portuguese e com simple) ou normalize os termos mais importantes na aplicação antes de montar a tsquery.

Sem índice: seq scan paralelo

 Limit  (cost=11132.86..11134.02 rows=10 width=37) (actual rows=10 loops=1)
   Buffers: shared hit=9165
   ->  Gather Merge  (actual rows=10 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (actual rows=10 loops=3)
               Sort Method: top-N heapsort  Memory: 26kB
               ->  Parallel Seq Scan on artigos  (actual rows=687 loops=3)
                     Filter: (busca @@ '''migraçã'' & ''segur'''::tsquery)
                     Rows Removed by Filter: 65980
                     Buffers: shared hit=9091
 Planning Time: 0.182 ms
 Execution Time: 30.420 ms

Três workers leem 9.091 páginas do heap e descartam 65.980 linhas cada um. Em 200 mil linhas isso custa 30 ms; em 20 milhões, custa a mesma proporção.

Com GIN: menos páginas, mas não necessariamente mais rápido

 Limit  (cost=100.40..100.42 rows=10 width=37) (actual rows=10 loops=1)
   Buffers: shared hit=2070
   ->  Sort  (actual rows=10 loops=1)
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Bitmap Heap Scan on artigos  (actual rows=2061 loops=1)
               Recheck Cond: (busca @@ '''migraçã'' & ''segur'''::tsquery)
               Heap Blocks: exact=2061
               ->  Bitmap Index Scan on artigos_busca_gin  (actual rows=2061 loops=1)
                     Index Cond: (busca @@ '''migraçã'' & ''segur'''::tsquery)
                     Buffers: shared hit=9
 Planning Time: 0.197 ms
 Execution Time: 58.287 ms

O índice reduz as páginas lidas de 9.165 para 2.070 e resolve a condição em 9 páginas do próprio índice. Mesmo assim, nesta máquina a execução foi mais lenta (58 ms contra 30 ms), porque o plano com índice roda num único processo e ainda visita 2.061 blocos do heap para calcular o ts_rank_cd, enquanto o seq scan usou três processos em paralelo. O índice ganha quando a consulta é seletiva (poucas linhas casam) ou quando o servidor não tem workers de sobra; com 1% das linhas casando e paralelismo disponível, o planejador estimou custo menor com o índice (100 contra 11.132) e errou na prática. Duas lições verificáveis: EXPLAIN sem ANALYZE não conta a história, e ORDER BY rank obriga a ler cada linha candidata, então limite o universo com um filtro adicional (categoria, data) antes de ranquear.

Resultado esperado e unaccent

 id  |                     titulo                     |  rank
-----+------------------------------------------------+--------
  97 | Artigo 97 sobre migrações seguras em produção  | 0.0816
 194 | Artigo 194 sobre migrações seguras em produção | 0.0816
 291 | Artigo 291 sobre migrações seguras em produção | 0.0816

Sem unaccent, "migracao" digitado sem acento não encontra nada; com unaccent nos dois lados da comparação, encontra as 2.061 linhas:

SELECT count(*) FROM artigos WHERE busca @@ plainto_tsquery('portuguese', 'migracao');            -- 0
SELECT count(*) FROM artigos
 WHERE to_tsvector('portuguese', unaccent(titulo || ' ' || corpo)) @@ plainto_tsquery('portuguese', unaccent('migracao'));  -- 2061

Para usar unaccent com índice, crie uma configuração de busca própria (CREATE TEXT SEARCH CONFIGURATION pt (COPY = portuguese) mais ALTER TEXT SEARCH CONFIGURATION pt ALTER MAPPING ... WITH unaccent, portuguese_stem) e gere a coluna com ela; a função unaccent direta na expressão não é imutável e não entra em coluna gerada.

O que NÃO fazer

Erro 1: usar LIKE com wildcard para busca textual

-- ERRADO: full table scan, ignora índices, não faz stemming
SELECT * FROM articles
WHERE title ILIKE '%implement%' OR body ILIKE '%implement%';

Esse pattern força o PostgreSQL a ler cada linha da tabela. Com 500k registros, a query leva segundos. Além disso, ILIKE '%implement%' encontra "implementação" por coincidência de substring, mas não encontra "implementou" nem "implementar".

-- CORRETO: usa índice GIN, faz stemming, retorna todas as flexões
SELECT * FROM articles
WHERE search_vector @@ plainto_tsquery('portuguese', 'implementação');

Erro 2: recalcular tsvector na query sem índice

-- ERRADO: recalcula to_tsvector em cada linha, a cada query
SELECT * FROM articles
WHERE to_tsvector('portuguese', title || ' ' || body)
      @@ plainto_tsquery('portuguese', 'busca');

Sem a coluna materializada, o PostgreSQL não consegue usar o índice GIN. Cada busca recalcula o vetor para todas as linhas. Com trigger e coluna indexada, o cálculo acontece uma vez (no INSERT/UPDATE) e a busca usa o índice.

Erro 3: concatenar input do usuário na query

// ERRADO: SQL injection direto
const results = await pool.query(
  `SELECT * FROM articles
   WHERE search_vector @@ to_tsquery('portuguese', '${userInput}')`
);
 
// CORRETO: parametrização via driver
const results = await pool.query(
  `SELECT * FROM articles
   WHERE search_vector @@ websearch_to_tsquery('portuguese', $1)`,
  [userInput]
);

to_tsquery aceita operadores como & e |. Um input malicioso pode quebrar a sintaxe ou, pior, explorar injection. websearch_to_tsquery é mais tolerante a input malformado, e a parametrização via $1 resolve o vetor de ataque.

Para mais sobre segurança em APIs, o post sobre OWASP Top 10 na prática cobre os vetores mais comuns.

Quando o PostgreSQL não basta

A busca full-text do PostgreSQL tem limites claros:

• Sem busca fuzzy nativa (typo tolerance). "postgre" não encontra "postgresql" sem configuração extra de trigram (pg_trgm).
• Sem faceted search (filtros dinâmicos com contagem por categoria).
• Sem busca semântica (vetores de embedding). Para isso, existe pgvector, mas é outro assunto.
• Performance degrada com mais de 10-20 milhões de documentos grandes, dependendo do hardware.

Se o projeto precisa de autocomplete com tolerância a typos, combine pg_trgm com tsvector. Se precisa de facetas, considere Meilisearch ou Typesense (mais simples que Elasticsearch para a maioria dos casos). Se o dataset cabe no PostgreSQL e a busca é por termos exatos ou flexões, tsvector resolve sem adicionar serviço externo.

Para quem roda PostgreSQL serverless, a configuração funciona igual: o Neon suporta extensões como unaccent e pg_trgm no plano gratuito.

Checklist de implementação

Antes de considerar a busca pronta para produção:

1. Definir a configuração de texto (portuguese, portuguese_unaccent, english) e usar a mesma em trigger e queries.
2. Criar coluna tsvector com trigger BEFORE INSERT OR UPDATE.
3. Criar índice GIN na coluna.
4. Usar websearch_to_tsquery para input de usuário (mais seguro que to_tsquery).
5. Parametrizar queries no código da aplicação.
6. Aplicar ts_headline só nas linhas já filtradas, nunca em SELECT sem WHERE.
7. Monitorar com EXPLAIN ANALYZE se o índice GIN está sendo usado.

Se a busca precisa alimentar um frontend com SSR para SEO, o ranking do PostgreSQL retorna rápido o suficiente para renderizar no servidor sem timeout, desde que o índice GIN esteja ativo.

Para projetos que seguem DDD com TypeScript e Prisma, a busca full-text pode ficar encapsulada em um repository específico, separando a query raw do resto do domínio.

FAQ

Preciso instalar algo extra no PostgreSQL para usar tsvector? Não. tsvector, tsquery, to_tsvector, ts_rank e ts_headline fazem parte do core do PostgreSQL desde a versão 8.3. A extensão unaccent precisa ser habilitada separadamente, mas vem incluída na instalação padrão.

Qual a diferença entre índice GIN e GiST para busca textual? GIN (Generalized Inverted Index) é mais rápido para leitura e tem precisão exata: se o índice diz que o documento contém o termo, contém. GiST é mais rápido para escrita e ocupa menos espaço, mas produz falsos positivos que precisam ser verificados contra a tabela. Para busca textual, GIN é a escolha padrão. GiST faz sentido quando a tabela tem volume altíssimo de escrita e a busca é pouco frequente.

Posso usar tsvector com Prisma sem raw queries? Não diretamente. O Prisma não tem tipo nativo para tsvector nem operador @@. A migration da coluna e do trigger precisa ser SQL raw, e as queries de busca precisam de $queryRaw. Se busca textual é central no projeto, avalie usar o driver pg diretamente para essas queries e manter o Prisma para o resto do CRUD.

Como faço autocomplete (busca enquanto digita) com tsvector? Use o operador de prefixo :* via to_tsquery: to_tsquery('portuguese', 'implement:*') encontra "implementação", "implementar", "implementou". Para autocomplete mais sofisticado com tolerância a typos, combine com a extensão pg_trgm e um índice GIN sobre gin_trgm_ops. As duas abordagens podem coexistir na mesma tabela.

ts_rank é a única forma de ordenar por relevância? Existem duas funções: ts_rank e ts_rank_cd. A diferença é que ts_rank_cd (cover density) considera a proximidade entre os termos no documento, penalizando documentos onde os termos aparecem distantes. Para buscas com múltiplos termos, ts_rank_cd tende a dar resultados mais relevantes. Para termo único, ambas retornam o mesmo valor.

A busca full-text do PostgreSQL não substitui um Elasticsearch em cenários de busca facetada com bilhões de documentos. Mas para a maioria dos projetos: se você já tem PostgreSQL rodando, tem busca textual funcional sem adicionar serviço, sem sincronização, sem fila de indexação. Adicionar complexidade de infraestrutura para resolver um problema que o banco atual resolve é decisão de arquitetura que precisa de justificativa concreta, não de hype sobre "search engine dedicado".