To sidebar

samedi 1 septembre 2018

PostgreSQL 11 : ajout de colonnes avec valeurs par défaut & bombe à retardement

Dans les améliorations de performances de la v11, l’une d’elles peut faire gagner beaucoup de temps lors des mises à jour applicatives, voire peut en rendre certaines simplement possibles :

Many other useful performance improvements, including making ALTER TABLE .. ADD COLUMN with a non-null column default faster

Les détails sont chez Brandur. En résumé, lors de l’ajout d’une colonne avec une valeur par défaut, PostgreSQL pré-v11 devait réécrire la table pour la rajouter physiquement. Ce qui prend son temps si ladite table fait 1 To. Et l’application utilisatrice est bloquée, ce qui peut être intolérable. Les contournements, comme l’ajout d’une colonne nullable puis la mise à jour progressive par batchs, introduisent un état transitoire un peu bancal.

Vient la v11, qui se contente de rajouter l’information que la colonne a été ajoutée, et que si elle ne se trouve pas dans la ligne, il faut prendre la valeur par défaut.

Petit test avec une table tellement vide qu’elle n’a même pas de colonnes :

  CREATE TABLE fantome
  AS SELECT  /* rien */ FROM generate_series(1,1000000) ;

  SELECT pg_size_pretty(pg_relation_size('fantome')) ;
   pg_size_pretty 
  ────────────────
   27 MB

Donc 27 Mo d’informations administratives sur 1 millions de lignes de néant. Soit, c’est un cas dégénéré.

Ajout d’une colonne avec une valeur par défaut :

  ALTER TABLE fantome ADD COLUMN a1 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
  ALTER TABLE
  Temps : 24,337 ms

C’est instantané en v11, qui se contente de stocker la valeur par défaut des colonnes non remplies (alors que cette opération durait une bonne seconde en v10 sur mon portable à SSD). Qu’il y ait un NOT NULL ou pas ne change rien.

En toute rigueur PostgreSQL applique toujours sur la table le verrou de plus haut niveau, AccessExclusive, qui va bloquer toutes vos requêtes (ce qui se verra très vite si lui-même est bloqué et qu’elles « s’empilent » derrière lui). Et la valeur par défaut doit être une constante, du moins pendant la durée de l’ordre (donc un timetz DEFAULT now() sera ajouté instantanément, mais la même en DEFAULT clock_timestamp() donne des valeurs différentes presque sur chaque ligne, donc n’évitera pas la réécriture de la table).

En conséquence, la taille de la table n’a pas bougé :

  SELECT pg_size_pretty(pg_relation_size('fantome')) ;
   pg_size_pretty 
  ────────────────
   27 MB

Si on se met à jour cette nouvelle colonne, évidemment on va devoir en payer le prix (en gros ici 1 million * 19 octets du nouveau texte + les Mo d’administration des lignes + la place des anciennes lignes marquées comme périmées ) :

  UPDATE fantome SET a1='beaucoup plus court' ;
  UPDATE 1000000
  Durée : 4823,868 ms (00:04,824)

  SELECT pg_size_pretty(pg_relation_size('fantome')) ;
   pg_size_pretty 
  ────────────────
   77 MB

Ensuite on va se faire plaisir en rajoutant plein d’autres colonnes aux valeurs par défaut :

    ALTER TABLE fantome ADD COLUMN a2 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
    ALTER TABLE fantome ADD COLUMN a3 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
    ALTER TABLE fantome ADD COLUMN a4 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
    ALTER TABLE fantome ADD COLUMN a5 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
    ALTER TABLE fantome ADD COLUMN a6 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
    ALTER TABLE fantome ADD COLUMN a7 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
    ALTER TABLE fantome ADD COLUMN a8 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
    ALTER TABLE fantome ADD COLUMN a9 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
    ALTER TABLE fantome ADD COLUMN a10 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
    ALTER TABLE fantome ADD COLUMN a11 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
    ALTER TABLE fantome ADD COLUMN a12 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
    ALTER TABLE fantome ADD COLUMN a13 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
    ALTER TABLE fantome ADD COLUMN a14 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';

Comme pour le premier ordre, chaque ordre fait 1 ms, et la taille n’a pas bougé :

  77 MB

Ce n’est pas une arnaque, les valeurs sont bien là (du moins elles sont facilement reconstituées) :

  SELECT a14 FROM fantome LIMIT 1 ;

                     a14                    
  ──────────────────────────────────────────
   azertyuiopqsdfghjklmwxcvbn,;:!0123456789

Maintenant on suppose qu’il faut reconstruire la table :

  VACUUM FULL fantome ;
  SELECT pg_size_pretty(pg_relation_size('fantome')) ;

  601 MB

Ce qui est la « vraie » taille, qu’on aurait obtenue d’entrée en v10, avec les lignes contenant toute en dur la valeur par défaut.

Par contre, on est très loin de la politique habituelle « pour récupérer de l’espace avec un VACUUM FULL, prévoyez transitoirement un espace supplémentaire équivalent à la taille de la table » : ici la taille de la table reconstruite explose.

Big bang à effet retard :

Quel est le use case pour cette nouvelle fonctionnalité ? Les grosses tables que l’on ne pouvait plus modifier parce qu’une réécriture serait bloquante trop longtemps. Ce problème-là est résolu. La croissance du volume se fera de manière plus progressive au fil des mises à jour (pas seulement des anciennes colonnes, puisqu’on met à jour toute la ligne en cas de mise à jour de n’importe quelle colonne).

Ce peut aussi être une fonctionnalité sympa sur une table « presque vide » aux très nombreuses colonnes presque toujours toutes avec la valeur par défaut, et jamais mises à jour (ça doit bien exister quelque part). Elle prendra très peu de place par rapport à son volume théorique.

Par contre, après une purge, on peut penser que l’on va récupérer de la place et on passe enfin ce VACUUM FULL qui a tant tardé… pour voir la taille exploser.

Idem en cas de restauration logique (même si on l’évite sur de grosses tables) : la table restaurée peut prendre beaucoup plus de place que l’ancienne.

Dans les deux cas, avec de la chance, cela sera compensé par des DROP COLUMNS plus anciens, dont la place est gaspillée jusqu’à reconstruction de la table.

© SQL & éléphant, after the WP Dusk To Dawn theme Propulsé par Dotclear