Busca com LIKE que parou de escalar: full-text search no Postgres antes de adicionar um motor de busca

Um índice B-tree guarda os valores ordenados pelo começo do texto. Ele resolve nome LIKE 'paraf%', porque sabe onde começam as palavras que iniciam com "paraf". Ele não resolve nome LIKE '%inox%', porque "inox" pode estar em qualquer posição, e a única forma de saber é ler cada linha. Com 300 mil produtos, ler tudo cabia na memória e custava pouco. Com 4,2 milhões, a consulta lê 310 mil páginas, boa parte do disco, e expulsa do cache as páginas que as outras telas usavam.

-- A busca atual: coringa dos dois lados, sem distinguir acento nem maiuscula.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, nome, preco
FROM produtos
WHERE ativo
  AND (nome ILIKE '%' || 'parafuso inox' || '%' OR descricao ILIKE '%' || 'parafuso inox' || '%')
ORDER BY nome
LIMIT 20;

-- Resultado tipico com 4,2 milhoes de linhas:
--   Limit  (actual time=1793.402..1793.411 rows=20)
--     ->  Sort  (Sort Method: top-N heapsort)
--       ->  Seq Scan on produtos  (rows=11873)
--             Filter: (ativo AND ((nome ~~* '%parafuso inox%') OR (descricao ~~* '%parafuso inox%')))
--             Rows Removed by Filter: 4188127
--             Buffers: shared hit=48211 read=262904
-- O indice B-tree em nome nao serve: ele ordena pelo inicio do texto, e o coringa
-- a esquerda obriga a ler a tabela inteira. "inox parafuso" e "parafusos" nem encontram nada.

O tempo é só metade do problema. O LIKE compara caracteres, não palavras: não entende plural, não ignora acento, exige que os termos apareçam na ordem digitada e não tem noção de relevância. O resultado é ordenado por nome, então o produto que tem "parafuso inox" no título aparece misturado com o que só cita a expressão no meio da descrição. A busca é lenta e, ao mesmo tempo, ruim.

A extensão pg_trgm quebra o texto em trigramas, sequências de três caracteres, e indexa cada uma num índice GIN. Para responder '%inox%', o banco procura as linhas que contêm os trigramas de "inox" e só depois confere o padrão nelas. Não é preciso mudar a consulta: o mesmo ILIKE passa a usar o índice. É a correção certa quando o que o usuário procura é um trecho literal, como código de produto, SKU, número de pedido ou o começo de um nome no autocomplete.

-- 1. Trigramas: indice para LIKE/ILIKE com coringa em qualquer posicao.
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- CONCURRENTLY nao bloqueia escrita; demora mais e nao pode rodar dentro de transacao.
CREATE INDEX CONCURRENTLY produtos_nome_trgm_idx
  ON produtos USING gin (nome gin_trgm_ops);
CREATE INDEX CONCURRENTLY produtos_sku_trgm_idx
  ON produtos USING gin (sku gin_trgm_ops);

-- A mesma consulta de antes, agora usando o indice (Bitmap Index Scan).
SELECT id, nome, sku
FROM produtos
WHERE ativo AND (nome ILIKE '%' || $1 || '%' OR sku ILIKE '%' || $1 || '%')
LIMIT 20;

-- Tolerancia a erro de digitacao: o operador % usa o mesmo indice GIN.
SET pg_trgm.similarity_threshold = 0.35;
SELECT id, nome, similarity(nome, $1) AS parecido
FROM produtos
WHERE ativo AND nome % $1
ORDER BY parecido DESC
LIMIT 20;

• Termos com menos de 3 caracteres geram poucos trigramas e o índice deixa de filtrar. Exija pelo menos 3 caracteres no autocomplete.
• O índice GIN de trigramas costuma ficar entre 1 e 3 vezes o tamanho da coluna. Para 4,2 milhões de nomes curtos, foram 410 MB.
• O operador % (semelhança) também usa o índice e resolve erro de digitação: "parafuzo" encontra "parafuso" com similaridade acima de 0,35.
• Ordenar por distância com <-> só usa índice GiST, não GIN. Com GIN, filtre com % e ordene por similarity() dentro do LIMIT.

Trigrama resolve a lentidão, mas não resolve a qualidade. "Parafusos inox" continua sem achar "Parafuso Inóx", porque plural e acento mudam os trigramas, e a ordem das palavras ainda importa. Para buscar por significado de palavra, e não por trecho de caracteres, o recurso certo é o full-text search.

O full-text search transforma o texto em um tsvector, uma lista de lexemas normalizados com posição: "Parafusos Inóx sextavados" vira algo como 'inox':2 'parafus':1 'sextav':3. A consulta vira um tsquery normalizado da mesma forma, e o operador @@ responde se o documento contém os termos, em qualquer ordem e flexão. O índice GIN sobre o tsvector torna isso uma busca em índice invertido, o mesmo princípio de um motor de busca.

