Pesquisa em todo lugar no PostgreSQL: busca por texto completo, trigramas e índices parciais

O que “pesquisa em todo lugar” realmente significa para ferramentas internas

Em uma tela interna, “pesquisa em todo lugar” geralmente significa: “Ajude-me a encontrar o registro exato que eu estou pensando, rápido, mesmo que eu não lembre perfeitamente.” Pessoas não estão navegando — estão tentando ir direto para um cliente, ticket, fatura ou dispositivo.

É por isso que uma busca lenta parece pior do que uma página lenta. Um carregamento de página acontece uma vez. Busca acontece muitas vezes seguidas, frequentemente enquanto alguém está em uma chamada ou fazendo triagem. Se os resultados levam 2–3 segundos, os usuários alteram a consulta, apagam caracteres, tentam outro termo, e você acaba com mais carga e mais frustração.

A partir de uma única caixa de busca, os usuários esperam um conjunto de comportamentos: correspondências parciais ("alex" encontra "Alexander"), tolerância a pequenos erros ("microsfot" ainda encontra "Microsoft"), ordenação sensata por “melhor resultado” (IDs ou emails exatos sobem para o topo), um pouco de viés de recência e filtros aplicados por padrão (tickets abertos, clientes ativos).

O complicado é que uma entrada muitas vezes oculta múltiplas intenções. Um agente pode colar um número de ticket, digitar um fragmento de nome, buscar um email ou inserir um telefone. Cada intenção quer uma estratégia diferente, índices diferentes e às vezes uma regra de ranqueamento diferente.

Então não comece pelos índices. Comece listando as poucas intenções de busca que os usuários realmente têm, e separe campos de identidade (IDs, emails) de campos difusos (nomes, assuntos) e de texto longo (notas).

Comece nomeando os dados e os comportamentos de busca

Antes de escolher um índice, escreva o que as pessoas digitam de fato. “Pesquisa PostgreSQL em todo lugar” soa como um recurso único, mas normalmente é uma mistura de buscas bem diferentes.

Ferramentas internas misturam identificadores “duros” (ID de pedido, número de ticket, código de fatura) com texto “macio” (nome do cliente, email, notas, tags). Esses grupos se comportam de forma diferente no PostgreSQL, então tratá-los igual é um caminho rápido para consultas lentas.

Em seguida, separe os comportamentos:

• Busca exata: alguém procurando TCK-104883 espera um resultado preciso.
• Busca difusa: alguém digitando john smth quer uma correspondência tolerante entre nomes (e talvez emails) e vai varrer uma lista curta.
• Busca guiada por filtros: alguém selecionando “Status = Open” e “Assigned to = Me” está principalmente filtrando; a caixa de texto é secundária.

Decida cedo se os resultados precisam ser ranqueados (melhores correspondências primeiro) ou simplesmente filtrados. Ranqueamento importa para notas e descrições mais longas. Para IDs e emails, ranqueamento costuma parecer aleatório e adiciona custo.

Uma lista curta de verificação geralmente basta:

• Quais campos são pesquisados todo dia?
• Quais entradas são exatas (IDs, códigos), difusas (nomes) ou texto longo (notas)?
• Quais filtros se aplicam em quase toda busca?
• Você precisa de ordenação por “melhor correspondência” ou qualquer correspondência é aceitável?
• Quão rápido a tabela vai crescer: milhares, centenas de milhares ou milhões?

Se você nomear essas decisões desde o início, as escolhas de índice depois deixam de parecer um palpite.

O básico: buscas exatas e por que ILIKE costuma atrapalhar

Garanta as vitórias fáceis primeiro. Para muitas telas internas, um índice B-tree simples já dá resultados instantâneos para correspondências exatas como IDs, números de pedido, emails e referências externas.

Se as pessoas colam um valor exato, assegure que sua consulta seja realmente exata. WHERE id = ... ou WHERE email = ... pode ser extremamente rápido com um índice normal. Um índice único em email costuma pagar duas vezes: velocidade e melhor qualidade de dados.

O problema começa quando “pesquisa em todo lugar” vira silenciosamente ILIKE. Uma consulta como name ILIKE '%ann%' tem curinga à esquerda, então o PostgreSQL não consegue usar um índice B-tree normal. Acaba verificando muitas linhas e fica previsivelmente mais lento conforme a tabela cresce.

Busca por prefixo pode funcionar, mas apenas quando o padrão está ancorado no começo: name ILIKE 'ann%'. Mesmo assim, detalhes importam (collation, tratamento de maiúsculas/minúsculas e se você indexou a mesma expressão que consulta). Se sua UI precisa ser case-insensitive, uma abordagem comum é consultar lower(name) e criar um índice correspondente em lower(name).

