Remplissage des données historiques sans casser votre base de production

Vous venez de déployer une nouvelle migration qui ajoute une colonne last_login_at à la table users. Le changement de schéma s'est déroulé sans accroc. Mais maintenant, en regardant les données, vous constatez que chaque utilisateur existant a une valeur nulle dans cette colonne. Leur historique de connexion de la semaine dernière, du mois dernier, de l'année dernière — tout cela est invisible pour le nouveau champ.

C'est le moment où vous avez besoin d'un backfill.

Ce que backfill signifie vraiment

Le backfill est le processus qui consiste à remplir les données qui existaient avant l'application d'une migration. Il ne s'agit pas de déplacer des données vers une nouvelle structure — c'est le rôle des scripts de migration. Le backfill consiste à mettre à jour les anciennes données pour qu'elles soient conformes aux nouvelles règles que votre système applique désormais.

Les situations où un backfill devient nécessaire sont courantes :

  • Vous avez ajouté une nouvelle colonne, mais les lignes existantes n'ont pas de valeur pour celle-ci.
  • Vous avez modifié la façon dont les adresses sont stockées, passant d'un champ texte unique à des colonnes distinctes pour la rue, la ville et le code postal.
  • Vous avez introduit un nouveau calcul, comme un score de risque pour les transactions, mais les transactions passées n'ont jamais été notées.

Dans chaque cas, les données sont là, valides mais incomplètes. Le système sait quoi faire avec les nouvelles données entrantes, mais les anciennes données sont bloquées dans le format précédent.

Pourquoi vous ne pouvez pas tout traiter d'un coup

L'approche naïve consiste à exécuter une seule requête qui met à jour toutes les lignes en une fois. Pour une petite table de quelques centaines de lignes, cela fonctionne très bien. Pour une table de plusieurs millions de lignes, c'est une catastrophe annoncée.

Une seule mise à jour massive verrouille des lignes, consomme les journaux de transactions et ralentit toutes les autres requêtes qui accèdent à la même table. Si votre application sert des utilisateurs pendant le backfill, ces utilisateurs subiront des timeouts, des réponses lentes ou des requêtes échouées. La base de données pourrait même manquer de mémoire ou d'espace disque pour gérer l'opération.

La solution consiste à traiter les données par petits lots contrôlés.

Traitement par lots : la technique de base

Au lieu de mettre à jour un million de lignes en une seule fois, vous mettez à jour dix mille lignes à la fois, faites une pause, puis traitez le lot suivant. C'est ce qu'on appelle le traitement par lots, et c'est le fondement des backfills sécurisés.

Voici comment cela fonctionne en pratique :

Le diagramme suivant illustre la boucle complète de backfill, incluant les vérifications d'idempotence et le throttling :

