Die Schwierigkeit von Soft Deletes

• Für Datenwiederherstellung und Compliance wird häufig Soft Delete auf Basis einer archived_at-Spalte verwendet, doch mit der Zeit nehmen Komplexität und Ineffizienz zu
• Dieser Ansatz macht Abfragen, Indizes, Migrationen und Restore-Logik komplexer, und da die meisten archivierten Daten nie wieder gelesen werden, entsteht unnötige Last für die Datenbank
• Als Alternativen werden anwendungsereignisbasierte Archivierung, triggerbasierte Archivierung und WAL-(Change-Data-Capture)-basierte Archivierung vorgestellt
• Die einzelnen Ansätze unterscheiden sich bei betrieblicher Komplexität, Infrastruktur-Anforderungen und Einfachheit der Wiederherstellung; insbesondere der WAL-basierte Ansatz erfordert Integration mit externen Systemen wie Kafka
• Für neue Projekte ist der triggerbasierte Ansatz die am besten ausbalancierte Wahl in Bezug auf Einfachheit und Wartbarkeit

---

Probleme von Soft Deletes

• Üblicherweise werden Daten logisch gelöscht, indem eine boolesche Spalte deleted oder eine Zeitstempelspalte archived_at verwendet wird
  • Daten können wiederhergestellt werden, wenn ein Kunde sie versehentlich gelöscht hat
  • In manchen Fällen ist eine Aufbewahrung aus regulatorischen oder Audit-Zwecken erforderlich
• Eine archived_at-Spalte verursacht jedoch Komplexität in Abfragen, Betrieb und Anwendungscode
  • Die meisten archivierten Daten werden nie wieder gelesen
  • Durch Probleme im API-Verhalten oder Automatisierungs-Tools wie Terraform können sich Millionen unnötiger Zeilen ansammeln
• Wenn keine Bereinigungsjobs für archivierte Daten eingerichtet sind, kommt es zu Leistungseinbußen bei Datenbank-Backups und -Restores
• In Abfragen und Indizes müssen archivierte Daten herausgefiltert werden, und es besteht ein Risiko von Datenlecks
• Bei Migrationen ist es schwierig, alte Daten zu verarbeiten oder Standardwerte zu ändern
• Die Restore-Logik wird komplexer, und wenn Aufrufe externer Systeme nötig sind, können Bugs entstehen
• Insgesamt wirkt der archived_at-Ansatz zwar einfach, verursacht aber langfristig hohe Wartungskosten

Archivierung auf Anwendungsebene

• Beim Löschen wird ein Event veröffentlicht, das an SQS gesendet wird, damit ein anderer Service die Daten in S3 archiviert
• Vorteile
  • Vereinfachung der primären Datenbank und des Anwendungscodes
  • Bereinigung externer Ressourcen kann asynchron erfolgen, was Leistung und Stabilität verbessert
  • Durch Serialisierung als JSON können Daten in einer anwendungsfreundlichen Struktur archiviert werden
• Nachteile
  • Durch Bugs im Anwendungscode kann es zu Verlust archivierter Daten kommen
  • Betriebliche Infrastruktur-Komplexität durch Message Queues und Ähnliches nimmt zu
  • Für in S3 archivierte Daten werden Werkzeuge für Suche und Wiederherstellung benötigt

Triggerbasierte Archivierung

• Ein Trigger vor dem Löschen kopiert die Zeile als JSON in eine separate Archive-Tabelle
  • Beispiel-Tabelle: archive(id, table_name, record_id, data, archived_at, caused_by_table, caused_by_id)
• Bei Löschungen per Foreign-Key-Cascade werden zur Nachverfolgung der Löschursache Session-Variablen (archive.cause_table, archive.cause_id) verwendet
  • So lässt sich abfragen, welcher übergeordnete Datensatz untergeordnete Daten gelöscht hat
• Vorteile
  • Live-Tabellen bleiben sauber, eine archived_at-Spalte ist nicht nötig
  • Die Bereinigung der Archivtabelle (WHERE archived_at < NOW() - INTERVAL '90 days') ist einfach
  • Effiziente Abfragen und Indizes bleiben erhalten, Migrationen werden vereinfacht
  • Geringere Backup-Größe
• Die Archivtabelle kann über einen separaten Tablespace oder zeitbasierte Partitionierung verwaltet werden

WAL-(Change-Data-Capture)-basierte Archivierung

• Die WAL-Logs von PostgreSQL werden gelesen, um Delete-Events in externe Systeme zu streamen
  • Typisches Tool: Debezium (integriert mit Kafka)
  • Beispielpfad: PostgreSQL → Debezium → Kafka → Consumer → Archive Storage
• Leichtgewichtigere Alternativen
  • pgstream: sendet WAL direkt an Webhooks oder Message Queues
  • wal2json: gibt WAL als JSON aus
  • pg_recvlogical: integriertes Tool für logische Replikation in PostgreSQL
• Betriebliche Komplexität
  • Kafka-basierte Setups erfordern Monitoring, Incident-Handling und Tuning
  • Wenn der Consumer verzögert ist, können sich WAL-Dateien ansammeln → Risiko von zu wenig Festplattenspeicher
  • Mit der Einstellung max_slot_wal_keep_size in PostgreSQL 13+ lässt sich das begrenzen
  • Monitoring und Alerts für Replikationsslot-Lag sind essenziell
• Vorteile
  • Alle Änderungen lassen sich ohne Änderungen am Anwendungscode erfassen
  • Streaming zu verschiedenen Zielen (S3, Data Warehouse, Suchindex) ist möglich
  • Keine zusätzliche Last auf der primären Datenbank
• Nachteile
  • Hohe betriebliche Komplexität und Infrastrukturkosten
  • Bei Consumer-Lag kann Datenverlust auftreten oder eine erneute Synchronisierung nötig werden
  • Bei Schemaänderungen ist Abstimmung zwischen Quelle und Consumer erforderlich

Idee eines Replikats, das Löschungen nicht verarbeitet

• Es wird die Idee vorgeschlagen, ein PostgreSQL-Replikat zu betreiben, das DELETE-Abfragen ignoriert
  • So könnten alle nicht gelöschten Daten kumulativ archiviert werden
  • Archivierte Daten wären direkt abfragbar
• Mögliche Probleme
  • Es könnte unmöglich sein, Löschinformationen eindeutig zu unterscheiden
  • Bei der Anwendung von Migrationen besteht Konfliktgefahr
  • Speicherbedarf und Betriebskosten steigen

Fazit

• In neuen Projekten ist die triggerbasierte Archivierung die praktikabelste Wahl
  • Sie ist einfach einzurichten und hält Live-Tabellen sauber
  • Auch ohne separate Infrastruktur lassen sich Archivdaten leicht einsehen und verwalten
• Wenn bereits komplexe Infrastruktur vorhanden ist oder Streaming zu mehreren Zielen benötigt wird, ist ein WAL-basierter Ansatz geeignet