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

 (postgres.ai)
5 Punkte von GN⁺ 2024-04-29 | 1 Kommentare | Auf WhatsApp teilen
  • Schemamigrationen in Postgres können durch Sperren, Tabellen-Neuschreibungen und Replikationsverzögerungen zu Betriebsstörungen führen und sind daher insbesondere in großen OLTP-Umgebungen riskant
  • Die Risiken konzentrieren sich auf Änderungen, die vollständige Scans und lange Sperren auslösen, etwa das gleichzeitige Hinzufügen von DEFAULT und NOT NULL, das Erstellen von Indizes ohne CONCURRENTLY, das sofortige Löschen von Spalten, unsichere Typänderungen oder das Hinzufügen von Fremdschlüsseln ohne Validierung
  • Seit PostgreSQL 11 sind einige Kosten beim Hinzufügen von Spalten gesunken, aber für Indizes ist weiterhin CREATE INDEX CONCURRENTLY nötig, und bei Fremdschlüsseln sind Verfahren wie erst NOT VALID und dann VALIDATE CONSTRAINT erforderlich, um Auswirkungen im Betrieb zu reduzieren
  • Umfangreiche Änderungen sollten in kleine Batches aufgeteilt werden; außerdem sollten Lesereplikate, Replikationsverzögerung, abhängige Objekte und Verweise auf Spalten durch bestehende Anwendungsinstanzen gemeinsam geprüft werden
  • Mit Daten in Produktionsgröße sollte vorab getestet werden; destruktive Änderungen sollten erst mit mehrstufigen Deployments und einem verifizierten Rollback-Plan durchgeführt werden

Voraussetzungen für Schemamigrationen

  • Mit DB-Migration ist hier nicht ein Wechsel des DBMS gemeint, sondern eine Änderung des DB-Schemas
  • Die betrachteten Änderungen haben drei Eigenschaften
    • Versionsverwaltete Änderungen mit eigener Kennung und automatisiertem Anwendungsprozess
    • Unveränderliche Änderungen, bei denen nach dem Einsatz in Produktion nichts mehr angepasst wird und nur neue Änderungen hinzukommen
    • Inkrementelle Änderungen, bei denen sich das Datenbankschema schrittweise weiterentwickelt
  • Der Fokus liegt auf OLTP-Anwendungsfällen wie mobilen und Web-Anwendungen; Query-Laufzeiten von mehr als 1 Sekunde gelten meist bereits als zu langsam
  • In kleinen Datenbanken mit geringer Aktivität fallen manche Probleme kaum auf, aber bei etwa 10 TiB und einer Last von 10⁴ bis 10⁵ Transaktionen pro Sekunde können die meisten Probleme sichtbar werden
  • Die Database Lab Engine wird mit Thin Clones für Entwicklung und Tests genutzt und kann eine 10-TiB-Datenbank in 10 Sekunden klonen, um Risiken von Schemaänderungen vor dem Deployment zu prüfen
  • Der GitLab Migration Style Guide ist eine nützliche Referenz aus der Erfahrung mit automatisch ausgerollten Postgres-Schemaänderungen in großer Zahl

Spalten hinzufügen und Tabellen-Neuschreibung

  • Das Hinzufügen einer Spalte mit DEFAULT und NOT NULL gleichzeitig ist insbesondere in älteren PostgreSQL-Versionen riskant
    • Vor PostgreSQL 11 war dafür eine vollständige Tabellen-Neuschreibung erforderlich
    • Bei großen Tabellen kann das Stunden oder Tage dauern und währenddessen Schreibsperren verursachen
  • Ein riskantes Beispiel ist folgendes
ALTER TABLE users ADD COLUMN status text DEFAULT 'active' NOT NULL;
  • Sicherer ist ein Vorgehen, bei dem Spaltenanlage, Datenaktualisierung und Constraint getrennt werden
    • Zuerst wird die Spalte ohne NOT NULL hinzugefügt
    • Falls nötig, werden bestehende Zeilen aktualisiert
    • Danach wird die NOT NULL-Constraint gesetzt
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- UPDATE users SET status = 'active' WHERE status IS NULL;

