MySQL vs PostgreSQL : qui transforme ALTER TABLE en cauchemar

Ce que signifie réellement « cauchemar ALTER TABLE » en production

Un changement de schéma devient un cauchemar quand il viole l’une des trois vérités de la production :

• Les budgets de latence sont réels. Tout ce qui bloque les écritures pendant des secondes se traduit par des timeouts, des retries, des files d’attente et parfois des pannes en cascade.
• La réplication fait partie de votre chemin de service. Même si vous « ne lisez pas sur les répliques », le lag peut casser les basculements, les sauvegardes, l’analytics et tout mécanisme de sécurité qui dépend de secondaires à jour.
• L’espace de stockage n’est pas infini et l’E/S n’est pas gratuite. Réécrire une table de 2 To n’est pas seulement lent : ça peut remplir les disques, chasser les caches tampon et déclencher des tempêtes d’autovacuum ou de purge.

En pratique, la douleur des changements de schéma se concentre autour de :

• Verrous qui bloquent le trafic (verrous explicites, verrous implicites, et le genre particulièrement épicé : les metadata locks dans MySQL).
• Réécritures de table (courantes en PostgreSQL ; possibles en MySQL selon le moteur et le mode DDL).
• Comportement de la réplication (implications statement-based vs row-based, sérialisation du DDL, apply lag des répliques).
• Transactions longues (les deux bases les détestent, mais différemment).
• Attentes des outils (quelqu’un suppose que « en ligne » signifie « zéro impact », et la production enseigne l’humilité).

Une citation à garder sur un post-it :

« idée paraphrasée » — Werner Vogels : vous construisez la fiabilité en supposant que des choses vont échouer et en concevant pour récupérer rapidement.

Les changements de schéma sont du travail de fiabilité. Traitez-les comme des déploiements, pas comme l’édition d’une colonne de tableur.

Quelques faits historiques qui expliquent le comportement actuel

Ce ne sont pas des anecdotes : ce sont les raisons pour lesquelles votre DDL se comporte comme il le fait.

1. PostgreSQL a choisi le DDL transactionnel tôt : le DDL participe aux transactions, donc il peut être annulé. Excellent pour la correction, mais cela signifie aussi que des verrous peuvent être détenus jusqu’au commit.
2. Le « DDL en ligne » de MySQL est principalement une histoire d’InnoDB : le comportement dépend des capacités du moteur de stockage, et historiquement MyISAM verrouillait volontiers tout.
3. InnoDB a introduit le « Fast Index Creation » (FIC) pour réduire le coût de reconstruction, mais le détail compte : certains alters reconstruisent encore la table.
4. PostgreSQL 9.2+ a rendu CREATE INDEX CONCURRENTLY pratique comme outil opérationnel, avec des compromis : ça prend plus de temps et peut échouer si on ne le surveille pas.
5. PostgreSQL 11 a amélioré « add column with default » en évitant souvent une réécriture complète de la table pour des valeurs par défaut constantes — une des plus grandes améliorations opérationnelles.
6. MySQL 5.6/5.7 a élargi les options de DDL en ligne avec les clauses ALGORITHM et LOCK, mais ce sont plutôt des négociations que des garanties.
7. MySQL 8.0 a introduit « instant ADD COLUMN » pour certains cas, mais dès que vous touchez certains attributs ou l’ordre des colonnes, vous revenez à des opérations plus lourdes.
8. Le MVCC de PostgreSQL signifie que les anciennes versions de lignes persistent jusqu’à ce que vacuum nettoie ; de grosses mises à jour pendant les changements de schéma peuvent provoquer un effet secondaire vacuum/I/O.
9. La réplication MySQL appliquait historiquement le DDL en un seul thread (et même avec la réplication parallèle, le DDL a des contraintes de sérialisation), rendant parfois « rapide sur le primaire » synonyme de « lent partout ailleurs ».

Blague #1 : Appeler un ALTER « en ligne » parce qu’il ne prend pas un verrou exclusif, c’est comme appeler un ours « amical » parce qu’il ne vous a pas encore mordu.

MySQL : DDL en ligne, verrous de métadonnées et l’illusion de « instantané »

Problème central de MySQL : les metadata locks se moquent de votre intention

Dans MySQL, une grande partie de la douleur liée aux ALTER n’est pas le travail lui-même — c’est le MDL (metadata locks). Tout DDL a besoin de verrous de métadonnées, et ces verrous interagissent avec les requêtes en cours de façons qui surprennent les équipes qui assimilent « DDL en ligne » à « pas de blocage ».

Le mode de défaillance classique ressemble à ceci :

• Vous démarrez un ALTER TABLE.
• Il attend un MDL parce qu’une session lit la table.
• De nouvelles requêtes s’accumulent derrière l’ALTER en attente parce qu’elles veulent des verrous conflictuels.
• Votre appli fond alors que « l’ALTER n’a pas commencé ».

Clauses ALGORITHM et LOCK : utiles, mais pas magiques

