Migrations — Flyway, forward-only, index manuels¶

La décision¶

Le schéma PostgreSQL est versionné en SQL pur avec Flyway (migration/flyway/V<YYYYMMDDHHMM>__description.sql), en forward-only : pas de down-migration. Les gros index sur tables chargées se créent à la main (CREATE INDEX CONCURRENTLY), hors Flyway.

Le contexte¶

Flyway a été choisi par le passé : il correspondait aux contraintes, il marche bien et il est fiable — les raisons précises du choix initial se sont perdues, mais rien ne motive d'en changer. L'historique pré-Flyway (199 fichiers numérotés) sert de baseline dans migration/legacy-before-flyway/. TypeORM est à synchronize: false et ne touche jamais au schéma ; les outils de migration Kysely ne sont pas utilisés — la migration est l'affaire du SQL, pas du runtime applicatif.

Pourquoi forward-only¶

Pas de down-migrations : ça simplifie énormément le process, et un rollback de schéma est de toute façon très pénible — et rarement testé honnêtement. En cas de problème, on corrige en avant avec une nouvelle migration. Flyway n'est invoqué qu'avec info / migrate / validate (flyway.sh).

Le runbook des index manuels¶

Le problème. CREATE INDEX bloque les écritures pendant la construction ; CREATE INDEX CONCURRENTLY ne bloque pas, mais PostgreSQL l'interdit dans une transaction — et Flyway exécute chaque migration en transaction. Sur les tables volumineuses (souvent les tables invest-facing : customers ~700k rows, wallet_transactions, bricks…), l'index passe donc hors Flyway.

Le process.

Écrire le script dans migration/manual/, nommé YYYY-MM-DD-<description>.sql : contrairement à Flyway, rien ne trace ces scripts en base, donc la date du fichier est la seule façon de savoir ce qui a été joué et dans quel ordre. Contenu : CREATE INDEX CONCURRENTLY IF NOT EXISTS … suivi d'un ANALYZE <table>, et le contexte documenté — requêtes ciblées et métriques avant/après, ex. 2025-12-24-portfolio-home-metrics-indexes-concurrent.sql (30 590 ms → 246 ms). Les fichiers antérieurs ont été datés rétroactivement depuis leur commit d'ajout.
L'exécuter manuellement en production avant le déploiement du code qui s'appuie dessus.
IF NOT EXISTS rend le script idempotent : un environnement reconstruit peut le rejouer sans douleur.

Tables partitionnées. CREATE INDEX CONCURRENTLY ne s'applique pas à la table parente — il faut créer l'index sur chaque partition séparément. Exemple : bricks est partitionnée en bricks_not_primary_available, bricks_primary_available, etc. Le script 2025-12-24-portfolio-home-metrics-indexes-concurrent.sql montre le pattern (un CREATE INDEX CONCURRENTLY par partition, puis ANALYZE sur la table parente).

FK sur table partitionnée. ADD FOREIGN KEY … NOT VALID est interdit sur le parent (PG 16/17). Poser NOT VALID sur chaque partition (détention du SHARE ROW EXCLUSIVE = ordre de la milliseconde, métadonnées seules ; après COMMIT les nouvelles lignes sont checkées), puis VALIDATE CONSTRAINT dans une migration Flyway suivante (SHARE UPDATE EXCLUSIVE — writes app OK). Ne pas poser la FK validée sur le parent : SHARE ROW EXCLUSIVE (writes bloqués) le temps du scan. Flyway initSql pose statement_timeout = 0. Réf. V202608282315 + V202608290933 (bricks).

lock_timeout. Flyway initSql le pose à 0, donc toute demande de verrou attend sans borne. Deux cas à distinguer, parce que la gravité n'est pas la même :

