Erreurs fréquentes lors des changements de schéma de base de données dans Postgres

Here is a summary of the common database schema change mistakes, translated and structured in Korean:

Erreurs liées à la concurrence

• Échec d’acquisition de verrou
• Mise à jour d’un trop grand nombre de lignes en une seule fois
• Garder une transaction ouverte trop longtemps après avoir acquis un verrou exclusif

Erreurs liées à la justesse des étapes - problèmes logiques

• Divergence de schéma inattendue
• Incohérence entre le schéma et le code de l’application
• Données inattendues

Autres erreurs

• Atteinte de statement_timeout
• Utiliser une clé primaire en entier 4 octets pour une table susceptible de croître
• Ignorer le fonctionnement de VACUUM et le risque de bloat

Cas 1. Incohérence de schéma

• Cela fonctionnait en développement/test, mais a échoué en QA/Staging/Production
• Il faut en identifier la cause et résoudre le problème en améliorant le workflow

Cas 2. Mauvaise utilisation de IF [NOT] EXISTS

• Ne pas chercher à ignorer une erreur de divergence de schéma avec IF NOT EXISTS
• Il faut identifier et corriger la cause profonde du problème

Cas 3. Atteinte de statement_timeout

• Tester à l’avance toutes les modifications avec des volumes de données importants

Cas 4. Modifications massives sans limite

• Modifier trop de lignes dans une seule transaction affecte les autres transactions
• Si le checkpointer n’est pas correctement réglé, cela peut générer un volume excessif de données WAL
• Une saturation des écritures disque peut entraîner une baisse générale des performances
• Des problèmes liés à VACUUM / au bloat peuvent survenir
• Traiter par lots et gérer VACUUM correctement

Cas 5. Attente dans une transaction après acquisition d’un verrou exclusif

• Si l’on effectue d’autres opérations entre BEGIN / ALTER TABLE / COMMIT, le verrou est conservé longtemps
• Une fois le verrou exclusif acquis, il faut terminer la transaction le plus vite possible

Cas 6. Transaction contenant DDL + DML massif

• Le verrou acquis à l’étape DDL reste longtemps en place jusqu’à la phase DML
• Séparer DDL et DML en transactions / étapes de migration distinctes

Cas 7. Blocage d’autres sessions dû à l’attente d’un verrou exclusif

• Quand autovacuum est en mode de prévention du wraparound, il ne cède pas la main au DDL
• Même les SELECT se retrouvent bloqués pendant l’attente d’acquisition du verrou
• Définir un lock_timeout faible et mettre en place une logique de retry

Cas 8. Précautions lors de la création d’une FK

• Lors de la création d’une FK sur une grande table, le scan de la table référencée prend du temps
• Définir la FK avec l’option not valid, puis valider dans une transaction séparée

Cas 9. Précautions lors de la suppression d’une FK

• Comme un verrou est nécessaire sur les deux tables, une logique de retry avec lock_timeout est requise

Cas 10. Précautions lors de l’ajout d’une contrainte CHECK

• Un scan complet de la table se produit, il faut donc utiliser une approche en deux étapes similaire à celle des FK

Cas 11. Précautions lors de l’ajout de NOT NULL

• Avant Postgres 11, ajouter NOT NULL à une nouvelle colonne provoque un scan de table
• À partir de Postgres 11, l’ajout d’une colonne NOT NULL DEFAULT permet de contourner ce problème
• À partir de Postgres 12, il est possible de définir NOT NULL en ajoutant une contrainte CHECK

Cas 12. Précautions lors du changement de type de données d’une colonne

• Une réécriture complète de la table peut se produire
• Il faut une approche consistant à ajouter une nouvelle colonne puis à copier les données via un trigger

Cas 13. Précautions lors de CREATE INDEX

• En OLTP, il faut utiliser CREATE INDEX CONCURRENTLY
• Si la création d’un index unique échoue, il faut nettoyer l’index invalide

Cas 14. Précautions lors de DROP INDEX

• Comme il y a des problèmes potentiels d’acquisition de verrou, utiliser DROP INDEX CONCURRENLTY

Cas 15. Précautions lors du renommage d’objets

• Il faut ajuster l’ordre de déploiement pour éviter les incohérences entre le code applicatif et le schéma DB

Cas 16. Ajout d’une colonne avec une valeur DEFAULT

• Avant PG 11, cela provoquait une réécriture complète de la table
• À partir de PG 11, l’ajout d’une colonne avec une valeur DEFAULT est devenu plus rapide

Cas 17. Nettoyage des résidus après l’échec de CREATE INDEX CONCURRENTLY

• En cas d’échec, un index invalide reste en place, il faut donc le nettoyer avant de réessayer

Cas 18. Utiliser une clé primaire en entier 4 octets sur une grande table

• Il faut utiliser int8. La plupart des frameworks utilisent déjà int8.

Recommandations

• Tester avec des volumes de données réalistes
• Vérifier la durée de maintien des verrous exclusifs
• Améliorer l’automatisation des déploiements
• Apprendre des autres et partager les connaissances

Avis de GN⁺

Cet article récapitule très bien les différentes erreurs et précautions à connaître lors de changements de schéma de base de données en conditions réelles. Les problèmes liés aux verrous exclusifs, en particulier, sont souvent mentionnés, et ils peuvent devenir bien plus graves sur des bases de données volumineuses.

Il explique aussi de façon concrète les précautions à prendre avec les FK, NOT NULL, les index, etc., autant d’éléments que les développeurs ont souvent tendance à sous-estimer. Comprendre et exploiter les améliorations apportées selon les versions de Postgres semble également très utile.

Surtout, je partage l’idée que tester rigoureusement avec des tailles de données réalistes et améliorer l’automatisation des déploiements sont essentiels pour minimiser les risques liés aux changements de schéma. Il peut aussi être intéressant d’utiliser des outils comme Database Lab Engine pour les tests et l’automatisation des déploiements.

J’aimerais voir davantage de billets de blog techniques partageant ce type d’astuces utiles. Plus ce genre d’information circule, plus cela aidera clairement les développeurs travaillant avec des bases de données à monter en compétence.