MySQL vous permet de demander un algorithme DDL et un comportement de verrouillage. Exemples :

• ALGORITHM=INSTANT pour les changements metadata-only quand c’est pris en charge.
• ALGORITHM=INPLACE pour éviter la copie complète de la table quand c’est possible.
• LOCK=NONE ou LOCK=SHARED pour réduire le blocage.

L’astuce opérationnelle : spécifiez-les toujours quand vous avez des exigences strictes de disponibilité, parce qu’ils transforment les surprises en erreurs immédiates. Une erreur est un cadeau. Elle vous dit : « Pas aujourd’hui, pas comme ça. »

Ce que MySQL fait bien

• Certaines modifications peuvent être réellement instantanées (par ex. certains ADD COLUMN sur 8.0), ce qui est agréable quand ça fonctionne.
• L’écosystème d’outils pour les modifications de schéma en ligne est mature : pt-online-schema-change, gh-ost, et des variantes maison sont courants.
• InnoDB peut faire beaucoup d’opérations d’index en mode plus ou moins en ligne, avec un impact gérable si vous comprenez les contraintes.

Où MySQL fait mal

• Les accumulations de MDL sont catastrophiques : elles ressemblent à des symptômes de « base de données en panne » même quand le serveur est sain.
• Le lag de réplication peut devenir votre panne cachée : le DDL peut bloquer l’application ou entrer en conflit avec le parallélisme.
• « En ligne » consomme toujours des E/S et du CPU : construire un index secondaire sur une grosse table concurrencera la charge de production. L’absence d’un grand verrou ne signifie pas l’absence de douleur.

PostgreSQL : DDL transactionnel, réécritures et conséquences en forme de vacuum

Problème central de PostgreSQL : les réécritures sont honnêtes, brutales et parfois inévitables

PostgreSQL est cohérent : si un changement de schéma nécessite de toucher chaque ligne, il le fera exactement. Et il le fera sous des sémantiques transactionnelles. Cela signifie :

• Les réécritures de table peuvent être énormes (temps + E/S + espace disque supplémentaire).
• Les verrous peuvent être forts (certaines opérations requièrent ACCESS EXCLUSIVE).
• Les transactions longues aggravent tout, car elles empêchent le nettoyage des anciennes tuples et peuvent maintenir des verrous plus longtemps que prévu.

Super-pouvoir opérationnel de PostgreSQL : primitives d’outillage prévisibles

Postgres vous donne des briques de construction qui se comportent de façon prévisible :

• CREATE INDEX CONCURRENTLY évite les verrous lourds (au prix d’un temps d’exécution plus long et d’une sensibilité aux échecs).
• ALTER TABLE ... ADD COLUMN est souvent peu coûteux, et depuis la v11, ajouter une valeur par défaut constante évite souvent de réécrire toute la table.
• Introspection riche via les catalogues système et pg_stat_activity, rendant la question « qui bloque quoi ? » répondable.

Où Postgres fait mal

• Certaines DDL prennent toujours un ACCESS EXCLUSIVE, ce qui bloque lectures et écritures. La liste n’est pas courte : changements de type de colonne, certaines validations de contraintes, certaines réécritures de table.
• Effets secondaires du MVCC : les changements qui touchent beaucoup de lignes peuvent générer du bloat ; vacuum concurrence alors sur les E/S. Votre migration peut « se terminer » et votre douleur commencer plus tard.
• Réplication sur standbys physiques : une forte activité d’écriture pendant une réécriture signifie beaucoup de WAL, donc lag des standbys et pression sur le stockage.

Blague #2 : PostgreSQL vous laissera faire presque tout dans une transaction ; c’est juste que parfois la transaction est « reconstruire l’univers ».

Qui aggrave le plus la situation ? Verdict pratique par type de changement

1) Ajouter une colonne nullable

Habituellement : les deux s’en sortent.

• MySQL 8.0 : souvent ALGORITHM=INSTANT si vous ne forcez pas le changement d’ordre des colonnes et si vous restez dans les patterns supportés.
• PostgreSQL : opération metadata-only, rapide.

Risque de cauchemar : faible, sauf si vous déclenchez une accumulation MDL (MySQL) ou si vous prenez des verrous forts en enchaînant plusieurs opérations (Postgres).

2) Ajouter une colonne avec une valeur par défaut

PostgreSQL s’est considérablement amélioré en v11. Pour des valeurs par défaut constantes, il peut stocker le défaut dans les métadonnées et l’appliquer à la lecture, évitant une réécriture complète dans bien des cas.

MySQL : peut encore nécessiter la reconstruction de la table selon le changement exact et la version/engine. « Default » n’est rarement le seul changement ; on glisse souvent un NOT NULL et un reorder et puis on s’étonne que la table se copie elle-même.

Verdict : Postgres moderne gagne souvent pour les defaults constants. MySQL peut gagner s’il est instantané ; il peut aussi perdre spectaculairement s’il reconstruit la table.

3) Ajouter un index