Verrou	Exemple	Sans `lock_timeout`
`ACCESS EXCLUSIVE`	`ADD CONSTRAINT`, `ADD COLUMN` avec réécriture	La demande en attente met le trafic en file derrière elle → incident
`SHARE UPDATE EXCLUSIVE`	`ALTER TABLE … SET (autovacuum_*)`, `VALIDATE CONSTRAINT`	Le trafic passe (vérifié : `SELECT`/`INSERT`/`UPDATE` à 30 ms pendant qu'un `ALTER` attend) ; seul le deploy reste pendu

Donc SET LOCAL lock_timeout est obligatoire dans le premier cas. Dans le second, il protège l'opération manuelle en cours, pas le deploy : un REINDEX CONCURRENTLY détient ce verrou pendant des minutes, et sa phase « waiting for old snapshots » attend la transaction Flyway, qui attend le verrou du REINDEX. Postgres détecte le cycle et sacrifie le REINDEX (ERROR: deadlock detected), pas la migration. Le timeout doit donc rester court devant la phase de build du REINDEX pour que Flyway abandonne avant que le cycle se forme — 30 s face à ~7 min de build. Corollaire opérationnel : ne pas déclencher de déploiement pendant un REINDEX CONCURRENTLY sur la même table.

REINDEX CONCURRENTLY. Même contrainte transactionnelle que CREATE INDEX CONCURRENTLY → migration/manual/. Trois pièges en plus : il construit un second index avant de basculer (prévoir la taille de l'index en disque libre, un index à la fois) ; il attend les transactions détenant un snapshot conflictuel (vérifier pg_stat_activity avant) ; interrompu, il laisse un index invalide suffixé _ccnew qui occupe le disque jusqu'à un DROP INDEX CONCURRENTLY explicite. Réf. 2026-08-30-p2p-worker-indexes-reindex-concurrent.sql.

Autovacuum par table. ALTER TABLE … SET (autovacuum_*) est métadonnées seules (pas de scan, pas de réécriture) : ça reste dans Flyway. Le seuil de vacuum est autovacuum_vacuum_threshold + scale_factor * reltuples — avec le scale factor global de 0.2, une table de dizaines de millions de lignes n'est jamais nettoyée, et ses index partiels sur colonnes à fort churn bloatent. Baisser le scale factor par table est le seul remède durable ; le REINDEX ne fait que rendre le disque déjà perdu. Réf. V202608301613 (wallet_transactions : seuil 8,7 M → 87 k).

Sur Neon, c'est le seul levier — et il compte davantage. Neon n'expose pas les paramètres instance (hors contexte session / base / rôle), donc le scale factor global reste à 0.2 : les reloptions par table sont la seule façon de le contourner. Elles sont dans le catalogue, donc elles survivent aux restarts de compute et suivent les branches créées depuis prod. Les compteurs qui déclenchent l'autovacuum, eux, viennent du cumulative statistics system, que Neon perd à chaque suspend / restart de compute — autovacuum ne voit que le churn accumulé depuis l'activation du compute. Un seuil à 8,7 M n'est donc jamais atteint entre deux restarts, là où 87 k l'est en quelques minutes. Corollaires : ne pas conclure d'un last_autovacuum à NULL juste après un restart que le réglage est inopérant (lire après une fenêtre de trafic, ou utiliser neon inspect db vacuum-stats) ; et si neon.pgstat_file_size_limit vaut 0 sur le projet (réglage Neon, pas modifiable côté app), prévoir un VACUUM planifié en filet de sécurité.

Vacuum ≠ reindex. Le vacuum retire les entrées mortes de l'arbre — le scan redevient court — mais les pages vidées repassent dans la free space map, elles ne sont pas rendues au disque. Seul un REINDEX rétrécit le fichier. Corollaire pour le diagnostic : le signal de bloat d'un index est sa taille rapportée au nombre d'entrées vivantes, pas avg_leaf_density (qui reste à ~90 % tant que les pages sont pleines d'entrées mortes).

Le parent ne porte aucune FK. Toute partition bricks recréée ou ré-attachée doit re-poser les 4 contraintes. CREATE TABLE … (LIKE bricks INCLUDING ALL) n'en copie aucune — c'est exactement comme ça qu'elles ont disparu en 088.

Enfiler un job au déploiement¶

Le problème. Un changement de schéma peut laisser des données vides ou obsolètes jusqu'au prochain cron planifié (souvent la nuit). Exemple : nouvelles colonnes sur leaderboard_investor_live à 0 jusqu'au refresh de 3h UTC.

La solution. Une migration Flyway peut enfiler un run unique via graphile_worker.add_job, avec un run_at proche du déploiement :

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = 'graphile_worker') THEN
    PERFORM graphile_worker.add_job(
      identifier := 'refresh-leaderboard',
      payload := '{}'::json,
      queue_name := 'refresh-leaderboard-queue',
      run_at := now() + interval '5 minutes',
      job_key := 'refresh-leaderboard-initial-deploy'
    );
  END IF;
