Migrations PostgreSQL avec Claude Code : consignes pour des changements sûrs

Qu’est‑ce qui rend une modification de schéma PostgreSQL risquée

Une modification de schéma PostgreSQL semble simple jusqu’à ce qu’elle rencontre le vrai trafic et de vraies données. Le plus souvent, le risque ne vient pas du SQL lui‑même, mais du fait que le code de l’app, l’état de la base et le calendrier de déploiement ne sont plus synchrones.

La plupart des échecs sont pratiques et douloureux : un déploiement casse parce que l’ancien code touche une nouvelle colonne, une migration verrouille une table chaude et les timeouts explosent, ou un changement « rapide » supprime ou réécrit des données sans qu’on le voie. Même sans crash, on peut livrer des bugs subtils : mauvais defaults, contraintes cassées, ou index qui n’ont jamais fini de se construire.

Les migrations générées par l’IA ajoutent une couche de risque. Les outils peuvent produire du SQL valide qui reste dangereux pour votre charge, le volume de données ou votre processus de release. Ils peuvent deviner des noms de table, ignorer des verrous longue durée, ou éluder le rollback parce que les down migrations sont difficiles. Si vous utilisez Claude Code pour des migrations, vous avez besoin de garde‑fous et d’un contexte concret.

Quand ce post dit qu’un changement est « sûr », cela signifie trois choses :

• Rétrocompatible : les anciennes et nouvelles versions de l’app peuvent coexister pendant le rollout.
• Observable : vous pouvez mesurer l’avancement et détecter rapidement les problèmes.
• Rétrogradable : vous avez un plan de rollback exécutable sous pression.

L’objectif est que les migrations deviennent du travail routinier : prévisible, testable et ennuyeux.

Règles de sécurité à suivre avant d’écrire un prompt

Commencez par quelques règles non négociables. Elles gardent le modèle concentré et vous évitent d’expédier un changement qui ne marche que sur votre laptop.

Fractionnez le travail en petites étapes. Une modification de schéma, un backfill, un changement d’app et une étape de nettoyage sont des risques différents. Les regrouper rend plus difficile d’identifier la cause d’un échec et de revenir en arrière.

Préférez les changements additifs avant les opérations destructrices. Ajouter une colonne, un index ou une table est généralement peu risqué. Renommer ou supprimer des objets, c’est là que les pannes arrivent. Faites d’abord la partie sûre, migrez l’app, puis supprimez l’ancien objet seulement quand vous êtes sûr qu’il n’est plus utilisé.

Faites en sorte que l’app tolère les deux formes pendant un certain temps. Le code doit pouvoir lire soit l’ancienne colonne soit la nouvelle pendant le rollout. Cela évite la course où certains serveurs exécutent le nouveau code alors que la base est encore à l’ancien schéma (ou l’inverse).

Traitez les migrations comme du code de production, pas comme un script rapide. Même si vous construisez sur une plateforme comme Koder.ai (backend Go avec PostgreSQL, clients React ou Flutter), la base est partagée par tout le monde. Les erreurs coûtent cher.

Si vous voulez un petit ensemble de règles à placer en tête de chaque requête SQL, utilisez quelque chose comme :

• Un changement par migration : expand, puis backfill, puis switch du code, puis cleanup.
• Éviter les verrous longs : construire les index CONCURRENTLY et faire des mises à jour par petits lots.
• Exiger un plan de rollback pour chaque étape, y compris comment arrêter un backfill en cours.
• Exiger des requêtes de vérification et des métriques de succès (comptes de lignes, taux de null, temps).
• Exiger un runbook : comment l’exécuter, quoi surveiller, qui contacter.

Exemple pratique : au lieu de renommer une colonne dont dépend l’app, ajoutez la nouvelle colonne, backfillez lentement, déployez du code qui lit la nouvelle puis l’ancienne, et seulement plus tard supprimez l’ancienne colonne.

