Vinicius Aguiar
Arquitetura

A query de ativação mente no primeiro dia: anti-join e backfill em PostgreSQL

20 de set de 2026 · 9 min de leitura

A métrica de churn mais comum é uma razão: uso dos últimos 7 dias dividido pelo uso dos 7 dias anteriores. Ela funciona bem para detectar queda. E é cega para o caso pior — a conta que paga todo mês e nunca usou nada. Este post é sobre as três armadilhas de implementar essa métrica em PostgreSQL, e por que o primeiro resultado dela quase sempre é falso.

O problema nao e a divisao por zero

A explicação intuitiva é que a razão divide por zero. Não é isso. Uma conta que nunca gerou evento não produz nenhuma linha na tabela de eventos — ela não chega no GROUP BY. Não existe divisão para dar errado, porque não existe linha. E COALESCE não resolve: não dá para aplicar valor padrão a uma linha ausente.

-- Errado: o FROM parte de events. Uma conta que nunca gerou
-- evento nao produz nenhuma linha aqui, entao ela nao chega
-- no GROUP BY. Nao e uma razao 0/0 -- e uma conta ausente.
SELECT
  e.account_id,
  count(*) FILTER (
    WHERE e.created_at >= now() - interval '7 days'
  )::numeric
  / nullif(count(*) FILTER (
      WHERE e.created_at >= now() - interval '14 days'
        AND e.created_at <  now() - interval  '7 days'
    ), 0) AS razao_uso
FROM events e
GROUP BY e.account_id;

O defeito está no FROM, não na aritmética. Enquanto a query parte de events, o universo dela é "contas que já fizeram alguma coisa". Para enxergar quem nunca fez, o FROM precisa partir de subscriptions — a tabela que sabe quem paga — e procurar eventos a partir dali.

Anti-join, nao LEFT JOIN

Invertido o FROM, a pergunta vira "quais assinaturas não têm nenhuma linha correspondente em eventos". Isso é um anti-join contra a sua maior tabela, e há duas formas populares de escrevê-lo errado. A primeira é NOT IN:

-- Pior ainda: se algum account_id retornado for NULL, o
-- predicado inteiro vira UNKNOWN e a query devolve ZERO linhas.
-- Nao da erro. So responde "nao ha contas inativas".
SELECT s.account_id, s.mrr_cents
FROM subscriptions s
WHERE s.status = 'active'
  AND s.account_id NOT IN (
    SELECT e.account_id FROM events e
    WHERE e.type IN ('report_created', 'integration_connected')
  );

Se account_id for anulável e qualquer linha retornar NULL, a comparação vira UNKNOWN para todas as contas e o resultado é um conjunto vazio. A query não falha, não avisa — apenas responde que está tudo bem.

A segunda é LEFT JOIN ... WHERE e.id IS NULL. Essa funciona: o planner do PostgreSQL reconhece o padrão e executa como anti-join. O problema é fragilidade, não desempenho. No dia em que alguém acrescentar um filtro sobre e no WHERE em vez do ON, o LEFT JOIN vira INNER JOIN silenciosamente e a query passa a descartar exatamente as linhas que você procurava. NOT EXISTS declara a intenção e não tem esse modo de falha:

-- O FROM parte de subscriptions: toda conta que paga entra,
-- tenha evento ou nao. NOT EXISTS declara a intencao (anti-join)
-- e o predicado correlacionado fica onde pertence.
SELECT s.account_id, s.mrr_cents
FROM subscriptions s
WHERE s.status = 'active'
  AND NOT EXISTS (
    SELECT 1
    FROM events e
    WHERE e.account_id = s.account_id
      AND e.type IN ('report_created', 'integration_connected')
  );

-- Indice parcial: eventos relevantes sao uma fracao do total,
-- entao o indice fica pequeno e e exatamente o que o anti-join
-- sonda. CONCURRENTLY para nao travar a escrita em producao.
CREATE INDEX CONCURRENTLY idx_events_relevantes
  ON events (account_id)
  WHERE type IN ('report_created', 'integration_connected');

O backfill que nao existe

Aqui está a armadilha que faz a métrica mentir no primeiro dia. first_meaningful_event_at IS NULL tem dois significados que a coluna não distingue: "esta conta nunca ativou" e "a gente não instrumentava esse evento quando ela assinou". Se você começou a emitir report_created em março, toda conta anterior a março parece inativa — e o número sai catastrófico por bug, não por realidade.

-- Guarda contra o falso positivo: so conta assinaturas que
-- comecaram depois que a instrumentacao existia. Antes disso,
-- ausencia de evento nao significa ausencia de uso.
SELECT sum(s.mrr_cents) AS mrr_sem_uso
FROM subscriptions s
WHERE s.status = 'active'
  AND s.started_at >= timestamp '2026-03-01'  -- instrumentacao
  AND NOT EXISTS (
    SELECT 1
    FROM events e
    WHERE e.account_id = s.account_id
      AND e.type IN ('report_created', 'integration_connected')
  );

Não existe conserto retroativo. Evento que não foi emitido não pode ser reconstruído, e nenhuma query recupera um dado que nunca foi escrito. O que dá para fazer é ser explícito sobre a fronteira: a métrica só vale para assinaturas que começaram depois da instrumentação. O recorte encolhe a amostra, mas o número passa a significar alguma coisa.

Onde a coluna mora

Rodar esse anti-join sob demanda, num dashboard que atualiza a cada carregamento, é caro. A alternativa é manter uma coluna derivada — first_meaningful_event_at na conta — escrita uma única vez, quando o primeiro evento relevante chega:

-- Write-once: o WHERE ... IS NULL torna a escrita idempotente
-- e segura sob concorrencia. Nao precisa ler antes, nao precisa
-- transacao, e o segundo evento nao sobrescreve o primeiro.
UPDATE accounts
   SET first_meaningful_event_at = $2
 WHERE id = $1
   AND first_meaningful_event_at IS NULL;

O WHERE ... IS NULL faz o trabalho pesado: torna a escrita idempotente, dispensa leitura prévia e é segura sob concorrência, porque dois eventos simultâneos disputam a mesma linha e só o primeiro encontra NULL. A partir daí, a métrica vira uma varredura em accounts, sem tocar em events. O custo é o de sempre em coluna derivada: se a definição de "uso relevante" mudar, você precisa recalcular.

O que conta como uso relevante

A parte que nenhuma query resolve é a definição. Login não é ativação — é a pessoa entrando para descobrir de novo que não sabe o que fazer ali. Uso relevante é a ação que produz o valor que a conta comprou:

  • Um relatório gerado, não um relatório aberto
  • Uma integração conectada e com dado fluindo, não um token salvo
  • Um convite aceito por um segundo usuário, não um convite enviado
  • Um registro criado pelo cliente, não os registros de exemplo do onboarding

Escolher quais eventos entram nessa lista é decisão de produto. Fazer com que sejam emitidos de forma confiável, indexados e calculáveis sem derrubar o banco é decisão de engenharia. A métrica só é honesta quando as duas foram tomadas — e tomadas antes, não depois.

Leitura relacionada

Este post trata da implementação. O argumento de produto — por que a conta esquecida é pior que a conta em queda, e por que ela deveria vir antes na fila — está em Paga e nunca usou é pior que uso caiu e em The most uncomfortable number in your customer base.