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

 (postgres.ai)
5 points par GN⁺ 2024-04-29 | 1 commentaires | Partager sur WhatsApp
  • Les migrations de schéma dans Postgres sont particulièrement risquées, car les verrous, la réécriture de table et les retards de réplication peuvent provoquer des incidents en production, surtout dans les grands environnements OLTP
  • Les risques se concentrent sur les opérations qui déclenchent des scans complets et de longs verrous, comme l’ajout simultané de DEFAULT et NOT NULL, la création d’index sans CONCURRENTLY, la suppression immédiate de colonnes, les changements de type non sûrs et l’ajout de clés étrangères sans validation
  • Depuis PostgreSQL 11, le coût de certains ajouts de colonnes a diminué, mais pour les index il faut toujours suivre des procédures qui réduisent l’impact en production, comme CREATE INDEX CONCURRENTLY, et pour les clés étrangères NOT VALID puis VALIDATE CONSTRAINT
  • Les modifications massives doivent être découpées en petits lots, tout en vérifiant aussi les réplicas de lecture, le retard de réplication, les objets dépendants et le fait que d’anciennes instances de l’application référencent encore la colonne
  • Sur des volumes de données de taille production, il faut tester en amont et n’exécuter les opérations destructrices qu’avec un déploiement en plusieurs étapes et un plan de rollback validé

Les prérequis d’une migration de schéma

  • Ici, une migration de base de données ne désigne pas un changement de SGBD, mais une modification du schéma de la base
  • Les changements concernés ont trois caractéristiques
    • des changements versionnés, avec un identifiant unique et une procédure d’application automatisée pour chaque modification
    • des changements immuables, qu’on ne modifie plus après application en production et auxquels on n’ajoute que de nouveaux changements
    • des changements incrémentaux, où le schéma de la base évolue étape par étape
  • L’accent est mis sur les cas d’usage OLTP comme les applications web et mobiles, où une requête de plus d’une seconde est généralement considérée comme trop lente
  • Sur une petite base de données et avec une faible activité, certains problèmes peuvent passer inaperçus, mais à l’échelle d’environ 10 TiB et sous une charge de 10⁴ à 10⁵ transactions par seconde, la plupart des problèmes peuvent apparaître
  • Database Lab Engine sert au développement et aux tests à l’aide de clones légers, et permet de cloner une base de 10 TiB en moins de 10 secondes pour vérifier les risques d’un changement de schéma avant déploiement
  • Le GitLab Migration Style Guide est une ressource de référence issue de l’expérience de nombreux déploiements automatisés de changements de schéma Postgres

Ajout de colonnes et réécriture de table

  • Ajouter une colonne avec DEFAULT et NOT NULL en même temps est particulièrement risqué sur les anciennes versions de PostgreSQL
    • Avant PostgreSQL 11, cela nécessite une réécriture complète de la table
    • Sur une grande table, cela peut prendre des heures ou des jours, avec un verrou d’écriture pendant toute la durée
  • Exemple à risque
ALTER TABLE users ADD COLUMN status text DEFAULT 'active' NOT NULL;
  • La procédure la plus sûre consiste à séparer l’ajout de la colonne, la mise à jour des données et l’ajout de la contrainte
    • ajouter d’abord la colonne sans NOT NULL
    • mettre à jour les lignes existantes si nécessaire
    • ajouter ensuite la contrainte NOT NULL
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- UPDATE users SET status = 'active' WHERE status IS NULL;

ALTER TABLE users ALTER COLUMN status SET NOT NULL;
  • À partir de PostgreSQL 11, l’ajout d’une colonne avec une valeur DEFAULT non volatile ne nécessite plus de réécriture de table

Création d’index et ajout de clés étrangères

  • Créer un index sans CONCURRENTLY fait prendre à la création d’index standard un verrou exclusif sur la table
    • Toutes les écritures, et parfois certaines lectures, peuvent être bloquées jusqu’à la fin de la création de l’index
  • Exemple à risque
