Comment modifier une grosse table PostgreSQL

Pourquoi il n'est pas simple de modifier une table PostgreSQL directement en production ?

Lorsqu'il s'agit de votre base de données, la source de vérité sur laquelle vous vous appuyez pour des opérations, des calculs, ou même diffuser du contenu sur vos plateformes, vous ne pouvez pas risquer de tout perdre. Les enjeux sont importants au point que certaines compagnies prennent le temps de faire un backup de base avant ce genre d'opérations (il est notamment conseillé de le faire régulièrement). L'idée étant de pouvoir retomber sur vos pattes si vous rencontrez un problème. Cependant, vous conviendrez que si votre base fait 30go, le backup ne sera pas simple à faire.

Par ailleurs, il est difficile de mettre à jour une table de production car si l'opération est trop lourde pendant la modification de cette table, cela peut empêcher tout accès en parallèle. Ce qui implique littéralement un freeze de cette ressource pendant la durée de l'opération. Aussi, la question que vous devez vous poser est : pouvez-vous vous permettre une interruption ? Pour vous donner une idée, Amazon vend 150 produits par minutes. Imaginer rendre une table produits inaccessible pendant 1 heure signifie se tirer une balle le pied !

Donc, la gestion d'une base de données sur un projet actif n'est pas une mince affaire.

Les tâches obligatoires sur votre base

• 1. Toilettage Quotidien avec VACUUM

L'un des premiers défis réside dans le maintien de votre base de données en bonne santé au quotidien. C'est là que l'outil VACUUM entre en jeu. Il vous permet de récupérer de l'espace disque inutilisé suite aux suppressions, de prévenir la fragmentation, de nettoyer les transactions inachevées, et plus encore. La compréhension et l'utilisation appropriée de VACUUM sont cruciales pour maintenir la stabilité et la performance de votre base.

• 2. Optimisation de l'Architecture

Une architecture de base de données bien conçue est un élément clé de la performance. Savoir quand utiliser la redondance d'informations, c'est-à-dire répéter une colonne sur différentes tables pour éviter des jointures inutiles ou des sous-requêtes, peut considérablement accélérer vos requêtes.

• 3. Gestion du Volume de Données

Pour les projets de grande envergure, où des dizaines de sociétés vendent des milliers de produits chacune, le volume de données peut atteindre des dizaines, voire des centaines de gigaoctets. Il est essentiel de maintenir la base de données active dans une "tranche raisonnable" d'espace disque, idéalement en dessous de 1 Go pour garantir des performances optimales. Cela peut impliquer la mise en place d'une base de données active distincte de la base d'archives, avec des bascules périodiques pour libérer de l'espace et améliorer la réactivité de l'instance.

Il y a plusieurs façon de mettre à jour une table surtout si elle contient des données. Et par données, j'entends des dizaines, centaines de milliers de lignes, des millions voire plus. Croyez moi, à ce moment là, vous ne faîtes plus seulement un :

ALTER TABLE ADD COLUMN ... DEFAULT ...

Sous peine de mettre littéralement votre base de données en PLS ✝️.

Lorsque vous avez trop de données l'activation du DEFAULT VALUE peut faire très très mal à votre instance de base, le temps de passer sur chaque ligne pour y mettre la fameuse valeur par défaut. Au même moment, toutes les requêtes entrante vers cette table sont compromises, gros risques de perte d'accès à cause des lock de ressource etc.

Ne pas pouvoir répondre à une QUERY c'est une chose, mais perdre un INSERT/UPDATE/DELETE ça peut carrément être grave.

Il y a plusieurs façon de procéder dans ce cas de figure, certaines méthode sont plus complexes que d'autres, même si ce n'est pas mon domaine premier d'expertise (car je tiens à rappeler que c'est un métier à part entière et que nos experts SysDBA/DBOps sont biens plus qualifiés que moi pour vous aiguiller au mieux) je vais vous expliquer ce que je ferai dans ce cas. Bien sûr, ces méthodologies ont été testés et éprouvés sur le terrains avec mes confrères DBOps et sous couvert de leur expertise.

1- La nouvelle table

Il est tout à fait possible et plein de sens de créer une nouvelle table pour y effectuer les modification par rapport à une table initiale. On parle ici uniquement de la structure de table dans un premier temps.

Bien que cela comporte aussi ses inconvénients dans un second temps : Supposez que votre table principale fasse 10go, cela signifie que vous allez vous retrouver avec le double, du moins, pendant un instant. Si vous êtes sur un serveur dont les limitations disque ne vous permettent pas d'aller aussi loin, cette procédure semble déjà compromise.

Pour autant, cette méthode comporte aussi quelques avantages: La sécurité et l'intégrité des données sur la table principale qui n'est pas altérée, cela vous permet de structurer souplement vos colonnes et de prendre le temps d'améliorer le modèle. De plus, votre table principale est toujours accessible pour les utilisateurs.