Também ajuda concordar sobre o que “rápido” significa:

• Cerca de 200 ms ou menos para o trabalho do banco com cache quente
• Menos de 1 segundo fim-a-fim incluindo rede e renderização
• Nenhum estado de carregamento visível para buscas comuns

Com metas assim, fica mais fácil decidir se você pode ficar com buscas exatas e por prefixo ou se é hora de full-text ou índices trigram.

Quando a busca por texto completo é a ferramenta certa

Busca por texto completo é o melhor encaixe quando as pessoas digitam linguagem natural e esperam que o sistema encontre os itens certos, não apenas correspondências exatas. Pense em mensagens de tickets, notas internas, descrições longas, artigos de base de conhecimento e registros de chamadas.

A grande vantagem é o ranqueamento. Em vez de retornar uma lista longa onde o melhor resultado está enterrado, full-text pode ordenar por relevância. Em ferramentas internas isso importa: alguém precisa de uma resposta em segundos, não depois de vasculhar 50 linhas.

Em alto nível, full-text tem três partes móveis:

• Um tsvector (o texto pesquisável, armazenado ou gerado)
• Um tsquery (o que o usuário digitou, convertido em consulta)
• Uma configuração de linguagem (como as palavras são normalizadas)

A configuração de linguagem é onde o comportamento fica visível. PostgreSQL remove stop words comuns (como “o”, “e”) e aplica stemming, então “pay”, “paid” e “payment” podem casar. Isso é ótimo para notas e mensagens, mas pode surpreender quando alguém busca uma palavra curta comum e não encontra nada.

Sinônimos são outro ponto de decisão. Ajudam quando a empresa usa palavras diferentes para a mesma coisa (por exemplo, “refund” vs “chargeback”), mas exigem manutenção. Mantenha a lista de sinônimos curta e baseada no que suporte ou ops realmente digitam.

Um exemplo prático: buscar “can’t login after reset” deveria trazer tickets onde a mensagem diz “cannot log in after password reset”, mesmo com redações diferentes. Esse comportamento de “encontrar o relevante” é o que full-text entrega e costuma ser melhor do que tentar fazer ILIKE agir como um motor de busca.

Quando índices trigram ganham

Índices trigram são uma escolha forte quando usuários digitam fragmentos, cometem erros de digitação ou só lembram “algo assim”. Eles brilham em campos curtos onde full-text é muito rígido: nomes de pessoas, nomes de empresas, assuntos de tickets, SKUs, números de pedido e códigos de produto.

Um trigram é um bloco de 3 caracteres. PostgreSQL compara duas strings pelo número de trigramas que compartilham. Por isso consegue casar "Jon Smth" com "John Smith", ou "ACM" com "ACME", e encontrar resultados quando a consulta está no meio de uma palavra.

Muitas vezes esse é o caminho mais rápido para uma caixa de busca tolerante quando a tarefa é “encontre a linha certa”, não “encontre documentos sobre um tópico”.

Onde supera full-text

Full-text é ótimo para texto mais longo e ranqueamento por significado, mas não lida naturalmente com strings parciais e pequenos erros em campos curtos. Trigram foi feito para esse tipo de fuzziness.

Mantenha o custo de escrita razoável

Índices trigram são maiores e adicionam overhead em operações de escrita, então seja seletivo. Indexe as colunas que as pessoas realmente usam:

• Nome, email, empresa, username
• Identificadores curtos (SKU, código, referência)
• Um campo de título conciso (não um campo grande de notas/comentários)

Se você consegue nomear os campos exatos que a equipe digita na caixa de busca, geralmente dá para manter o índice trigram pequeno e rápido.

Índices parciais para os filtros que as pessoas realmente usam

Uma caixa “search everywhere” normalmente tem padrões escondidos. Pessoas buscam dentro de um workspace, em itens ativos e com deletados excluídos. Se esses filtros estão presentes em quase toda requisição, acelere o caso comum indexando só as linhas que importam.

Um índice parcial é um índice normal com uma cláusula WHERE. PostgreSQL o mantém menor porque só armazena entradas para as linhas que você mais usa. Isso costuma significar menos páginas para ler e melhores taxas de hit no cache.

Alvos comuns de índices parciais incluem linhas ativas (status = 'active'), soft deletes (deleted_at IS NULL), escopo por tenant e janelas de “recentes” (por exemplo, últimos 90 dias).

A chave é combinar com sua UI. Se a tela sempre oculta linhas deletadas, suas consultas devem sempre incluir deleted_at IS NULL, e o índice parcial deve usar a mesma condição. Pequenos desalinhamentos, como usar is_deleted = false em um lugar e deleted_at IS NULL em outro, podem impedir que o planner use o índice.

