Häufige Fehler bei DB-Schemaänderungen in Postgres

Hier ist eine Zusammenfassung der häufigen Fehler bei Änderungen am Datenbankschema, auf Deutsch übersetzt und strukturiert:

Fehler im Zusammenhang mit Nebenläufigkeit

• Fehler beim Erwerb von Locks
• Zu viele Zeilen auf einmal aktualisieren
• Nach dem Erwerb eines exklusiven Locks die Transaktion zu lange offen lassen

Fehler bei der Korrektheit der Schritte – logische Probleme

• Unerwartete Schema-Abweichungen
• Inkonsistenz zwischen Schema und App-Code
• Unerwartete Daten

Sonstige Fehler

• statement_timeout erreicht
• Verwendung eines 4-Byte-Integer-Primärschlüssels für Tabellen, die weiter wachsen können
• Ignorieren des Verhaltens von VACUUM und des Risikos von Bloat

Fall 1. Schema-Inkonsistenz

• In Entwicklungs-/Testumgebungen funktionierte es, in QA/Staging/Produktion jedoch nicht
• Nach der Ursachenanalyse sollte das Problem durch Verbesserungen im Workflow behoben werden

Fall 2. Missbrauch von IF [NOT] EXISTS

• Fehler durch Schema-Inkonsistenzen nicht mit IF NOT EXISTS unterdrücken
• Die eigentliche Ursache des Problems analysieren und beheben

Fall 3. statement_timeout erreicht

• Alle Änderungen vorab mit großen Datenmengen testen, um Probleme frühzeitig zu erkennen

Fall 4. Unbegrenzte Änderungen in großem Umfang

• Werden zu viele Zeilen in einer Transaktion geändert, beeinträchtigt das andere Transaktionen
• Wenn der Checkpointer nicht abgestimmt ist, kann übermäßig viel WAL-Datenvolumen entstehen
• Eine Sättigung der Festplatten-Schreiblast kann zu einem allgemeinen Performance-Einbruch führen
• VACUUM-/Bloat-Probleme können auftreten
• In Batches aufteilen und VACUUM-Management einplanen

Fall 5. Warten innerhalb einer Transaktion nach dem Erwerb eines exklusiven Locks

• Wenn zwischen BEGIN/ALTER TABLE/COMMIT noch andere Arbeit erfolgt, bleibt der Lock lange bestehen
• Nach dem Erwerb eines exklusiven Locks sollte die Transaktion so schnell wie möglich beendet werden

Fall 6. Transaktion mit DDL + großem DML-Anteil

• In der DDL-Phase erworbene Locks bleiben bis in die DML-Phase hinein lange erhalten
• DDL und DML in getrennte Transaktionen/Migrationsschritte aufteilen

Fall 7. Blockierung anderer Sessions durch Warten auf einen exklusiven Lock

• Autovacuum gibt bei DDL im Modus zur Verhinderung von Wraparound nicht nach
• Selbst SELECT wird blockiert, während auf den Lock gewartet wird
• lock_timeout niedrig setzen und eine Retry-Logik implementieren

Fall 8. Hinweise beim Erstellen von Foreign Keys

• Beim Erstellen eines Foreign Keys auf großen Tabellen kann das Scannen der referenzierten Tabelle Zeit kosten
• Den Foreign Key zunächst mit der Option NOT VALID definieren und in einer separaten Transaktion validieren

Fall 9. Hinweise beim Löschen von Foreign Keys

• Da Locks auf zwei Tabellen erforderlich sind, wird eine Retry-Logik mit lock_timeout benötigt

Fall 10. Hinweise beim Hinzufügen von CHECK-Constraints

• Da ein vollständiger Table-Scan erfolgt, einen ähnlichen zweistufigen Ansatz wie bei Foreign Keys verwenden

Fall 11. Hinweise beim Hinzufügen von NOT NULL

• Vor Postgres 11 führt das Hinzufügen von NOT NULL zu einer neuen Spalte zu einem Table-Scan
• Ab Postgres 11 kann dies durch das Hinzufügen einer NOT NULL DEFAULT-Spalte gelöst werden
• Ab Postgres 12 kann NOT NULL durch das Hinzufügen eines CHECK-Constraints gesetzt werden

Fall 12. Hinweise beim Ändern des Datentyps einer Spalte

• Es kann zu einem vollständigen Rewrite der Tabelle kommen
• Ein Ansatz mit neuer Spalte und Datenkopie per Trigger ist erforderlich

Fall 13. Hinweise zu CREATE INDEX

• In OLTP-Umgebungen sollte CREATE INDEX CONCURRENTLY verwendet werden
• Wenn die Erstellung eines Unique Index fehlschlägt, muss der ungültige Index bereinigt werden

Fall 14. Hinweise zu DROP INDEX

• Wegen möglicher Lock-Probleme DROP INDEX CONCURRENTLY verwenden

Fall 15. Hinweise zum Umbenennen von Objekten

• Die Reihenfolge des Deployments anpassen, um Inkonsistenzen zwischen App-Code und DB-Schema zu vermeiden

Fall 16. Hinzufügen einer Spalte mit DEFAULT-Wert

• Vor PG 11 kommt es zu einem vollständigen Rewrite der Tabelle
• Ab PG 11 ist das Hinzufügen einer Spalte mit DEFAULT-Wert schneller geworden

Fall 17. Bereinigung von Resten nach Fehlschlag von CREATE INDEX CONCURRENTLY

• Bei einem Fehlschlag bleibt ein ungültiger Index zurück, der vor einem erneuten Versuch bereinigt werden muss

Fall 18. Verwendung eines 4-Byte-Integer-Primärschlüssels für große Tabellen

• Es sollte int8 verwendet werden. Die meisten Frameworks nutzen bereits int8.

Empfehlungen

• Mit realistischen Datenmengen testen
• Die Haltezeit exklusiver Locks prüfen
• Die Deployment-Automatisierung verbessern
• Von anderen lernen und Wissen teilen

Meinung von GN⁺

Dieser Artikel fasst sehr gut verschiedene Fehler und Vorsichtsmaßnahmen zusammen, die bei echten Änderungen am DB-Schema auftreten können. Besonders häufig werden Probleme im Zusammenhang mit exklusiven Locks erwähnt, was vor allem bei großen Datenbanken noch gravierendere Auswirkungen haben kann.

Auch die konkreten Hinweise zu Themen wie Foreign Keys, NOT NULL und Indizes, die von Entwicklerinnen und Entwicklern leicht übersehen werden, sind gut erklärt. Es dürfte außerdem hilfreich sein, die Verbesserungen zwischen den verschiedenen Postgres-Versionen zu verstehen und gezielt zu nutzen.

Vor allem ist nachvollziehbar, dass gründliche Tests mit realistischen Datenmengen und eine bessere Deployment-Automatisierung der Schlüssel dazu sind, die Risiken von Schemaänderungen zu minimieren. Für Tests und Deployment-Automatisierung kann es auch sinnvoll sein, Tools wie Database Lab Engine einzusetzen.

Es wäre wünschenswert, wenn mehr technische Blogbeiträge solche nützlichen Tipps teilen würden. Je breiter sich dieses Wissen verbreitet, desto stärker dürfte das die Fähigkeiten von Entwicklerinnen und Entwicklern im Umgang mit Datenbanken verbessern.