Schema-Migrationen auf großen Live-Tabellen sind eines der riskantesten Themen in PostgreSQL: ein falsches ALTER TABLE kann minutenlange Downtime erzeugen, weil die ganze Tabelle mit ACCESS EXCLUSIVE Lock blockiert wird. Hier die Pattern-Sammlung für sichere Migrationen — Lock-Levels, NOT VALID, CONCURRENTLY und der zweistufige Weg.
Lock-Levels verstehen
Postgres kennt acht Lock-Levels auf Tabellen-Ebene. Drei sind hier relevant:
| Lock-Level | Was es blockiert | Wann |
|---|---|---|
ACCESS SHARE | nichts | normales SELECT |
ROW EXCLUSIVE | DDL | normales INSERT/UPDATE/DELETE |
SHARE UPDATE EXCLUSIVE | andere DDL | CREATE INDEX CONCURRENTLY, ANALYZE |
ACCESS EXCLUSIVE | alles | viele ALTER TABLE Varianten |
ACCESS EXCLUSIVE heißt: jede laufende Query muss durch sein, dann können neue Queries erst nach dem ALTER weiter. Bei langen Queries (Reports, ETL) wird der ALTER warten, und in der Zwischenzeit blockiert er neue Queries → Cascade-Stop.
Das eigentliche Risiko ist meist nicht der ALTER selbst, sondern: Lock-Wartung blockiert alle nachfolgenden Connections in der Connection-Queue.
Schnelle ALTERs (kurzer Lock)
Diese sind typischerweise instant — Lock zwar ACCESS EXCLUSIVE, aber nur für Millisekunden:
-- Spalte hinzufügen ohne Default oder mit konstantem Default (PG 11+)
ALTER TABLE orders ADD COLUMN priority int;
ALTER TABLE orders ADD COLUMN priority int NOT NULL DEFAULT 0;
-- Spalte umbenennen
ALTER TABLE orders RENAME COLUMN total TO amount;
-- Constraint mit NOT VALID anlegen
ALTER TABLE orders ADD CONSTRAINT chk_positive
CHECK (amount > 0) NOT VALID;
-- Default ändern (für FUTURE Inserts)
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
-- Spalte droppen
ALTER TABLE orders DROP COLUMN priority;Diese darfst du fast bedenkenlos machen — Lock-Dauer kürzer als ein durchschnittlicher User-Klick.
Gefährliche ALTERs (Tabellen-Scan)
Diese Operationen scannen die ganze Tabelle mit ACCESS EXCLUSIVE Lock — bei Millionen Zeilen mehrere Minuten:
-- VOLATILE-Default → Tabellen-Scan, Wert pro Zeile schreiben
ALTER TABLE orders ADD COLUMN created_at timestamptz DEFAULT now();
-- NOT NULL nachträglich → Tabellen-Scan zur Validierung
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
-- Type-Change → Tabellen-Rewrite
ALTER TABLE orders ALTER COLUMN total TYPE numeric(20,2);
-- Constraint VALIDATE auf Bestandsdaten (mind. SHARE-Lock)
ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);Diese sicher zu machen ist der Hauptinhalt dieses Artikels.
Pattern: Constraint mit NOT VALID
Für CHECK- und FOREIGN-KEY-Constraints:
-- 1. Constraint anlegen, ohne Bestand zu prüfen (instant)
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers(id)
NOT VALID;
-- 2. Bestand sauber machen (falls nötig)
UPDATE orders SET customer_id = ... WHERE customer_id NOT IN (SELECT id FROM customers);
-- 3. Validate (kein ACCESS EXCLUSIVE Lock)
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;Schritt 3 nutzt SHARE UPDATE EXCLUSIVE — andere Reader und Writer sind nicht blockiert.
Wichtig: ab Schritt 1 werden neue INSERTs/UPDATEs sofort gegen den Constraint geprüft. Der „NOT VALID" bezieht sich nur auf den Bestand.
Pattern: NOT NULL nachträglich
-- 1. CHECK mit NOT VALID
ALTER TABLE orders
ADD CONSTRAINT orders_customer_id_chk
CHECK (customer_id IS NOT NULL) NOT VALID;
-- 2. Bestand updaten
UPDATE orders SET customer_id = ... WHERE customer_id IS NULL;
-- 3. Constraint validieren (schwächerer Lock)
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_chk;
-- 4. NOT NULL setzen — instant! Postgres erkennt den CHECK
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
-- 5. Aufräumen
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_chk;Der Trick: PG 12+ erkennt einen validierten CHECK (col IS NOT NULL) und überspringt beim SET NOT NULL den Tabellen-Scan. Vorher war Schritt 4 selbst ein Vollscan.
Pattern: Index CONCURRENTLY
CREATE INDEX CONCURRENTLY orders_customer_id_idx
ON orders (customer_id);CONCURRENTLY lässt Reader und Writer weiter laufen — Lock-Level SHARE UPDATE EXCLUSIVE. Dauert länger als ein normaler Index-Build, aber kein Service-Stop.
Caveats:
- Nicht in einer Transaktion erlaubt
- Falls fehlgeschlagen: Index in
INVALID-Status — mitDROP INDEXlöschen, dann erneut versuchen - Mit
psqlaußerhalb vonBEGIN/COMMITausführen
Mehr im Artikel Index CONCURRENTLY (Übersicht).
Pattern: Type-Change ohne Rewrite
Type-Änderungen sind tricky. Manche sind kostenlos (binary-kompatibel), andere brauchen Tabellen-Rewrite.
-- varchar(20) → text: kostenlos (text ist Variable-Lange)
ALTER TABLE orders ALTER COLUMN status TYPE text;
-- varchar(20) → varchar(50): kostenlos (Erweitern, kein Rewrite)
ALTER TABLE orders ALTER COLUMN status TYPE varchar(50);
-- timestamp → timestamptz: NICHT kostenlos (Wert-Konvertierung nötig)Bei nicht-binary-kompatiblen Type-Changes ist der safer Weg:
-- 1. Neue Spalte hinzufügen (instant)
ALTER TABLE orders ADD COLUMN total_new numeric(20,2);
-- 2. In Batches kopieren
UPDATE orders SET total_new = total WHERE id BETWEEN 1 AND 100000;
UPDATE orders SET total_new = total WHERE id BETWEEN 100001 AND 200000;
-- ...
-- 3. App schreibt ab jetzt auf BEIDE Spalten (deploy)
-- 4. Letzten Drift abfangen
UPDATE orders SET total_new = total WHERE total_new IS DISTINCT FROM total;
-- 5. App schaltet auf neue Spalte um (deploy)
-- 6. Alte Spalte droppen
ALTER TABLE orders DROP COLUMN total;
ALTER TABLE orders RENAME COLUMN total_new TO total;Aufwendig, aber vermeidet stundenlange Locks.
lock_timeout als Sicherheits-Netz
Auch bei sicheren ALTERs: Wenn eine andere Transaktion einen Lock hält, wartet dein ALTER — und blockiert alle neuen Queries dahinter.
BEGIN;
SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN priority int DEFAULT 0;
COMMIT;Wenn der Lock nicht innerhalb von 5 Sekunden zu kriegen ist: Fehler statt Blocking-Cascade. Dann: kurz warten und retry.
Best Practice für Migrations-Tools: jede Migration startet mit SET lock_timeout = '5s'; und SET statement_timeout = '5min'; — Notbremsen, falls was schief läuft.
Häufige Stolperfallen
Lock-Wartung ist gefährlicher als der Lock selbst.
Ein langer ALTER blockiert nicht nur sich selbst — er sammelt eine Schlange wartender Connections an, die alle nicht durchkommen. Selbst SELECTs warten dann. Ergebnis: User-Anfragen timeout-en, App scheint hängend. Daher: lock_timeout setzen.
VOLATILE Default beim ADD COLUMN = Vollscan.
Bei Postgres 11+ sind konstante Defaults instant. Aber now(), random(), gen_random_uuid() etc. brechen die Optimierung — Tabellen-Scan, lange Locks. Workaround: ohne Default hinzufügen, dann in Batches updaten.
FOREIGN KEY ohne NOT VALID = ACCESS EXCLUSIVE.
ALTER TABLE … ADD FOREIGN KEY scannt direkt die referenzierende Tabelle UND nimmt einen Lock auf der referenzierten Tabelle. Bei großen Tabellen Stundenfall. Immer mit NOT VALID + späterem VALIDATE.
CREATE INDEX CONCURRENTLY kann fehlschlagen — Cleanup nicht vergessen.
Wenn der Build abstürzt (z. B. Verbindung weg), bleibt ein INVALID-Index zurück. \d tabelle zeigt's. Mit DROP INDEX CONCURRENTLY name aufräumen, dann erneut versuchen. Sonst sieht der Plan-Optimizer den Index, kann ihn aber nicht nutzen.
statement_timeout bricht laufende Queries ab — Vorsicht in Transaktionen.
SET statement_timeout = '5min' kann mitten in der Transaktion einen Bruch erzeugen — was bei DDL eine teilweise migrierte Schema-Version hinterlässt. Migrations-Tools sollten Timeouts mit BEGIN; SET LOCAL statement_timeout = ...; ... COMMIT; setzen.
Test-Replay auf Staging mit echtem Daten-Volumen.
Eine Migration, die auf 1000-Zeilen-Test-DB instant läuft, kann auf Production mit 100 Mio. Zeilen Stunden brauchen. Vor dem Production-Run die exakte Migration auf einer Staging-Kopie mit produktivem Daten-Volumen testen — und Lock-Verhalten messen.