ALTER TABLE sans panne : la file de verrous

Ajouter une colonne nullable prend un millième de seconde, puisque PostgreSQL modifie le catalogue et ne touche pas aux lignes. C'est vrai, et c'est précisément ce qui rend la panne incompréhensible la première fois. La migration part pendant le déploiement, et pendant quatre minutes plus aucune requête ne passe sur la table invoice, pas même un SELECT. L'ALTER TABLE n'a pas été lent. Il n'a même pas commencé. Il attendait, et tout le monde attendait derrière lui.

Le danger d'une migration tient donc rarement à la durée de l'opération. Il tient à la durée de l'attente du verrou, pendant laquelle votre commande bloque déjà les autres.

Quand un SELECT attend derrière l'ALTER

ALTER TABLE demande dans la plupart de ses formes un verrou ACCESS EXCLUSIVE, incompatible avec tous les autres, y compris l'ACCESS SHARE que prend un simple SELECT. S'il y a sur la table une transaction ouverte qui l'a lue, l'ALTER doit attendre qu'elle se termine. Jusque-là, rien d'anormal.

La panne vient de la file. Une demande de verrou qui entre en conflit avec une demande déjà en attente se range derrière elle. Le SELECT qui arrive après votre ALTER n'est pas en conflit avec la transaction en cours, mais il l'est avec votre ACCESS EXCLUSIVE en attente, et il attend donc aussi.

t0   Transaction A : SELECT … FROM invoice   (ACCESS SHARE, reste ouverte)
t1   Migration     : ALTER TABLE invoice …   (ACCESS EXCLUSIVE, attend A)
t2   Requête web   : SELECT … FROM invoice   (attend la migration)
t3   Requête web   : UPDATE invoice …        (attend la migration)
…    le pool de connexions se vide, l'API répond 503
tN   A se termine → ALTER s'exécute en 1 ms → tout repart

La transaction A n'a pas besoin d'être pathologique. Un export qui tourne dix minutes, un worker qui a ouvert une transaction puis appelle une API externe, une session psql oubliée dans un terminal avec un BEGIN : n'importe laquelle suffit. La panne dure exactement ce qui reste à vivre à la plus vieille transaction ayant touché la table.

lock_timeout et retry : échouer vite plutôt qu'attendre

La parade consiste à refuser d'attendre longtemps. lock_timeout fait échouer une commande qui n'obtient pas son verrou dans le délai imparti ; l'échec retire la demande de la file et libère tous ceux qui s'étaient rangés derrière.

#!/usr/bin/env bash
# Une instruction DDL, un verrou, plusieurs tentatives.
for attempt in 1 2 3 4 5 6 7 8; do
  psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL' && exit 0
SET lock_timeout = '2s';
SET statement_timeout = '30s';
ALTER TABLE invoice ADD COLUMN paid_at timestamptz;
SQL
  sleep $(( attempt * 5 + RANDOM % 5 ))
done
echo "verrou jamais obtenu, migration abandonnée" >&2
exit 1

Dans le pire des cas, les requêtes sur invoice subissent deux secondes de latence par tentative, puis repartent. Vous échangez une panne de durée inconnue contre une série de ralentissements bornés. Deux secondes est un ordre de grandeur à ajuster ; il doit rester inférieur au timeout de vos requêtes applicatives, sinon le ralentissement redevient une erreur visible.

Le patron s'accompagne de quelques règles.

Une instruction DDL par transaction. Un verrou est tenu jusqu'au COMMIT. Un outil de migration qui enveloppe dans une seule transaction trois ALTER sur trois tables et un UPDATE de reprise de données garde le premier ACCESS EXCLUSIVE pendant tout l'UPDATE. Il faut découper, ou désactiver l'enveloppe transactionnelle pour ces migrations.

lock_timeout posé par la migration elle-même. Mis globalement dans la configuration du rôle applicatif, il ferait échouer des requêtes métier légitimes qui attendent un verrou de ligne.

Pas de transactions longues. idle_in_transaction_session_timeout à quelques minutes sur le rôle applicatif coupe les sessions qui dorment avec une transaction ouverte. De toutes les mesures, c'est celle qui réduit le plus le nombre de tentatives nécessaires.