Une fois votre table terminée, vous pourrez à une heure de faible sollicitation (souvent le soir à 2h/3h du mat 🛏️), effectuer une bascule de données, via des batchs ou non. Il est recommandé, pour avoir la garantie de récupérer 100% de l'état de la table initiale en sortie, d'utiliser des lock manuels pendant l'opération.

Cette solution est tout à fait convenable et fonctionne très bien. Quand votre opération est terminée, rien ne vous empêche de renommer les tables pour que la nouvelle puisse enfin remplacer l'ancienne.

2- Le batch update sur la table cible

Une autre alternative vous est possible : directement modifier la table cible !

Cependant, pas si vite jeune fougeux(se) ! ✋

Rappelez vous ce que je vous ai dit plus haut, on ne fait pas un ALTER TABLE gratuit sur une table avec une DEFAULT VALUE. Votre base va cracher ses poumons ! 😷

Pour cela je vous propose un peu d'algorithme et d'élégance pour paraphraser un ancien confrère.

Voici un exemple de batch que je ferai, je batcherai par lot de 1000 les lignes afin de ne pas saturer ma base. De cette façon, j'ai plus de garantie qu'elle reste accessible en parallèle pour tout autre accès.

DO $$
DECLARE
  total_rows FLOAT; -- we need a float to calculate coeff 
  total_pages INTEGER; 
  pg_offset INTEGER = 1000; -- our limit for each iteration
  id_val INTEGER;
  temp_row RECORD;
BEGIN
  SELECT INTO total_rows count(*) FROM movies;
  total_pages = ceil(total_rows / pg_offset);
  
  -- log infos
  RAISE NOTICE 'Total rows: %', total_rows; 
  RAISE NOTICE 'Pages count: %', total_pages; 
  
  -- add new column without default value 
  ALTER TABLE movies ADD COLUMN in_collection BOOLEAN;
  
  -- batch update value
  FOR id_val IN 1..total_pages
  LOOP
    RAISE NOTICE 'Updating page % of %', id_val, total_pages; 
      FOR temp_row IN
        SELECT * FROM movies LIMIT pg_offset offset pg_offset * (id_val - 1)
    LOOP
      UPDATE movies SET in_collection=true WHERE id = temp_row.id;
      COMMIT;
    END LOOP;
  END LOOP;
  
  RAISE NOTICE 'Job done, Rest soldier !';
  
END $$;

⚠️ Petite subtilité: l'usage du FLOAT pour total_rows est important pour la variable de coefficient.

Le passage, même temporaire à un état flottant, bien que le CEIL() va suivre, est obligatoire ! Si vous déclarez cette variable en INTEGER, vous rencontrez un problème lors de l'opération

CEIL(var / 1000); 

Si var = 1300, var / 1000 ne sera plus 1.3 mais 1 et ça change tout avant exécution du CEIL. Oui, même si vous n'assignez pas le fameux 1.3 à votre var ! 👈

__

Parenthèse fermée, rien ne vous empêche, quand tout est fini de faire un ALTER COLUMN et d'y ajouter votre DEFAULT VALUE. Au moins, on sait que les opérations d'écritures sont terminées.

Les notices permettent de suivre dans la console ce qu'il se passe pour ne pas vous faire poireauter à l'aveugle.

Pour information, j'ai lancé cette requête sur une base temporaire de films avec exactement 7392 lignes et voici l'output console :

NOTICE:  Total rows: 7392
NOTICE:  Pages count: 8
NOTICE:  Updating page 1 of 8
NOTICE:  Updating page 2 of 8
NOTICE:  Updating page 3 of 8
NOTICE:  Updating page 4 of 8
NOTICE:  Updating page 5 of 8
NOTICE:  Updating page 6 of 8
NOTICE:  Updating page 7 of 8
NOTICE:  Updating page 8 of 8
NOTICE:  Job done, Rest soldier !
DO

Query returned successfully in 11 secs 493 msec.

11.5sec pour 7392 lignes ça laisse présager de ce que ça serait avec 10 millions de lignes. En extrapolant un peu, on serait au alentours de 4H30...

Rien ne vous empêche de mettre des hard limit à vos batchs par exemple, d'ajouter des conditions pour s'arrêter à 1 000 000 de lignes, en vous obligeant ainsi à relancer plusieurs fois le script mais au moins vous aurez la possibilité de faire des contrôles entre les exécutions. N'oubliez pas ceci-dit, de commenter (double dash/tiret) la partie ADD COLUMN si vous le faites.

Pour conclure, je dirai que des solutions possibles pour faire une mise à jour de la structure de table en production sont :

• le backup de table et l'exécution brute des requêtes ALTER TABLE (bien que déconseillée)
• la nouvelle table et la copie incrémentale avec un batch SELECT -> INSERT (bien que nécessitant un espace disque suffisant)
• le batch opération sur la table elle même

C'est tout pour cet article qui j'espère vous apportera une solution dans vos problématiques quotidiennes. N'hésitez pas à le partager si vous avez apprécié, ou à me faire vos retours en commentaire ou sur la rubrique contact du site. Oui, je lis les messages ! 😉