0Pricing
SQL Academy · Leçon

Migrations en ligne : pourquoi ALTER TABLE verrouille

Comprenez quelles formes de ALTER TABLE prennent un verrou ACCESS EXCLUSIVE et réécrivent la table, et lesquelles ne modifient que les métadonnées.

Migrations en ligne : pourquoi ALTER TABLE verrouille est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 1 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Le problème des migrations en production

Sur une petite base de données, ALTER TABLE s’exécute instantanément. Sur une table active de 500 GB, la même commande peut bloquer les écritures pendant 20 minutes. Il est essentiel de savoir quels ALTER sont sûrs et lesquels ne le sont pas.

Niveaux de verrouillage

Les verrouillages PostgreSQL sont organisés en niveaux :

  • ACCESS SHARE — lectures
  • ROW EXCLUSIVE — écritures
  • SHARE / SHARE ROW EXCLUSIVE — DDL pouvant coexister avec les lectures
  • EXCLUSIVE — bloque les lectures
  • ACCESS EXCLUSIVE — bloque EVERYTHING

Ce que prend ALTER TABLE

La plupart des variantes de ALTER prennent un verrou ACCESS EXCLUSIVE — elles bloquent les lectures et les écritures jusqu’à leur achèvement.

ALTER rapides (uniquement les métadonnées)

Certaines commandes ALTER modifient uniquement le catalogue et s’achèvent en quelques millisecondes, même sur d’immenses tables :

ALTER TABLE t RENAME COLUMN a TO b;
ALTER TABLE t ALTER COLUMN a SET DEFAULT ...;
ALTER TABLE t ADD COLUMN x INT;             -- nullable, no default: metadata only (PG 11+)
ALTER TABLE t ADD COLUMN x INT NOT NULL DEFAULT 0;  -- metadata only PG 11+ if default is constant

ALTER lentes (réécriture)

Ces commandes réécrivent la table entière :

ALTER TABLE t ALTER COLUMN x TYPE BIGINT;     -- when types not binary-compatible
ALTER TABLE t SET LOGGED;
CLUSTER t USING idx;                          -- physically reorders rows
VACUUM FULL t;                                -- rewrites whole table

Ajouter NOT NULL en toute sécurité

Sur une grande table :

-- BAD: full table scan + ACCESS EXCLUSIVE lock
ALTER TABLE t ALTER COLUMN x SET NOT NULL;

-- BETTER:
ALTER TABLE t ADD CONSTRAINT x_not_null CHECK (x IS NOT NULL) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT x_not_null;     -- scans without exclusive lock
-- then drop the CHECK and add NOT NULL (still cheap because already validated):
ALTER TABLE t ALTER COLUMN x SET NOT NULL;
ALTER TABLE t DROP CONSTRAINT x_not_null;

Ajouter des clés étrangères en ligne

La même astuce NOT VALID :

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

Problèmes d’attente de verrouillage

Un ALTER qui attend un verrou ACCESS EXCLUSIVE se place derrière chaque transaction de longue durée. Les nouvelles transactions se placent également derrière ALTER — une chaîne de requêtes bloquées.

lock_timeout

Ne laissez pas les migrations attendre indéfiniment :

SET lock_timeout = '5s';
ALTER TABLE t ...;
-- ERROR if it can't get the lock in 5s — retry.

Boucles de nouvelle tentative

Les migrations doivent effectuer une nouvelle tentative en cas de lock_timeout :

for (let i = 0; i < 20; i++) {
  try {
    await client.query('SET lock_timeout = 5000');
    await client.query('ALTER TABLE ...');
    break;
  } catch (e) {
    if (e.code === '55P03') continue;     // lock_not_available
    throw e;
  }
}

statement_timeout pour la sécurité

Limitez la durée maximale d’exécution d’une instruction individuelle au sein d’une migration :

SET statement_timeout = '30s';

Outils utiles

  • strong_migrations (Rails)
  • django-migrate-zero-downtime
  • pg-osc (modification de schéma PostgreSQL en ligne)
  • pgRoll

Récapitulatif

Les migrations en ligne nécessitent de connaître :

  • les ALTER qui ne modifient que les métadonnées et ceux qui réécrivent la table
  • l’utilisation de NOT VALID + VALIDATE pour les contraintes
  • la configuration de lock_timeout et les nouvelles tentatives
  • l’évitement des transactions de longue durée qui bloquent les migrations

Vérification rapide

Vous ajoutez une contrainte NOT NULL à une table de 500 GB avec un seul ALTER TABLE — qu’arrive-t-il aux écritures ?

Questions Fréquemment Posées

La leçon « Migrations en ligne : pourquoi ALTER TABLE verrouille » est-elle gratuite ?

Oui — le texte complet de « Migrations en ligne : pourquoi ALTER TABLE verrouille » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Migrations en ligne : pourquoi ALTER TABLE verrouille » ?

Comprenez quelles formes de ALTER TABLE prennent un verrou ACCESS EXCLUSIVE et réécrivent la table, et lesquelles ne modifient que les métadonnées. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 1 sur 4.

Combien de temps prend la leçon « Migrations en ligne : pourquoi ALTER TABLE verrouille » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?

Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. Migrations en ligne : pourquoi ALTER TABLE verrouille
  2. Index concurrents (CREATE INDEX CONCURRENTLY)
  3. Renommer des colonnes sans interruption
  4. Outils : Flyway, Liquibase, Sqitch
← Retour à SQL Academy