Ni l’un ni l’autre n’est gratuit. Vous relisez beaucoup de données et écrivez beaucoup de structure.

• MySQL : la création d’index peut être « en ligne » mais consomme quand même E/S/CPU ; les metadata locks importent toujours, et l’application sur les répliques peut laguer.
• PostgreSQL : CREATE INDEX CONCURRENTLY est la solution pour la disponibilité, avec la mise en garde opérationnelle qu’il peut prendre plus de temps et ne peut pas être exécuté dans un bloc transactionnel.

Verdict : Postgres gagne sur l’évitement de verrou avec CONCURRENTLY. MySQL gagne quand la charge est tolérante et que vous pouvez planifier l’opération — en plus il dispose d’outils externes matures pour le schéma en ligne.

4) Changer le type d’une colonne

Là vous gagnez votre salaire.

• PostgreSQL : peut réécrire la table, et exige fréquemment ACCESS EXCLUSIVE. Il existe des astuces (ajouter une nouvelle colonne, backfill, swap) qui évitent de longs verrous exclusifs.
• MySQL : reconstruit souvent la table ; peut être bloqué par MDL ; le support « inplace » varie.

Verdict : les deux peuvent être des cauchemars. Postgres est plus transparent sur la réécriture ; MySQL est plus susceptible de vous surprendre avec des MDL et des symptômes « pourquoi tout est bloqué ? ».

5) Ajouter une clé étrangère

Opérationnellement risqué dans les deux cas. La base doit valider les données existantes, ce qui peut impliquer des scans de tables et des verrous.

• PostgreSQL : vous pouvez ajouter la contrainte NOT VALID et valider plus tard pour gérer le temps de verrou. C’est une vraie fonctionnalité production, pas un tour de magie.
• MySQL : la validation et le comportement de verrou dépendent du moteur/version ; vous pouvez vous retrouver avec de longs blocages ou de fortes E/S.

Verdict : Postgres offre généralement une approche en étapes plus sûre. MySQL peut aller, mais a moins de primitives pour différer la validation.

6) Supprimer une colonne

Les deux sont dangereusement trompeurs. Pas parce que supprimer est difficile, mais parce que les applications et les ORM excellent à être erronés de nouvelles façons.

Verdict : la base de données est rarement le cauchemar ; votre chorégraphie de déploiement l’est. Faites-le en phases.

Playbook de diagnostic rapide

Ceci est la séquence « on a lancé un ALTER et maintenant tout brûle ». L’objectif est d’identifier si vous êtes bloqué par des verrous, saturé en E/S/CPU, ou noyé dans le lag de réplication — rapidement.

Première étape : est-ce bloqué par des verrous ou en attente de métadonnées ?

• MySQL : vérifiez le processlist et les attentes de metadata lock ; cherchez des sessions en « Waiting for table metadata lock ».
• PostgreSQL : vérifiez pg_stat_activity et pg_locks pour les chaînes de blocage et des modes de verrou comme AccessExclusiveLock.

Si vous trouvez un blocage : décidez s’il faut tuer le bloqueur, annuler le DDL ou attendre. Tuer la mauvaise session peut être pire que la migration.

Deuxième étape : le serveur est-il saturé en ressources ?

• CPU saturé : construction d’index, validation de contraintes, ou triggers lourds.
• E/S saturées : réécriture de table, construction d’index, vacuum, activité de fond InnoDB.
• Pression mémoire : churn du buffer pool, tris qui débordent sur disque.

Si saturation : limitez, replanifiez ou migrez vers des outils en ligne qui copient en petits morceaux.

Troisième étape : le lag de réplication devient-il une seconde panne ?

• MySQL : surveillez le délai d’application des répliques et si le thread SQL est bloqué derrière du DDL.
• PostgreSQL : surveillez la génération WAL et le replay lag ; un DDL qui réécrit génère beaucoup de WAL.

Si le lag est critique : pausez le changement, réduisez le taux d’écriture, ou acceptez de ne pas pouvoir basculer sans risque tant que le lag n’a pas récupéré.

Tâches pratiques : commandes, sorties et décisions (14)

Ce sont des sondes pratiques à exécuter lors de la planification ou de la réponse à incident. Chaque tâche inclut la commande, ce que signifie la sortie et la décision qu’elle conduit.

Task 1 (MySQL): see who is waiting on metadata locks

cr0x@server:~$ mysql -uroot -p -e "SHOW FULL PROCESSLIST\G"
*************************** 1. row ***************************
     Id: 12345
   User: app
   Host: 10.0.2.15:53412
     db: prod
Command: Query
   Time: 120
  State: Waiting for table metadata lock
   Info: ALTER TABLE orders ADD COLUMN promo_code varchar(32)
*************************** 2. row ***************************
     Id: 12201
   User: bi
   Host: 10.0.9.20:41233
     db: prod
Command: Query
   Time: 980
  State: Sending data
   Info: SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 30 DAY

Signification : L’ALTER est bloqué par une requête en cours qui détient un verrou de métadonnées conflictuel.

