- 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
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
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
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
Aucun commentaire pour le moment.