Zero-Downtime-Postgres-Migrationen: Ein vollständiger Leitfaden

Die Migration, die die Produktion lahmlegt, ist fast nie diejenige, vor der alle Angst hatten. Es ist selten ein DROP TABLE — solche Änderungen werden von allen sorgfältig geprüft. Es ist die „harmlos aussehende“ Änderung: das Hinzufügen einer NOT NULL-Spalte mit Standardwert oder das Erstellen eines Indexes auf einer stark genutzten Tabelle während der Geschäftszeiten, ausgeführt von jemandem, der vernünftigerweise annahm, dass eine automatisch vom Migrationstool erzeugte Migration sicher ausführbar sein müsse.

Die unangenehme Wahrheit ist: „sicher“ bedeutet aus Sicht eines Migrationstools meist „syntaktisch korrekt“, nicht „wird die Tabelle nicht blockieren“. Die DDL-Operationen von Postgres erwerben echte Sperren, und mehrere der häufigsten Schemaänderungen erwerben eine Sperre — ACCESS EXCLUSIVE — die jedes Lesen und Schreiben auf die Tabelle blockiert, und zwar so lange, wie die Operation dauert. Auf einer kleinen Tabelle sind das Millisekunden, unsichtbar. Auf einer Tabelle mit zig Millionen Zeilen und konstantem Traffic ist das ein mehrminütiger Produktionsausfall, verursacht durch eine Migration, die wie ein Ein-Zeilen-Diff aussah.

Sie werden lernen:

• Welche gängigen Schemaänderungen eine tabellenblockierende Sperre auslösen und welche in modernen Postgres-Versionen standardmäßig bereits sicher sind
• Wie man eine Spalte mit Standardwert hinzufügt, ohne die gesamte Tabelle neu zu schreiben
• Das korrekte Drei-Schritte-Muster zum Hinzufügen einer NOT NULL-Einschränkung ohne Schreibvorgänge zu blockieren
• Wie CREATE INDEX CONCURRENTLY die Standard-Sperre beim Indexaufbau vermeidet und worin sein tatsächlicher Fehlermodus besteht
• Wie man pg_locks und pg_stat_activity liest, um vor dem Ausführen genau zu sehen, was eine Migration blockieren wird
• Wie man eine Alembic- oder Django-Migration so strukturiert, dass dieses Muster standardmäßig sicher angewendet wird

Inhaltsverzeichnis

1. Warum manche DDL die ganze Tabelle blockiert
2. Spalten hinzufügen ohne zu sperren
3. NOT NULL hinzufügen, ohne Schreibvorgänge zu blockieren
4. Indizes mit CONCURRENTLY erstellen
5. Große Tabellen sicher backfillen
6. pg_locks lesen, bevor Sie eine Migration ausführen
7. Sichere Migrationen in Alembic und Django
8. Häufige Fallstricke

Warum manche DDL die ganze Tabelle blockiert

Jedes ALTER TABLE in Postgres erwirbt für die Dauer der Operation eine Sperre auf der Tabelle, und die Sperrstufe bestimmt, was sonst noch gleichzeitig passieren kann. Die strengste, ACCESS EXCLUSIVE, blockiert buchstäblich alles andere — Lesezugriffe, Schreibzugriffe, sogar andere DDL — bis sie freigegeben wird. Ob ein bestimmtes ALTER TABLE diese strengste Sperre benötigt oder mit etwas deutlich Leichterem auskommt, hängt konkret davon ab, ob Postgres die bereits vorhandenen Zeilen der Tabelle neu schreiben muss, um die Änderung durchzuführen.

Operationen, die nur Metadaten betreffen — das Hinzufügen einer nullable Spalte, das Hinzufügen einer als NOT VALID markierten Check-Constraint, das Löschen einer Spalte — erhalten ebenfalls ACCESS EXCLUSIVE, aber nur sehr kurz, weil keine Daten neu geschrieben werden müssen; die Sperre wird nur so lange gehalten, wie es dauert, den Katalog zu aktualisieren. Operationen, bei denen jede vorhandene Zeile neu geschrieben werden muss — das Ändern eines Spaltentyps auf eine Weise, die nicht binär kompatibel ist, das Hinzufügen einer Spalte mit einem volatilen Standardwert in älteren Postgres-Versionen, das Hinzufügen bestimmter Constraint-Arten ohne NOT VALID — halten dieselbe ACCESS EXCLUSIVE-Sperre so lange, wie das Umschreiben dauert. Diese Dauer skaliert direkt mit der Tabellengröße und kann auf einer großen Tabelle unter echter Last Minuten oder länger betragen.

