La difficulté de la suppression logique

 (atlas9.dev)
23 points par GN⁺ 2026-01-23 | 4 commentaires | Partager sur WhatsApp
  • La suppression logique basée sur une colonne archived_at est souvent utilisée pour la récupération des données et la conformité réglementaire, mais avec le temps, la complexité et l’inefficacité augmentent
  • Cette approche complique les requêtes, les index, les migrations et la logique de restauration, et comme la plupart des données archivées ne sont jamais relues, elle impose une charge inutile sur la base de données
  • Comme alternatives, sont proposées l’archivage basé sur les événements applicatifs, l’archivage basé sur des triggers et l’archivage basé sur le WAL (Change Data Capture)
  • Chaque méthode diffère en termes de complexité opérationnelle, d’exigences d’infrastructure et de facilité de restauration ; en particulier, l’approche basée sur le WAL nécessite une intégration avec des systèmes externes comme Kafka
  • Pour un nouveau projet, l’approche basée sur des triggers constitue le choix le plus équilibré en matière de simplicité et de maintenabilité

Les problèmes de la suppression logique

  • En général, les données sont supprimées logiquement à l’aide d’un booléen deleted ou d’une colonne horodatée archived_at
    • Cela permet de restaurer les données lorsqu’un client les a supprimées par erreur
    • Certaines données doivent aussi être conservées pour des raisons réglementaires ou d’audit
  • Cependant, la colonne archived_at introduit de la complexité dans les requêtes, les opérations et le code applicatif
    • La plupart des données archivées ne sont jamais relues
    • À cause de problèmes de comportement d’API ou d’outils d’automatisation (comme Terraform), des millions de lignes inutiles peuvent s’accumuler
  • Si aucun nettoyage des données archivées n’est mis en place, on observe une dégradation des performances lors des sauvegardes et restaurations de la base de données
  • Il faut filtrer les données archivées dans les requêtes et les index, avec un risque de fuite de données
  • Lors des migrations, il devient difficile de traiter les anciennes données ou de modifier les valeurs par défaut
  • La logique de restauration devient complexe et, lorsqu’elle nécessite des appels à des systèmes externes, des bugs peuvent apparaître
  • En conséquence, l’approche archived_at semble simple, mais son coût de maintenance à long terme est élevé

Archivage au niveau applicatif

  • Lors d’une suppression, un événement est publié, envoyé vers SQS, puis un autre service archive les données dans S3
  • Avantages
    • Simplification de la base de données principale et du code applicatif
    • Le nettoyage des ressources externes est traité de manière asynchrone, ce qui améliore les performances et la stabilité
    • Les données peuvent être sérialisées en JSON et archivées dans une structure adaptée à l’application
  • Inconvénients
    • Un bug dans le code applicatif peut entraîner une perte des données archivées
    • La complexité de l’infrastructure d’exploitation augmente, avec par exemple l’ajout d’une file de messages
    • Les données archivées dans S3 nécessitent des outils de recherche et de restauration
    Publicité

Archivage basé sur des triggers

  • Avant la suppression, un trigger copie la ligne dans une table archive distincte au format JSON
    • Exemple de table : archive(id, table_name, record_id, data, archived_at, caused_by_table, caused_by_id)
  • Lors d’une suppression par clé étrangère (cascade), des variables de session (archive.cause_table, archive.cause_id) sont utilisées pour tracer la cause de la suppression
    • Cela permet de savoir quel enregistrement parent a supprimé les données filles
  • Avantages
    • Les tables actives restent propres, sans colonne archived_at
    • Le nettoyage de la table d’archive (WHERE archived_at < NOW() - INTERVAL '90 days') est simple
    • L’efficacité des requêtes et des index est préservée, et les migrations sont simplifiées
    • La taille des sauvegardes diminue
  • La table d’archive peut être gérée dans un tablespace distinct ou via un partitionnement temporel
Publicité

Archivage basé sur le WAL (Change Data Capture)

  • Les logs WAL de PostgreSQL sont lus pour diffuser les événements de suppression vers des systèmes externes
    • Outil représentatif : Debezium (intégré à Kafka)
    • Exemple de chaîne : PostgreSQL → Debezium → Kafka → Consumer → Archive Storage
  • Alternatives légères
    • pgstream : envoi direct du WAL vers un webhook ou une file de messages
    • wal2json : sortie du WAL au format JSON
    • pg_recvlogical : outil intégré de réplication logique de PostgreSQL
  • Complexité opérationnelle
    • Une architecture basée sur Kafka nécessite supervision, gestion des incidents et tuning
    • Si le consumer prend du retard, les fichiers WAL s’accumulent → risque de manque d’espace disque
    • Il est possible de limiter cela avec le paramètre max_slot_wal_keep_size de PostgreSQL 13+
    • La surveillance du retard des slots de réplication et les alertes sont indispensables
  • Avantages
    • Toutes les modifications peuvent être capturées sans modifier le code applicatif
    • Le flux peut être envoyé vers plusieurs destinations (S3, data warehouse, index de recherche)
    • Aucune charge supplémentaire n’est ajoutée à la base de données principale
  • Inconvénients
    • Complexité opérationnelle et coût d’infrastructure élevés
    • En cas de retard du consumer, il peut y avoir perte de données ou besoin de resynchronisation
    • Lors d’un changement de schéma, une coordination entre la source et les consumers est nécessaire