ALTER TABLE users ALTER COLUMN status SET NOT NULL;
  • Ab PostgreSQL 11 erfordert das Hinzufügen einer Spalte mit nichtflüchtigem DEFAULT-Wert keine Tabellen-Neuschreibung mehr

Indexerstellung und Hinzufügen von Fremdschlüsseln

  • Wird ein Index ohne CONCURRENTLY erstellt, nimmt die normale Indexerstellung eine exklusive Sperre auf der Tabelle
    • Bis zum Abschluss der Indexerstellung können alle Schreibzugriffe und ein Teil der Lesezugriffe blockiert werden
  • Ein riskantes Beispiel ist folgendes
CREATE INDEX idx_users_email ON users(email);
  • Im laufenden Betrieb ist CREATE INDEX CONCURRENTLY sicherer
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
  • CONCURRENTLY hat Einschränkungen
    • Es dauert länger, blockiert aber den Tabellenzugriff nicht
    • Es kann nicht innerhalb eines Transaktionsblocks verwendet werden
    • Bei Fehlschlägen kann ein ungültiger Index zurückbleiben, der gelöscht werden muss
  • Wenn auf großen Tabellen direkt eine Fremdschlüssel-Constraint hinzugefügt wird, wird zur Prüfung bestehender Daten die gesamte Tabelle gescannt, was lange Sperren verursachen kann
  • Sicherer ist ein Verfahren, bei dem die Constraint zunächst mit NOT VALID hinzugefügt und dann zu einem Zeitpunkt mit wenig Traffic validiert wird
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user_id;

Spalten löschen und Typen ändern

  • Wird eine Spalte in Produktion sofort gelöscht, kann das zu Anwendungsfehlern führen, wenn Anwendungscode weiterhin auf diese Spalte verweist
  • Das Löschen einer Spalte sollte mehrstufig erfolgen
    • Zuerst wird Anwendungscode ausgerollt, der die Spalte nicht mehr verwendet
    • Dann wartet man, bis alle alten Anwendungsinstanzen ersetzt wurden
    • In einer separaten Migration wird die Spalte gelöscht
  • Das Ändern eines Spaltentyps kann eine Tabellen-Neuschreibung oder Kompatibilitätsprobleme verursachen
    • Das kann zu Downtime, Datenverlust oder Anwendungsfehlern führen
  • Problematische Beispiele sind folgende
ALTER TABLE users ALTER COLUMN id TYPE bigint;
ALTER TABLE users ALTER COLUMN email TYPE varchar(100);
  • Beim Wechsel von integer zu bigint ist ein mehrstufiges Verfahren mit einer neuen Spalte erforderlich
  • Wenn die Länge von varchar reduziert wird, sollten die Daten vorher geprüft und die Notwendigkeit der Änderung hinterfragt werden

Umfangreiche Änderungen, Replikation und abhängige Objekte

  • Migrationen, die zu viele Daten in einer einzigen Transaktion ändern, sollten vermieden werden
    • Sperrkonflikte und Speicherverbrauch steigen
    • Die Wiederherstellung dauert bei Problemen länger
    • Die Replikationsverzögerung kann zunehmen
  • Große Datenmigrationen sollten sicherheitshalber in kleine Batches aufgeteilt werden
  • Auch die Auswirkungen einer Migration auf Lesereplikate und Replikationsverzögerung sollten betrachtet werden
    • Große Migrationen können erhebliche Replikationsverzögerungen verursachen
    • Sie können die Leistung von Lesereplikaten beeinträchtigen
  • Auch Objekte, die von den geänderten Spalten oder Tabellen abhängen, müssen geprüft werden
    • Werden abhängige Objekte wie Views, Funktionen oder Trigger übersehen, kann das zu Folgefehlern oder zusätzlichem manuellem Eingreifen führen