Décision : Si la disponibilité prime sur cette requête BI, tuez le bloqueur (ou annulez l’ALTER). Sinon, planifiez le DDL dans une fenêtre ou imposez des timeouts aux requêtes.

Task 2 (MySQL): identify InnoDB lock waits (row locks, not MDL)

cr0x@server:~$ mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G" | sed -n '1,160p'
=====================================
2025-12-29 10:18:07 0x7f9a2c1a0700 INNODB MONITOR OUTPUT
=====================================
------------
TRANSACTIONS
------------
Trx id counter 92839110
Purge done for trx's n:o < 92839000 undo n:o < 0 state: running
History list length 2341
...
LATEST DETECTED DEADLOCK
...

Signification : Montre la pression transactionnelle, la longueur de la history list (purge lag), les deadlocks et les attentes de verrou.

Décision : Si la longueur de la history list augmente rapidement pendant le DDL + backfill, ralentissez le backfill et raccourcissez les transactions.

Task 3 (MySQL): check whether your ALTER can be INSTANT/INPLACE

cr0x@server:~$ mysql -uroot -p -e "ALTER TABLE orders ADD COLUMN promo_code varchar(32), ALGORITHM=INSTANT, LOCK=NONE;"
ERROR 1845 (0A000) at line 1: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE.

Signification : MySQL a refusé INSTANT ; cela peut encore être INPLACE, ou nécessiter une COPY (rebuild de table).

Décision : Ne laissez pas le serveur choisir. Réessayez avec ALGORITHM=INPLACE, LOCK=NONE et acceptez l’échec si il ne peut pas s’y conformer — ensuite passez à l’outillage de schéma en ligne.

Task 4 (MySQL): verify replica lag and whether SQL thread is stuck on DDL

cr0x@server:~$ mysql -uroot -p -e "SHOW SLAVE STATUS\G" | egrep "Seconds_Behind_Master|Slave_SQL_Running_State|Slave_IO_Running|Slave_SQL_Running"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Waiting for dependent transaction to commit
Seconds_Behind_Master: 187

Signification : La réplique accuse du retard ; le thread SQL d’application attend, souvent derrière une grosse transaction ou une sérialisation DDL.

Décision : Si le basculement est dans votre plan de sécurité, arrêtez la migration ou freinez l’émetteur jusqu’à récupération du lag.

Task 5 (MySQL): check table size before a potentially rebuilding ALTER

cr0x@server:~$ mysql -uroot -p -e "SELECT table_name, ROUND((data_length+index_length)/1024/1024/1024,2) AS size_gb FROM information_schema.tables WHERE table_schema='prod' AND table_name='orders';"
+------------+---------+
| table_name | size_gb |
+------------+---------+
| orders     | 612.84  |
+------------+---------+

Signification : Vous vous apprêtez à toucher un objet d’environ ~613 GB. Les reconstructions prendront du temps et de l’espace, et vont pilonner les E/S.

Décision : Traitez ceci comme un projet : envisagez gh-ost/pt-osc, assurez-vous d’avoir de la marge disque et coordonnez un plan de throttling.

Task 6 (PostgreSQL): see active queries and whether your DDL is waiting

cr0x@server:~$ psql -d prod -c "SELECT pid, state, wait_event_type, wait_event, now()-query_start AS age, left(query,80) AS query FROM pg_stat_activity WHERE datname='prod' ORDER BY age DESC LIMIT 10;"
  pid  | state  | wait_event_type |     wait_event     |   age   |                                        query
-------+--------+-----------------+--------------------+---------+--------------------------------------------------------------------------------
 81231 | active | Lock            | relation           | 00:02:01| ALTER TABLE orders ALTER COLUMN total TYPE numeric(12,2)
 80910 | active |                 |                    | 00:18:22| SELECT * FROM orders WHERE created_at > now() - interval '30 days'

Signification : L’ALTER attend un verrou ; une autre requête est active depuis assez longtemps pour être suspecte.

Décision : Trouvez les bloqueurs, puis soit annulez-les, soit annulez la migration et replanifiez avec une stratégie plus sûre (dual-write, backfill, swap).

Task 7 (PostgreSQL): find the blocking chain

cr0x@server:~$ psql -d prod -c "SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.query AS blocked_query, blocking.query AS blocking_query FROM pg_locks blocked JOIN pg_stat_activity blocked_activity ON blocked.pid = blocked_activity.pid JOIN pg_locks blocking ON blocking.locktype = blocked.locktype AND blocking.database IS NOT DISTINCT FROM blocked.database AND blocking.relation IS NOT DISTINCT FROM blocked.relation AND blocking.page IS NOT DISTINCT FROM blocked.page AND blocking.tuple IS NOT DISTINCT FROM blocked.tuple AND blocking.virtualxid IS NOT DISTINCT FROM blocked.virtualxid AND blocking.transactionid IS NOT DISTINCT FROM blocked.transactionid AND blocking.classid IS NOT DISTINCT FROM blocked.classid AND blocking.objid IS NOT DISTINCT FROM blocked.objid AND blocking.objsubid IS NOT DISTINCT FROM blocked.objsubid AND blocking.pid != blocked.pid JOIN pg_stat_activity blocking_activity ON blocking.pid = blocking_activity.pid WHERE NOT blocked.granted AND blocking.granted LIMIT 5;"
 blocked_pid | blocking_pid |                  blocked_query                   |                  blocking_query
