SQL Academy · Lektion

Online-Migrationen: Warum ALTER TABLE Sperren verursacht

Verstehen Sie, welche Formen von ALTER TABLE eine ACCESS-EXCLUSIVE-Sperre erfordern und die Tabelle umschreiben und welche nur Metadaten ändern.

Lektion 1 von 414 Schritte

Online-Migrationen: Warum ALTER TABLE Sperren verursacht ist eine kostenlose SQL Academy-Lektion auf CoddyKit. Dies ist Lektion 1 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des SQL Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Das Problem bei Migrationen in der Produktion

Bei einer kleinen Datenbank wird ALTER TABLE sofort ausgeführt. Bei einer 500-GB-Tabelle im laufenden Betrieb kann derselbe Befehl Schreibzugriffe 20 Minuten lang blockieren. Es ist entscheidend zu wissen, welche ALTER-Befehle sicher sind und welche nicht.

Sperrstufen

PostgreSQL-Sperren gibt es in verschiedenen Stufen:

ACCESS SHARE — SELECT-Abfragen
ROW EXCLUSIVE — Schreibzugriffe
SHARE / SHARE ROW EXCLUSIVE — DDL parallel zu Lesezugriffen
EXCLUSIVE — blockiert SELECT-Abfragen
ACCESS EXCLUSIVE — blockiert ALLES

Welche Sperre ALTER TABLE verwendet

Die meisten ALTER-Varianten verwenden ACCESS EXCLUSIVE — sie blockieren Lese- und Schreibzugriffe bis zum Abschluss.

Schnelle (nur Metadaten betreffende) ALTER-Befehle

Einige ALTER-Befehle ändern nur den Katalog und werden selbst bei riesigen Tabellen innerhalb von Millisekunden abgeschlossen:

ALTER TABLE t RENAME COLUMN a TO b;
ALTER TABLE t ALTER COLUMN a SET DEFAULT ...;
ALTER TABLE t ADD COLUMN x INT;             -- nullable, no default: metadata only (PG 11+)
ALTER TABLE t ADD COLUMN x INT NOT NULL DEFAULT 0;  -- metadata only PG 11+ if default is constant

Langsame (Tabellen neu schreibende) ALTER-Befehle

Diese Befehle schreiben die gesamte Tabelle neu:

ALTER TABLE t ALTER COLUMN x TYPE BIGINT;     -- when types not binary-compatible
ALTER TABLE t SET LOGGED;
CLUSTER t USING idx;                          -- physically reorders rows
VACUUM FULL t;                                -- rewrites whole table

NOT NULL sicher hinzufügen

Bei einer großen Tabelle:

-- BAD: full table scan + ACCESS EXCLUSIVE lock
ALTER TABLE t ALTER COLUMN x SET NOT NULL;

-- BETTER:
ALTER TABLE t ADD CONSTRAINT x_not_null CHECK (x IS NOT NULL) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT x_not_null;     -- scans without exclusive lock
-- then drop the CHECK and add NOT NULL (still cheap because already validated):
ALTER TABLE t ALTER COLUMN x SET NOT NULL;
ALTER TABLE t DROP CONSTRAINT x_not_null;

Fremdschlüssel online hinzufügen

Verwenden Sie denselben NOT VALID-Trick:

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

Probleme mit wartenden Sperren

Ein ALTER-Befehl, der auf eine ACCESS-EXCLUSIVE-Sperre wartet, reiht sich hinter jede lang laufende Transaktion ein. Auch neuere Transaktionen warten dann hinter dem ALTER-Befehl — eine Kette blockierter Abfragen entsteht.

lock_timeout

Lassen Sie Migrationen nicht auf unbestimmte Zeit hängen:

SET lock_timeout = '5s';
ALTER TABLE t ...;
-- ERROR if it can't get the lock in 5s — retry.

Wiederholungsschleifen

Migrationen sollten bei einem lock_timeout erneut ausgeführt werden:

for (let i = 0; i < 20; i++) {
  try {
    await client.query('SET lock_timeout = 5000');
    await client.query('ALTER TABLE ...');
    break;
  } catch (e) {
    if (e.code === '55P03') continue;     // lock_not_available
    throw e;
  }
}

statement_timeout zur Sicherheit

Begrenzen Sie, wie lange eine einzelne Anweisung innerhalb einer Migration ausgeführt werden darf:

SET statement_timeout = '30s';

Hilfreiche Tools

strong_migrations (Rails)
django-migrate-zero-downtime
pg-osc (Postgres Online Schema Change)
pgRoll

Zusammenfassung

Bei Online-Migrationen müssen Sie Folgendes beachten:

Welche ALTER-Befehle nur Metadaten ändern und welche die Tabelle neu schreiben
NOT VALID + VALIDATE für Constraints verwenden
lock_timeout setzen und Wiederholungsversuche einplanen
Lang laufende Transaktionen vermeiden, die Migrationen blockieren

Schnelltest

Sie fügen einer 500-GB-Tabelle in einem einzigen ALTER TABLE einen NOT-NULL-Constraint hinzu — was geschieht mit den Schreibzugriffen?

Beantworte es in der interaktiven Lektion

Kostenlos starten

Lerne SQL mit einem KI-Tutor — kostenlos

Schreibe und führe echten Code in deinem Browser aus, bekomme sofortige Hilfe von einem 24/7 KI-Tutor und setze dein Lernen im Web oder in der App fort.

Kurse: 46
Lektionen: 183

Kostenlos lernen Den vollständigen Kurs ansehen

Häufig gestellte Fragen

Ist die Lektion „Online-Migrationen: Warum ALTER TABLE Sperren verursacht“ kostenlos?

Ja — der vollständige Text von „Online-Migrationen: Warum ALTER TABLE Sperren verursacht“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des SQL Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Online-Migrationen: Warum ALTER TABLE Sperren verursacht“?

Verstehen Sie, welche Formen von ALTER TABLE eine ACCESS-EXCLUSIVE-Sperre erfordern und die Tabelle umschreiben und welche nur Metadaten ändern. Du übst SQL Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um SQL Academy zu starten?

Keine Vorkenntnisse erforderlich. SQL Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 1 von 4.

Wie lange dauert die Lektion „Online-Migrationen: Warum ALTER TABLE Sperren verursacht“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser SQL Academy-Lektion Code schreiben und ausführen?

Ja. Jede SQL Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

Online-Migrationen: Warum ALTER TABLE Sperren verursacht
Konkurrierende Indizes (CREATE INDEX CONCURRENTLY)
Spalten ohne Ausfallzeit umbenennen
Tools: Flyway, Liquibase, Sqitch

← Zurück zu SQL Academy