END $$;

Les invariants.

Point	Pourquoi
Garde `graphile_worker`	Le schéma n'existe pas sur une DB fraîche (CI, nouvel env) — le cron planifié rattrapera
`run_at` décalé (5–15 min)	Laisse le worker finir son rollout et enregistrer la task avant exécution
`job_key` unique	Déduplication — un seul run par déploiement
Migration après le DDL concerné	La table/colonnes doivent exister avant que le job ne tourne

Quand l'utiliser : backfill déclenché par un changement de schéma, dépendant d'un worker déjà déployé — pas pour remplacer un cron récurrent.

Quand ne pas l'utiliser : logique métier complexe (→ service ou cron dédié) ; sur un env sans worker, la garde sur le schéma suffit — le cron planifié rattrapera.

Exemples en prod :

V202606181500__schedule_initial_leaderboard_refresh.sql — premier peuplement de leaderboard_investor_live
V202606221300__schedule_leaderboard_refresh_contract_split.sql — backfill après ajout de colonnes split
V202606221401__schedule_leaderboard_refresh_per_investor.sql — repeuplement après changement de schéma de métriques

sequenceDiagram
  participant CI as Deploy_Prod
  participant Flyway
  participant DB as PostgreSQL
  participant Worker as Graphile_Worker

  CI->>Flyway: migrate
  Flyway->>DB: DDL + add_job run_at=now+5min
  CI->>Worker: rollout nouvelle version
  Worker->>DB: execute job refresh-leaderboard

Où et quand Flyway s'exécute¶

Contexte	Mécanisme
Production	`api-release-prod.yml` : job `preflight` avant l'approbation `API Prod` (`flyway-check-pending.sh` : info, fail sur `Ignored`, pending listées dans le summary lu par le relecteur) → approbation `API Prod` → job `migrate` (`api-release-env.yml`) : `migrate` toujours → `validate` toujours → seconde approbation `API Prod` → job `deploy`
Dev	`api-release-dev.yml` → même job `migrate` (`api-release-env.yml`) : `flyway-check-pending.sh` → `migrate` toujours → `validate` toujours. Mêmes steps que prod, sans preflight ni approbation
Tests d'intégration	Service Flyway du docker-compose : snapshot baseline + migrations delta (guide)
Dev local	Branches Neon personnelles via Doppler — pas de Postgres local

Out-of-order. Un timestamp de migration plus ancien que le max déjà appliqué en DB → Flyway ne l'applique pas. Fix : renommer le fichier avec une version strictement après le max de __flyway_schema_history__ (contenu inchangé), uniquement si l'ancienne version n'a jamais été appliquée. Jamais outOfOrder=true en CI permanente.

Une migration appliquée est gelée — commentaires compris. Flyway stocke un checksum du contenu dans __flyway_schema_history__ et validate (dernière étape du deploy prod) échoue sur toute divergence. Corriger une coquille ou rafraîchir un lien dans un fichier déjà appliqué casse donc le déploiement. Conséquence assumée : deux migrations appliquées (V202605250743, V202608121100) citent encore des scripts de migration/manual/ sous leur ancien nom sans préfixe de date. Un grep sur la partie descriptive du nom retombe sur le bon fichier.

Migrate before traffic. En prod, Flyway tourne avant le deploy du code : le nouvel app ne sert jamais de traffic contre un schéma pré-migration. Conséquence : pas de dual-read / dual-write « pour la race de deploy » sur un rename ou une migration JSON — le code peut assumer que la migration a déjà run.

Les types suivent à la main¶

Une migration qui touche une table de la stack Kysely implique, dans la même PR : le SQL Flyway, le schema zod (__new/lib/kysely/schemas/) et le type Database. Pas de codegen (pourquoi) — la discipline est le mécanisme de sync, et ky_parse* attrape le drift au runtime.