------------+--------------+--------------------------------------------------+-----------------------------------------------
      81231 |        80910 | ALTER TABLE orders ALTER COLUMN total TYPE ...    | SELECT * FROM orders WHERE created_at > ...

Signification : Vous avez un bloqueur direct. Souvent il s’agit d’une requête de lecture que vous supposiez « ne bloque pas les changements de schéma ». Si, elle le fait.

Décision : Annulez la requête bloquante si c’est sans risque (pg_cancel_backend), ou terminez-la si elle est coincée (pg_terminate_backend) — mais comprenez l’impact applicatif.

Task 8 (PostgreSQL): estimate table size and whether rewrite is scary

cr0x@server:~$ psql -d prod -c "SELECT pg_size_pretty(pg_total_relation_size('public.orders')) AS total_size, pg_size_pretty(pg_relation_size('public.orders')) AS heap_size;"
 total_size | heap_size
------------+----------
 835 GB     | 612 GB

Signification : Réécrire le heap pourrait signifier des centaines de GB d’écritures supplémentaires, plus du WAL, plus des besoins d’espace libre.

Décision : N’essayez pas une réécriture pendant les pics. Utilisez une approche expand/backfill/contract sauf si vous tolérez de longs verrous exclusifs et de lourdes E/S.

Task 9 (PostgreSQL): check if adding a default will rewrite in your version

cr0x@server:~$ psql -d prod -c "SHOW server_version;"
 server_version
----------------
 14.10

Signification : Sur v11+, l’optimisation pour les defaults constants existe ; sur les versions plus anciennes, ADD COLUMN DEFAULT pouvait réécrire la table.

Décision : Sur un vieux Postgres, évitez d’ajouter un default directement ; ajoutez une colonne nullable, backfillez par lots, puis définissez le default pour les nouvelles lignes.

Task 10 (PostgreSQL): create an index without blocking writes

cr0x@server:~$ psql -d prod -c "CREATE INDEX CONCURRENTLY idx_orders_created_at ON public.orders(created_at);"
CREATE INDEX

Signification : L’index a été construit sans prendre le verrou le plus lourd. Il a probablement pris plus de temps qu’une création normale.

Décision : Préférez ceci en production. Surveillez la progression et soyez prêt à réessayer si cela échoue à cause de conflits ou d’annulations.

Task 11 (PostgreSQL): watch progress of index builds

cr0x@server:~$ psql -d prod -c "SELECT pid, relid::regclass AS table_name, index_relid::regclass AS index_name, phase, blocks_done, blocks_total FROM pg_stat_progress_create_index;"
  pid  | table_name |        index_name         |  phase   | blocks_done | blocks_total
-------+------------+---------------------------+----------+-------------+-------------
 82110 | orders     | idx_orders_created_at     | building |      120331 |      612840

Signification : La vue de progression montre où vous en êtes ; blocks_done/blocks_total donne une estimation de l’avancement.

Décision : Si c’est trop lent ou que le système est affamé, pensez à brider au niveau infrastructure (limites I/O) ou replanifier.

Task 12 (PostgreSQL): measure replication replay lag during heavy DDL/backfill

cr0x@server:~$ psql -d prod -c "SELECT application_name, state, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS byte_lag FROM pg_stat_replication;"
 application_name |   state   | byte_lag
------------------+-----------+----------
 standby-a        | streaming | 3.1 GB

Signification : Votre standby est plusieurs GB en retard en termes de WAL ; cela peut représenter des minutes ou des heures selon le débit.

Décision : Si vous avez besoin d’un basculement chaud, arrêtez ou ralentissez l’opération jusqu’à ce que le lag soit acceptable.

Task 13 (MySQL): use pt-online-schema-change as a safer alternative

cr0x@server:~$ pt-online-schema-change --alter "ADD COLUMN promo_code varchar(32)" D=prod,t=orders --execute
No slaves found.  See --recursion-method if host has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Creating new table...
Created new table prod._orders_new OK.
Altering new table...
Altered `prod`.`_orders_new` OK.
Creating triggers...
Created triggers OK.
Copying rows...
Copying `prod`.`orders`:  12% 00:03 remain

Signification : L’outil crée une table fantôme, copie les lignes et utilise des triggers pour la garder synchronisée. Cela évite un blocage long sur la table originale au prix d’une charge et d’une complexité supplémentaires.

Décision : Utilisez-le quand un ALTER direct risque de bloquer ou de reconstruire. Assurez-vous de comprendre les triggers, la réplication et le comportement de cutover.

Task 14 (MySQL): detect MDL contention from performance_schema