Índices parciais também funcionam junto com full-text e trigram. Por exemplo, indexar busca por texto apenas para linhas não deletadas mantém o tamanho do índice sob controle.

Trade-off: índices parciais ajudam menos em consultas raras. Se alguém eventualmente busca entre registros deletados ou em todos os workspaces, o PostgreSQL pode cair para um plano mais lento. Trate isso com um caminho separado só para admin, ou adicione um segundo índice se a consulta rara virar comum.

Misturar abordagens sem transformar a busca em mistério

A maioria das equipes acaba misturando técnicas porque uma caixa precisa lidar com diferentes intenções. O objetivo é deixar a ordem de operações clara para que os resultados pareçam previsíveis.

Uma ordem de prioridade simples ajuda, seja implementada como consultas separadas ou como uma única consulta com lógica CASE clara.

Uma escada de prioridade previsível

Comece estrito e vá ficando mais permissivo só se necessário:

• Primeiro correspondência exata (IDs, email, número de ticket, SKU) usando índices B-tree
• Depois busca por prefixo onde fizer sentido
• Em seguida busca trigram para erros e fragmentos em nomes e títulos
• Por último full-text para notas longas, descrições e conteúdo livre

Quando você segue a mesma escada, os usuários aprendem o que a caixa “significa”. Eles param de achar que o sistema quebrou quando “12345” encontra um ticket instantaneamente enquanto “refund policy” busca texto por mais tempo.

Filtre primeiro, depois aplique fuzzy

Busca difusa fica cara quando precisa considerar a tabela inteira. Reduza o conjunto de candidatos com os filtros que as pessoas realmente usam (status, time atribuído, intervalo de datas, conta), depois rode trigram ou full-text no que restou. Mesmo um índice trigram rápido pode parecer lento se precisar pontuar milhões de linhas.

Também vale a pena escrever uma regra de uma frase que colegas não técnicos entendam, do tipo: “A gente confere número de ticket exatamente, depois nome do cliente com tolerância a erro, depois nota.” Essa definição compartilhada evita discussões depois sobre por que uma linha apareceu.

Passo a passo: escolha uma abordagem e implemente com segurança

Uma caixa de busca rápida é um conjunto de pequenas decisões. Escreva-as primeiro e o trabalho com o banco fica mais simples.

1. Defina as entradas. É só uma caixa, ou uma caixa mais filtros (status, responsável, intervalo)?
2. Escolha tipos de correspondência por campo. IDs e códigos querem buscas exatas. Nomes e emails frequentemente precisam de prefixo ou correspondência difusa. Notas longas e descrições ficam melhores com busca por linguagem natural.
3. Adicione os índices certos e confirme que são usados. Crie o índice e depois verifique sua consulta real com EXPLAIN (ANALYZE, BUFFERS).
4. Adicione ranqueamento ou ordenação que reflita a intenção. Se usuário digita “invoice 1042”, correspondências exatas devem subir. Se digita um nome com erro, ranking por similaridade deve vencer.
5. Teste com consultas reais. Tente erros de digitação, termos muito curtos (como “al”), texto longo colado, entrada vazia e modo “só filtros”.

Para lançar com segurança, mude uma coisa por vez e mantenha rollback fácil. Para novos índices em tabelas grandes, prefira CREATE INDEX CONCURRENTLY para não bloquear writes. Se possível, libere por feature flag e compare latência antes e depois.

Um padrão prático para “PostgreSQL search everywhere” é: correspondência exata primeiro (rápida e precisa), trigram para campos “humanos” onde há erros de digitação, e full-text para texto longo que se beneficia de ranqueamento.

Um exemplo realista: uma caixa de busca no painel de suporte

Imagine um painel de suporte onde a equipe tem uma caixa de busca, mas espera encontrar clientes, tickets e até notas. Esse é o clássico problema de “uma entrada, muitos significados”.

A primeira vitória é tornar a intenção visível sem adicionar atrito. Se a consulta parece um email ou telefone, trate como busca por cliente. Se parece um ID de ticket (por exemplo, "TKT-10482"), direcione direto para tickets. O resto cai para busca por texto em assunto de ticket e notas.

Para busca de cliente, índices trigram geralmente funcionam melhor. Nomes e strings de empresa são bagunçados, e as pessoas digitam fragmentos. Um índice trigram torna buscas como “jon smi” ou “acm” rápidas e tolerantes.

Para notas de ticket, use full-text. Notas são sentenças reais e normalmente você quer correspondências relevantes, não apenas “contém essa substring.” Rankeamento ajuda quando dezenas de tickets mencionam a mesma palavra-chave.

