Cache em 3 camadas pra query de 55M linhas: Redis → Postgres snapshot → SQL

Estatística pública no CNPJ Aberto significa agregar todas as empresas do Brasil (55M+ estabelecimentos) por ano, UF, CNAE, MEI, capital social. Em SQL puro isso é um SEQ SCAN de vários minutos. Em produção, se um único usuário acessa /panorama/2024 sem cache, o banco trava por 3+ minutos pra todo mundo.

Duas opções óbvias falham:

• Cachear em Redis com TTL longo. Quando o Redis reinicia, a próxima visita paga 3 minutos de latência enquanto tudo bloqueia.
• Gerar estático no build. A base é re-ingerida mensalmente, build não ajuda. Anos passados são imutáveis, mas o overview e o ano corrente mudam.

A saída foi uma pirâmide de 3 camadas:

  Redis (in-memory, TTL 7-90d)
       ↓ miss
  Postgres panorama_snapshots (durável)
       ↓ miss
  SQL ad-hoc sobre 55M linhas (minutos)

Por que Postgres no meio

Snapshot em Postgres parece redundante. Não é. Anos passados da Receita Federal são imutáveis: o panorama de 2022 em 2026 é o mesmo que era em 2024. Pagar 3 minutos de SQL pra recomputar algo que nunca vai mudar só porque o Redis esvaziou é desperdício puro.

A tabela é trivial:

class PanoramaSnapshot(Base):
    __tablename__ = "panorama_snapshots"
    key = Column(String(64), primary_key=True)      # panorama:ano:2024:v2
    payload = Column(Text, nullable=False)           # JSON cru do payload
    generated_at = Column(Text, nullable=False)      # ISO timestamp

Chave versionada (v2). Ao mudar o schema do payload, bumpa a versão e todos os snapshots antigos ficam órfãos. O sistema recomputa naturalmente sem DELETE explícito.

Fluxo de leitura

def _tiered_get_or_compute(db, key, compute, ttl):
    # 1. Redis (hot path)
    cached = _redis_get(key)
    if cached:
        return cached

    # 2. Snapshot Postgres (warm path)
    snap = _snapshot_load(db, key)
    if snap:
        _redis_set(key, snap, ttl)   # re-aquece Redis
        return snap

    # 3. Compute (cold path, minutos)
    result = compute()
    _redis_set(key, result, ttl)
    _snapshot_save(db, key, result)
    return result

Visita #1 depois de um restart do Redis: lê do Postgres (~5ms), repopula Redis. Visita #2: Redis direto. Anos passados jamais tocam o SEQ SCAN depois que o snapshot nasce.

TTLs que respeitam a natureza do dado

PANORAMA_TTL_PAST    = 90 * 24 * 3600    # anos fechados: 90 dias
PANORAMA_TTL_CURRENT = 7  * 24 * 3600    # overview e ano corrente: 7 dias

7 dias no overview bate com a cadência de re-ingestão mensal da RF. O pior caso é servir dado 1 semana desatualizado, aceitável.

Stampede protection

O maior perigo de TTL longo é o thundering herd: TTL expira, 50 requests chegam no mesmo segundo, todos vão pro SQL simultaneamente, servidor derrete. Solução é um lock:

lock = r.set(f"lock:{key}", "1", nx=True, ex=120)
if not lock:
    # Alguém já está computando. Espera o cache popular.
    for _ in range(80):  # até 8s
        time.sleep(0.1)
        v = _redis_get(key)
        if v: return v
    # Fallback: retorna snapshot antigo (stale) se existir
    return _snapshot_load(db, key) or _compute_inline()

Só um worker entra no cálculo pesado. Os outros esperam 8s pelo resultado e, no pior caso, servem o snapshot velho em vez de derrubar o banco. Stale-while-revalidate à moda Redis.

Warm-up agressivo pós-ingestão

Depois da ingestão mensal da RF tudo é invalidado e um warm_all roda:

def warm_all(db):
    get_overview(db)                    # 1 query pesada
    payloads = _bulk_compute_years(db, range(2020, 2026))
    for ano, payload in payloads.items():
        _redis_set(...)
        _snapshot_save(...)

O truque do _bulk_compute_years: em vez de rodar 8 queries × 7 anos = 56 SEQ SCANs, o código reescreve como 8 queries que já fazem GROUP BY ano internamente = 8 SEQ SCANs servindo os 7 anos. Tempo caiu de 50-90min pra 8-15min.

Os ajustes de sessão que custaram caro aprender

SET LOCAL statement_timeout = '30min'
SET LOCAL max_parallel_workers_per_gather = 0
SET LOCAL work_mem = '128MB'
SET LOCAL enable_parallel_hash = off

Cada um desses levou uma madrugada debugando produção:

• statement_timeout: o default da app é 30s (ótimo pra user), absurdo pra warm. Sobe só na sessão do warm com SET LOCAL.
• parallel_workers = 0: QUALQUER operador paralelo (Hash Join, Hash Aggregate, Append) aloca DSA em /dev/shm. Com shm_size=512MB em docker, 4 workers com work_mem=256MB estouraram na PERCENTILE_CONT do capital social. Reduzir pra 2 workers não bastou: Parallel Hash Aggregate do GROUP BY também usa DSA. Serial puro (=0) é 1.5× mais lento mas não toca /dev/shm. Pra warm de 1× por mês, estabilidade vale mais que 3 min.
• enable_parallel_hash = off: redundante com parallel_workers=0, fica como defesa em profundidade.

Resultado em números

Cenário	Antes	Depois
Hit no Redis	~1ms	~1ms
Miss Redis, hit snapshot	3min (recomputava)	~5ms
Cold total (warm pós-ingest)	50-90min	12-18min
Latência P95 da página /panorama/2024	indefinida (timeout)	80ms