Que se passe-t-il réellement lorsqu’on supprime une colonne dans Postgres ?

• Dans PostgreSQL, même si on fait un DROP sur une colonne, les données ne sont pas réellement supprimées — elles sont simplement « masquées » dans les métadonnées
• Comme la colonne continue d’exister en interne après DROP COLUMN, on peut atteindre la limite de 1600 colonnes
• Pour supprimer complètement les données, il faut VACUUM FULL ou une réécriture manuelle de la table
• C’est un choix de conception pour optimiser les performances, mais il faut être vigilant du point de vue de la conformité, par exemple avec le RGPD
• Comprendre « ce qui se passe réellement » aide pour le dépannage, l’optimisation des performances et la gestion des données

---

Fonctionnement réel de DROP COLUMN dans PostgreSQL

Le problème : que se passe-t-il si on ajoute/supprime une colonne à répétition ?

• Avec un code comme celui-ci, on ajoute puis supprime une colonne 2000 fois :

  ALTER TABLE t ADD COLUMN c1 int;  
  ALTER TABLE t DROP COLUMN c1;  
  ...  
  

• Au final, il ne reste que 2 colonnes dans la table, mais PostgreSQL déclenche quand même une erreur liée à la limite de 1600 colonnes
• Pourquoi ? Parce que les colonnes supprimées existent toujours en interne

Que se passe-t-il à l’intérieur de PostgreSQL ?

La suppression d’une colonne n’est pas une « vraie suppression »

• PostgreSQL stocke les données par pages de 8 KB
• Supprimer physiquement une colonne obligerait à réécrire toute la table, ce qui est inefficace
• À la place, la colonne est marquée comme dropped dans les métadonnées puis ignorée

On peut le vérifier dans la table système pg_attribute

SELECT attnum, attname, attisdropped FROM pg_attribute WHERE attrelid = 'test2'::regclass AND attnum > 0;  

• Exemple de sortie :

  attnum | attname                  | attisdropped  
  --------+--------------------------+--------------  
        1 | a                        | f  
        2 | ........pg.dropped.2.... | t  
        3 | c                        | f  
  

• Les colonnes avec attisdropped = t sont ignorées par les requêtes, mais toujours présentes en interne

Vérification dans le fichier de données (avec pg_filedump)

• En analysant les fichiers de données PostgreSQL, on peut constater que les valeurs de la colonne supprimée sont encore réellement présentes
• Les anciennes données (Item 1) contiennent encore les valeurs des 3 colonnes
• Pour les données insérées après la suppression (Item 3), la valeur de cette colonne n’existe plus et est traitée comme NULL

Comment supprimer réellement une colonne supprimée

1. VACUUM FULL

• Réécrit toute la table et supprime aussi les données des colonnes supprimées
• Inconvénient : la colonne elle-même reste malgré tout présente dans pg_attribute avec l’état dropped

2. Réécriture manuelle de la table

• Créer une nouvelle table et n’y copier, via SELECT, que les colonnes nécessaires

  CREATE TABLE new_table AS SELECT a, c FROM old_table;  
  

• Il faut ensuite recréer manuellement les contraintes, index, triggers, etc.
• On peut aussi faire une sauvegarde avec pg_dump → modifier le fichier de dump → restaurer

DROP COLUMN et le « droit à l’oubli » du RGPD

• Certains s’inquiètent : « si la colonne n’est pas réellement supprimée, n’est-ce pas une violation du RGPD ? »
• Mais en pratique, la suppression de données personnelles se fait généralement au niveau de la ligne (row)

  DELETE FROM users WHERE id = <user_id>; -- ou suppression dans les tables liées  
  

• DROP COLUMN n’est pas directement lié au RGPD ; l’essentiel est de bien modéliser et supprimer les données personnelles

Points d’attention

• PostgreSQL fonctionne avec MVCC, donc même après suppression d’une ligne, les données restent présentes jusqu’à la fin du VACUUM
• Au niveau du système d’exploitation aussi, il peut s’agir d’un marquage comme supprimé plutôt que d’un effacement physique
• Juridiquement, ce qui compte est généralement un « effort raisonnable de suppression » ; un effacement complet du disque physique n’est le plus souvent pas exigé

Conclusion : DROP COLUMN ne fait que « masquer », pas « supprimer »

• C’est un choix de conception favorable aux performances, mais l’accumulation de colonnes peut faire buter sur la limite de 1600
• Si nécessaire, il faut nettoyer les données avec VACUUM FULL ou une réécriture de table
• Du point de vue de l’architecture système comme de la conformité, comprendre le fonctionnement interne de PostgreSQL est très utile

Références

• Postgres File Layout
• Postgres Page Layout
• Internal Layout of Heap Tables
• Documentation de VACUUM
• pg_filedump

[ohyecloudy]

L’idée que les choix d’implémentation pour l’optimisation des performances peuvent aussi être mis en relation avec la question du droit à l’oubli du RGPD est assez éclairante. Au final, la conclusion est que l’essentiel est de modéliser et supprimer correctement les données personnelles, donc que ce n’est pas vraiment lié. C’est propre.

[click]

Même si postgresql est très populaire en ce moment, je préfère les implémentations de MVCC où les zones redo/undo existent séparément.
Comme on peut se permettre de sacrifier un peu de temps réel sur les zones redo/undo, il y a aussi une marge pour optimiser les coûts en utilisant un stockage de moindre catégorie.
Et le fait de devoir, un jour ou l’autre, verrouiller toute la base de données et exécuter un VACUUM FULL est aussi un point que je n’aime pas.

[salsa]

Est-ce qu’il faut forcément exécuter VACUUM FULL à un moment donné ? La plupart des documents que j’ai vus disent plutôt qu’il ne faut pas le faire.

L’une des ressources que j’ai consultées :
https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/