Migrations Postgres qui verrouillent la table : le verrou pris par chaque ALTER TABLE, et comment déployer sans risque

Une migration qui ajoute une colonne nullable se termine en moins d’une milliseconde. Elle peut pourtant faire tomber votre application, car avant de s’exécuter elle doit attendre un verrou, et pendant qu’elle attend, toutes les autres requêtes sur cette table attendent derrière elle. Cet article montre la file d’attente des verrous en action dans trois sessions psql, liste le verrou que prend chaque ALTER TABLE courant et indique s’il réécrit la table, puis détaille les techniques qui rendent les migrations sûres : lock_timeout avec nouvelles tentatives, NOT VALID suivi de VALIDATE, et CREATE INDEX CONCURRENTLY. Chaque instruction et chaque sortie ci-dessous ont été exécutées sur PostgreSQL 17.11, la version majeure utilisée par Relyt One (la seule remarque sur PostgreSQL 18 a été vérifiée sur la 18.6).

Si vous ne retenez que quatre choses :

1. La plupart des formes d’ALTER TABLE prennent un verrou ACCESS EXCLUSIVE, qui bloque les lectures comme les écritures. Même quand la modification elle-même est instantanée, l’instruction commence par faire la queue pour obtenir ce verrou.
2. Pendant qu’elle attend, les nouvelles requêtes sur la même table font la queue derrière elle. Une longue transaction plus une migration suffisent à bloquer toutes les requêtes qui touchent la table.
3. Donnez à chaque migration un lock_timeout, pour qu’elle abandonne au bout de quelques secondes au lieu de bloquer le trafic indéfiniment, et relancez-la.
4. Séparez les vérifications coûteuses du verrou : ajoutez les contraintes en NOT VALID et validez-les plus tard avec VALIDATE, construisez les index en CONCURRENTLY, et évitez les instructions qui réécrivent une grosse table.

La suite de cet article illustre chacun de ces points avec des sorties que vous pouvez reproduire.

Deux tables : un million de comptes et deux millions de commandes. Exécutez ceci dans psql sur une base de données jetable :