Die praktische Regel lautet: Jede DDL-Anweisung, deren Dauer mit der Zeilenanzahl skaliert, ist auf einer stark genutzten Tabelle ein echtes Risiko, und jede Anweisung, deren Dauer unabhängig von der Zeilenanzahl konstant ist (nur Metadatenänderungen), ist fast immer sicher. Zu wissen, in welche Kategorie ein bestimmtes ALTER TABLE fällt, bevor man es ausführt, ist die eigentliche Fähigkeit, um die es in diesem Beitrag geht.

Eine kurze Referenz für die am häufigsten vorkommenden Operationen:

Das Muster hinter jedem „nein“ in den letzten beiden Spalten ist dasselbe: Postgres bietet für fast jede Operation, deren Dauer mit der Tabellengröße skaliert, einen leichteren Sperrmodus an, und der einzige Preis für dessen Nutzung ist, dass die Operation selbst in Echtzeit länger bis zum Abschluss braucht — ein Kompromiss, der sich auf einer Tabelle, die aktiv von anderen genutzt wird, fast immer lohnt.

Spalten hinzufügen ohne zu sperren

Das Hinzufügen einer nullable Spalte ohne Standardwert war in Postgres schon immer eine schnelle Operation nur auf Metadatenebene — diese war eigentlich nie gefährlich:

ALTER TABLE orders ADD COLUMN discount_code TEXT;

Das Hinzufügen einer Spalte mit einem Standardwert war früher die klassische Falle: Vor Postgres 11 wurden dabei alle vorhandenen Zeilen neu geschrieben, um den Standardwert einzutragen, wodurch während des gesamten Rewrites ACCESS EXCLUSIVE gehalten wurde. Seit Postgres 11 wird ein nicht-volatiler Standardwert (ein Literal, nicht now() oder random()) rein als Metadatum gespeichert und für alte Zeilen beim Lesen verzögert angewendet — kein Tabellen-Rewrite, keine lange Sperre, unabhängig von der Tabellengröße:

-- Safe and fast on Postgres 11+, any table size
ALTER TABLE orders ADD COLUMN status TEXT DEFAULT 'pending';

Die Ausnahme, die weiterhin wichtig ist: ein volatiler Standardwert — DEFAULT now(), DEFAULT gen_random_uuid() oder alles, was pro Zeile einen tatsächlich anderen Wert berechnen muss — erzwingt selbst in modernen Postgres-Versionen weiterhin einen vollständigen Tabellen-Rewrite, weil ein verzögert angewendeter Standardwert nur funktioniert, wenn jede Zeile exakt denselben gespeicherten Wert teilen kann. Für alles, was einen pro Zeile berechneten Wert benötigt, fügen Sie die Spalte nullable und ohne Default hinzu, führen das Backfill in Batches durch (siehe unten) und setzen dann den Default nur noch für zukünftige Zeilen.

NOT NULL hinzufügen, ohne Schreibvorgänge zu blockieren

Ein einfaches ALTER TABLE orders ALTER COLUMN status SET NOT NULL muss die gesamte Tabelle scannen, um zu prüfen, dass keine vorhandene Zeile gegen die neue Constraint verstößt, und hält während dieses gesamten Scans ACCESS EXCLUSIVE. Auf einer großen, stark genutzten Tabelle ist genau ein solcher Scan, der mit echtem Traffic konkurriert, die Art von Migration, die einen Ausfall verursacht. Das sichere Muster teilt dies in drei Schritte auf, die niemals lange eine exklusive Sperre halten:

-- Step 1: add a CHECK constraint as NOT VALID — instant, metadata-only
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
  CHECK (status IS NOT NULL) NOT VALID;

-- Step 2: validate it separately — takes a lock, but only ROW SHARE,
-- which permits concurrent reads and writes
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;

-- Step 3 (Postgres 12+): now SET NOT NULL is instant, because
-- the planner can prove it from the already-validated constraint
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
DROP CONSTRAINT orders_status_not_null;  -- optional cleanup

