Como buscar em milhões linhas sem Elasticsearch: pg_trgm no PostgreSQL
Conforme comentei no meu último post, segue a continuação da saga de importaçãod e 55m+ de informações em um banco relacional.
Resumo: No CNPJ Aberto eu precisava cobrir razão social, nome fantasia e nome de sócio, com resposta rápida. Elasticsearch resolveria, mas eu queria menos um serviço. Eu deixei tudo no Postgres + pg_trgm.
Por que trigram?
Eu uso a extensão porque ela decompõe a string em trigramas (janelas de 3 caracteres) e me permite indexar similaridade/substring de forma útil.
CREATE EXTENSION pg_trgm;
Onde eu indexei
Criei três frentes, todas com GIN + gin_trgm_ops (meu hot path é leitura; a carga é mensal):
empresas.razao_social(~55M)estabelecimentos.nome_fantasia(~70M)socios.nome_socio(~25M)
Escolhi GIN em vez de GiST porque eu leio muito mais do que atualizo em loop.
Tradeoff: cada índice ficou grande — ordem de GB em disco. Foi o preço que eu paguei por performance.
Como eu monto a query
Uso UNION ALL em cada tabela com ILIKE '%termo%' e LIMIT em cada branch (ex.: 1000). Sem isso, um termo genérico virava varredura infinita.
Devolvo uma lista de empresas (cnpj_basico), então quando o match vem de sócio ou fantasia eu ainda converjo pra empresa certa na hora de montar o resultado.
Por que ILIKE em vez do operador % de similaridade? No meu caso, substring com GIN atrás costuma bater com o que as pessoas digitam (“contém X”) e eu evito ficar tunando threshold de % o tempo todo.
CNPJ numérico
Se a query é só dígitos, eu não passo pelo trigram: eu faço igualdade ou prefixo em cnpj_basico com índice B-tree — pra mim isso vira resposta instantânea.
Cache
Busca textual é cara no meu pipeline (3 ramos + dedup + hidratar). Eu coloquei Redis com TTL curto (minutos) pra query repetida; em produção eu vi hit rate relevante nos termos populares.
Hidratação sem N+1
Depois da lista de cnpj_basico, eu carrego empresa + matriz em poucas queries com IN (...), em vez de um round-trip por linha.
Performance (ordem de magnitude)
| Caso | Sem cache | Com cache |
|---|---|---|
| Nome conhecido | ~100–150 ms | ~2 ms |
| Termo amplo | ~300–400 ms | ~2 ms |
| CNPJ completo | ~5 ms | ~2 ms |
Minha stack aqui: Postgres 16, hardware modesto, sem Meilisearch/ES.
Quando eu não usaria só pg_trgm
Não insistiria só nisso pra busca semântica (“empresas parecidas com Nubank”), relevância tipo BM25 em texto longo, ou dataset que já não cabe confortável no mesmo Postgres, aí eu olharia outra ferramenta.