CREATE INDEX idx_users_email ON users(email);
  • En production, il est plus sûr d’utiliser CREATE INDEX CONCURRENTLY
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
  • CONCURRENTLY a toutefois des limites
    • l’opération est plus longue, mais sans bloquer l’accès à la table
    • elle ne peut pas être utilisée dans un bloc de transaction
    • en cas d’échec, elle peut laisser un index invalide qu’il faudra supprimer
  • Ajouter directement une contrainte de clé étrangère sur une grande table entraîne un scan complet de la table pour valider les données existantes et provoque de longs verrous
  • Une procédure plus sûre consiste à ajouter d’abord la contrainte avec NOT VALID, puis à la valider à un moment de faible trafic
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user_id;

Suppression de colonnes et changement de type

  • Supprimer immédiatement une colonne en production peut provoquer des erreurs applicatives si le code de l’application continue à la référencer
  • La suppression d’une colonne doit se faire en plusieurs étapes
    • déployer d’abord le code applicatif qui n’utilise plus cette colonne
    • attendre que toutes les anciennes instances de l’application aient été remplacées
    • supprimer la colonne dans une migration séparée
  • Changer le type d’une colonne peut provoquer une réécriture de table ou des problèmes de compatibilité
    • cela peut entraîner une indisponibilité, une perte de données ou des erreurs applicatives
  • Exemples problématiques
ALTER TABLE users ALTER COLUMN id TYPE bigint;
ALTER TABLE users ALTER COLUMN email TYPE varchar(100);
  • Pour passer de integer à bigint, il faut une procédure en plusieurs étapes avec une nouvelle colonne
  • Lorsqu’on réduit la taille d’un varchar, il faut d’abord vérifier les données et se demander si le changement est réellement nécessaire

Modifications massives, réplication et objets dépendants

  • Il faut éviter les migrations qui modifient trop de données dans une seule transaction
    • la contention sur les verrous et l’utilisation mémoire augmentent
    • le temps de récupération en cas de problème s’allonge
    • le retard de réplication peut s’aggraver
  • Pour une migration de données à grande échelle, il est plus sûr de la découper en petits lots
  • Il faut également examiner l’impact de la migration sur les réplicas de lecture et le retard de réplication
    • une grosse migration peut entraîner un retard de réplication important
    • elle peut affecter les performances des réplicas de lecture
  • Il faut aussi vérifier les objets qui dépendent des colonnes ou des tables modifiées
    • oublier des objets dépendants comme des vues, des fonctions ou des triggers peut provoquer des échecs en cascade ou nécessiter des interventions manuelles supplémentaires

Tests et plan de rollback

  • Si l’on teste une migration uniquement sur un petit jeu de données de développement, il est difficile d’observer les caractéristiques de performance d’un grand jeu de données
  • Il faut tester sur un clone de données à l’échelle de la production, avec des outils comme Database Lab Engine
  • S’il n’existe aucun moyen d’annuler une migration quand un problème survient, un incident de production peut se transformer en indisponibilité prolongée
  • En particulier pour les opérations destructrices, un plan de rollback validé est indispensable
  • Les bases d’un changement de schéma sûr sont les suivantes
    • tester sur des données à l’échelle de la production
    • utiliser une approche en plusieurs étapes pour les opérations risquées
    • tirer parti des fonctionnalités PostgreSQL comme CONCURRENTLY et NOT VALID
    • surveiller les performances et l’impact sur la réplication
    • toujours préparer un plan de rollback

À lire aussi