Schritt 1 ist sofort abgeschlossen, weil NOT VALID Postgres anweist, die Constraint nur für Zeilen zu erzwingen, die ab diesem Zeitpunkt geschrieben werden, ohne bestehende Zeilen zu prüfen. Schritt 2 führt die teure Prüfung der ganzen Tabelle durch, aber unter einer viel leichteren Sperre als ACCESS EXCLUSIVE — gleichzeitige Lese- und Schreibvorgänge laufen normal weiter, zum Preis von etwas zusätzlicher I/O-Konkurrenz während der Validierung selbst. Schritt 3 ist unter Postgres 12 und neuer tatsächlich eine reine Metadatenoperation, weil der Planner anhand der bereits validierten Constraint beweisen kann, dass die Spalte bereits keine NULL-Werte mehr enthält, ohne einen zweiten Tabellenscan. Dieses Drei-Schritte-Muster ist der einzelne wertvollste Trick in diesem gesamten Beitrag, und es lässt sich auf jede CHECK- oder Foreign-Key-Constraint verallgemeinern, nicht nur auf NOT NULL.

Indizes mit CONCURRENTLY erstellen

Ein normales CREATE INDEX nimmt eine SHARE-Sperre, die Schreibzugriffe (wenn auch nicht Lesezugriffe) auf die Tabelle während des gesamten Aufbaus blockiert — auf einer großen Tabelle kann das Minuten blockierter INSERT-/UPDATE-/DELETE-Anweisungen bedeuten, was für jeden schreiblastigen Dienst funktional einem Ausfall entspricht. CREATE INDEX CONCURRENTLY vermeidet dies, indem der Index in mehreren Durchläufen aufgebaut wird und die Sperre dazwischen freigegeben wird, sodass Schreibvorgänge die ganze Zeit weiterlaufen können:

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

Die tatsächlichen Kosten sollte man genau kennen: Der Aufbau dauert spürbar länger (oft zwei- bis dreimal so lange wie ein normales CREATE INDEX auf derselben Tabelle) und, entscheidend, er kann nicht innerhalb eines Transaktionsblocks laufen — die meisten Migrations-Frameworks verpacken jede Migration standardmäßig in eine Transaktion. CONCURRENTLY korrekt auszuführen bedeutet daher meist, dem Framework ausdrücklich mitzuteilen, das transaktionale Wrapping für genau diese Migration zu überspringen.

Der andere echte Fehlermodus: Wenn der Aufbau unterbrochen wird — Verbindungsabbruch, abgebrochene Anweisung, Serverneustart — kann ein ungültiger Index zurückbleiben, sichtbar in \d tablename als INVALID, der weiterhin Speicherplatz verbraucht und bei jedem Schreibvorgang weiterhin gepflegt wird, aber nie für Abfragen verwendet wird. Postgres bereinigt das nicht automatisch:

-- Check for invalid indexes across the database
SELECT indexrelid::regclass, indrelid::regclass
FROM pg_index WHERE NOT indisvalid;

-- Drop and rebuild
DROP INDEX CONCURRENTLY idx_orders_status;
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

DROP INDEX CONCURRENTLY existiert genau für diesen Bereinigungsfall und vermeidet, wie die Create-Variante, die blockierende Sperre, die ein normales DROP INDEX nehmen würde. Sobald der Index sicher erstellt ist, bestätigen Sie mit EXPLAIN ANALYZE, dass er tatsächlich so verwendet wird, wie Sie es erwarten — der Leitfaden zum Lesen von Postgres-Abfrageplänen zeigt genau, wie man überprüft, dass ein neuer Index die Form des Plans verändert hat, statt einfach anzunehmen, dass eine schnellere Abfrage dies schon beweise.

Große Tabellen sicher backfillen

Ein einzelnes UPDATE orders SET status = 'pending' WHERE status IS NULL über Millionen von Zeilen ist logisch richtig, aber operativ falsch — es ist eine riesige einzelne Transaktion, hält Zeilensperren auf jeder betroffenen Zeile bis zum Commit, erzeugt einen Schub an WAL-Traffic und riskiert eine lang laufende Transaktion, die Autovacuum daran hindert, an anderer Stelle in der Datenbank tote Tupel zu bereinigen, und zwar über ihre gesamte Laufzeit. Backfills gehören stattdessen in kleine, committete Batches:

import time

BATCH_SIZE = 5000

while True:
    with connection.cursor() as cursor:
        cursor.execute("""
            UPDATE orders SET status = 'pending'
            WHERE id IN (
                SELECT id FROM orders
                WHERE status IS NULL
                LIMIT %s
                FOR UPDATE SKIP LOCKED
            )
        """, [BATCH_SIZE])
        rows_updated = cursor.rowcount
    if rows_updated == 0:
        break
    time.sleep(0.1)  # let replication and autovacuum keep up