Três decisões fazem a diferença entre uma busca que funciona em português e uma que frustra. A primeira é a configuração: a portuguese padrão não remove acento, então criamos uma cópia que passa o texto pelo dicionário unaccent antes do radical. A segunda é o peso: um termo no nome (A) vale mais do que na marca (B), que vale mais do que na descrição (C), e o ts_rank_cd usa esses pesos para ordenar. A terceira é a forma de criar a coluna: uma coluna GENERATED ALWAYS reescreve a tabela inteira com lock exclusivo, o que em 4,2 milhões de linhas é uma janela de manutenção. Uma coluna comum mantida por trigger pode ser preenchida em lotes, com a loja no ar.

-- 2. Full-text search: palavras normalizadas, sem acento, com radical e peso por campo.
CREATE EXTENSION IF NOT EXISTS unaccent;

-- Configuracao propria: tira acento antes do radical do portugues.
-- "Parafusos sextavados" e "parafuso sextavado" viram os mesmos lexemas.
CREATE TEXT SEARCH CONFIGURATION public.pt_sem_acento (COPY = pg_catalog.portuguese);
ALTER TEXT SEARCH CONFIGURATION public.pt_sem_acento
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, portuguese_stem;

-- Coluna mantida pela aplicacao (e nao GENERATED) para poder preencher em lotes,
-- sem reescrever a tabela de 4,2 milhoes de linhas com lock exclusivo.
ALTER TABLE produtos ADD COLUMN busca tsvector;

CREATE FUNCTION produtos_busca_atualizar() RETURNS trigger
LANGUAGE plpgsql AS $
BEGIN
  NEW.busca :=
    setweight(to_tsvector('public.pt_sem_acento', coalesce(NEW.nome, '')), 'A') ||
    setweight(to_tsvector('public.pt_sem_acento', coalesce(NEW.marca, '')), 'B') ||
    setweight(to_tsvector('public.pt_sem_acento', coalesce(NEW.descricao, '')), 'C');
  RETURN NEW;
END $;

CREATE TRIGGER produtos_busca_trg
  BEFORE INSERT OR UPDATE OF nome, marca, descricao ON produtos
  FOR EACH ROW EXECUTE FUNCTION produtos_busca_atualizar();

-- Preenchimento em lotes de 5 mil ids (repetir avancando a faixa ate o maior id).
-- O UPDATE de nome = nome dispara o trigger sem mudar o dado.
UPDATE produtos SET nome = nome
WHERE id > 0 AND id <= 5000 AND busca IS NULL;

CREATE INDEX CONCURRENTLY produtos_busca_idx ON produtos USING gin (busca);

-- Consulta: websearch_to_tsquery aceita o que o usuario digita ("parafuso inox -zincado").
SELECT id, nome, marca, preco,
       ts_rank_cd(busca, consulta) AS relevancia
FROM produtos,
     websearch_to_tsquery('public.pt_sem_acento', $1) AS consulta
WHERE ativo AND busca @@ consulta
ORDER BY relevancia DESC, id
LIMIT 20;

O websearch_to_tsquery é o que permite colocar a entrada do usuário direto na consulta sem erro de sintaxe: ele aceita aspas para frase exata, OR e o sinal de menos para excluir, e ignora o resto. O to_tsquery, ao contrário, exige operadores explícitos e lança erro com uma entrada como "parafuso inox", o que vira 500 na API. Também vale registrar os termos que retornaram zero resultados: essa lista mostra sinônimos que faltam e produtos mal cadastrados melhor do que qualquer métrica de latência.

Os dois índices se complementam. O full-text resolve a maioria das buscas por palavra, com relevância. Quando ele não acha nada, quase sempre é erro de digitação ou termo que não existe no catálogo, e é aí que a semelhança por trigrama entra, como um "você quis dizer". A função abaixo encapsula essa ordem, limita o tamanho do termo e aplica um statement_timeout local por consulta, para que uma busca patológica nunca segure uma conexão do pool por segundos.

// busca-produtos.js
// Full-text primeiro; se nao achar nada, tenta por semelhanca (erro de digitacao).
// Cada consulta tem teto de tempo proprio: busca lenta nao pode segurar conexao do pool.
import pg from 'pg';

const { Pool } = pg;
export const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 20 });

const SQL_FULL_TEXT = `
  SELECT id, nome, marca, preco, ts_rank_cd(busca, consulta) AS relevancia
  FROM produtos, websearch_to_tsquery('public.pt_sem_acento', $1) AS consulta
  WHERE ativo AND busca @@ consulta
  ORDER BY relevancia DESC, id
  LIMIT $2`;