Ce qu’il faut inclure dans votre prompt pour que Claude Code reste réaliste

Claude peut écrire du SQL correct à partir d’une demande vague, mais les migrations sûres exigent du contexte. Traitez votre prompt comme un mini cahier des charges : montrez ce qui existe, expliquez ce qui ne doit pas casser, et définissez ce que « sûr » signifie pour votre rollout.

Commencez par coller uniquement les faits de base de données qui comptent. Incluez la définition de la table ainsi que les indexes et contraintes pertinents (clés primaires, uniques, clés étrangères, check constraints, triggers). Si des tables liées sont impliquées, incluez aussi ces extraits. Un petit extrait précis empêche le modèle de deviner des noms ou d’ignorer une contrainte importante.

Ajoutez l’échelle réelle. Nombre de lignes, taille des tables, débit d’écriture et pics de trafic doivent influencer le plan. « 200M de lignes et 1k écritures/sec » est une migration différente de « 20k lignes et lecture majoritaire ». Indiquez aussi votre version de Postgres et comment les migrations s’exécutent dans votre système (transaction unique vs étapes multiples).

Décrivez comment l’application utilise les données : lectures importantes, écritures et jobs en arrière‑plan. Exemples : « l’API lit par email », « des workers mettent à jour le statut », ou « des reports scannent par created_at ». C’est ce qui déterminera si vous avez besoin d’un pattern expand/contract, de feature flags, et à quel point un backfill est sûr.

Enfin, soyez explicite sur les contraintes et les livrables. Une structure simple fonctionne bien :

• Extraits du schéma actuel et objectif
• Hypothèses d’échelle (lignes, écritures/sec, fenêtre de maintenance si elle existe)
• Dépendances de l’app (requêtes/endpoints/jobs)
• Contraintes strictes (pas de downtime, éviter les réécritures complètes)
• Livrables : SQL plus plan d’exécution en langage clair, vérification et rollback

Demander à la fois le SQL et un runplan force le modèle à penser à l’ordre, aux risques et à ce qu’il faut vérifier avant d’envoyer en production.

Expand/contract en clair (et quand l’utiliser)

Le pattern expand/contract change une base PostgreSQL sans casser l’app pendant que le changement est en cours. Plutôt qu’un seul basculement risqué, vous faites en sorte que la base supporte les deux formes pendant un temps donné.

Considérez‑le comme : ajouter de nouvelles choses en sécurité (expand), transférer le trafic et les données progressivement, puis supprimer les anciennes pièces (contract). C’est particulièrement utile avec l’aide de l’IA parce que cela vous oblige à planifier le milieu du processus, souvent chaotique.

Les quatre phases

Un flux pratique ressemble à ceci :

• Expand : ajouter une nouvelle colonne nullable ou une table, ajouter un index si nécessaire, et ajouter des contraintes d’une manière qui évite le blocage (par exemple ajouter les contraintes en NOT VALID quand approprié).
• Compatibilité : mettre à jour l’app pour qu’elle gère les deux champs. Cela peut être du dual‑write (écrire dans les deux emplacements) ou du read‑fallback (lire la nouvelle valeur et retomber sur l’ancienne si besoin).
• Backfill : copier les anciennes données vers la nouvelle colonne en petits lots, avec points de contrôle et possibilité de reprendre.
• Contract : une fois que tout utilise la nouvelle voie, renforcer les règles (mettre NOT NULL, valider les contraintes), puis supprimer l’ancienne colonne ou table.

Utilisez ce pattern chaque fois que des utilisateurs peuvent rester sur une ancienne version de l’app pendant que la base change. Ça inclut les déploiements multi‑instance, les apps mobiles qui mettent à jour lentement, ou toute release où une migration peut durer des minutes ou des heures.

Une tactique utile est de planifier deux releases. La Release 1 fait l’expand et la compatibilité pour que rien ne casse si le backfill est incomplet. La Release 2 fait le contract seulement après confirmation que le nouveau code et les nouvelles données sont en place.