cr0x@server:~$ mysql -uroot -p -e "SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA='prod' AND OBJECT_NAME='orders' LIMIT 5;"
+--------------+-------------+-----------+------------+-----------+
| OBJECT_SCHEMA| OBJECT_NAME | LOCK_TYPE | LOCK_STATUS| THREAD_ID |
+--------------+-------------+-----------+------------+-----------+
| prod         | orders      | SHARED_READ | GRANTED  |     88121 |
| prod         | orders      | EXCLUSIVE   | PENDING  |     88210 |
+--------------+-------------+-----------+------------+-----------+

Signification : Un verrou exclusif en attente (votre DDL) est bloqué derrière des verrous partagés accordés (lectures).

Décision : Tuez ou drenez les lecteurs longs, imposez un maxi temps d’exécution, ou déplacez les changements de schéma vers une fenêtre de maintenance dédiée.

Trois mini-récits d’entreprise depuis le terrain

Mini-récit 1 : l’incident provoqué par une mauvaise hypothèse

Dans une entreprise de taille moyenne, l’équipe a planifié la création d’un index « en ligne » sur MySQL. Le changement avait été validé par un développeur senior qui se souvenait qu’InnoDB peut construire des index sans bloquer les écritures. Tout le monde est parti.

Dix minutes plus tard, le taux d’erreurs API a grimpé. La latence est montée en flèche. Le CPU MySQL était correct. Le disque n’était même pas très occupé. Les graphiques ressemblaient à un incident réseau, ce qui montre bien que c’était une panne de base de données.

Le coupable était la contention du verrou de métadonnées. Une requête reporting longue détenait un verrou de métadonnées partagé sur la table. Le ALTER TABLE ... ADD INDEX attendait un verrou de métadonnées exclusif. Une fois en attente, les requêtes entrantes ont commencé à faire la queue derrière lui, parce que leurs demandes de verrou de métadonnées ne pouvaient pas être accordées de manière à affamer le verrou exclusif en attente. La table n’a pas seulement ralenti ; elle a effectivement cessé d’accepter de nouveaux travaux.

La mauvaise hypothèse était « les lectures ne bloquent pas les changements de schéma ». Dans MySQL, elles le peuvent. La correction n’a pas été héroïque : ils ont tué la session de reporting, le DDL s’est exécuté, le trafic a récupéré. Le vrai gain est venu après : timeouts pour les requêtes reporting, déport du BI sur des répliques avec garde-fous, et une étape de pré-vérification qui contrôle les requêtes longues sur les tables cibles.

Mini-récit 2 : l’optimisation qui a mal tourné

Une équipe fintech sur PostgreSQL voulait réduire la latence des requêtes en changeant une colonne monétaire de numeric en bigint de cents. C’est une optimisation saine : l’arithmétique entière est moins coûteuse, les index sont plus petits, et on évite des cas bizarres avec les décimales.

Ils ont tenté la voie directe : ALTER TABLE ... ALTER COLUMN ... TYPE bigint USING .... Sur la staging, cela s’est terminé rapidement. En production, cela a pris un ACCESS EXCLUSIVE et a commencé à réécrire une grosse table chaude.

L’appli n’est pas tombée immédiatement. Elle s’est figée. Les connexions se sont entassées. Comme le changement était dans une transaction (comme Postgres l’encourage), le verrou a persisté jusqu’au commit. Pendant ce temps, le volume WAL a bondi, les réplicas ont pris du retard, et l’équipe a perdu leur filet de sécurité : ils ne pouvaient plus basculer sans perdre une partie des écritures reconnues.

L’« optimisation » était correcte en principe et désastreuse en exécution. La correction finale a été une migration en phases : ajouter une nouvelle colonne, dual-write au niveau applicatif, backfill par petits lots, valider, basculer les lectures, puis supprimer l’ancienne colonne plus tard. Le bénéfice de performance est arrivé — simplement pas via un ALTER héroïque unique.

Mini-récit 3 : la pratique ennuyeuse mais correcte qui a sauvé la situation

Une équipe SaaS d’entreprise avait un runbook de migration strict : chaque changement de schéma nécessitait un pré-flight, un canary sur une réplique, des paramètres explicites de verrou/algorithme (pour MySQL) ou des contraintes en étapes (pour Postgres), et un plan de rollback qui n’impliquait pas de restauration depuis une sauvegarde.

Ils devaient ajouter une clé étrangère et rendre une colonne NOT NULL sur PostgreSQL. C’est exactement le type de changement qui peut déclencher une fête de verrous. Au lieu de le faire directement, ils ont ajouté la clé étrangère en NOT VALID, corrigé les lignes orphelines avec un job de nettoyage contrôlé, puis validé pendant une période de faible trafic.

Pour le changement NOT NULL, ils ont d’abord ajouté un CHECK (col IS NOT NULL) NOT VALID, backfillé les nulls par lots, validé le check, et seulement alors fait SET NOT NULL en une opération rapide.

