Migrations PostgreSQL sans interruption : verrous et étapes

Pour modifier le schéma d’une base PostgreSQL en production sans interrompre le service, il faut faire trois choses : connaître le verrou que chaque commande prend, découper les changements risqués en petites étapes, et empêcher une commande d’attendre indéfiniment un verrou. Une migration qui passe en quelques secondes sur une base de test peut bloquer les lectures et les écritures sur une grande table. Voici les vérifications que nous ferions en premier, tirées de la documentation de PostgreSQL et d’un article de Stripe.

Pourquoi une migration bloque-t-elle une application?

Chaque commande ALTER TABLE prend un verrou. Selon la documentation de ALTER TABLE, un verrou ACCESS EXCLUSIVE est pris sauf indication contraire, et quand plusieurs sous-commandes sont combinées, c’est le verrou le plus strict qui s’applique. Selon la page sur les verrous explicites, ce verrou est en conflit avec tous les autres modes : il bloque donc les lectures comme les écritures des autres sessions.

Le danger a donc deux sources : la commande qui garde le verrou longtemps (par exemple pendant qu’elle réécrit ou parcourt toute la table) et la commande qui attend le verrou. La même page précise qu’une transaction qui demande un verrou en conflit attend sans limite, sauf interblocage détecté, tant que le verrou n’est pas libéré. Sans limite d’attente, une migration lancée au mauvais moment peut rester suspendue sans que vous sachiez pour combien de temps.

1. 1Demande du verrou
2. 2Attente de libération
3. 3Verrou obtenu
4. 4Commande exécutée

Avec lock_timeout, l’attente s’arrête à la limite fixée et la commande échoue au lieu de rester suspendue.

Quand l’ajout d’une colonne réécrit-il la table?

La documentation de PostgreSQL est précise sur l’ajout d’une colonne :

• Avec ADD COLUMN et une valeur par défaut non volatile, la valeur est stockée dans les métadonnées de la table. L’opération est très rapide, même sur une grande table, sans réécriture.
• Avec une valeur par défaut volatile (par exemple clock_timestamp()), une colonne générée stockée, une colonne d’identité ou une colonne d’un type domaine qui a des contraintes, la table entière et ses index sont réécrits.

Vérifiez donc le type de valeur par défaut avant de lancer la commande. Le même texte précise que l’ajout d’une contrainte CHECK ou NOT NULL exige un parcours de la table, mais pas de réécriture.

Comment ajouter une contrainte sans bloquer?

La documentation décrit une approche en deux temps :

1. Ajouter la contrainte avec NOT VALID. Le long parcours qui vérifie les lignes existantes n’a alors pas lieu. Cette étape prend quand même un verrou fort, mais brièvement : selon la documentation, la plupart des contraintes, dont CHECK, prennent un verrou ACCESS EXCLUSIVE, et une clé étrangère un SHARE ROW EXCLUSIVE sur les deux tables. Combinez-la avec lock_timeout (voir plus bas).
2. Lancer ensuite VALIDATE CONSTRAINT, qui ne prend qu’un verrou SHARE UPDATE EXCLUSIVE sur la table modifiée (plus un ROW SHARE sur la table référencée dans le cas d’une clé étrangère).

L’exemple officiel pour une clé étrangère :

ALTER TABLE distributors
  ADD CONSTRAINT distfk FOREIGN KEY (address)
  REFERENCES addresses (address) NOT VALID;
ALTER TABLE distributors VALIDATE CONSTRAINT distfk;

Pour passer une colonne à NOT NULL, la documentation indique que le parcours de la table est évité si une contrainte CHECK valide prouve déjà l’absence de valeurs nulles. On peut donc ajouter un CHECK (colonne IS NOT NULL) NOT VALID, le valider, puis exécuter SET NOT NULL, puis, à notre avis, supprimer le CHECK une fois SET NOT NULL terminé (la documentation précise que le CHECK ne doit pas être supprimé dans la même commande). Les versions récentes de PostgreSQL acceptent aussi NOT VALID directement sur une contrainte NOT NULL, selon la page de ALTER TABLE. Testez cette séquence sur une copie de vos données avant de l’appliquer.

Comment créer un index sans bloquer les écritures?