Modèle de prompt sûr pour les migrations expand/contract

Copiez ce modèle et remplissez les crochets. Il pousse Claude Code à produire du SQL exécutable, des contrôles pour prouver que ça a fonctionné, et un plan de rollback exécutable.

You are helping me plan a PostgreSQL expand-contract migration.

Context
- App: [what the feature does, who uses it]
- Database: PostgreSQL [version if known]
- Table sizes: [rough row counts], write rate: [low/medium/high]
- Zero/near-zero downtime required: [yes/no]

Goal
- Change: [describe the schema change]
- Current schema (relevant parts):
  [paste CREATE TABLE or \d output]
- How the app will change (expand phase and contract phase):
  - Expand: [new columns/indexes/triggers, dual-write, read preference]
  - Contract: [when/how we stop writing old fields and remove them]

Hard safety requirements
- Prefer lock-safe operations. Avoid full table rewrites on large tables when possible.
- If any step can block writes, call it out explicitly and suggest alternatives.
- Use small, reversible steps. No “big bang” changes.

Deliverables
1) UP migration SQL (expand)
   - Use clear comments.
   - If you propose indexes, tell me if they should be created CONCURRENTLY.
   - If you propose constraints, tell me whether to add them NOT VALID then VALIDATE.

2) Verification queries
   - Queries to confirm the new schema exists.
   - Queries to confirm data is being written to both old and new structures (if dual-write).
   - Queries to estimate whether the change caused bloat/slow queries/locks.

3) Rollback plan (realistic)
   - DOWN migration SQL (only if it is truly safe).
   - If down is not safe, write a rollback runbook:
     - how to stop the app change
     - how to switch reads back
     - what data might be lost or need re-backfill

4) Runbook notes
   - Exact order of operations (including app deploy steps).
   - What to monitor during the run (errors, latency, deadlocks, lock waits).
   - “Stop/continue” checkpoints.

Output format
- Separate sections titled: UP.sql, VERIFY.sql, DOWN.sql (or ROLLBACK.md), RUNBOOK.md

Deux lignes supplémentaires utiles en pratique :

• Demandez au modèle d’étiqueter toute étape qui bloque les écritures par RISK: blocks writes, plus quand l’exécuter (heures creuses vs n’importe quand).
• Forcez l’honnêteté sur les verrous : « If you're not sure whether a statement takes an ACCESS EXCLUSIVE lock, say so and offer a safer option. »

Opérations de schéma courantes et comment demander du SQL plus sûr

De petits changements de schéma peuvent toujours faire mal s’ils provoquent des verrous longs, réécrivent de larges tables ou échouent à mi‑parcours. Quand vous utilisez Claude Code pour des migrations, demandez du SQL qui évite les réécritures et garde l’app fonctionnelle pendant que la base rattrape son retard.

Ajouter des colonnes et des defaults (sans verrous longs)

Ajouter une colonne nullable est généralement sûr. Ajouter une colonne avec un default non null peut être risqué sur d’anciennes versions de Postgres car cela peut réécrire toute la table.

Une approche plus sûre est un changement en deux étapes : ajouter la colonne NULL sans default, backfiller par lots, puis définir le default pour les nouvelles lignes et mettre NOT NULL une fois que les données sont propres.

Si vous devez imposer un default immédiatement, exigez une explication du comportement des verrous pour votre version de Postgres et un plan de repli si l’exécution dure plus longtemps que prévu.

Index, FKs, contraintes, suppressions

Pour les indexes sur de grandes tables, demandez CREATE INDEX CONCURRENTLY afin que lectures et écritures continuent. Notez aussi que cette commande ne peut pas s’exécuter dans un bloc transactionnel, ce qui signifie que votre outil de migration doit supporter une étape non transactionnelle.