Filtros importam mais do que a maioria das equipes espera. Se agentes vivem em “tickets abertos”, adicione um índice parcial que cubra apenas linhas abertas. Faça o mesmo para “clientes ativos”. Isso mantém os índices menores e torna o caminho comum rápido.

Consultas muito curtas merecem regras, senão o banco faz trabalho caro por ruído:

• 1–2 caracteres: mostre tickets abertos recentes e clientes atualizados recentemente
• 3+ caracteres: rode trigram para campos de cliente e full-text para texto de ticket
• Sem intenção clara: mostre uma lista mista, mas limite por grupo (por exemplo, 10 clientes e 10 tickets)

Erros comuns que deixam a busca lenta ou confusa

A maioria dos bugs “por que a busca está lenta?” é auto infligida. O objetivo não é indexar tudo, é indexar o que as pessoas realmente fazem.

Uma armadilha comum é adicionar índices em muitas colunas “só por precaução”. Leitura melhora, mas cada insert e update passa a ter trabalho extra. Em ferramentas internas onde registros mudam o dia todo (tickets, pedidos, usuários), a velocidade de escrita importa.

Outro erro é usar full-text quando o que você precisa é busca tolerante a erros em nomes ou emails. Full-text é ótimo para documentos e descrições. Não é uma correção mágica para “Jon” vs “John” ou “gmail.con” vs “gmail.com.” Isso geralmente é território de trigram.

Filtros também podem quebrar seu plano silenciosamente. Se a maioria das buscas acontece com um filtro fixo (como status = 'open' ou org_id = 42), o melhor índice pode ser um índice parcial que combine essa condição. Se você esquecer, PostgreSQL pode escanear muito mais linhas do que espera.

Alguns erros que aparecem repetidamente:

• Adicionar muitos índices sem medir o custo em escrita
• Esperar que full-text se comporte como autocomplete tolerante a erros
• Ignorar como filtros comuns mudam qual índice pode ser usado
• Testar com dados pequenos e limpos em vez de frequências reais de termos
• Ordenar por uma coluna sem índice de suporte, forçando um sort lento

Exemplo: uma tela de suporte busca tickets por assunto, nome do cliente e número do ticket, depois ordena por última atividade. Se latest_activity_at não estiver indexado para o conjunto filtrado (por exemplo, tickets abertos), essa ordenação pode anular a velocidade ganha pelo índice de busca.

Checagens rápidas antes de lançar

Antes de considerar a função “search everywhere” pronta, seja concreto sobre o comportamento prometido.

• As pessoas procuram um registro por identificador exato (número de ticket, email)?
• Esperam correspondência difusa para erros de digitação?
• Querem resultados ranqueados a partir de notas e descrições longas?

Se você mistura modos, decida qual vence em caso de conflito.

Depois identifique os 2–3 campos que dirigem a maioria das buscas. Se 80% das buscas são por email, nome e ID de ticket, otimize esses primeiro e trate o resto como secundário.

Uma pequena lista de pré-lançamento:

• Confirme o modo de correspondência principal por campo (lookup exato, correspondência difusa ou texto ranqueado)
• Liste os filtros que os usuários aplicam diariamente e assegure que índices cubram essas combinações
• Decida como tratar buscas muito curtas e entradas vazias (por exemplo, exigir 2–3 caracteres para fuzzy; mostrar “recentes” quando vazio)
• Torne a ordenação explicável: mais recente, melhor correspondência de texto ou uma regra combinada simples

Finalmente, teste com tamanho e tempo realistas de dados, não apenas correção. Uma consulta instantânea com 1.000 linhas pode arrastar com 1.000.000.

Próximos passos: transforme o plano em uma tela de busca interna rápida

Uma caixa de busca permanece rápida quando a equipe concorda sobre o que ela deve fazer. Escreva as regras em linguagem simples: o que “corresponder” significa (exato, prefixo, tolerante a erros), quais campos são pesquisados e como filtros mudam o conjunto de resultados.

Mantenha um pequeno conjunto de testes com buscas reais e trate-o como uma suíte de regressão. Dez a vinte consultas normalmente bastam: alguns nomes comuns, alguns emails parciais, um erro de digitação, um trecho longo de nota e um caso de “resultado vazio”. Rode-os antes e depois das mudanças para que o trabalho de performance não quebre relevância sem notar.

Se você está construindo ferramentas internas com AppMaster (appmaster.io), ajuda definir essas regras de busca junto ao modelo de dados e à lógica de negócio, para que o comportamento da UI e as escolhas de banco de dados não se desviem conforme os requisitos mudam.