Busca fuzzy em 55 milhões de registros: como pg_trgm salvou meu projeto

"Buscar por nome" parece simples até você ter 55 milhões de registros e o usuário esperar resposta em menos de 300ms.

No CNPJ Aberto, os usuários buscam empresas por razão social ("Magazine Luiza"), nome fantasia ("Magalu"), CNPJ ("33.000.167/0001-01"), ou até nome de sócio ("Luiza Helena Trajano"). A busca precisa ser fuzzy (tolerar erros de digitação), rápida, e funcionar em três tabelas diferentes simultaneamente.

Neste post, vou mostrar como fizemos isso sem Elasticsearch — usando apenas PostgreSQL com pg_trgm.

Por que não Elasticsearch?

Elasticsearch é a resposta óbvia para full-text search. Mas:

1. Mais um serviço para manter, monitorar e escalar
2. Sincronização entre PostgreSQL e ES é complexa (lag, inconsistências)
3. Custo — ES consome muita RAM (55M docs = 16+ GB de heap)
4. Complexidade — para o nosso caso de uso, é um canhão para matar formiga

PostgreSQL com pg_trgm resolve o problema com uma fração da complexidade.

O que é pg_trgm?

pg_trgm (trigram) decompõe strings em conjuntos de 3 caracteres consecutivos e calcula similaridade entre eles.

SELECT show_trgm('Magazine');
-- {"  m"," ma","aga","azi","gaz","ine","mag","ne ","zin"}

Quando você busca "Magazin" (sem o 'e'), o PostgreSQL compara os trigramas e encontra alta similaridade com "Magazine". Isso é o que torna a busca tolerante a erros.

CREATE EXTENSION pg_trgm;

A estrutura das tabelas

A busca precisa cruzar três tabelas:

empresas          → razao_social        (55M linhas)
estabelecimentos  → nome_fantasia       (70M linhas)
socios            → nome_socio          (25M linhas)

Detalhe importante: O resultado final que o usuário vê é uma lista de empresas (identificadas por cnpj_basico). Então mesmo que o match venha da tabela de sócios, precisamos retornar a empresa correspondente.

Os indexes GIN

O coração da performance são os indexes GIN com gin_trgm_ops:

CREATE INDEX CONCURRENTLY ix_empresas_razao_trgm 
ON empresas USING gin (razao_social gin_trgm_ops);

CREATE INDEX CONCURRENTLY ix_estab_fantasia_trgm 
ON estabelecimentos USING gin (nome_fantasia gin_trgm_ops);

CREATE INDEX CONCURRENTLY ix_socios_nome_trgm 
ON socios USING gin (nome_socio gin_trgm_ops);

Por que GIN e não GiST? GIN é mais rápido para leitura (que é 99% do nosso caso). GiST é melhor quando há muitas atualizações. Como nossa base atualiza uma vez por mês, GIN é a escolha certa.

Tamanho dos indexes: cada um ocupa ~3-5 GB. Total de ~12 GB só de indexes trigram. Esse é o tradeoff: espaço em disco por velocidade de busca.

A query: UNION ALL com ILIKE

A estratégia é buscar em cada tabela independentemente e depois unir os resultados:

CANDIDATE_LIMIT = 1000

def search_text(query: str, page: int, per_page: int):
    sql = """
        SELECT cnpj_basico FROM empresas 
        WHERE razao_social ILIKE :pattern
        LIMIT :lim

        UNION ALL

        SELECT cnpj_basico FROM estabelecimentos 
        WHERE nome_fantasia ILIKE :pattern
        LIMIT :lim

        UNION ALL

        SELECT cnpj_basico FROM socios 
        WHERE nome_socio ILIKE :pattern
        LIMIT :lim
    """

    candidates = db.execute(sql, {
        "pattern": f"%{query}%", 
        "lim": CANDIDATE_LIMIT
    })

    # Deduplica e pagina
    unique_cnpjs = list(dict.fromkeys(
        row.cnpj_basico for row in candidates
    ))
    page_cnpjs = unique_cnpjs[(page-1)*per_page : page*per_page]

    # Hydrata com dados de exibição
    return hydrate(page_cnpjs)

Por que ILIKE e não o operador % (similarity)?

O operador % do pg_trgm (WHERE razao_social % 'Magazine') calcula similaridade e retorna matches acima de um threshold. É ótimo para fuzzy search puro, mas:

