Le chiffre à fixer dans cette histoire de fintech, ce n'est pas les dix-huit heures. C'est les huit. Après le week-end où un seul UPDATE sur une table transactions de 1,2 milliard de lignes a rempli le disque du primaire à 95 %, mis le réplica quatre heures en retard, puis passé quatre heures de plus à faire un rollback pour modifier exactement zéro ligne, la même équipe est revenue et a fait le même travail en huit heures environ, avec une taille de lot de 1000, un sleep de 20 ms entre les lots, le dernier id traité rangé dans Redis et le throttle qui réagit au retard de réplication. Personne n'a été réveillé. Ma thèse : la boucle qui a rendu ça possible est la chose la moins intéressante qu'ils aient construite.

backfill_state.sql
CREATE TABLE backfill_state (
  name TEXT PRIMARY KEY,
  last_id BIGINT NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO backfill_state (name, last_id)
VALUES ('transactions_usd', 12345)
ON CONFLICT (name) DO UPDATE
  SET last_id = EXCLUDED.last_id, updated_at = NOW();

Regarde les mesures et tu comprendras pourquoi la vitesse n'a jamais été l'argument. Sur 100K lignes, l'UPDATE unique s'est terminé en 122 ms. Le même travail découpé en 100 lots de 1000 a pris 688 ms de temps réel, dont seulement 176 ms de temps de requête, soit 1,77 ms par lot en moyenne. La version découpée est environ cinq fois plus lente et c'est précisément l'intérêt. Tu n'achètes pas du débit, tu achètes des transactions qui tiennent des verrous pendant deux millisecondes au lieu de vingt minutes, pour que le WAL puisse se tronquer et que le réplica puisse respirer. Extrapole linéairement la version rapide à un milliard de lignes et tu obtiens environ 1200 secondes d'une seule transaction ininterrompable, avec cette délicieuse propriété que l'annuler coûte plus cher que la laisser finir.

La pagination par curseur est ce qu'on cite le plus, et elle mérite sa citation. Même à l'échelle jouet de 100K lignes sous PHP 8.3.6 face à SQLite, OFFSET 0 coûtait 0,6 ms là où OFFSET 80000 coûtait 2,0 ms, parce que le moteur parcourt et jette tout ce qu'il saute. Les lectures par curseur restaient entre 0,2 ms et 0,5 ms, peu importe où elles tombaient dans la table. À 900 millions de lignes de profondeur, cet écart cesse d'être un facteur trois et devient des minutes par lot. Très bien. Mais WHERE id > :last_id ORDER BY id LIMIT :batch_size tient sur une ligne, et le piège autour est étroit : ta clé doit être indexée et monotone, et si elle n'est pas unique il te faut quelque chose comme (created_at, id) pour garder un tri stable. Si OFFSET est le problème le plus dur de ton backfill, tu as déjà résolu les vrais.

L'objection honnête d'abord, parce qu'elle est bonne. La plupart des tables ne font pas un milliard de lignes. Écrire un framework avec checkpoint, throttle et rythme adaptatif pour toucher 40 000 lignes, c'est du théâtre : l'UPDATE tout simple aura commité avant que ton hook de déploiement ait fini d'afficher sa ligne de log. Et pour les changements structurels, gh-ost, pt-online-schema-change, pg_repack et pg_squeeze découpent, throttlent et échangent des tables depuis des années, mieux que le script que tu écriras un mardi. Les deux sont vrais. Là où je reste sur une autre position, c'est sur la forme du risque : se tromper du côté du chunking te coûte un après-midi, se tromper de l'autre côté te coûte un rollback que tu ne peux pas annuler, un samedi, sur le primaire.

Ce qui m'amène à la partie qui n'a rien à voir avec SQL. Un backfill sûr, c'est une séquence de releases. D'abord tu livres le code applicatif qui écrit à la fois total_cents et total_usd_cents, pour que chaque nouvelle ligne arrive déjà correcte. Ensuite, et seulement ensuite, tu lances le job qui remplit les lignes historiques où total_usd_cents IS NULL. Plus tard, dans une release séparée, tu bascules les lectures. Ce prédicat est aussi ce qui rend les relances gratuites : une ligne déjà traitée ne correspond simplement plus. Trois déploiements et un job qui tourne huit heures entre les deux ne peuvent pas vivre dans un fichier de migration que ton pipeline de déploiement attend. Ça doit être un worker, ou un job en file avec des retries sous Symfony Messenger ou Laravel Queue, avec un nom, une procédure écrite et quelqu'un qui sait l'arrêter.

Deux points que j'appuierais plus fort que le playbook. Un : le signal de fin, SELECT COUNT(*) FROM transactions WHERE total_usd_cents IS NULL qui atteint zéro, est un instantané, et les lignes continuent d'arriver, donc vérifie-le deux fois avant de crier victoire et de supprimer des colonnes. Deux, et c'est celui qui me fait vraiment peur : le chunking protège la base et ne fait absolument rien pour la justesse. Si compute_usd arrondit dans le mauvais sens ou prend le taux du mauvais jour, tu possèdes désormais 1,2 milliard de lignes d'argent faux avec assurance, et si tu n'as pas gardé les valeurs d'origine quelque part, tu n'as rien contre quoi comparer. Teste la conversion sur un échantillon jusqu'à ce que ça devienne ennuyeux, conserve les valeurs sources, et mets un flag de pause dans Redis pour qu'un opérateur puisse geler le job pendant un incident sans rapport, sans tuer le processus. Nos voisins qui écrivent du Go te montreront un pool de workers pour ça. Un script CLI PHP avec usleep et un flag fait très bien l'affaire, parce que le goulot d'étranglement n'a jamais été le langage, c'était le disque sous le WAL.

Alors, elle est où, ta limite ? La mienne se situe autour de quelques millions de lignes, au-delà desquelles je ne valide pas un UPDATE nu dans une migration, mais je n'ai jamais écrit ce chiffre noir sur blanc et je soupçonne que toi non plus. Tu poses la barrière en revue de code, ou tu as une classe de base dont tout le monde hérite ? Et la question de suivi sur laquelle je suis sincèrement indécis : pour une table de cette taille, est-ce que tu découpes vraiment l'espace des id entre quatre workers parallèles, ou est-ce qu'un seul worker avec un sleep adaptatif est la version qui te laisse dormir aussi ?