flowchart TD A([Début]) --> B[Sélectionner le prochain lot de lignes non traitées WHERE processed_at IS NULL LIMIT batch_size] B --> C{Des lignes trouvées ?} C -- Non --> D([Fin]) C -- Oui --> E[Traitement / transformation des données pour chaque ligne] E --> F[Mettre à jour les lignes : SET target_column = new_value, processed_at = NOW()] F --> G[Logger le lot : numéro du lot, lignes traitées, durée, erreurs] G --> H[Throttle : Pause selon l'intervalle configuré] H --> B 
-- Traiter un lot de lignes qui nécessitent encore un backfill
UPDATE users
SET last_login_at = (
    SELECT MAX(login_time)
    FROM login_history
    WHERE login_history.user_id = users.id
)
WHERE last_login_at IS NULL
LIMIT 10000;

Après cette exécution, vous vérifiez combien de lignes ont été affectées. Si le nombre correspond à la taille du lot, vous attendez quelques secondes et relancez la requête. S'il retourne moins de lignes, le backfill est presque terminé.

Choisir la bonne taille de lot

Il n'existe pas de taille de lot universelle qui fonctionne pour toutes les bases de données. La bonne taille dépend de :

  • La puissance de votre serveur de base de données.
  • La charge que l'application impose à la base de données.
  • La complexité de la logique de mise à jour.
  • L'espace disponible dans les journaux de transactions.

Commencez par une taille prudente, comme 5 000 lignes. Exécutez quelques lots et surveillez les métriques de la base de données : utilisation CPU, I/O disque, latence des requêtes côté application. Si la base de données gère facilement, doublez la taille du lot. Si vous observez des pics de latence ou de contention de verrous, réduisez la taille de moitié.

L'objectif est de trouver une taille de lot qui s'exécute en quelques secondes sans impact notable sur les autres requêtes. Un lot qui prend trente secondes est probablement trop volumineux pour un système de production sous charge normale.

Throttling : laisser la base de données respirer

La taille du lot contrôle la quantité de travail effectuée en une seule unité. Le throttling contrôle le temps qui s'écoule entre les unités.

Après chaque lot, ajoutez une pause délibérée avant de commencer le suivant. Cette pause permet à la base de données de vider les écritures en attente, de libérer les verrous et de servir d'autres requêtes sans concurrence de la part de votre backfill.

Un throttling typique peut être de deux à cinq secondes entre les lots. Pendant les heures de pointe, vous pouvez l'augmenter à dix ou quinze secondes. Pendant les fenêtres de maintenance, vous pouvez le réduire à une seconde ou le supprimer complètement.

Le throttling est votre soupape de sécurité. Si quelque chose tourne mal — un pic soudain de trafic applicatif, une requête lente d'une autre équipe, un avertissement de retard de réplication — vous pouvez augmenter la pause et laisser le système se stabiliser avant de continuer.

Rendre les backfills idempotents

Un script de backfill doit pouvoir être exécuté plusieurs fois sans risque. Si un lot échoue à mi-parcours, ou si vous devez redémarrer tout le processus, exécuter le même script ne doit pas produire de données en double ni d'erreurs.

L'idempotence pour les backfills signifie généralement l'une des deux choses suivantes :

  • Vérifier avant d'écrire : ne mettre à jour que les lignes qui ont encore des valeurs nulles ou anciennes.
  • Utiliser une logique d'upsert : insérer ou mettre à jour selon que la ligne possède déjà les nouvelles données.

Pour l'exemple last_login_at, la requête ci-dessus est déjà idempotente car elle ne cible que les lignes où la colonne est encore nulle. Si un lot échoue après avoir mis à jour 5 000 lignes, l'exécution suivante ignorera ces lignes et continuera avec les lignes restantes.

Pour des backfills plus complexes, comme le recalcul d'une valeur dérivée, vous pouvez ajouter une colonne timestamp processed_at. Le script de backfill vérifie si processed_at est nul avant de traiter chaque ligne. Une fois traité, le timestamp est défini, et les exécutions suivantes ignorent cette ligne.

Logging : le détail auquel personne ne pense jusqu'à ce que ça casse

Lorsqu'un backfill s'exécute pendant des heures, vous devez savoir où il en est et s'il fonctionne toujours correctement. Enregistrez chaque lot :

  • Numéro du lot et plage horaire.
  • Nombre de lignes traitées.
  • Durée du lot.
  • Toutes les erreurs rencontrées.
  • Progression actuelle en pourcentage ou nombre de lignes.

Ce journal sert à deux fins. Premièrement, si le backfill s'arrête de manière inattendue, vous pouvez reprendre à partir du dernier lot terminé au lieu de recommencer. Deuxièmement, lorsque le backfill se termine, vous avez un enregistrement précis de ce qui s'est passé, ce qui facilite le débogage et l'audit.

Une entrée de journal simple pourrait ressembler à ceci :

2025-03-15 14:32:01 | Lot 47 | 10 000 lignes traitées | Durée 3,2s | Aucune erreur
2025-03-15 14:32:06 | Lot 48 | 10 000 lignes traitées | Durée 3,1s | Aucune erreur
2025-03-15 14:32:11 | Lot 49 | 10 000 lignes traitées | Durée 3,5s | Aucune erreur

Une checklist pratique pour le backfill

Avant d'exécuter un backfill en production, parcourez cette liste :

  • La taille du lot est testée sur un environnement de staging avec un volume de données similaire.
  • L'intervalle de throttling est configuré et ajustable sans modification de code.
  • Le script est idempotent — l'exécuter deux fois produit le même résultat.
  • Le logging capture la progression des lots, les erreurs et le timing.
  • Un plan de rollback existe : vous pouvez annuler le backfill si quelque chose tourne mal.
  • Une surveillance est en place pour détecter la dégradation des performances de la base de données.
  • Un essai à blanc a été exécuté sur une copie des données de production.

Ce qu'il faut retenir

Le backfill n'est pas un script unique que vous écrivez et oubliez. C'est une opération contrôlée qui respecte le fait que votre base de données sert des utilisateurs pendant que vous modifiez leurs données. Le traitement par lots et le throttling ne sont pas des optimisations — ce sont les exigences minimales pour effectuer ce travail en toute sécurité. Sans eux, vous n'êtes qu'à une grosse requête d'un incident de production.