FOR UPDATE SKIP LOCKED bedeutet, dass gleichzeitige Backfill-Batches (oder laufende Anwendungsschreibvorgänge, die dieselben Zeilen betreffen) sich nicht gegenseitig blockieren — ein Batch überspringt einfach Zeilen, die bereits von einem anderen Prozess gesperrt sind, und nimmt sie in einem späteren Durchlauf mit. Das kurze sleep zwischen den Batches ist absichtlich gewählt: Es gibt Replikationsverzögerung, Autovacuum und Connection-Pool-Konkurrenz Raum, sich zwischen den Schüben zu erholen, statt die Tabelle kontinuierlich zu hämmern, wie es eine riesige Transaktion im Grunde ebenfalls tun würde, nur verteilt statt konzentriert.

pg_locks lesen, bevor Sie eine Migration ausführen

Eine überraschend große Zahl von Vorfällen rund um Sperren beim Hinzufügen von Spalten in Postgres lässt sich darauf zurückführen, dass niemand geprüft hat, was vor Beginn der Migration bereits auf der Tabelle lief — eine lang laufende Reporting-Abfrage oder ein unabhängiger Batch-Job hält eine Sperre, hinter der sich die Migration dann einreiht, wodurch aus einer eigentlich sofortigen Metadatenänderung ein minutenlanges Warten wird, wobei sich alles andere wiederum dahinter aufstaut. Statt zu raten, ob eine Migration sicher ist, stellt Postgres genau offen dar, was gerade Sperren hält und worauf gewartet wird:

SELECT
    pg_locks.pid,
    pg_locks.mode,
    pg_locks.granted,
    pg_stat_activity.query,
    pg_stat_activity.state
FROM pg_locks
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE pg_locks.relation = 'orders'::regclass;

Zeilen mit granted = false sind diejenigen, die tatsächlich festhängen und warten — das ist das Live-Signal dafür, dass gerade etwas blockiert ist, nicht nur hypothetisch. Diese Abfrage in einer zweiten Session unmittelbar nach dem Start einer Migration auszuführen, während sie noch läuft, ist der schnellste Weg, in Echtzeit zu bestätigen, ob sie die erwartete Sperrstufe oder eine strengere erhalten hat und ob sich noch etwas anderes dahinter aufstaut. Kombinieren Sie dies mit pg_stat_activity.query_start, um zu sehen, wie lange eine blockierende Anweisung bereits läuft — das ist oft der entscheidende Faktor dafür, ob man eine Migration fertig laufen lässt oder sie abbricht, bevor sie echten Schaden verursacht. pg_blocking_pids(pid) sollte man ebenfalls kennen — übergeben Sie ihm die PID einer festhängenden Abfrage, und es liefert genau die anderen Sessions zurück, die sie blockieren. Damit wird aus „irgendetwas blockiert meine Migration“ eine konkrete, handhabbare PID zur Untersuchung oder Beendigung statt bloß einer Vermutung.

Für eine wirklich riskante Migration auf einer großen Produktionstabelle lohnt es sich, für die Session, die sie ausführt, ein konservatives lock_timeout zu setzen, damit eine Migration, die ihre Sperre nicht schnell bekommt, laut und sofort fehlschlägt, statt sich still hinter anderem Traffic einzureihen und dann ihrerseits alles zu blockieren, was danach ansteht:

SET lock_timeout = '2s';
ALTER TABLE orders ADD CONSTRAINT ...;

Eine Migration, die schnell mit einem klaren lock not available-Fehler fehlschlägt, ist einer Migration, die minutenlang in der Warteschlange sitzt, während sich jede andere Abfrage auf dieser Tabelle ebenfalls dahinter aufstaut, bei Weitem vorzuziehen — eine fehlgeschlagene Migration kann einfach erneut versucht werden; eine Warteschlange blockierter Produktionsabfragen ist bereits ein Incident, sobald ihn irgendjemand bemerkt.

Sichere Migrationen in Alembic und Django

Alembic wendet diese Muster nicht automatisch an — ein einfaches op.create_index(...) in einer automatisch generierten Migration erzeugt standardmäßig ein normales, blockierendes CREATE INDEX. Eine sichere Alembic-Migration für einen Index zu schreiben bedeutet, sowohl postgresql_concurrently=True hinzuzufügen als auch die Migration ausdrücklich als nicht-transaktional zu markieren:

from alembic import op

