Vinicius Aguiar
Arquitectura

La query de activación miente el primer día: anti-join y backfill en PostgreSQL

20 de sept. de 2026 · 9 min de lectura

La métrica de churn más común es una razón: uso de los últimos 7 días dividido por el uso de los 7 días anteriores. Funciona bien para detectar caídas. Y es ciega al caso peor — la cuenta que paga todos los meses y nunca usó nada. Este post trata de las tres trampas de implementar esa métrica en PostgreSQL, y de por qué su primer resultado casi siempre es falso.

El problema no es la division por cero

La explicación intuitiva es que la razón divide por cero. No es eso. Una cuenta que nunca generó un evento no produce ninguna fila en la tabla de eventos — no llega al GROUP BY. No hay división que pueda fallar, porque no hay fila. Y COALESCE no resuelve: no se puede dar valor por defecto a una fila 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;

El defecto está en el FROM, no en la aritmética. Mientras la query parta de events, su universo es "cuentas que ya hicieron algo". Para ver a quienes nunca hicieron nada, el FROM debe partir de subscriptions — la tabla que sabe quién paga — y buscar eventos desde ahí.

Anti-join, no LEFT JOIN

Invertido el FROM, la pregunta pasa a ser "qué suscripciones no tienen ninguna fila correspondiente en eventos". Eso es un anti-join contra tu tabla más grande, y hay dos formas populares de escribirlo mal. La primera es 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')
  );

Si account_id admite nulos y alguna fila devuelve NULL, la comparación se vuelve UNKNOWN para todas las cuentas y el resultado es un conjunto vacío. La query no falla, no avisa — simplemente responde que todo está bien.

La segunda es LEFT JOIN ... WHERE e.id IS NULL. Esa funciona: el planner de PostgreSQL reconoce el patrón y lo ejecuta como anti-join. El problema es la fragilidad, no el rendimiento. El día en que alguien agregue un filtro sobre e en el WHERE en vez del ON, el LEFT JOIN se convierte en INNER JOIN silenciosamente y la query empieza a descartar exactamente las filas que buscabas. NOT EXISTS declara la intención y no tiene ese modo de falla:

-- 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');

El backfill que no existe

Aquí está la trampa que hace que la métrica mienta el primer día. first_meaningful_event_at IS NULL tiene dos significados que la columna no distingue: "esta cuenta nunca se activó" y "no emitíamos ese evento cuando se suscribió". Si empezaste a emitir report_created en marzo, toda cuenta anterior a marzo parece inactiva — y el número sale catastrófico por un bug, no por la realidad.

-- 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')
  );

No hay arreglo retroactivo. Un evento que no se emitió no se puede reconstruir, y ninguna query recupera un dato que nunca se escribió. Lo que sí se puede hacer es ser explícito sobre la frontera: la métrica solo vale para suscripciones que empezaron después de la instrumentación. El recorte achica la muestra, pero el número empieza a significar algo.

Donde vive la columna

Correr ese anti-join bajo demanda, en un dashboard que se actualiza en cada carga, es caro. La alternativa es mantener una columna derivada — first_meaningful_event_at en la cuenta — escrita una sola vez, cuando llega el primer evento relevante:

-- 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;

El WHERE ... IS NULL hace el trabajo pesado: vuelve la escritura idempotente, elimina la lectura previa y es segura bajo concurrencia, porque dos eventos simultáneos compiten por la misma fila y solo el primero encuentra NULL. A partir de ahí, la métrica es un recorrido sobre accounts, sin tocar events. El costo es el habitual de toda columna derivada: si cambia la definición de "uso relevante", hay que recalcular.

Que cuenta como uso relevante

La parte que ninguna query resuelve es la definición. Un login no es activación — es alguien entrando para redescubrir que no sabe qué hacer ahí. Uso relevante es la acción que produce el valor que la cuenta compró:

  • Un reporte generado, no un reporte abierto
  • Una integración conectada y con datos fluyendo, no un token guardado
  • Una invitación aceptada por un segundo usuario, no una invitación enviada
  • Un registro creado por el cliente, no los registros de ejemplo del onboarding

Elegir qué eventos entran en esa lista es una decisión de producto. Lograr que se emitan de forma confiable, se indexen bien y se calculen sin tumbar la base es una decisión de ingeniería. La métrica solo es honesta cuando ambas fueron tomadas — y tomadas antes, no después.

Lectura relacionada

Este post trata de la implementación. El argumento de producto — por qué la cuenta olvidada es peor que la cuenta en caída, y por qué debería ir primero en la fila — está en Paga e nunca usou é pior que uso caiu y en The most uncomfortable number in your customer base.