Tests und Rollback-Plan

  • Wenn Migrationen nur mit kleinen Entwicklungsdatensätzen getestet werden, lassen sich die Leistungsmerkmale großer Datensätze nur schwer beurteilen
  • Getestet werden sollte auf Klonen mit Daten in Produktionsgröße; dafür können Werkzeuge wie die Database Lab Engine verwendet werden
  • Gibt es im Problemfall keine Möglichkeit, eine Migration rückgängig zu machen, kann ein Produktionsproblem zu längerer Downtime führen
  • Gerade für destruktive Änderungen ist ein verifizierter Rollback-Plan erforderlich
  • Die Grundlagen sicherer Schemaänderungen sind folgende
    • Auf Daten in Produktionsgröße testen
    • Für riskante Änderungen einen mehrstufigen Ansatz verwenden
    • PostgreSQL-Funktionen wie CONCURRENTLY und NOT VALID nutzen
    • Performance- und Replikationsauswirkungen überwachen
    • Immer einen Rollback-Plan bereithalten

Verwandte Beiträge

1 Kommentare

 
GN⁺ 2024-04-29
Hacker-News-Kommentare

  • Ich mag Postgres wirklich sehr, aber das meiste in diesem Artikel ist vermeidbar und etwas, worauf man achten sollte. Das Schlimmste an Postgres ist meiner Meinung nach jedoch die Rollenverwaltung
    Die Funktionen sind mächtig und sicher großartig, wenn man sie gut nutzt, aber der Prozess, sie tatsächlich zum Laufen zu bringen, fühlt sich wie schwarze Magie an. Überall in der Oberfläche wirkt es wie ein obskurer Zauber, bei dem man nie weiß, ob er wie erwartet funktioniert, und es ist eine schreckliche Art, etwas so Wichtiges zu verwalten
    Auch das Handbuch zu diesem Teil ist dünn und erklärt nur grob, wie es in einem engen Anwendungsfall funktionieren soll. Wenn es nicht wie erwartet läuft, muss man per Versuch und Irrtum herausfinden, was man falsch gemacht hat, und wie der richtige Weg aussieht, bleibt trotzdem unklar. Eine DB mit komplexen Benutzerrechten zu migrieren, ist wirklich mühsam
    Ich habe das Gefühl, ich sollte mir etwa einen Monat Zeit nehmen und ein Cookbook schreiben. Wenn dadurch auch nur eine Person nicht mehr weinend einschläft, wäre es das wert

    • Ich stimme zu, dass PostgreSQL-IAM komplex ist. Ein Grund dafür ist, dass die Objekthierarchie aus drei Ebenen besteht: Database, Schema und Tables, und dass DB-Objektbesitzern implizit Rechte gewährt werden
      Um von einer Tabelle SELECT auszuführen, braucht man CONNECT auf der Database und USAGE auf dem Schema; dem Schema-Eigentümer wird Letzteres implizit gewährt. Außerdem ist SELECT auf der Table nötig, was dem Tabellenbesitzer implizit gewährt wird
      Um Berechtigungen einzusehen, muss man ACL-Einträge im Format grantee=privilege-abbreviation[]/grantor: verstehen. Database-Rechte sieht man mit \l+, Schema-Rechte mit \dn+, Table-Rechte mit \dp+
      Die Liste der Rechte steht hier. Zum Beispiel bedeutet user=arwdDxt/postgres, dass die Rolle postgres dem Benutzer alle Rechte gegeben hat
      Wenn die Spalte grantee eines Objekts leer ist, kann das entweder die Standardrechte des Eigentümers, also alle Rechte, bedeuten oder Rechte für die Rolle PUBLIC, also für alle existierenden Rollen. Ein Beispiel ist =r/postgres
      Mit dem Schema public wird es noch verwirrender. Das Schema hat das Recht CREATE, sodass beim Anlegen einer Tabelle mit demselben Benutzer, der auch die Daten abfragt, standardmäßig Eigentümerrechte gesetzt werden und die Abfrage sofort funktioniert
    • Auch die Dokumentation von postgREST, die sich bei Authentifizierung auf Rollen stützt, wirkt nicht besonders ausführlich: https://postgrest.org/en/v12/explanations/db_authz.html
      Wenn du ernsthaft ein Cookbook zu Postgres-Rollen schreiben und dafür etwas wie Kickstarter starten würdest, wäre ich wahrscheinlich einer der ersten Unterstützer
    • Ich stimme der Aussage zu, dass es sich wie schwarze Magie anfühlt, es zum Laufen zu bringen. Letztes Jahr habe ich einen einfachen postgREST-Server mit Row-Level Security umgesetzt, und der Weg dorthin war ziemlich schwierig
      Aber als es dann einmal lief, wirkte es wirklich magisch, und die zugrunde liegenden Mechanismen waren überraschend einfach
    • So einen Artikel würde ich wahrscheinlich lesen. Bei der Rollenverwaltung wird zu viel geraten, und dadurch bekommen Rollen viel zu oft übermäßige Berechtigungen
    • Bitte schreib das unbedingt. Für Inhalte in diesem Umfang würde ich etwa 20 Dollar gern zahlen

  • Wenn man Schema-Migrationen in der Produktionsumgebung ausführt, sollte man lock_timeout verwenden
    Selbst Änderungen, die harmlos wirken und in Tests fast sofort fertig sind, wie das Löschen einer Tabelle mit Fremdschlüsseln oder das Entfernen eines Fremdschlüssels, können in einer stark ausgelasteten Produktions-DB wegen bestehender Transaktionen oder autovacuum auf Lock-Konflikte stoßen
    Dieses ALTER wartet dann auf den Lock der ersten Transaktion und versucht dabei, einen ACCESS EXCLUSIVE Lock zu bekommen, wodurch alle Queries auf die gesperrte Tabelle blockiert werden
    Wenn man Postgres in größerem Maßstab betreibt, sind solche Konflikte nur eine Frage der Zeit. Setzt man lock_timeout, schlägt die Migration nach Ablauf des Zeitlimits fehl, statt wartend alle anderen Queries zu blockieren

    • statement_timeout schließt auch die Wartezeit auf Locks ein und erlaubt dadurch eine bessere Abschätzung der Auswirkungen auf stark genutzte Tabellen
      Wenn man das Limit auf 5 Sekunden setzt, weiß man, dass die gesamte Unterbrechung höchstens 5 Sekunden dauert, danach läuft die Transaktion weiter. Mit nur lock_timeout hat man nach dem Erhalt des Locks keine Kontrolle darüber, wie lange die Arbeit dauert; je nach gleichzeitiger Last kann es schnell oder langsam gehen
    • Je nach Postgres-Version unterscheidet es sich ziemlich stark, ob bestimmte DML-Queries exklusive Locks nehmen
      Ich frage mich, ob es eine gute Methode gibt, eine Query zu analysieren und herauszufinden, welche Art von Lock sie nehmen wird. Wenn ich mir unsicher war, habe ich bisher immer wieder die Dokumentation gelesen
    • Guter Hinweis. Technisch gesehen hatte ich es aber so verstanden, dass nicht bereits ein ACCESS EXCLUSIVE Lock gehalten wird, während gewartet wird, sondern dass wegen der Lock-Queue gewartet wird
      Das ALTER wartet darauf, dass Locks freigegeben werden, die schwächer als ACCESS EXCLUSIVE sind
    • Das kann dazu führen, dass ALTER niemals ausgeführt wird. Bei genug Traffic auf dieser Tabelle kann das passieren
      Wenn sich die App in so einem Fall erholen kann, halte ich es für das Beste, andere laufende Queries zu beenden, die das ALTER blockieren

  • Ich schaue mehrmals pro Woche in den Fly.io-Leitfaden Safe Migrations in Ecto. Ecto ist der DB-Adapter von Elixir
    Er ist als Referenz sehr nützlich, um schnell zu prüfen, ob Standardmigrationen ausreichen oder ob ein komplexeres Vorgehen nötig ist
    https://fly.io/phoenix-files/safe-ecto-migrations/

  • Das Überraschendste an Postgres-Indizes für mich als Anfänger war, dass ein UNIQUE-Index durch zusätzliche Sperren die Ergebnisse paralleler Queries beeinflussen kann
    Queries wie INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo); können bei gleichzeitiger Ausführung im Standardmodus doppelte bar-Werte einfügen. Der Grund ist, dass eine Transaktion den neuen Maximalwert, den eine andere Transaktion erzeugt hat, möglicherweise nicht sieht
    Man könnte denken, dass beim Hinzufügen eines UNIQUE-Index die „verlierende“ Transaktion einen Constraint-Fehler bekommt, tatsächlich würden aber beide Transaktionen erfolgreich sein und die Race Condition verschwände

    • Das stimmt nicht. Die Subtransaktion, die den Index-Wettlauf verliert, wird abgebrochen
      =# INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);
      ERROR: duplicate key value violates unique constraint "foo_bar_idx"
      DETAIL: Key (bar)=(2) already exists.
    • Falls gemeint ist, dass mit einem UNIQUE-Index beide Inserts erfolgreich sind und am Ende trotzdem duplizierte Werte eingefügt werden, dann wäre das, falls es stimmt, ein Bug
    • Wenn es keine Verwechslung ist, kann man das unterbrechungsfrei machen, indem man einen normalen Index mit CONCURRENTLY erstellt und einen nicht validierten UNIQUE-Constraint anlegt
      Dieser Constraint gilt dann nur für neue INSERT/UPDATE. Führt man später VALIDATE für den Constraint aus, wird er zu einem vollständigen UNIQUE-Constraint
    • Wenn sich das überraschend anfühlt, liegt das wohl daran, dass man zu viel imperativen Sprachen ausgesetzt war
      Ich stimme zu, dass das häufig vorkommt, aber das Problem liegt eher in der Softwareentwicklung allgemein als bei Postgres
    • Auf welchem Isolation Level passiert das?

  • Wegen solcher Fallstricke habe ich Reshape [0] gebaut, mit dem Ziel, unterbrechungsfreie Schema-Migrationen zu automatisieren
    Ich kann nicht behaupten, dass es alle Probleme vermeidet, aber ich baue ein neues Produkt mit genau diesem Ziel. Wenn dich dieser Bereich interessiert, besonders Postgres, würde ich mich über eine Nachricht freuen: fabian@reshapedb.com
    [0] https://github.com/fabianlindfors/reshape

    • Könnte es vielleicht auch mit crdb funktionieren?

  • Ein weiterer häufiger Fehler ist, eine Tabelle zu duplizieren und dabei die Indizes zu vergessen
    CREATE TABLE SELECT * FROM WHERE <> funktioniert nicht auf diese Weise. Leute machen das oft, wenn sie eine Backup-Tabelle anlegen oder Massenlöschungen durchführen wollen

    • Wenn es darum geht, eine Backup-Tabelle zu erstellen, also wenn man eine komplexe und schwer vorhersehbare Operation durchführen will, die sofort auf unvorhersehbare Weise schiefgehen kann, dann kümmere ich mich überhaupt nicht um Indizes oder Constraints
      Man will einfach eine sofort verfügbare Kopie der Daten, die man vermutlich nie verwenden wird, damit man nicht auf DB-Backups und Wiederherstellung aus dem WAL angewiesen ist. Indizes zu erstellen wäre Verschwendung von Serverzeit und Speicherplatz
      Wenn etwas schiefläuft oder man sie wirklich braucht, kann man diese Indizes später immer noch anlegen
    • Kannst du dann auch sagen, was stattdessen der richtige Weg wäre?

  • Der Abschnitt „Case 2. Misusing IF [NOT] EXISTS“ hat kein gutes Beispiel für Missbrauch geliefert
    Und tatsächlich verwendet man es genau so korrekt. Es ist sauber, einfach und hat keine versteckten Fallstricke. Wenn es nur ein paar Tabellen gibt, sind Schema-Migrations-Tools unnötiger Overhead

    • Der Fallstrick ist einfach: „Probleme mit Logik zu überdecken und damit das Risiko eines inkonsistenten Zustands zu erhöhen“
      Ein Pflaster auf schlechte Daten zu kleben löst das Problem nicht, sondern verdeckt es nur. Je nach Art des Problems kann es später auf unerwartete Weise und zum denkbar schlechtesten Zeitpunkt hochgehen
      In diesem Fall sind die „schlechten Daten“ Tabellen, Spalten oder Views, die vorhanden sein oder fehlen sollten, aber stattdessen im falschen Zustand existieren. Warum existiert eine Tabelle noch, die eigentlich nicht mehr da sein sollte? Ist das Löschen fehlgeschlagen? Entspricht das Schema einer bestehenden Tabelle überhaupt dem erwarteten Stand? Wurde dieselbe Migration versehentlich zweimal ausgeführt?
      Nach jeder Migration sollte das Schema exakt im richtigen Zustand sein. Wenn eine Migration IF [NOT] EXISTS enthält, bedeutet das, dass das Schema nach einer früheren Migration nicht in einem exakt korrekten Zustand geblieben ist. Sich beim Zustand des Schemas nicht sicher zu sein, ist schlecht
    • Ich finde, der Artikel hat den Missbrauch ziemlich gut erklärt. Der Kernpunkt ist, dass Schema-Änderungen über separate Pfade ein Prozess- und Workflow-Problem sind und direkt gelöst werden müssen
      Was macht man, wenn die Spalten einer bereits existierenden Tabelle nicht zu dem passen, was die Migration anlegen will? IF EXISTS lässt die Migration erfolgreich durchlaufen, aber das Schema bleibt in einem schlechten Zustand. In solchen Fällen ist es besser, wenn die Migration schnell fehlschlägt

  • Eine kleine Anmerkung zu dem Teil über int4 als surrogate primary key
    Ist nicht eher die Indexgröße entscheidend als die Tabellengröße? Die Tabellengröße hat ohnehin schon einen 23-Byte-Header und Alignment-Padding, daher machen 4 Byte Unterschied dort nicht viel aus. Wenn man aber mehr Indizes im Speicher halten kann, kann das ein Vorteil sein. Ein Index-Entry hat einen 8-Byte-Header
    Außerdem liegen die im Beispiel genannten 1 Milliarde Zeilen unangenehm nah am Maximalwert von int4
    Trotzdem ist der Artikel großartig

    • Stimmt. Es gibt die Indexgröße und die Größe auf der Platte. Postgres packt Tabellenzeilen auf der Platte dicht, aber im RAM nicht
      Bedeutet das dann, dass eine 8-KB-Seite auf der Platte im RAM größer als 8 KB sein kann?
      Es scheint nur Auswirkungen auf den Working Memory für Tabellenzeilendaten zu haben. Das ist weiterhin wichtig. Vor allem, weil Postgres bei Zeilen in zufälliger Reihenfolge schrecklich schlecht bei der Lokalität von Range-Queries ist. Ich würde aber nicht sagen, dass das die entscheidende Einsicht ist

  • Ich bin ein Entwickler, der bei DB-bezogenen Problemen meist ziemlich gut abgeschirmt war. Innerhalb von Django weiß ich, wie man Migrationen erstellt, Modelltabellen anlegt und mit dem ORM Queries schreibt, aber vieles von dem, was darunter passiert, fühlt sich wie schwarze Magie an
    Jetzt, wo ich eine Firma gründe, macht es mir Sorgen, solchen Problemen zu begegnen und sie allein lösen zu müssen. Wie sollte ich das Lernen angehen, um zu verstehen, was man in Entwicklungsumgebungen tun sollte?

    • Einfach scheitern und aus den Fehlern lernen. Oder Entwickler einstellen und gemeinsam scheitern und gemeinsam lernen

  • Ich mag Postgres, aber ich hasse wirklich, dass es keine eingebaute Methode für Batch-Updates/-Deletes gibt
    Das ist der nervigste Teil, und ich muss meinen Batcher fast jeden Monat neu schreiben, sobald ich wieder gegen diese Wand laufe