1 commentaires

 
GN⁺ 2024-04-29
Avis sur Hacker News

  • J’aime vraiment Postgres, mais la plupart de ce billet porte sur des points évitables et auxquels il faut faire attention. Cela dit, le pire dans Postgres, à mon avis, c’est la gestion des rôles
    La fonctionnalité est puissante et peut être excellente si elle est bien utilisée, mais la faire fonctionner en pratique donne l’impression de pratiquer de la magie noire. Un peu partout, l’interface ressemble à des incantations obscures dont on ne sait jamais si elles vont se comporter comme prévu, et c’est une façon horrible de gérer quelque chose d’aussi important
    La documentation sur ce sujet est également mince : elle indique seulement, dans les grandes lignes, comment cela est censé fonctionner pour des cas d’usage étroits. Quand ça ne se passe pas comme prévu, il faut tâtonner pour trouver ce qu’on a mal fait, sans pour autant avoir l’intuition de la bonne manière de procéder. Migrer une DB avec des droits utilisateur complexes est vraiment pénible
    J’ai l’impression qu’il faudrait que je prenne environ un mois pour écrire un cookbook. Si cela peut éviter ne serait-ce qu’à une personne de s’endormir en pleurant, ça en vaudrait la peine

    • Je suis d’accord pour dire que l’IAM de PostgreSQL est complexe. C’est complexe parce que la hiérarchie des objets comporte trois niveaux — Database, Schema, Tables — et parce qu’il existe aussi des droits accordés implicitement au propriétaire des objets de la DB
      Pour faire un SELECT sur une table, il faut CONNECT sur la Database et USAGE sur le Schema, accordés implicitement au propriétaire du Schema. Il faut aussi SELECT sur la Table, accordé implicitement au propriétaire de la table
      Pour voir les droits, il faut comprendre les entrées ACL au format grantee=privilege-abbreviation[]/grantor:. Les droits sur les Database se consultent avec \l+, ceux sur les Schema avec \dn+, et ceux sur les Table avec \dp+
      La liste des droits se trouve ici. Par exemple, user=arwdDxt/postgres signifie que le rôle postgres a accordé tous les droits à l’utilisateur
      Si la colonne grantee d’un objet est vide, cela peut désigner les droits par défaut du propriétaire, c’est-à-dire tous les droits, ou bien les droits accordés au rôle PUBLIC, qui correspond à tous les rôles existants. Exemple : =r/postgres
      L’utilisation du Schema public rend les choses encore plus confuses. Comme le Schema a le droit CREATE, si vous créez une table avec le même utilisateur que celui qui lit les données, les droits de propriétaire sont ajoutés par défaut et la lecture fonctionne immédiatement
    • La documentation de postgREST, qui s’appuie sur les rôles pour l’authentification, ne semble pas non plus très détaillée : https://postgrest.org/en/v12/explanations/db_authz.html
      Si quelqu’un écrivait sérieusement un cookbook sur les rôles Postgres et lançait quelque chose comme un Kickstarter, je ferais probablement partie des tout premiers contributeurs
    • Je suis d’accord avec l’idée que « le faire fonctionner ressemble à de la magie noire ». L’an dernier, j’ai implémenté un petit serveur postgREST avec de la sécurité au niveau des lignes, et le chemin pour y arriver a été assez difficile
      Mais une fois que ça a fonctionné, c’était vraiment magique, et les mécanismes concernés eux-mêmes étaient étonnamment assez simples
    • Je pense que je lirais un tel texte. La gestion des rôles implique beaucoup de suppositions, et le résultat est trop souvent que des rôles se retrouvent avec des droits excessifs
    • J’aimerais vraiment que tu l’écrives. Pour un contenu de ce niveau, je serais prêt à payer environ 20 dollars

  • Si vous exécutez des migrations de Schema en production, vous devriez utiliser lock_timeout
    Même des changements qui semblent inoffensifs et qui se terminent presque instantanément en test, comme supprimer une table avec des clés étrangères ou supprimer une clé étrangère, peuvent rencontrer des conflits de verrouillage sur une DB de production très sollicitée, à cause de transactions existantes ou d’autovacuum
    Ce ALTER va attendre le verrou de la première transaction tout en demandant un verrou ACCESS EXCLUSIVE, ce qui bloque ensuite toutes les requêtes sur la table verrouillée
    Quand on exploite Postgres à une certaine échelle, ce genre de conflit n’est qu’une question de temps. En définissant lock_timeout, la migration échoue une fois le délai dépassé, au lieu d’attendre en bloquant toutes les autres requêtes

    • statement_timeout inclut aussi le temps d’attente des verrous, ce qui permet de mieux estimer l’impact sur une table très sollicitée
      Si vous fixez la limite à 5 secondes, vous savez que l’interruption totale sera d’au plus 5 secondes, puis les transactions suivantes continuent. Avec seulement lock_timeout, vous ne contrôlez pas la durée de l’opération une fois le verrou obtenu ; selon le trafic concurrent, elle peut être rapide ou lente
    • Selon la version de Postgres, le fait que certaines requêtes DML prennent ou non un verrou exclusif varie assez fortement
      Je me demande s’il existe une bonne méthode pour analyser une requête et savoir quel type de verrou elle va prendre. Quand je n’en suis pas sûr, j’ai toujours fini par relire la documentation
    • Bon conseil. Cela dit, techniquement, je croyais que ce n’était pas parce que le verrou ACCESS EXCLUSIVE avait déjà été acquis puis attendait, mais plutôt à cause de la file d’attente des verrous
      ALTER est en attente de la libération de verrous de niveau inférieur à ACCESS EXCLUSIVE
    • Avec cette approche, il se peut que le ALTER ne s’exécute jamais. Si la table reçoit suffisamment de trafic, c’est possible
      Dans ce cas, si l’application peut s’en remettre, je pense que le mieux est de tuer les autres requêtes en cours qui bloquent le ALTER

  • Je consulte plusieurs fois par semaine le guide Safe Migrations in Ecto de Fly.io. Ecto est l’adaptateur DB d’Elixir
    C’est une référence très utile pour vérifier rapidement si une migration de base suffit ou si une procédure plus complexe est nécessaire
    https://fly.io/phoenix-files/safe-ecto-migrations/

  • Ce qui m’avait le plus surpris, à mes débuts avec les index Postgres, c’est qu’un index UNIQUE pouvait affecter les résultats de requêtes concurrentes à cause des verrous supplémentaires
    Une requête comme INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);, exécutée simultanément en mode par défaut, peut insérer des valeurs bar en double. En effet, une transaction peut ne pas voir la nouvelle valeur maximale créée par une autre transaction
    On pourrait penser qu’en ajoutant un index UNIQUE, la transaction « perdante » recevrait une erreur de contrainte, mais en réalité les deux transactions réussissent et la condition de concurrence disparaît

    • Ce n’est pas vrai. La sous-transaction qui perd la compétition sur l’index est interrompue
      =# INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);
      ERROR: duplicate key value violates unique constraint "foo_bar_idx"
      DETAIL: Key (bar)=(2) already exists.
    • Si tu veux dire que, même avec un index UNIQUE, les deux insertions réussissent et qu’on finit avec des valeurs en double, alors si c’est vrai, c’est un bug
    • Si je ne me trompe pas, on peut le faire sans interruption en créant un index normal avec CONCURRENTLY, puis en créant une contrainte UNIQUE non vérifiée
      Cette contrainte ne s’applique qu’aux nouveaux INSERT/UPDATE. Ensuite, exécuter VALIDATE sur la contrainte en fait une contrainte UNIQUE complète
    • Si ça te paraît surprenant, je pense que c’est parce que tu as été trop exposé aux langages impératifs
      Je suis d’accord pour dire que c’est courant, mais le problème relève davantage du développement logiciel en général que de Postgres
    • À quel niveau d’isolation cela se produit-il ?

  • C’est à cause de ce genre de pièges que j’ai créé Reshape [0], avec l’objectif d’automatiser les migrations de schéma sans interruption
    Je ne peux pas dire que nous évitons tous les problèmes, mais nous construisons un nouveau produit qui vise cela. Si ce domaine, et en particulier Postgres, vous intéresse, j’aimerais avoir de vos nouvelles : fabian@reshapedb.com
    [0] https://github.com/fabianlindfors/reshape

    • Y a-t-il une chance que ça fonctionne aussi avec crdb ?

  • Une autre erreur que je vois souvent consiste à dupliquer une table en oubliant les index
    CREATE TABLE SELECT * FROM WHERE <> ne fonctionne pas comme ça. Les gens font souvent cela lorsqu’ils veulent créer une table de sauvegarde ou effectuer une suppression massive

    • Si c’est pour créer une table de sauvegarde, c’est-à-dire si l’on s’apprête à faire une opération complexe et ambiguë qui peut tout casser immédiatement de façon imprévisible, alors je ne me soucie absolument pas des index ni des contraintes
      Ce que je veux, c’est une copie des données déjà présente, que je n’utiliserai probablement pas, afin de ne pas avoir à restaurer depuis une sauvegarde de la DB et le WAL. Créer les index serait un gaspillage de temps serveur et d’espace disque
      Si les choses tournent mal ou si j’en ai vraiment besoin, je pourrai créer ces index plus tard
    • Dans ce cas, peux-tu aussi dire quelle serait la bonne méthode ?

  • La partie « Case 2. mauvaise utilisation de IF [NOT] EXISTS » ne donne pas de bon exemple de mauvaise utilisation
    Et en pratique, c’est bien comme ça qu’il faut l’utiliser. C’est propre, simple, et il n’y a pas de piège caché. Quand il n’y a que quelques tables, un outil de migration de schéma est une surcharge excessive

    • Le piège est simple : « masquer le problème avec de la logique et augmenter le risque d’un état anormal »
      Mettre un pansement sur de mauvaises données ne résout pas le problème, cela ne fait que le cacher. Selon le type de problème, il peut exploser plus tard d’une manière inattendue, au pire moment
      Ici, les « mauvaises données » sont des tables, colonnes ou vues qui devraient exister ou ne pas exister, mais se trouvent dans l’état inverse. Pourquoi une table qui ne devrait pas encore exister existe-t-elle ? Une suppression a-t-elle échoué ? Le schéma de la table existante est-il correct ? La même migration a-t-elle été exécutée deux fois par erreur ?
      Après chaque migration, le schéma doit être dans l’état exact attendu. Si une migration contient IF [NOT] EXISTS, cela signifie qu’après la migration précédente, le schéma n’est pas resté dans l’état exact attendu. Ne pas être certain de l’état du schéma n’est pas une bonne chose
    • Je trouve que l’article explique assez bien cette mauvaise utilisation. Le point clé est que les changements de schéma par des chemins séparés sont un problème de processus et de workflow, et doivent donc être résolus directement
      Que faire si la colonne d’une table déjà existante diffère de celle que la migration essaie de créer ? IF EXISTS fera réussir la migration, mais laissera le schéma dans un mauvais état. Dans ce genre de cas, il vaut mieux que la migration échoue rapidement

  • Petite remarque sur l’utilisation de int4 comme clé primaire de substitution
    Ce qui compte, n’est-ce pas plutôt la taille de l’index que celle de la table ? La taille de la table inclut déjà un en-tête de 23 octets et du padding d’alignement, donc 4 octets de différence ont peu d’effet. En revanche, si cela permet de garder davantage d’index en mémoire, il peut y avoir un avantage. Une entrée d’index a un en-tête de 8 octets
    De plus, le milliard de lignes dans l’exemple est trop proche de la valeur maximale de int4, ce qui est inquiétant
    Cela dit, l’article est excellent

    • Exact. Il y a aussi la taille de l’index, et la taille sur disque. Postgres compacte les lignes de table sur disque, mais pas en RAM
      Cela signifie-t-il qu’une page de 8 Ko sur disque peut dépasser 8 Ko en RAM ?
      Cela semble surtout affecter la mémoire de travail des données de lignes de table. Cela reste important, surtout que Postgres est catastrophique pour la localité des requêtes par plage, puisque les lignes sont dans un ordre aléatoire. Mais je ne pense pas que ce soit une observation décisive

  • Je suis un développeur qui a été globalement protégé des problèmes liés aux DB. Dans Django, je sais créer des migrations, créer les tables de modèles et interroger via l’ORM, mais beaucoup de choses qui se passent en interne me semblent relever de la magie noire
    Maintenant que je lance une entreprise, j’ai peur de devoir affronter ce genre de problèmes et les résoudre seul. Quelle approche adopter pour apprendre quoi faire dans un environnement de développement ?

    • Il faut échouer et apprendre de ses erreurs. Ou alors embaucher un développeur, échouer ensemble et apprendre ensemble

  • J’aime Postgres, mais je déteste vraiment le fait qu’il n’existe pas de méthode intégrée pour les mises à jour/suppressions par lots
    C’est la partie la plus agaçante, et chaque fois que je me heurte à ce mur, je dois réécrire un batcher presque tous les mois