1. ILIKE '%query%' + GIN trigram é surpreendentemente eficiente — o PostgreSQL usa o index GIN para filtrar candidatos pelo trigram e depois aplica o ILIKE como filtro final
2. ILIKE dá matches exatos de substring, que é o que o usuário geralmente quer
3. O threshold do % precisa de tuning fino e pode retornar resultados irrelevantes

O LIMIT por branch

Cada branch do UNION tem LIMIT 1000. Isso é crucial:

• Sem limit, uma query genérica como "COMERCIO" varreria milhões de linhas
• Com limit, mesmo o pior caso retorna em < 500ms
• O cap total (MAX_TOTAL_VISIBLE = 1000) garante que nunca paginamos além de resultados relevantes

Buscas numéricas: atalho por CNPJ

Quando a query é numérica, pulamos o trigram e vamos direto:

def search_cnpj(digits: str):
    if len(digits) >= 8:
        # CNPJ basico completo — equality, instantâneo
        return db.query(Empresa).filter(
            Empresa.cnpj_basico == digits[:8]
        ).all()
    else:
        # CNPJ parcial — prefix match
        return db.query(Empresa).filter(
            Empresa.cnpj_basico.like(f"{digits}%")
        ).limit(MAX_TOTAL_VISIBLE).all()

B-tree index no cnpj_basico (PK) = resposta em < 1ms.

Cache com Redis

Buscas textuais são caras (3 scans trigram + dedup + hydrate). Cache é obrigatório:

import json, hashlib

def search_with_cache(query, page, per_page):
    cache_key = f"search:{query}:{page}:{per_page}"

    cached = redis.get(cache_key)
    if cached:
        return json.loads(cached)

    results = search_text(query, page, per_page)

    # Cache por 5 minutos — dados mudam mensalmente, 
    # mas consultas novas precisam aparecer rápido
    redis.setex(cache_key, 300, json.dumps(results))

    return results

Hit rate observado: ~40% em produção. Queries populares ("Petrobras", "Magazine Luiza") são servidas do cache.

Hydratação: evitando N+1

Depois de obter a lista de cnpj_basico da busca, precisamos carregar razão social, CNPJ completo e situação cadastral para exibição. Fazer uma query por resultado seria N+1:

def hydrate(cnpj_basicos: list[str]):
    # Uma única query com IN clause
    empresas = db.query(Empresa).filter(
        Empresa.cnpj_basico.in_(cnpj_basicos)
    ).all()
    empresa_map = {e.cnpj_basico: e for e in empresas}

    # Buscar estabelecimento matriz para cada empresa
    matrizes = db.query(Estabelecimento).filter(
        Estabelecimento.cnpj_basico.in_(cnpj_basicos),
        Estabelecimento.identificador_matriz_filial == "1"
    ).all()
    matriz_map = {m.cnpj_basico: m for m in matrizes}

    results = []
    for cnpj in cnpj_basicos:
        emp = empresa_map.get(cnpj)
        mat = matriz_map.get(cnpj)
        if emp and mat:
            results.append({
                "cnpj": format_cnpj(mat.cnpj_basico, mat.cnpj_ordem, mat.cnpj_dv),
                "razao_social": emp.razao_social,
                "situacao_cadastral": mat.situacao_cadastral,
            })

    return results

2 queries no total, independente do número de resultados.

Resultados de performance

Query	Tempo (sem cache)	Tempo (com cache)
"Petrobras"	~120ms	~2ms
"Comércio varejista"	~350ms	~2ms
"33000167000101"	~5ms	~2ms
"João Silva" (sócio)	~280ms	~2ms

Tudo isso rodando em um PostgreSQL 16 com 8 GB de RAM. Sem Elasticsearch, sem Meilisearch, sem nada além do PostgreSQL.

Conclusão

pg_trgm é subestimado. Para aplicações onde:

• O dataset cabe em PostgreSQL (até centenas de milhões de registros)
• A busca é por substring/similaridade, não full-text semântico
• Você quer manter a stack simples

...ele é a escolha certa. Adicione cache no Redis para queries repetidas e você tem um search engine que aguenta muito tráfego com hardware modesto.

Quer ver funcionando na prática? Teste uma busca no CNPJ Aberto e digite qualquer nome de empresa ou CNPJ e veja o autocomplete em tempo real.