Rien d’excitant n’est arrivé. Aucun incident. Pas d’applaudissements. Mais les meilleures migrations sont celles dont personne n’entend parler, y compris vous.

Erreurs courantes : symptôme → cause racine → correctif

1) Symptom: MySQL traffic suddenly stalls; CPU is low; many connections “waiting”

Cause racine : Accumulation de MDL. Un ALTER attend un verrou de métadonnées ; les nouvelles requêtes se mettent en file derrière.

Fix : Identifiez les bloqueurs, tuez/limitez les lecteurs longs, et exécutez le DDL avec les clauses ALGORITHM/LOCK. Utilisez des outils de schéma en ligne pour les tables chaudes.

2) Symptom: PostgreSQL ALTER TABLE appears “stuck” and blocks everything on the table

Cause racine : verrou ACCESS EXCLUSIVE attendant une transaction ou requête longue.

Fix : Trouvez le bloqueur avec pg_locks/pg_stat_activity, annulez-le ou replanifiez. Pour les changements de type, évitez l’ALTER direct ; utilisez expand/backfill/contract.

3) Symptom: Disk usage spikes during Postgres migration, then replicas fall behind

Cause racine : Réécriture de table générant de lourdes écritures heap et WAL ; le replay des standbys ne suit pas.

Fix : Migration en étapes, throttle du backfill, surveillez le WAL lag, assurez la marge disque, et envisagez de relaxer temporairement les attentes de basculement.

4) Symptom: MySQL replica lag grows after “online” index creation

Cause racine : DDL et/ou grosses transactions sérialisent l’application sur la réplication ; la construction d’index concurrence le thread d’application.

Fix : Surveillez Seconds_Behind_Master et l’état SQL. Utilisez des outils de schéma en ligne throttlés ; réduisez le taux d’écriture ; planifiez pendant les heures creuses.

5) Symptom: Postgres CONCURRENTLY index build fails mid-way

Cause racine : CREATE INDEX CONCURRENTLY est plus fragile : il peut échouer à cause d’annulations, de tuples morts ou de conflits ; il laisse aussi des index invalides derrière lui.

Fix : Vérifiez les index invalides et supprimez/recréez. Exécutez avec supervision opérationnelle et timeouts ; évitez de le faire en période instable.

6) Symptom: “ALTER succeeded” but application errors keep happening

Cause racine : Mésentente dans la chorégraphie de déploiement : l’appli attend une colonne pas encore présente / déjà supprimée ; ORM cache le schéma ; statements préparés.

Fix : Déploiements en deux phases : étendez le schéma d’abord, déployez le code qui l’utilise de façon optionnelle, puis contractez plus tard. Maintenez la compatibilité entre versions d’appli.

7) Symptom: Migration causes severe query slowdown weeks later

Cause racine : Bloat/vacuum debt Postgres ou change buffer/purge debt InnoDB suite à de gros backfills.

Fix : Backfills par lots, tuning du vacuum, surveillez le bloat et la history list length, et planifiez une maintenance post-migration.

Checklists / plan étape par étape

Plan A: expand → backfill → switch → contract (works on both)

1. Expand : ajoutez de nouvelles colonnes/tables/indexs d’une manière sûre en ligne (colonnes nullable, index concurrently, outillage shadow-table).
2. Dual-write : si vous changez la sémantique (changement de type, dénormalisation), faites écrire l’appli dans l’ancien et le nouveau champ.
3. Backfill : remplissez les lignes historiques par lots avec de petites transactions ; faites des pauses entre les lots ; surveillez le lag et les verrous.
4. Validate : contraintes en Postgres via NOT VALID puis VALIDATE ; en MySQL, validez via des requêtes et/ou des vérifications applicatives.
5. Switch reads : basculez les lectures applicatives vers le nouveau champ derrière un feature flag ; surveillez le taux d’erreurs.
6. Contract : supprimez les anciennes colonnes/indexs après un cycle complet de déploiement et après confirmation qu’aucun lecteur n’existe plus.

Checklist spécifique MySQL

• Spécifiez toujours ALGORITHM et LOCK. Si MySQL ne peut pas se conformer, laissez-le échouer.
• Avant de lancer un DDL, vérifiez les requêtes longues sur la table et tuez-les ou drenez-les.
• Assurez une marge disque pour les rebuilds même si vous « attendez un inplace ». Attendez-vous à vous tromper au moins une fois.
• Décidez de votre posture vis-à-vis de la réplication : pouvez-vous tolérer du lag ? Sinon, throttlez ou utilisez de l’outillage avec checks de replica-lag.
• Privilégiez gh-ost/pt-osc pour les grandes tables chaudes où le risque MDL est inacceptable.

Checklist spécifique PostgreSQL

• Supposez que les changements de type et certaines opérations de contraintes nécessiteront des verrous forts ou des réécritures.
• Utilisez CREATE INDEX CONCURRENTLY pour les écritures en production ; surveillez la progression et planifiez la récupération d’échec.
• Utilisez NOT VALID pour les clés étrangères/check constraints, puis validez plus tard.
• Surveillez le volume WAL et le replay lag des standbys ; traitez-le comme partie intégrante de la disponibilité.
• Gardez les transactions courtes pendant les backfills ; les transactions longues sabotent vacuum et la gestion des verrous.