Pour les clés étrangères, le chemin plus sûr est souvent d’ajouter la contrainte en NOT VALID puis de la valider plus tard. Cela accélère l’étape initiale tout en appliquant l’intégrité pour les nouvelles écritures.

Quand vous renforcez des contraintes (NOT NULL, UNIQUE, CHECK), demandez « nettoyer d’abord, appliquer ensuite ». La migration doit détecter les lignes problématiques, les corriger, puis activer la règle plus stricte.

Ne supprimez un objet qu’après un cycle de release complet et après avoir confirmé que rien ne le lit.

Si vous voulez une checklist courte à coller dans les prompts :

• Inclure des notes sur les verrous et le temps d’exécution attendu.
• Utiliser CONCURRENTLY pour les gros indexes et rappeler la limite transactionnelle.
• Préférer NOT VALID puis VALIDATE pour les nouvelles FKs.
• Séparer le backfill de l’application des contraintes NOT NULL/UNIQUE.
• Supprimer les objets seulement après un cycle de release complet.

Prompts pour des backfills lents, réguliers et récupérables

Les backfills sont souvent la source principale de douleur, pas l’ALTER TABLE. Les prompts les plus sûrs traitent les backfills comme des jobs contrôlés : mesurables, redémarrables et peu intrusifs.

Commencez par des contrôles d’acceptation faciles à exécuter et difficiles à contester : comptes de lignes attendus, taux de null cible, et quelques vérifications ponctuelles (par ex. comparer ancienne vs nouvelle valeur pour 20 IDs aléatoires).

Puis demandez un plan de batching. Les lots gardent les verrous courts et réduisent les surprises. Une bonne requête précise :

• Comment batcher (plages de PK ou fenêtres temporelles comme created_at)
• Taille de lot cible (par ex. 5 000 à 50 000 lignes)
• Si dormir entre les lots sur les tables chaudes
• Que chaque lot soit une transaction claire et courte

Exigez l’idempotence parce que les backfills échouent parfois. Le SQL doit pouvoir être relancé sans dupliquer ou corrompre les données. Schémas typiques : « update seulement où la nouvelle colonne est NULL » ou une règle déterministe.

Précisez aussi comment l’app reste correcte pendant le backfill. Si des écritures continuent d’arriver, il vous faut une passerelle : dual‑write, trigger temporaire, ou read‑fallback (lire la nouvelle si présente, sinon l’ancienne). Indiquez l’approche que vous pouvez déployer en toute sécurité.

Enfin, intégrez pause et reprise dans la conception. Demandez un suivi de progression et des checkpoints, comme une petite table qui stocke la dernière ID traitée et une requête qui rend compte de la progression (lignes mises à jour, dernière ID, heure de début).

Exemple : vous ajoutez users.full_name dérivé de first_name et last_name. Un backfill sûr met à jour uniquement les lignes où full_name IS NULL, fonctionne par plages d’ID, enregistre la dernière ID traitée, et assure que les nouvelles inscriptions sont correctes via dual‑write jusqu’au basculement complet.

Comment demander des plans de rollback qui fonctionnent en vrai

Un plan de rollback n’est pas juste « écrire un down migration ». C’est deux problèmes : annuler le changement de schéma et gérer les données modifiées pendant que la nouvelle version était vivante. Le rollback de schéma est souvent possible. Le rollback de données, rarement, sauf si vous l’avez prévu.

Soyez explicite sur ce que « rollback » signifie pour votre changement. Si vous supprimez une colonne ou réécrivez des valeurs en place, exigez une réponse réaliste du type : « Le rollback restaure la compatibilité de l’app, mais les données originales ne peuvent être récupérées sans snapshot. » Cette honnêteté vous sauve.

Demandez des déclencheurs de rollback clairs pour éviter les discussions pendant un incident. Exemples :