Publicité

L’idée d’une réplique qui ne traite pas les suppressions

  • L’idée consiste à maintenir une réplique PostgreSQL qui ignore les requêtes DELETE
    • Cela permettrait d’accumuler et conserver toutes les données non supprimées
    • Les données archivées pourraient être interrogées directement
  • Problèmes potentiels
    • Impossible, potentiellement, de distinguer les informations de suppression
    • Risque de conflit lors de l’application des migrations
    • Hausse des coûts de stockage et d’exploitation

Conclusion

  • Dans un nouveau projet, l’archivage basé sur des triggers est le choix le plus pratique
    • Il est simple à mettre en place et permet de garder les tables actives propres
    • Il facilite la consultation et la gestion des données archivées, sans infrastructure supplémentaire
  • Si une infrastructure complexe existe déjà ou qu’un streaming vers plusieurs destinations est nécessaire, une approche basée sur le WAL est plus adaptée

À lire aussi

4 commentaires

 
love7peace 2026-01-23

J’avais appris qu’avec une approche basée sur des triggers, ça mettait de la charge sur la base de données…? Et là, on recommande les triggers.
 
nemorize 2026-01-24

Si la surcharge générée par des triggers de ce niveau devient un problème, alors même sans triggers, la situation est déjà pleine de problèmes.
 
cherrycoder 2026-01-23

Comme toujours, la réglementation a un coût. Bon, de toute façon, c’est aux consommateurs de le payer.
 