« Do we run it now? » decision gate

• Vert : l’opération est metadata-only ou concurrente/instantanée, la taille de la table est gérable, pas de transactions longues, lag de réplique faible.
• Jaune : l’opération est lourde mais throttlable ; vous avez un plan de retour arrière ; vous pouvez accepter un lag temporaire.
• Rouge : nécessite une réécriture de table + verrous forts en période de pic ; la réplication est déjà en retard ; la marge disque est serrée ; vous n’avez pas de chemin de rollback sûr.

FAQ

1) Is PostgreSQL better than MySQL for schema changes?

Postgres est souvent plus prévisible : vous pouvez généralement raisonner à partir des modes de verrou et de l’information sur si une opération réécrit. MySQL peut être plus fluide pour certaines opérations « instantanées », mais la contention MDL peut créer des pannes soudaines.

2) What’s the single most dangerous MySQL DDL behavior?

Accumulation de verrous de métadonnées. Un DDL en attente peut faire que de nouvelles requêtes se mettent en file derrière lui, transformant un problème mineur en blocage total.

3) What’s the single most dangerous PostgreSQL DDL behavior?

Déclencher accidentellement une réécriture de table sous un ACCESS EXCLUSIVE sur une table chaude. Ça bloque et ça écrit beaucoup — le pire des deux mondes.

4) Can I safely add a NOT NULL constraint in Postgres without downtime?

Souvent oui, avec une approche en étapes : ajoutez un CHECK (...) NOT VALID, backfill, validez, puis SET NOT NULL. L’étape finale peut être rapide si les données sont déjà conformes.

5) Why did my Postgres migration “finish” but performance got worse later?

Les backfills génèrent des tuples morts et du bloat ; le vacuum a alors plus de travail. De plus, les caches sont perturbés par les gros scans séquentiels et les écritures. Prévoyez l’après-coup.

6) Do online schema change tools in MySQL guarantee no impact?

Non. Ils échangent le temps de verrou pour une charge d’écritures supplémentaire (triggers), des durées d’exécution plus longues et plus de pièces mobiles. Ils sont plus sûrs pour la disponibilité, pas magiquement gratuits.

7) Should I run DDL inside a transaction in Postgres?

Ça dépend. Le DDL transactionnel est puissant, mais il peut aussi garder des verrous plus longtemps que prévu. Des opérations comme CREATE INDEX CONCURRENTLY ne peuvent pas s’exécuter dans un bloc transactionnel. Utilisez des transactions quand la sécurité de rollback importe et que le temps de verrou est contrôlé.

8) What’s the safest way to change a column type on either database?

Expand/backfill/contract. Ajoutez une nouvelle colonne du type désiré, dual-write, backfill par lots, basculez les lectures, puis supprimez l’ancienne colonne plus tard.

9) How do I stop an ALTER that’s causing trouble?

MySQL : trouvez la session et KILLez-la, mais sachez que le serveur peut encore nettoyer. Postgres : pg_cancel_backend d’abord ; pg_terminate_backend si nécessaire. Confirmez toujours ce que vous tuez.

10) Which database is “less nightmare” overall?

Si vous valorisez l’introspection et des primitives de correction par étapes, Postgres a tendance à être plus calme. Si vous comptez sur des changements metadata « instantanés » et des outils externes, MySQL peut être excellent — jusqu’à ce que le MDL transforme votre journée en danse interprétative.

Prochaines étapes à réaliser cette semaine

1. Inventoriez vos 10 plus grandes tables et étiquetez lesquelles sont « chaudes » (QPS élevé) vs « froides ». Cela change à lui seul la façon dont vous planifiez le DDL.
2. Ajoutez des contrôles préalables aux migrations : bloquez si des requêtes longues existent sur la table cible (risque MDL MySQL) ou si des bloqueurs existent (chaîne de verrous Postgres).
3. Standardisez un pattern de migration : expand/backfill/contract pour les changements risqués, et exigez des paramètres « online » explicites (ALGORITHM/LOCK ou CONCURRENTLY).
4. Définissez des SLO de réplication pendant la maintenance : quel lag est acceptable, et quelle action déclenche une pause.
5. Exercez le chemin d’abandon : répétez comment annuler/tuer en sécurité, comment détecter le nettoyage, et comment restaurer le service si votre changement bloque.

Quand quelqu’un demande « MySQL vs PostgreSQL : qui transforme ALTER TABLE en cauchemar », la réponse honnête est : celui que vous traitez à la légère. La deuxième réponse honnête est : MySQL vous surprendra avec des verrous que vous n’avez pas vus ; Postgres vous surprendra avec du travail que vous n’avez pas budgété. Choisissez vos surprises — puis concevez-les hors de votre système.