• Le taux d’erreur ou la latence dépasse un seuil défini pendant 10 minutes
• Un plan de requête critique se dégrade (par ex. seq scan sur une table chaude)
• Le job de backfill accuse un retard de plus de N heures
• Les vérifications de données échouent (nulls non attendus, doublons, lignes manquantes)
• Une étape de migration bloque les écritures plus de X secondes

Exigez le package complet de rollback, pas seulement du SQL : DOWN SQL (si sûr), modifications d’app/code pour la compatibilité, et comment arrêter les jobs en arrière‑plan.

Un pattern utile :

Produce a rollback plan for this migration.
Include: down migration SQL, app config/code switches needed for compatibility, and the exact order of steps.
State what can be rolled back (schema) vs what cannot (data) and what evidence we need before deciding.
Include rollback triggers with thresholds.

Avant de livrer, capture un « safety snapshot » léger pour comparer avant/après :

• Comptes de lignes pour les tables affectées (et sous‑ensembles clés)
• Un petit jeu d’exemples avec résultats attendus
• Agrégats simples (sum, min/max) pour les colonnes touchées
• Une courte liste d’IDs pour spot‑check avant/après

Indiquez aussi quand ne pas rollback. Si vous avez seulement ajouté une colonne nullable et que l’app dual‑write, une correction forward (hotfix, pause du backfill, reprise) est souvent plus sûre que revenir en arrière et créer encore plus de divergence.

Erreurs courantes à surveiller avec des migrations assistées par IA

L’IA peut écrire du SQL vite, mais elle ne voit pas votre base de production. La plupart des échecs viennent d’un prompt vague et d’un modèle qui comble les blancs.

Piège courant : sauter le schéma actuel. Si vous ne collez pas la définition de table, les indexes et contraintes, le SQL peut viser des colonnes inexistantes ou ignorer une règle d’unicité qui transforme un backfill en opération longue et verrouillante.

Autre erreur : expédier expand, backfill et contract dans un seul déploiement. Cela supprime votre filet de sécurité. Si le backfill est long ou échoue à mi‑parcours, vous vous retrouvez avec une app qui attend l’état final.

Les problèmes les plus fréquents :

• Backfills non idempotents sans suivi de progression
• Ajouter NOT NULL, UNIQUE ou des FKs avant nettoyage et validation des données
• Transactions longues sans timeouts de lock ou de statement
• Pas de requêtes de vérification, donc les problèmes restent cachés jusqu’à ce que les utilisateurs les rencontrent

Un exemple concret : « renommer une colonne et mettre à jour l’app ». Si le plan généré renomme et backfille dans une seule transaction, un backfill lent peut bloquer et casser le trafic. Un prompt plus sûr force des petits lots, des timeouts explicites et des vérifications avant suppression de l’ancien chemin.

Ce qu’il faut vérifier en staging avant de livrer

La staging révèle des problèmes qui n’apparaissent jamais sur une petite base dev : verrous longs, nulls inattendus, index manquants, chemins de code oubliés.

D’abord, vérifiez que le schéma correspond au plan après la migration : colonnes, types, defaults, contraintes et indexes. Un coup d’œil rapide ne suffit pas. Un index manquant peut transformer un backfill sûr en chaudron ardent.

Ensuite, exécutez la migration sur un dataset réaliste. Idéalement, c’est une copie récente de la production avec masquage des champs sensibles. Si ce n’est pas possible, au moins reproduisez le volume et les hotspots (grosses tables, lignes larges, tables très indexées). Enregistrez les temps d’exécution de chaque étape pour savoir à quoi vous attendre en production.

Checklist staging courte :

• Le schéma correspond au plan (colonnes, types, contraintes, indexes)
• Temps d’exécution enregistrés sur un volume réaliste
• Compatibilité testée : ancienne app avec nouveau schéma et nouvelle app avec ancien schéma (selon le plan)
• Requêtes de vérification exécutées : taux de null, comptes de lignes, checks d’orphan pour nouvelles FKs, exemples de lecture
• Signaux opérationnels surveillés pendant l’exécution : verrous, deadlocks, timeouts, requêtes lentes