const SQL_SEMELHANCA = `
  SELECT id, nome, marca, preco, similarity(nome, $1) AS relevancia
  FROM produtos
  WHERE ativo AND nome % $1
  ORDER BY relevancia DESC, id
  LIMIT $2`;

async function consultarComTeto(sql, params, tetoMs) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // SET LOCAL vale so para esta transacao: a conexao volta limpa para o pool.
    await client.query("SELECT set_config('statement_timeout', $1, true)", [String(tetoMs)]);
    await client.query("SELECT set_config('pg_trgm.similarity_threshold', '0.35', true)");
    const { rows } = await client.query(sql, params);
    await client.query('COMMIT');
    return rows;
  } catch (erro) {
    await client.query('ROLLBACK').catch(() => {});
    throw erro;
  } finally {
    client.release();
  }
}

export async function buscarProdutos(termoBruto, { limite = 20, tetoMs = 800 } = {}) {
  const termo = String(termoBruto ?? '').trim().slice(0, 120);
  if (termo.length < 2) return { modo: 'vazio', itens: [] };

  const porTexto = await consultarComTeto(SQL_FULL_TEXT, [termo, limite], tetoMs);
  if (porTexto.length > 0 || termo.length < 3) return { modo: 'full-text', itens: porTexto };

  // Trigrama precisa de pelo menos 3 caracteres para ter seletividade.
  const parecidos = await consultarComTeto(SQL_SEMELHANCA, [termo, limite], tetoMs);
  return { modo: parecidos.length > 0 ? 'semelhanca' : 'sem-resultado', itens: parecidos };
}

Caminho de uma busca depois da mudança.

  usuário digita "parafuzo inox"
          |
          v
  buscarProdutos()  -- termo < 2 caracteres --> resposta vazia
          |
          v
  full-text: busca @@ websearch_to_tsquery('pt_sem_acento')   [índice GIN em busca]
          |
          +-- achou --> ordena por ts_rank_cd, LIMIT 20 --> resposta (modo full-text)
          |
          +-- 0 linhas --> semelhança: nome % termo           [índice GIN trigrama]
                                |
                                +-- achou --> "você quis dizer" (modo semelhança)
                                +-- nada  --> registra termo sem resultado para análise

O set_config com terceiro argumento true equivale a SET LOCAL: o valor morre no fim da transação, então a conexão volta ao pool com os padrões originais. Sem isso, um SET comum vazaria o timeout curto para a próxima requisição que pegasse a mesma conexão, como um relatório que precisa de mais tempo.

A troca precisa de número, e não só de sensação. Rode EXPLAIN (ANALYZE, BUFFERS) com os 50 termos mais buscados do último mês, antes e depois, e acompanhe o p99 da rota de busca e o total de buffers lidos pelo banco. No caso do e-commerce de ferragens, o resultado foi este:

• Termo muito comum: o ranking é calculado para todas as linhas que casam antes do LIMIT. Combine com um filtro seletivo (categoria, ativo, estoque) ou limite os candidatos numa subconsulta antes de ranquear, aceitando ranking aproximado.
• Lista pendente do GIN: com fastupdate ligado, as escritas entram numa lista não indexada que a busca precisa varrer. Em catálogo com importação em massa, ajuste gin_pending_list_limit ou rode VACUUM depois da carga.
• Mudou a configuração de texto: o tsvector gravado não se recalcula sozinho. Qualquer mudança em dicionário ou pesos exige reprocessar a coluna em lotes.
• Stopwords: "de", "para" e "com" são descartadas. Uma busca só por stopwords volta vazia, então trate esse caso na interface em vez de mostrar "nenhum produto".
• Idioma: um catálogo em mais de um idioma precisa de uma coluna de configuração por linha, ou de um tsvector por idioma, porque o radical do português estraga palavras em espanhol.

Elasticsearch, OpenSearch, Meilisearch e Typesense são ótimos, mas trazem um segundo armazenamento que precisa ser sincronizado, consistência eventual entre o que foi salvo e o que aparece na busca, outro cluster para dimensionar e outro ponto de falha no plantão. A troca vale quando a busca deixou de ser uma funcionalidade e virou o produto, e não quando o LIKE ficou lento.

1. Meça o p99 da busca e os buffers lidos com os termos mais frequentes antes de mexer em qualquer coisa.
2. Crie índices GIN de trigramas com CONCURRENTLY para SKU, código e autocomplete.
3. Crie a configuração sem acento e a coluna tsvector com pesos, preenchida por trigger e em lotes.
4. Troque a consulta para websearch_to_tsquery com ts_rank_cd e mantenha a semelhança por trigrama como plano B.
5. Aplique statement_timeout local na rota de busca e registre os termos sem resultado.
6. Só considere um motor dedicado quando facetas, sinônimos ou relevância ajustável virarem requisito recorrente, e planeje a migração com consulta ligada.