Was passiert tatsächlich, wenn man in Postgres eine Spalte löscht?

• In PostgreSQL werden beim DROP einer Spalte die Daten nicht tatsächlich gelöscht — sie werden in den Metadaten lediglich „versteckt“
• Weil die Spalte nach DROP COLUMN intern weiterhin existiert, kann man das Limit von 1600 Spalten erreichen
• Um die Daten vollständig zu entfernen, ist VACUUM FULL oder ein manuelles Umschreiben der Tabelle nötig
• Das ist ein Design zur Performance-Optimierung, erfordert aber aus Compliance-Sicht wie bei der DSGVO besondere Aufmerksamkeit
• Wenn man versteht, „was tatsächlich passiert“, ist das bei Fehlerbehebung, Performance-Optimierung und Datenmanagement von Vorteil

---

Wie DROP COLUMN in PostgreSQL tatsächlich funktioniert

Das Problem: Was passiert, wenn man Spalten wiederholt hinzufügt und löscht?

• Mit Code wie dem folgenden wird eine Spalte 2000-mal hinzugefügt und wieder gelöscht:

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

• Am Ende verbleiben zwar nur 2 Spalten in der Tabelle, PostgreSQL wirft aber trotzdem einen Fehler wegen des 1600-Spalten-Limits
• Der Grund? Auch gelöschte Spalten existieren intern weiterhin

Was passiert intern in PostgreSQL?

Das Löschen einer Spalte ist keine „echte Löschung“

• PostgreSQL speichert Daten in 8-KB-Seiten
• Um eine Spalte physisch zu löschen, müsste die gesamte Tabelle neu geschrieben werden, was ineffizient ist
• Stattdessen wird die Spalte in den Metadaten als „dropped“ markiert und ignoriert

Über die Systemtabelle pg_attribute nachvollziehbar

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

• Beispielausgabe:

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

• Spalten mit attisdropped = t werden in Abfragen ignoriert, bleiben intern aber erhalten

Im Datenfile prüfen (pg_filedump verwenden)

• Analysiert man PostgreSQL-Datenfiles, sieht man, dass die Werte gelöschter Spalten tatsächlich erhalten bleiben
• In älteren Daten (Item 1) sind Werte für 3 Spalten vorhanden
• In nach dem Löschen eingefügten Daten (Item 3) fehlt der Wert der betreffenden Spalte und wird als NULL behandelt

Wie man gelöschte Spalten tatsächlich entfernt

1. VACUUM FULL

• Dabei wird die gesamte Tabelle neu geschrieben und auch die Daten gelöschter Spalten entfernt
• Nachteil: Die Spalte selbst existiert in pg_attribute weiterhin im Status „dropped“

2. Manuelles Umschreiben der Tabelle

• Eine neue Tabelle erstellen und nur die benötigten Spalten per SELECT kopieren

  CREATE TABLE new_table AS SELECT a, c FROM old_table;  
  

• Constraints, Indizes, Trigger usw. müssen manuell neu angelegt werden
• Auch ein Ablauf mit Backup per pg_dump → Bearbeitung der Dump-Datei → Wiederherstellung ist möglich

DROP COLUMN und das DSGVO-Problem des „Rechts auf Vergessenwerden“

• Manche äußern die Sorge: „Wenn eine Spalte nicht wirklich gelöscht wird, ist das dann nicht ein Verstoß gegen die DSGVO?“
• In der Praxis erfolgt das Löschen personenbezogener Daten aber meist zeilenweise (row)

  DELETE FROM users WHERE id = <user_id>; -- oder zusammen mit zugehörigen Tabellen löschen  
  

• DROP COLUMN steht nicht in direktem Zusammenhang mit der DSGVO; entscheidend ist, personenbezogene Daten korrekt zu modellieren und sauber zu löschen

Wichtige Hinweise

• PostgreSQL arbeitet mit MVCC, daher bleiben Daten nach dem Löschen einer Zeile erhalten, bis VACUUM abgeschlossen ist
• Auch auf Betriebssystemebene kann es sich nicht um physisches Löschen, sondern nur um ein „Delete-Flag“ handeln
• Rechtlich wichtig ist meist ein „angemessener Löschaufwand“; ein vollständiges physisches Überschreiben der Festplatte wird in den meisten Fällen nicht verlangt

Fazit: DROP COLUMN ist nur „Verstecken“, nicht „Löschen“

• Das ist ein auf Performance ausgelegtes Design, aber wenn sich Spalten ansammeln, kann man an das Limit von 1600 Spalten stoßen
• Falls nötig, sollte man die Daten mit VACUUM FULL oder durch Umschreiben der Tabelle bereinigen
• Für Systemdesign und Compliance ist ein Verständnis der internen Funktionsweise von PostgreSQL sehr hilfreich

Referenzen

• Postgres File Layout
• Postgres Page Layout
• Internal Layout of Heap Tables
• VACUUM-Dokumentation
• pg_filedump

[ohyecloudy]

Die Perspektive ist aufschlussreich, dass sich Implementierungsentscheidungen zur Performance-Optimierung auch mit der GDPR-Problematik des Rechts auf Vergessenwerden verbinden lassen. Entscheidend ist, personenbezogene Daten sauber zu modellieren und zu löschen, weshalb man sogar zu dem Schluss kommt, dass es damit nichts zu tun hat. Sauber.

[click]

Obwohl postgresql in letzter Zeit sehr beliebt ist, bevorzuge ich bei der MVCC-Implementierung eher einen Ansatz, bei dem Redo-/Undo-Bereiche getrennt existieren.
Da man bei Redo-/Undo-Bereichen eine gewisse Einbuße bei der Echtzeitfähigkeit in Kauf nehmen kann, gibt es auch Spielraum zur Kostenoptimierung, indem man Storage mit niedrigerer Leistungsklasse verwendet.
Auch der Umstand, dass man irgendwann die gesamte DB sperren und VACUUM FULL ausführen muss, ist für mich ein Minuspunkt.

[salsa]

Muss man VACUUM FULL irgendwann zwingend ausführen? In den meisten Dokumentationen, die ich gesehen habe, wird eher davon abgeraten.

Eine der Quellen, die ich gelesen habe:
https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/