Konkurrierende Indizes (CREATE INDEX CONCURRENTLY)
Erstellen Sie mit CREATE INDEX CONCURRENTLY Indizes, ohne Schreibvorgänge zu blockieren, und beheben Sie Folgen unterbrochener Erstellungsvorgänge.
Konkurrierende Indizes (CREATE INDEX CONCURRENTLY) ist eine kostenlose SQL Academy-Lektion auf CoddyKit. Dies ist Lektion 2 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 mit CREATE INDEX
Ein reguläres CREATE INDEX verwendet eine SHARE-Sperre — Schreibzugriffe werden während des gesamten Aufbaus blockiert, Lesezugriffe jedoch nicht. Bei einer Tabelle mit einer Milliarde Zeilen bedeutet das mehrere Minuten Ausfallzeit für Schreibzugriffe.
CONCURRENTLY: Die Lösung
CREATE INDEX CONCURRENTLY erstellt den Index, ohne Schreibzugriffe zu blockieren:
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);So funktioniert es
Zwei Tabellendurchläufe:
- Erster Durchlauf: Der Index wird aus den aktuellen Zeilen erstellt
- Zweiter Durchlauf: Alle Zeilenänderungen während des ersten Durchlaufs werden berücksichtigt
Außerdem wird kurz auf jede gleichzeitig laufende Transaktion gewartet, die vor dem Aufbau begonnen hat.
Nicht innerhalb einer Transaktion ausführbar
CONCURRENTLY führt zwei Commits aus — daher kann der Befehl nicht innerhalb eines expliziten BEGIN/COMMIT-Blocks stehen:
BEGIN;
CREATE INDEX CONCURRENTLY ...; -- ERROR
COMMIT;Langsam, aber online
CONCURRENTLY dauert länger als ein reguläres CREATE INDEX (zwei Durchläufe, Wartezeiten auf Sperren). Der Vorteil: keine Ausfallzeit.
Fehlerbehebung
Wenn CONCURRENTLY fehlschlägt (etwa durch eine Unterbrechung oder einen Systemfehler), bleibt der Index im Status INVALID:
SELECT relname FROM pg_class
WHERE relkind = 'i' AND NOT pg_index.indisvalid;
-- Fix: drop and re-create:
DROP INDEX CONCURRENTLY my_idx;
CREATE INDEX CONCURRENTLY my_idx ON ...;REINDEX CONCURRENTLY
Seit PG 12 ohne Blockierung neu aufbauen:
REINDEX INDEX CONCURRENTLY orders_user_id_idx;
REINDEX TABLE CONCURRENTLY orders;DROP INDEX CONCURRENTLY
Ebenfalls ohne Blockierung:
DROP INDEX CONCURRENTLY old_idx;Eindeutige Indizes
UNIQUE-Indizes können ebenfalls mit CONCURRENTLY erstellt werden. Das ist hilfreich, wenn Sie online UNIQUE-Constraints hinzufügen möchten:
CREATE UNIQUE INDEX CONCURRENTLY users_email_uidx ON users(email);
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_uidx;Indizes für Fremdschlüssel
Das Standardmuster beim Hinzufügen eines FK zu einer großen Tabelle:
-- 1. Build the index online:
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders(user_id);
-- 2. Add the FK with NOT VALID (instant):
ALTER TABLE orders ADD CONSTRAINT orders_user_fk
FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
-- 3. Validate in the background:
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;Kosten des parallelen Indexaufbaus
Zwei vollständige Tabellendurchläufe plus Verwaltungsaufwand. Das verbraucht CPU und I/O. Führen Sie den Vorgang nach Möglichkeit in Zeiten mit geringem Datenverkehr aus.
Fortschritt überwachen
PG 12+ stellt den Fortschritt in pg_stat_progress_create_index bereit:
SELECT phase,
blocks_done, blocks_total,
tuples_done, tuples_total
FROM pg_stat_progress_create_index;Zusammenfassung
CONCURRENTLY ist die produktionsfreundliche Methode zur Verwaltung von Indizes.
- CREATE/DROP/REINDEX CONCURRENTLY blockieren keine Schreibzugriffe
- Kann nicht innerhalb einer Transaktion ausgeführt werden
- Fehler hinterlassen INVALID-Indizes — bereinigen Sie diese
- Mit pg_stat_progress_create_index überwachen
Schnelltest
Warum kann CREATE INDEX CONCURRENTLY nicht innerhalb eines expliziten BEGIN/COMMIT-Blocks ausgeführt werden?
Häufig gestellte Fragen
Ist die Lektion „Konkurrierende Indizes (CREATE INDEX CONCURRENTLY)“ kostenlos?
Ja — der vollständige Text von „Konkurrierende Indizes (CREATE INDEX CONCURRENTLY)“ 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 „Konkurrierende Indizes (CREATE INDEX CONCURRENTLY)“?
Erstellen Sie mit CREATE INDEX CONCURRENTLY Indizes, ohne Schreibvorgänge zu blockieren, und beheben Sie Folgen unterbrochener Erstellungsvorgänge. 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 2 von 4.
Wie lange dauert die Lektion „Konkurrierende Indizes (CREATE INDEX CONCURRENTLY)“?
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