Modifier un schéma Postgres sans arrêter l’appli : élargir, puis contracter.

Chaque ALTER TABLE prend un verrou. La documentation de Postgres indique qu’ACCESS EXCLUSIVE est pris « sauf mention contraire », et que c’est le seul mode qui bloque un simple SELECT. Ce qui compte, c’est la durée : un changement qui ne touche que le catalogue garde le verrou quelques millisecondes, un balayage ou une réécriture le garde le temps de lire toute la table.

Un ALTER TABLE qui garde son verrou deux millisecondes peut quand même bloquer toutes les requêtes sur la table pendant des minutes. Il doit attendre la fin des transactions qui utilisent déjà la table. Pendant qu’il attend, les nouvelles requêtes qui demandent un verrou incompatible, simples SELECT compris, font la file derrière lui. Un rapport ou une tâche qui garde une transaction ouverte cinq minutes devient une panne de cinq minutes pour cette table. Xata et Postgres.ai le montrent étape par étape.

La solution, c’est lock_timeout. Postgres annule toute instruction qui attend un verrou plus longtemps que ce délai : la migration bloquée échoue et la file derrière elle se vide aussitôt. La documentation précise que la limite « s’applique séparément à chaque tentative d’obtention de verrou ».

• Choisissez une valeur courte. Quelques secondes suffisent pour un changement de catalogue. Si ça échoue, trouvez ce qui bloque et relancez la migration.
• Réglez-le par session, pas pour tout le serveur. La documentation de Postgres déconseille de le mettre dans postgresql.conf, « puisque cela toucherait toutes les sessions », y compris celles de votre appli.
• Trouvez ce qui bloque avant de relancer, avec la requête de la section suivante.

-- At the top of each migration that touches a busy table
SET lock_timeout = '3s';

ALTER TABLE orders ADD COLUMN status_code text;

pg_blocking_pids donne les sessions qui détiennent un verrou qu’une session attend. Lancez la requête pendant que la migration est coincée, ou juste avant un déploiement qui touche une table achalandée.

-- Who has had a transaction open the longest, and who is waiting on whom
SELECT pid, pg_blocking_pids(pid) AS blocked_by,
       now() - xact_start AS open_for, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

NOT VALID ajoute la contrainte sans vérifier les lignes existantes : c’est instantané, et les nouvelles écritures sont vérifiées dès lors. VALIDATE CONSTRAINT balaie la table plus tard sous SHARE UPDATE EXCLUSIVE, ce qui laisse passer lectures et écritures. Pour SET NOT NULL, la documentation de Postgres indique que le balayage est sauté quand une contrainte CHECK valide prouve déjà qu’aucun NULL ne peut exister.

-- Postgres 17: a CHECK constraint proves it, SET NOT NULL then skips the scan
ALTER TABLE orders ADD CONSTRAINT orders_status_nn
  CHECK (status_code IS NOT NULL) NOT VALID;          -- instant
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn; -- scans, writes keep going
ALTER TABLE orders ALTER COLUMN status_code SET NOT NULL; -- no scan
ALTER TABLE orders DROP CONSTRAINT orders_status_nn;

-- Postgres 18: NOT NULL itself can be NOT VALID
ALTER TABLE orders ADD CONSTRAINT orders_status_nn NOT NULL status_code NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn;

Un CREATE INDEX ordinaire laisse passer les lectures, mais chaque INSERT, UPDATE et DELETE sur la table attend la fin de la construction. CONCURRENTLY laisse passer les écritures, au prix de deux balayages de la table et d’attentes pour les transactions plus anciennes : c’est plus long au total.

Deux règles de la documentation de Postgres. La commande ne peut pas s’exécuter dans un bloc de transaction; il lui faut une migration qui n’en ouvre pas. Et si elle échoue (interblocage, valeur en double pour un index unique), elle laisse un index INVALID que les requêtes ignorent, mais que les écritures continuent de maintenir. Supprimez-le et relancez, ou faites REINDEX INDEX CONCURRENTLY.

Elle attend toutes les transactions qui pourraient utiliser la table : un lock_timeout court réglé par une migration précédente dans la même session peut l’annuler. Désactivez le délai pour cette instruction, comme ci-dessous.

-- Its own migration, outside a transaction
-- (Rails: disable_ddl_transaction!, Django: atomic = False)
SET lock_timeout = 0;
CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);