CREATE TABLE accounts (
  id         bigint PRIMARY KEY,
  email      text NOT NULL,
  plan       varchar(20),
  score      integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO accounts (id, email, plan, score)
SELECT g, 'user' || g || '@example.com',
       (ARRAY['free', 'pro', 'team'])[1 + g % 3], g % 1000
FROM generate_series(1, 1000000) AS g;

CREATE TABLE orders (
  id         bigint PRIMARY KEY,
  account_id bigint NOT NULL,
  total      numeric(10, 2) NOT NULL
);
INSERT INTO orders
SELECT g, 1 + g % 1000000, (g % 10000) / 100.0
FROM generate_series(1, 2000000) AS g;
ANALYZE;

Ouvrez trois sessions psql. Dans la première, démarrez une transaction qui lit la table et reste ouverte. Dans un vrai système, c’est un rapport lent, un traitement par lots, ou une connexion laissée idle in transaction par une application qui a oublié de valider :

-- Session A
SET application_name = 'a_report';
BEGIN;
SELECT count(*) FROM accounts;
-- laisser la transaction ouverte

Dans la deuxième, lancez la migration. Ajouter une colonne nullable est l’exemple type de migration « sûre » :

-- Session B
SET application_name = 'b_migration';
ALTER TABLE accounts ADD COLUMN note text;

Elle reste bloquée. Dans la troisième, lancez le genre de requête que votre application exécute des milliers de fois par minute :

-- Session C
SET application_name = 'c_app';
SELECT email FROM accounts WHERE id = 42;

Elle reste bloquée elle aussi, alors qu’elle ne lit qu’une ligne et que la session A ne fait que lire. Depuis une quatrième session, demandez à Postgres qui attend qui :

SELECT pid, application_name, state, wait_event_type AS wait_type,
       pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE application_name IN ('a_report', 'b_migration', 'c_app')
ORDER BY application_name;

 pid | application_name |        state        | wait_type | blocked_by |                   query
-----+------------------+---------------------+-----------+------------+--------------------------------------------
 478 | a_report         | idle in transaction | Client    | {}         | SELECT count(*) FROM accounts;
 488 | b_migration      | active              | Lock      | {478}      | ALTER TABLE accounts ADD COLUMN note text;
 495 | c_app            | active              | Lock      | {488}      | SELECT email FROM accounts WHERE id = 42;
(3 rows)

Vos pid seront différents. La colonne blocked_by raconte toute l’histoire. C n’est pas bloquée par A. A détient un verrou ACCESS SHARE, et C veut le même mode, donc les deux n’entrent pas en conflit. C est bloquée par B. B veut ACCESS EXCLUSIVE, qui entre en conflit avec tout, donc B attend A. Et une nouvelle demande de verrou doit attendre si elle entre en conflit avec une demande déjà en attente, pas seulement avec une demande accordée. Cette règle existe pour qu’un flux de lecteurs ne puisse pas affamer la migration indéfiniment, mais elle a pour effet de bord qu’un ALTER TABLE en attente devient un mur devant la table.

Lors de notre exécution, la session A a validé huit secondes après son démarrage, B s’est alors terminée immédiatement, et la recherche d’une seule ligne de C a pris 6,2 secondes. Sous un trafic réel, toutes les requêtes qui touchent accounts pendant cette fenêtre s’accumulent, les pools de connexions s’épuisent, et la panne semble causée par une migration d’une ligne. Elle l’est, mais pas parce que la migration était lente.

lock_timeout annule une instruction qui a attendu un verrou plus longtemps que la limite. Refaites la même expérience, mais donnez deux secondes à la session B :

-- Session B
SET application_name = 'b_migration';
SET lock_timeout = '2s';
ALTER TABLE accounts ADD COLUMN note text;

ERROR:  canceling statement due to lock timeout

Cette fois, C a attendu au plus deux secondes (1,8 seconde lors de notre exécution, au lieu de 6,2) : elle n’a attendu que le temps que B atteigne sa limite et quitte la file. La migration a échoué, et c’est tout l’intérêt : elle a échoué à moindre coût au lieu de faire tomber la table. Il ne reste plus qu’à la relancer.

Deux détails rendent cela efficace en pratique.

Utilisez SET LOCAL dans la transaction de la migration. SET LOCAL dure jusqu’à la fin de la transaction en cours puis revient à la valeur précédente, il ne peut donc pas déborder sur ce qui s’exécute ensuite sur cette connexion :

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT orders_account_fk
  FOREIGN KEY (account_id) REFERENCES accounts (id) NOT VALID;
COMMIT;

C’est important si vos migrations passent par un pooler en mode transaction. Chaque transaction peut atterrir sur une connexion serveur différente, donc un simple SET exécuté dans une transaction précédente n’est pas garanti de s’appliquer à l’ALTER TABLE. La chaîne de connexion avec pool de connexions de Relyt One passe par PgBouncer avec un pooling au niveau transaction. Exécutez les migrations via la chaîne de connexion directe, et gardez malgré tout le paramètre à l’intérieur de la transaction.

Relancez avec un délai croissant, mais uniquement sur les délais d’attente de verrou. Beaucoup d’outils de migration savent relancer ; si le vôtre ne le fait pas, un petit script d’enveloppe suffit. Celui-ci ne relance que lorsque la migration a renoncé à attendre un verrou, s’arrête au premier succès et échoue après cinq tentatives. Toute autre erreur, comme une faute de frappe dans le SQL, échoue immédiatement. Il compare le texte anglais de l’erreur, gardez donc lc_messages en anglais pour la session de migration :

#!/usr/bin/env bash
# Utilisation : ./migrate.sh migration.sql
# Ne relance que lorsque la migration a renoncé à attendre un verrou.
set -u
for attempt in 1 2 3 4 5; do
  if output=$(psql "$DATABASE_URL" -X -v ON_ERROR_STOP=1 -f "$1" 2>&1); then
    echo "$output"
    exit 0
  fi
  echo "$output" >&2
  if ! grep -q "canceling statement due to lock timeout" <<<"$output"; then
    exit 1  # toute autre erreur : corrigez-la, ne la relancez pas
  fi
  if [ "$attempt" -lt 5 ]; then
    echo "attempt $attempt hit the lock timeout; retrying in $((attempt * 5))s" >&2
    sleep $((attempt * 5))
  fi
done
echo "giving up after 5 attempts" >&2
exit 1

Nous avons enregistré la migration de clé étrangère ci-dessus sous le nom 0042_orders_account_fk.sql et lancé le script pendant qu’une autre session gardait ouverte une transaction ayant mis à jour une ligne de accounts. La première tentative a atteint le délai d’attente, la deuxième a réussi une fois cette transaction validée :

BEGIN
SET
psql:0042_orders_account_fk.sql:5: ERROR:  canceling statement due to lock timeout
attempt 1 hit the lock timeout; retrying in 5s
BEGIN
SET
ALTER TABLE
COMMIT

Comme le fichier enveloppe tout dans une seule transaction, une tentative échouée est entièrement annulée, et la nouvelle tentative repart de zéro. Ne relancez que des fichiers de ce type. Une instruction qui ne peut pas s’exécuter dans une transaction, comme CREATE INDEX CONCURRENTLY, peut laisser quelque chose derrière elle en cas d’échec (nous y reviendrons plus bas), et la tentative suivante buterait dessus.

Avant une migration sur une table très sollicitée, il vaut aussi la peine de chercher la transaction qui va la bloquer :

SELECT pid, state, now() - xact_start AS xact_age, left(query, 50) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 5;

Une transaction ouverte depuis plusieurs minutes, surtout dans l’état idle in transaction, fera échouer chaque tentative sur le délai d’attente. idle_in_transaction_session_timeout (désactivé par défaut) met fin automatiquement à ce type de session.

Vous pouvez le mesurer vous-même. Exécutez l’instruction dans une transaction, lisez pg_locks avant de valider, puis annulez. Pour savoir si Postgres a réécrit la table, comparez son file node avant et après : une réécriture écrit une nouvelle copie de la table dans un nouveau fichier.

CREATE VIEW my_locks AS
SELECT relation::regclass AS table_name, mode
FROM pg_locks
WHERE pid = pg_backend_pid()
  AND locktype = 'relation'
  AND relation IN ('accounts'::regclass, 'orders'::regclass)
ORDER BY 1, 2;

BEGIN;
SELECT pg_relation_filenode('accounts') AS before \gset
ALTER TABLE accounts ALTER COLUMN score TYPE bigint;
SELECT * FROM my_locks;
SELECT pg_relation_filenode('accounts') <> :before AS rewritten;
ROLLBACK;

 table_name |        mode
------------+---------------------
 accounts   | AccessExclusiveLock
 accounts   | ShareLock
(2 rows)

 rewritten
-----------
 t
(1 row)

(\gset est une commande psql qui stocke le résultat dans une variable.) Nous avons exécuté ainsi chaque instruction du tableau ci-dessous sur la table accounts d’un million de lignes et la table orders de deux millions de lignes. Pour les colonnes « Bloque », nous avons maintenu chaque verrou dans une transaction ouverte et tenté un SELECT et un UPDATE d’une ligne depuis une autre session avec un lock_timeout de 500 ms ; pour le reste, l’effet découle du mode de verrou dans la table des conflits de PostgreSQL.

Les clés étrangères verrouillent deux tables, elles ont donc leur propre tableau. orders est la table qui référence et accounts la table référencée :

Les durées proviennent d’un conteneur local et dépendent de la taille de la table et du matériel ; lisez-les donc comme des valeurs relatives, pas absolues. Quelques points de ces tableaux surprennent souvent :

• Toutes les instructions de ce tableau qui ajoutent, suppriment, renomment ou changent le type d’une colonne prennent ACCESS EXCLUSIVE, y compris les instantanées. « Instantané » décrit la durée pendant laquelle le verrou est détenu, pas le fait qu’il soit nécessaire. Toutes peuvent déclencher une file d’attente de verrous.
• Une valeur par défaut n’implique pas une réécriture. Depuis PostgreSQL 11, une valeur par défaut non volatile est stockée une seule fois dans le catalogue, et les lignes existantes la récupèrent à la lecture. now() compte ici comme non volatile : elle est évaluée une seule fois, à l’exécution de l’ALTER TABLE, et toutes les lignes existantes reçoivent le même horodatage. Une valeur par défaut volatile comme gen_random_uuid() ou clock_timestamp() exige une valeur différente par ligne, donc la table est réécrite.
• Élargir un varchar ne coûte rien, le rétrécir si. Passer de varchar(20) à varchar(50) ou à text ne modifie que le catalogue. Passer à varchar(10) a réécrit la table sous PostgreSQL 17.
• integer vers bigint réécrit toujours. Les valeurs changent de taille sur disque. Sur une grosse table, c’est la migration qui demande le plus de préparation : ajoutez une nouvelle colonne bigint, remplissez-la par lots, et basculez dans une transaction courte.
• Une clé étrangère bloque les écritures sur la table qu’elle référence, pas seulement sur la table que vous modifiez. En ajouter une sur orders a aussi bloqué les UPDATE sur accounts.
• CREATE INDEX laisse passer les lectures mais bloque les écritures pendant toute la durée de la construction.

ADD CONSTRAINT vérifie normalement chaque ligne existante pendant qu’il détient son verrou. NOT VALID saute cette vérification : la contrainte s’applique immédiatement aux lignes nouvelles et modifiées, et les anciennes lignes sont vérifiées plus tard par VALIDATE CONSTRAINT, qui prend un verrou bien plus faible. Dans les tableaux ci-dessus, ajouter directement la clé étrangère a bloqué les écritures sur les deux tables pendant 1,8 seconde. En deux étapes, la partie bloquante a pris 3 ms, et la validation de 1,2 seconde a laissé passer les lectures comme les écritures.

La migration 1 est le fichier du correctif 1 ; si vous l’avez déjà exécutée, il ne reste que la migration 2 :

-- Migration 1 : rapide, mais a quand même besoin de son verrou, donc gardez le lock_timeout
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT orders_account_fk
  FOREIGN KEY (account_id) REFERENCES accounts (id) NOT VALID;
COMMIT;

-- Migration 2 : parcourt la table, sans bloquer les lectures ni les écritures
ALTER TABLE orders VALIDATE CONSTRAINT orders_account_fk;

Si VALIDATE trouve une ligne qui viole la contrainte, il échoue, et la contrainte reste NOT VALID tout en continuant à s’appliquer aux nouvelles écritures. Corrigez les données et relancez la validation.

Sous PostgreSQL 17, SET NOT NULL n’a pas de forme NOT VALID, et il parcourt toute la table sous ACCESS EXCLUSIVE. Mais il saute ce parcours si une contrainte CHECK validée prouve déjà que la colonne ne contient aucun null. Vous pouvez voir cette décision avec client_min_messages = debug1 :

ALTER TABLE accounts ADD CONSTRAINT plan_not_null CHECK (plan IS NOT NULL) NOT VALID;
ALTER TABLE accounts VALIDATE CONSTRAINT plan_not_null;

SET client_min_messages = debug1;
ALTER TABLE accounts ALTER COLUMN plan SET NOT NULL;
RESET client_min_messages;

ALTER TABLE accounts DROP CONSTRAINT plan_not_null;

DEBUG:  existing constraints on column "accounts.plan" are sufficient to prove that it does not contain nulls

Sans la contrainte CHECK, la même instruction affiche à la place DEBUG: verifying table "accounts", c’est-à-dire le parcours complet. Une fois la colonne en NOT NULL, la contrainte CHECK est redondante et vous pouvez la supprimer.

PostgreSQL 18 raccourcit tout cela. Il accepte ALTER TABLE accounts ADD CONSTRAINT plan_nn NOT NULL plan NOT VALID, et nous avons vérifié sur la 18.6 que le VALIDATE CONSTRAINT ultérieur prend SHARE UPDATE EXCLUSIVE. PostgreSQL 17 rejette cette instruction avec une erreur de syntaxe ; sur Relyt One, utilisez donc la méthode CHECK ci-dessus.

CREATE INDEX CONCURRENTLY prend SHARE UPDATE EXCLUSIVE au lieu de SHARE, si bien que les écritures continuent pendant la construction. En contrepartie, comme l’explique la documentation, il parcourt la table deux fois et attend les transactions existantes susceptibles d’utiliser ou de modifier l’index ; il fait donc plus de travail et prend plus de temps. Il a aussi deux règles qui lui sont propres.

Il ne peut pas s’exécuter dans un bloc de transaction :

BEGIN;
CREATE INDEX CONCURRENTLY accounts_email_idx ON accounts (email);
ROLLBACK;

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Beaucoup d’outils de migration enveloppent chaque fichier dans une transaction ; vérifiez comment le vôtre permet de désactiver ce comportement pour une seule migration. Il attend quand même son verrou, donnez-lui donc aussi un délai d’attente. SET LOCAL n’a aucun effet hors d’une transaction (Postgres répond WARNING: SET LOCAL can only be used in transaction blocks) ; utilisez donc un SET lock_timeout = '2s'; au niveau de la session juste avant, sur une connexion directe.

Et s’il échoue, il laisse derrière lui un index invalide. Un index unique sur une colonne contenant des doublons permet de le constater facilement :

CREATE UNIQUE INDEX CONCURRENTLY accounts_plan_key ON accounts (plan);
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index WHERE indrelid = 'accounts'::regclass;

ERROR:  could not create unique index "accounts_plan_key"
DETAIL:  Key (plan)=(free) is duplicated.

    index_name     | indisvalid
-------------------+------------
 accounts_pkey     | t
 accounts_plan_key | f
(2 rows)

(La valeur en double de la ligne DETAIL peut varier d’une exécution à l’autre.) L’index invalide n’est jamais utilisé par les requêtes. Selon la phase de construction qui a échoué, les écritures peuvent encore devoir le maintenir (vérifiez indisready dans le même catalogue). Une construction concurrente qui abandonne sur son lock_timeout laisse aussi un index invalide : quand nous en avons lancé une sur une table avec une longue transaction d’écriture en cours, elle a atteint le délai d’attente et laissé indisvalid = f derrière elle. Quelle qu’en soit la cause, supprimez l’index avec DROP INDEX CONCURRENTLY accounts_plan_key; et réessayez.

1. Cherchez l’instruction dans les tableaux ci-dessus. Si elle prend ACCESS EXCLUSIVE, ou tout verrou qui bloque les écritures (SHARE, SHARE ROW EXCLUSIVE), elle a besoin d’un délai d’attente de verrou, aussi rapide soit-elle.
2. Commencez la transaction de la migration par SET LOCAL lock_timeout = '2s' (ou la pause que votre application peut tolérer), et relancez-la quand le délai expire.
3. Recherchez dans pg_stat_activity les sessions longues et celles en idle in transaction avant de commencer.
4. Ajoutez les clés étrangères et les contraintes CHECK en NOT VALID, puis validez-les avec VALIDATE dans une étape séparée.
5. Ajoutez NOT NULL via une contrainte CHECK validée sous PostgreSQL 17.
6. Créez et supprimez les index en CONCURRENTLY, hors transaction, avec un SET lock_timeout au niveau de la session, et vérifiez ensuite qu’il n’y a pas d’indisvalid = false.
7. Considérez tout ce qui réécrit la table (integer vers bigint, une valeur par défaut volatile, le rétrécissement d’un type) comme un projet, pas comme une migration.
8. Exécutez les migrations via une connexion directe, pas à travers un pooler en mode transaction.

Les tableaux ci-dessus vous indiquent quel verrou prend une instruction. Ils ne peuvent pas vous dire combien de temps le parcours ou la réécriture prend sur vos données, ni si vos données passent l’étape VALIDATE. Pour cela, il vous faut des données de la taille de la production, et obtenir une copie est généralement la partie difficile.

Sur Relyt One, chaque branche est un fork copy-on-write de sa branche parente, avec l’ensemble des données et sa propre instance de calcul. Créez une branche à partir de votre branche de production, exécutez-y le fichier de migration avec \timing on, utilisez la vérification du file node présentée plus haut pour voir s’il réécrit la table, et découvrez si VALIDATE échoue sur d’anciennes lignes avant de le lancer pour de vrai. Supprimez ensuite la branche. La production ne voit jamais l’expérience.

Une chose qu’une branche ne permet pas de répéter, c’est la file d’attente des verrous elle-même, car rien d’autre ne s’y exécute. Elle vous indique combien de temps la migration détient son verrou une fois qu’elle l’a obtenu ; ce sont toujours lock_timeout et les nouvelles tentatives qui vous protègent de l’attente du verrou en production. Et comparez des durées relatives, pas absolues : l’instance de calcul de la branche peut être dimensionnée différemment de celle de la production.

Commencer gratuitement