def upgrade():
    op.execute("COMMIT")  # end the implicit transaction Alembic opened
    op.create_index(
        "idx_orders_status", "orders", ["status"],
        postgresql_concurrently=True,
    )

Djangos Migrations-Framework hat für genau diesen Fall ein eigenes, gezielt dafür vorgesehenes Flag — atomic = False auf der Migrationsklasse, kombiniert mit AddIndexConcurrently aus django.contrib.postgres.operations:

from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models

class Migration(migrations.Migration):
    atomic = False

    operations = [
        AddIndexConcurrently(
            "order",
            models.Index(fields=["status"], name="idx_orders_status"),
        ),
    ]

Beide Frameworks unterstützen auch das oben erläuterte Constraint-Splitting-Muster als drei ausdrückliche, separate Migrationsschritte statt als ein einziges automatisch generiertes SET NOT NULL — teilen Sie die Migration in Ihren Migrationsdateien absichtlich in die Sequenz NOT VALID / VALIDATE / SET NOT NULL auf, statt sich bei einer NOT NULL-Änderung auf einer bestehenden, bereits befüllten Tabelle auf die standardmäßige Ein-Schritt-Ausgabe eines der beiden Tools zu verlassen.

Häufige Fallstricke

Fehler: Einer automatisch generierten Migration auf einer großen bestehenden Tabelle vertrauen, ohne zu lesen, was sie tatsächlich macht. Migrationstools erzeugen korrektes SQL, aber nicht zwingend sicheres SQL für eine Tabelle, die bereits Produktions-Traffic trägt. Lösung: Lesen Sie jedes automatisch generierte ALTER TABLE unter der Frage „Schreibt das die Tabelle neu?“ bevor Sie es in der Produktion ausführen.

Fehler: CREATE INDEX CONCURRENTLY innerhalb eines Transaktionsblocks ausführen. Postgres lehnt das direkt ab — es kann nicht gleichzeitig innerhalb einer Transaktion laufen. Lösung: Deaktivieren Sie das transaktionale Wrapping für diesen Migrationsschritt explizit in dem Framework, das Sie verwenden.

Fehler: Einen ungültigen Index nach einem unterbrochenen gleichzeitigen Aufbau zurücklassen. Er kostet unbemerkt Speicherplatz und Schreib-Overhead, ohne irgendetwas zur Abfrageleistung beizutragen. Lösung: Prüfen Sie regelmäßig pg_index.indisvalid und bereinigen Sie alle gefundenen ungültigen Indizes.

Fehler: Millionen von Zeilen in einer einzigen Transaktion backfillen. Das hält Sperren und erzeugt WAL-Druck über die gesamte Dauer der Transaktion und blockiert in der Zwischenzeit Autovacuum. Lösung: Führen Sie das Backfill mit LIMIT und FOR UPDATE SKIP LOCKED in Batches durch, mit einer kurzen Pause zwischen den Batches.

Fehler: Eine Spalte mit volatilem Standardwert hinzuzufügen und anzunehmen, dass die Fast-Default-Optimierung von Postgres 11 dies abdeckt. DEFAULT now() oder Ähnliches erzwingt weiterhin einen vollständigen Tabellen-Rewrite, anders als ein einfaches Literal als Default. Lösung: Fügen Sie die Spalte nullable hinzu, führen Sie das Backfill in Batches durch und wenden Sie den Standardwert anschließend nur auf zukünftige Zeilen an.

Zum Abschluss

Zero-Downtime-Migrationen sind keine eigene Disziplin getrennt von gewöhnlichen Schemaänderungen — sie sind gewöhnliche Schemaänderungen mit einer zusätzlichen Gewohnheit: vor dem Ausführen zu wissen, ob die Dauer der Operation mit der Tabellengröße skaliert, und stattdessen das Muster zu wählen (Fast Default, NOT VALID/VALIDATE, CONCURRENTLY, Backfill in Batches), das diese Dauer konstant hält. Jedes Muster in diesem Beitrag existiert, weil Postgres Ihnen bereits einen leichtergewichtigen Weg für fast jede gefährlich aussehende Änderung bietet — die Fähigkeit besteht darin, zu wissen, dass es diesen Weg gibt, und auf jeder Tabelle, die zu groß oder zu stark genutzt ist, um die naive Variante zu riskieren, standardmäßig danach zu greifen.

Bevor Ihre nächste Migration eine Produktionstabelle berührt: Wissen Sie, welche Sperre sie gleich nehmen wird, oder erfahren Sie es erst durch einen Incident?