-- If it failed, it left an INVALID index behind. Drop it and run again:
DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx;

ADD FOREIGN KEY prend SHARE ROW EXCLUSIVE sur la table et sur la table référencée, ce qui bloque les écritures dans les deux pendant le balayage. Ajoutez-la NOT VALID et validez dans une deuxième étape; la validation prend SHARE UPDATE EXCLUSIVE sur votre table et ROW SHARE sur la table référencée.

ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
-- A later migration (or the same one, after the first commits):
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

Renommer est instantané dans Postgres et casse quand même la production : pendant le déploiement, l’ancien code tourne encore et demande l’ancien nom. Élargir puis contracter (« expand and contract ») garde chaque déploiement compatible avec le code d’avant. Pour renommer orders.status en status_code :

• Déploiement 1, élargir. La migration ajoute status_code. Le code écrit dans les deux colonnes et lit encore status.
• Remplissez les anciennes lignes par lots (section suivante).
• Déploiement 2. Le code lit status_code et écrit encore dans les deux.
• Déploiement 3. Le code cesse d’écrire dans status. Avec Rails, ajoutez-la à ignored_columns pour qu’Active Record l’oublie.
• Déploiement 4, contracter. La migration supprime status. La version en ligne pendant ce temps est celle du déploiement 3, qui n’y touche plus.

Un seul UPDATE sur dix millions de lignes, c’est une longue transaction : elle garde un verrou sur chaque ligne touchée jusqu’à la fin et laisse dix millions de lignes mortes au nettoyage (vacuum). De petits lots validés un à un gardent les verrous courts et laissent l’autovacuum suivre.

• Écrivez d’abord dans les deux colonnes (déploiement 1 ci-dessus), sinon les lignes créées pendant le remplissage reviennent à NULL.
• Découpez sur une clé indexée. ORDER BY id LIMIT 5000 sur la clé primaire reste rapide à mesure que le remplissage avance.
• Gardez les longs remplissages hors du déploiement. Chez Canner, la commande de mise en production est arrêtée après 15 minutes. Lancez la boucle vous-même par l’accès externe de la base, limité aux adresses IP que vous autorisez.

# Run from your machine through the database's external access,
# or from a protected route in your app. Each UPDATE commits on its own.
while true; do
  out=$(psql "$DB_URL" -c "
    UPDATE orders SET status_code = status
    WHERE id IN (
      SELECT id FROM orders
      WHERE status_code IS NULL AND status IS NOT NULL
      ORDER BY id LIMIT 5000
    )")
  echo "$out"
  [ "$out" = "UPDATE 0" ] && break
  sleep 1
done

Les migrations vont dans la commande de mise en production (Paramètres → Compilation). Elle s’exécute une fois par déploiement en production, après la compilation et avant le démarrage de la nouvelle version, pendant que la version actuelle sert encore. C’est pourquoi chaque migration doit convenir au code déjà en ligne. Si elle échoue, lock_timeout compris, le déploiement s’arrête et la version actuelle reste en ligne. Trouvez ce qui bloque et redéployez. Avec Prisma, marquez d’abord la migration échouée avec prisma migrate resolve --rolled-back <nom>; selon la documentation de Prisma, migrate deploy refuse de continuer tant que ce n’est pas fait.

Ensuite, Canner arrête le processus en marche (il a jusqu’à 30 secondes pour finir ses requêtes) et démarre le nouveau, qui doit répondre à sa vérification d’état en moins de 30 secondes. Les requêtes qui arrivent pendant la bascule peuvent échouer. Un déploiement chez Canner comporte donc une courte interruption, même quand le changement de schéma n’en cause aucune. Ce que cette page évite, c’est la panne plus longue : une table verrouillée pendant des minutes, ou du vieux code devant une colonne disparue. Détails dans la documentation des déploiements.

Sur l’instance dédiée, vous pouvez régler pour toute l’instance les délais d’expiration des instructions, des transactions inactives et de l’attente de verrou (canner db tuning --lock-timeout 5, en secondes). Ils s’appliquent sans redémarrage. Ils valent aussi pour les sessions de votre appli : gardez des valeurs que l’appli tolère, et réglez un lock_timeout plus serré dans les migrations. Les nouvelles bases roulent Postgres 17 par défaut, ou 18 si vous le choisissez; la forme NOT NULL … NOT VALID exige la 18.