Enfin, testez des parcours utilisateurs réels, pas seulement du SQL. Créez, mettez à jour et lisez des enregistrements touchés par le changement. Si expand/contract est prévu, confirmez que les deux schémas fonctionnent jusqu’au cleanup final.

Exemple réaliste : changer une colonne sans casser les utilisateurs

Imaginez une colonne users.name qui contient des noms complets comme « Ada Lovelace ». Vous voulez first_name et last_name, mais vous ne pouvez pas casser les inscriptions, les profils ou l’admin pendant le rollout.

Commencez par un expand sûr même si aucun code n’est déployé : ajoutez des colonnes nullable, gardez l’ancienne colonne, et évitez les verrous longs.

ALTER TABLE users ADD COLUMN first_name text;
ALTER TABLE users ADD COLUMN last_name text;

Ensuite, adaptez le comportement de l’app pour gérer les deux schémas. Dans la Release 1, l’app doit lire les nouvelles colonnes si elles sont présentes, retomber sur name si elles sont nulles, et écrire dans les deux pour que les nouvelles données restent cohérentes.

Vient ensuite le backfill. Lancez un job par lots qui met à jour un petit nombre de lignes par exécution, enregistre la progression et peut être pausé en toute sécurité. Par exemple : mettre à jour users où first_name est null en ordre croissant d’ID, 1 000 à la fois, et logger le nombre de lignes modifiées.

Avant de durcir les règles, validez en staging :

• Les nouvelles inscriptions remplissent first_name et last_name tout en continuant à renseigner name
• Les utilisateurs existants s’affichent correctement même si seul name est présent
• Le backfill peut s’arrêter et redémarrer sans dupliquer le travail
• Aucune null inattendue n’existe après la fin du backfill
• Les requêtes basiques sur users ne sont pas sensiblement plus lentes

La Release 2 bascule les lectures sur les nouvelles colonnes uniquement. Ce n’est qu’après cela qu’il faut ajouter des contraintes (SET NOT NULL) et supprimer name, idéalement dans un déploiement séparé.

Pour le rollback, restez simple. L’app continue de lire name pendant la transition et le backfill est arrêt‑/reprenable. Si vous devez rollback la Release 2, repassez les lectures sur name et laissez les nouvelles colonnes en place jusqu’à stabilisation.

Prochaines étapes : transformer vos prompts en routine de migration répétable

Traitez chaque changement comme un petit runbook. L’objectif n’est pas un prompt parfait, mais une routine qui impose les bons détails : schéma, contraintes, plan d’exécution et rollback.

Standardisez ce que toute demande de migration doit inclure :

• Schéma actuel et changement exact (tables, colonnes, indexes)
• Contraintes et faits de trafic (taille des tables, débit d’écriture, downtime autorisé)
• Séquence de release (expand, déployer l’app, backfill, contract)
• Comment vous observerez la progression (requêtes/métriques, durée attendue)
• Étapes de rollback (quoi revert en premier, quelles données peuvent être perdues)

Décidez qui est responsable de chaque étape avant d’exécuter du SQL. Une répartition simple évite le « tout le monde pensait que quelqu’un d’autre s’en chargeait » : les développeurs rédigent le prompt et le code de migration, l’ops gère le timing en production et la surveillance, QA vérifie le comportement en staging et les cas limites, et une personne unique prend la décision finale go/no‑go.

Si vous construisez des apps via chat, rédiger la séquence avant de générer du SQL aide. Pour les équipes utilisant Koder.ai, Planning Mode est un bon endroit pour écrire cette séquence, et les snapshots + rollback réduisent le rayon d’impact si quelque chose d’inattendu survient pendant le rollout.

Après livraison, planifiez immédiatement le cleanup contract pendant que le contexte est frais, pour éviter que d’anciennes colonnes et du code de compatibilité temporaires traînent pendant des mois.