Quand le retry n'aboutit jamais, il faut trouver qui tient la table :

SELECT a.pid, a.state, a.xact_start, left(a.query, 80) AS query
FROM pg_stat_activity a
WHERE a.pid = ANY (pg_blocking_pids(<pid de la migration>));

Les ALTER qui parcourent ou réécrivent la table

lock_timeout règle l'attente. Reste ce que la commande fait une fois le verrou obtenu, car certaines formes d'ALTER TABLE gardent l'ACCESS EXCLUSIVE pendant un parcours complet ou une réécriture de la table. Pour celles-là, il existe presque toujours une variante en plusieurs étapes dont chacune ne tient un verrou fort qu'un instant.

Le principe est le même partout. NOT VALID inscrit la contrainte pour les nouvelles écritures sans vérifier l'existant, puis VALIDATE CONSTRAINT vérifie l'existant sous un verrou SHARE UPDATE EXCLUSIVE, qui laisse passer lectures et écritures.

ALTER TABLE invoice_line
  ADD CONSTRAINT invoice_line_invoice_fk
  FOREIGN KEY (invoice_id) REFERENCES invoice (id) NOT VALID;

-- Transaction distincte, peut durer : ne bloque ni lecture ni écriture.
ALTER TABLE invoice_line VALIDATE CONSTRAINT invoice_line_invoice_fk;

Pour le NOT NULL, PostgreSQL sait reconnaître une contrainte CHECK validée qui prouve l'absence de NULL et s'épargne alors le parcours.

Une ligne de SQL devient quatre déploiements

Ce que vous payez, c'est la simplicité. Un changement de type qui tenait en une ligne devient quatre déploiements : ajouter la colonne, écrire dans les deux, reprendre l'historique par lots, lire dans la nouvelle, supprimer l'ancienne. Entre chaque étape, le code doit tolérer les deux états du schéma, donc l'ancienne et la nouvelle version de l'application doivent pouvoir tourner ensemble. C'est du code temporaire à écrire, à tester et à ne pas oublier de retirer.

Le script de retry est aussi une source d'échec assumée. Un déploiement peut désormais s'arrêter parce que la migration n'a pas obtenu son verrou en huit tentatives. C'est le bon comportement, à condition que le pipeline sache s'arrêter proprement à ce stade et que l'application déjà déployée fonctionne sans la migration. D'où l'ordre que j'impose, avec un schéma qui s'élargit avant le code qui s'en sert et se rétrécit après le retrait du code qui s'en servait.

Dernier coût, moins visible, les contraintes NOT VALID oubliées. Une contrainte jamais validée protège les nouvelles lignes et ne dit rien des anciennes, et le planificateur ne s'appuie pas dessus. Une vérification en CI qui liste les contraintes non validées et les index invalides évite qu'elles s'accumulent.

Les tables où l'ALTER direct suffit

Le protocole en plusieurs étapes n'a de sens que si l'opération directe dure. Sur une table de quelques dizaines de milliers de lignes, un parcours complet prend quelques millisecondes, et l'ALTER direct est plus lisible, sans rien à nettoyer ensuite. Même chose avant la mise en service d'un produit, ou sur une table que seul un job nocturne touche. Et si vous disposez d'une vraie fenêtre de maintenance acceptée par vos clients, l'utiliser pour une réécriture lourde est parfois plus sûr que trois semaines de double écriture.

lock_timeout, en revanche, ne se discute pas. Il ne coûte rien, et la file de verrous se forme aussi sur une table de cent lignes si elle est lue à chaque requête. Avant de laisser partir une migration, je vérifie cinq points, et un « non » au premier suffit à la refuser :

• Chaque instruction DDL a-t-elle un lock_timeout et sa propre transaction ?
• Une fois le verrou obtenu, l'instruction parcourt-elle ou réécrit-elle la table ? Si oui, quelle taille fait la table ?
• La migration peut-elle être relancée après un échec à n'importe quelle étape ?
• L'application actuellement en production fonctionne-t-elle avec le schéma d'après ?
• Sait-on quelle est la plus longue transaction habituelle sur cette table, et qui la lance ?