Vinicius Aguiar
Architecture

La requête d'activation ment dès le premier jour : anti-join et backfill en PostgreSQL

20 septembre 2026 · 9 min de lecture

La métrique de churn la plus répandue est un ratio : usage des 7 derniers jours divisé par l'usage des 7 jours précédents. Elle détecte bien la baisse. Et elle est aveugle au cas le pire — le compte qui paie tous les mois et n'a jamais rien utilisé. Ce post porte sur les trois pièges de l'implémentation de cette métrique en PostgreSQL, et sur la raison pour laquelle son premier résultat est presque toujours faux.

Le probleme n'est pas la division par zero

L'explication intuitive est que le ratio divise par zéro. Ce n'est pas le cas. Un compte qui n'a jamais généré d'événement ne produit aucune ligne dans la table des événements — il n'atteint jamais le GROUP BY. Il n'y a pas de division qui échoue, parce qu'il n'y a pas de ligne. Et COALESCE n'y peut rien : on ne met pas de valeur par défaut sur une ligne absente.

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

Le défaut est dans le FROM, pas dans l'arithmétique. Tant que la requête part de events, son univers est « les comptes qui ont déjà fait quelque chose ». Pour voir ceux qui n'ont jamais rien fait, le FROM doit partir de subscriptions — la table qui sait qui paie — et aller chercher les événements à partir de là.

Anti-join, pas LEFT JOIN

Le FROM inversé, la question devient « quelles souscriptions n'ont aucune ligne correspondante dans les événements ». C'est un anti-join contre votre plus grosse table, et il existe deux façons répandues de l'écrire de travers. La première est 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 est nullable et qu'une ligne renvoie NULL, la comparaison devient UNKNOWN pour tous les comptes et le résultat est un ensemble vide. La requête n'échoue pas, n'avertit pas — elle répond simplement que tout va bien.

La seconde est LEFT JOIN ... WHERE e.id IS NULL. Celle-là fonctionne : le planner de PostgreSQL reconnaît le motif et l'exécute comme un anti-join. Le problème est la fragilité, pas la performance. Le jour où quelqu'un ajoute un filtre sur e dans le WHERE au lieu du ON, le LEFT JOIN devient silencieusement un INNER JOIN et la requête se met à écarter précisément les lignes que vous cherchiez. NOT EXISTS déclare l'intention et n'a pas ce mode de défaillance :

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

Le backfill qui n'existe pas

Voici le piège qui fait mentir la métrique dès le premier jour. first_meaningful_event_at IS NULL a deux sens que la colonne ne distingue pas : « ce compte ne s'est jamais activé » et « nous n'émettions pas cet événement au moment de la souscription ». Si vous avez commencé à émettre report_created en mars, tout compte antérieur à mars paraît inactif — et le nombre sort catastrophique à cause d'un bug, pas de la réalité.

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

Il n'y a pas de correctif rétroactif. Un événement non émis ne se reconstruit pas, et aucune requête ne récupère une donnée jamais écrite. Ce qu'on peut faire, c'est expliciter la frontière : la métrique ne vaut que pour les souscriptions démarrées après l'instrumentation. La coupe réduit l'échantillon, mais le nombre se met à vouloir dire quelque chose.

Ou vit la colonne

Faire tourner cet anti-join à la demande, dans un dashboard qui se rafraîchit à chaque chargement, coûte cher. L'alternative est de maintenir une colonne dérivée — first_meaningful_event_at sur le compte — écrite une seule fois, à l'arrivée du premier événement pertinent :

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

C'est le WHERE ... IS NULL qui fait le gros du travail : il rend l'écriture idempotente, supprime la lecture préalable et reste sûr en concurrence, car deux événements simultanés se disputent la même ligne et seul le premier trouve NULL. Ensuite, la métrique devient un parcours de accounts, sans jamais toucher events. Le coût est celui de toute colonne dérivée : si la définition de « usage pertinent » change, il faut recalculer.

Ce qui compte comme usage pertinent

La part qu'aucune requête ne règle, c'est la définition. Un login n'est pas une activation — c'est quelqu'un qui se connecte pour redécouvrir qu'il ne sait pas quoi faire là. L'usage pertinent, c'est l'action qui produit la valeur que le compte a achetée :

  • Un rapport généré, pas un rapport ouvert
  • Une intégration connectée avec des données qui circulent, pas un token enregistré
  • Une invitation acceptée par un second utilisateur, pas une invitation envoyée
  • Un enregistrement créé par le client, pas les exemples de l'onboarding

Choisir quels événements entrent dans cette liste est une décision produit. Faire en sorte qu'ils soient émis de façon fiable, bien indexés et calculables sans faire tomber la base est une décision d'ingénierie. La métrique n'est honnête qu'une fois les deux prises — et prises en amont, pas après.

Lectures associees

Ce post porte sur l'implémentation. L'argument produit — pourquoi le compte oublié est pire que le compte en baisse, et pourquoi il devrait passer en premier — se trouve dans Paga e nunca usou é pior que uso caiu et dans The most uncomfortable number in your customer base.