Selon la documentation de CREATE INDEX, une construction normale bloque les écritures (mais pas les lectures) pendant toute sa durée. Avec CONCURRENTLY, PostgreSQL construit l’index sans verrou qui empêche les insertions, mises à jour et suppressions.

Le prix à payer, selon la même page :

• Deux parcours de la table, et l’attente de la fin des transactions existantes qui pourraient utiliser l’index. La construction est donc plus lente.
• La commande ne peut pas s’exécuter dans un bloc de transaction. Votre outil de migration doit la lancer hors transaction.
• En cas d’échec (interblocage, violation d’unicité), un index « invalide » reste en place. Il est ignoré par les requêtes, mais il consomme quand même des ressources à chaque écriture. La page recommande de le supprimer et de recommencer, ou d’utiliser REINDEX INDEX CONCURRENTLY.
• Les index concurrents ne sont pas pris en charge sur les tables partitionnées. Pour ce cas, consultez la page de CREATE INDEX, qui décrit un contournement partition par partition.
• Un index unique invalide continue de faire respecter l’unicité dès le début du second parcours, même après un échec survenu pendant ce parcours : des violations peuvent apparaître dans d’autres requêtes.

Une fois la construction terminée, cherchez les index invalides dans le catalogue pg_index (colonne indisvalid) : SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;.

Comment éviter qu’une commande attende un verrou trop longtemps?

Le paramètre lock_timeout annule toute instruction qui attend un verrou plus longtemps que la limite fixée. Sa valeur par défaut est zéro, ce qui désactive la limite. La documentation précise qu’il ne s’applique qu’à l’attente de verrous, contrairement à statement_timeout, et qu’il n’est pas recommandé de le fixer dans postgresql.conf, puisque toutes les sessions seraient touchées.

On le fixe donc dans la session de migration (SET LOCAL si la migration s’exécute dans une transaction). La valeur de deux secondes ci-dessous est un exemple à ajuster :

SET lock_timeout = '2s';
ALTER TABLE factures ADD COLUMN note text;

Si la commande échoue, vous réessayez à un moment plus calme. C’est préférable à une migration suspendue pour une durée inconnue. À notre avis, votre outil de migration devrait relancer la commande automatiquement après un échec de ce type.

Comment changer le modèle de données sans arrêt?

Pour un changement plus profond, comme remplacer une table ou changer la forme des données, Stripe a décrit dans son article sur les migrations en ligne une démarche en quatre étapes, décrite sans lien avec un moteur de base de données précis :

1. Écrire dans les deux emplacements, l’ancien et le nouveau, en reprenant peu à peu les anciennes données.
2. Faire passer les lectures au nouvel emplacement, après avoir comparé les résultats.
3. Faire passer les écritures au nouvel emplacement seulement.
4. Supprimer les anciennes données et le code qui en dépend.

Stripe y précise que les écritures doubles ajoutent de la charge sur la base de production, et utilise la bibliothèque Scientist de GitHub pour comparer les résultats des deux lectures. À notre avis, le procédé coûte aussi du code temporaire et plusieurs déploiements : prévoyez-le dans votre calendrier.

1. 1Écrire aux deux endroits
2. 2Lire le nouveau
3. 3Écrire au nouveau seulement
4. 4Retirer l’ancien

La reprise des anciennes données se fait pendant la première étape.

Par quoi commencer cette semaine?

Voici une liste de départ, pour une équipe qui n’a pas encore de règles de migration :

• Recensez vos plus grandes tables et la durée de vos migrations sur une copie récente.
• Ajoutez lock_timeout à la session de migration et une relance automatique.
• Interdisez dans la revue de code les CREATE INDEX sans CONCURRENTLY sur les tables actives.
• Imposez le couple NOT VALID puis VALIDATE CONSTRAINT pour les nouvelles contraintes sur des tables déjà remplies.
• Séparez le déploiement du schéma de celui du code, pour pouvoir annuler l’un sans l’autre.

C’est l’approche par laquelle nous commencerions : un inventaire court, quelques règles, puis un essai sur un environnement de préproduction. Si vous voulez un avis senior sur vos pratiques de migration, la page CTO fractionnel explique notre façon de travailler.

Cet article est de l’information générale, pas un avis professionnel. Vérifiez la documentation de votre version de PostgreSQL et testez toute migration avant de l’appliquer en production.