GN⁺ 2026-01-23
Réactions sur Hacker News

  • Dans le domaine bancaire où je travaille, j’ai au contraire trouvé le soft delete avantageux
    Avec une colonne deleted_at, l’écriture des requêtes est claire, et les requêtes d’analyse comme les pages d’administration peuvent manipuler le même jeu de données
    Les suppressions sont rares dans la plupart des cas, et les lignes en soft delete n’ont presque jamais causé de problèmes de performance
    En plus, comme les relations restent intactes, la restauration (undo) est simple
    Je préfère même aller plus loin et rendre les lignes totalement immutables, en ajoutant une nouvelle ligne à chaque mise à jour
    Pour conserver un journal, je trouve pertinent d’utiliser des triggers DB afin d’enregistrer dans une table de réplication chaque opération INSERT/UPDATE/DELETE

    • Tu as raison, mais seulement dans les cas où les suppressions sont rares
      Dans des tables que j’ai vues, où 50 à 70 % des lignes étaient en soft delete, la baisse de performances était bien réelle
      Au final, le soft delete dépend du contexte, et une analyse préalable est nécessaire
    • Dans Postgres, on peut aussi configurer l’indexation uniquement sur les données non supprimées en soft delete
      Ce n’est pas nécessaire dans la plupart des cas, mais cela peut aider à économiser la RAM
    • Dans une banque, le soft delete n’est qu’un cache-misère temporaire pour masquer le manque de traçabilité d’audit (auditability)
      La vraie solution, c’est l’Event Sourcing, où tous les changements doivent être enregistrés comme des événements
      Les performances en pâtissent, mais on peut compenser avec des snapshots et de la synchronisation (sync)
    • Si l’on veut exploiter une DB avec une structure immuable, on peut envisager un système comme Datomic
      Sa fonction de time travel permet d’interroger complètement les états passés
    • Quand je travaillais autrefois dans une compagnie d’assurance, chaque table fonctionnait aussi comme un journal append-only
      L’état le plus récent se trouvait sur la ligne avec le plus grand timestamp, et les états passés pouvaient être consultés via des filtres
      Cette approche permet une gestion d’historique très robuste

  • Le plus gros piège du soft delete, c’est la complexité des requêtes
    Au début, on pense qu’il suffit d’ajouter WHERE deleted_at IS NULL, mais quelques mois plus tard, des données fantômes apparaissent dans les rapports à cause de filtres oubliés
    On peut le résoudre avec des views, mais il faut malgré tout maintenir des patterns d’accès parallèles, et lorsqu’il faut consulter les données supprimées, on doit contourner l’abstraction
    L’event sourcing est plus propre, mais comme la charge opérationnelle est élevée, la plupart choisissent une approche hybride

    • Les views sont des outils largement suffisants
      Le problème, c’est que beaucoup de SWE et d’ingénieurs BI ne sont pas vraiment à l’aise avec SQL et la conception de schémas
      Un problème encore plus courant que le soft delete, c’est la gestion des Type 2 Slowly Changing Dimension
      La plupart créent inutilement des audit tables et répètent des UPDATE/INSERT inefficaces
      En réalité, une DB est un système vraiment magnifique, et c’est dommage qu’elle soit si peu respectée

  • Je pense que ce serait bien si le soft delete était fourni comme fonctionnalité native de la DB
    Pouvoir l’activer au niveau d’une table et choisir une stratégie de suppression serait idéal

    • En pratique, des systèmes comme Iceberg, Delta Lake et BigQuery proposent déjà une fonction de time travel
      Mais beaucoup d’équipes finissent quand même par l’implémenter en SCD (Slowly Changing Dimension) à cause d’exigences spécifiques

  • D’après mon expérience, l’approche basée sur des triggers a été la plus fiable
    La table d’archive doit rester en append-only, et la restauration doit être gérée dans la couche applicative
    Une mise à jour doit être considérée comme un soft delete, et le trigger doit capturer l’état précédent
    Le trigger doit absolument s’exécuter en phase BEFORE, et la logique doit rester simple
    Le partitionnement mensuel est courant, et avec une forte charge d’écriture, il vaut mieux passer à un découpage journalier

  • J’aimerais voir les DB évoluer de stateful → stateless
    Je préfère une architecture où tous les changements sont enregistrés comme des événements append-only, et où les données nécessaires sont exposées via des views
    L’idéal serait que la DB gère automatiquement des materialized index
    Certaines DB modernes offrent déjà ce type de fonctionnalité, mais il manque encore des avancées centrées sur l’OLTP

  • Dans une entreprise où j’ai travaillé auparavant, le soft delete était appliqué à tous les systèmes
    Je me souviens aussi d’un professeur qui disait : « Dans le monde de l’entreprise, les données ne sont jamais supprimées »

    • Supprimer définitivement revient à limiter soi-même ses futures capacités d’analyse de données
      Le stockage ne coûte pas cher, donc il ne faudrait jamais effacer les données
    • En revanche, c’est intéressant qu’il n’ait rien dit au sujet des modifications

  • Une base de données est un endroit où l’on stocke des faits (facts)
    La création d’un enregistrement est un nouveau fait, et sa suppression en est un autre
    Mais si l’on efface physiquement une ligne, le fait disparaît
    Dans la plupart des cas, ce type de suppression n’est pas souhaitable

    • Mais si les données constituent un actif avec un risque de fuite, il peut au contraire être nécessaire de les supprimer massivement
      Il faut prendre en compte le coût de conservation et le risque de sécurité
    • Si la DB n’est pas immuable, alors la modification elle-même produit déjà des faits disparus
      La décision de conserver les données pour toujours doit être prise avec prudence
    • Personnellement, je pense qu’un stockage de données ne devrait prendre en charge que deux opérations : lecture et insertion
      Pour cela, il est important de comprendre le cycle de vie des données

  • Chez Firezone, on a d’abord utilisé le soft delete pour les logs d’audit, mais on y a renoncé à cause de problèmes de migration
    À la place, l’équipe est passée à l’utilisation de Postgres CDC (Change Data Capture) pour exporter les événements vers une table séparée optimisée pour l’écriture
    Le soft delete est utile pour la restauration côté utilisateur, mais à mon avis inadapté pour l’audit ou la conformité

    • Sur un projet simple, il est souvent plus efficace d’auditer directement les appels API plutôt que les modifications DB

  • Créer une view au-dessus d’une table qui contient un champ de soft delete, afin de masquer les lignes supprimées, est une approche propre
    De cette manière, l’application n’a pas à se soucier de l’état de suppression

    • Avec la RLS (Row Level Security) de Postgres, on peut masquer automatiquement les lignes en soft delete
      L’application continue malgré tout à lire, écrire et supprimer sur la même table

  • Certains demandent comment gérer la schema drift
    Si l’on veut restaurer plus tard des données sérialisées selon le schéma au moment de la suppression, les changements de schéma posent problème

    • D’après mon expérience, les objets archivés sont rarement consultés
      Dans la plupart des cas, ils sont restaurés dans les jours qui suivent leur suppression, donc l’impact des changements de schéma reste limité
      Faire migrer d’anciennes archives vers un nouveau modèle était une tâche complexe et sujette aux erreurs
      Au final, la stratégie à adopter dépend de la manière dont le système est utilisé