Index CONCURRENTLY erstellen

Die Grundform

CREATE INDEX CONCURRENTLY orders_customer_id_idx
ON orders (customer_id);

Was passiert hinter den Kulissen:

1. Postgres scannt die Tabelle einmal komplett (mit SHARE UPDATE EXCLUSIVE Lock — Reader und Writer dürfen weiter)
2. Baut eine erste Version des Index aus dem Snapshot
3. Wartet auf alle Transaktionen, die zu Beginn liefen
4. Macht einen zweiten Pass, um in der Zwischenzeit eingefügte/geänderte Zeilen einzuholen
5. Markiert den Index als valide

Insgesamt: deutlich langsamer als ein normales CREATE INDEX, aber kein Service-Stop.

Einschränkungen

CONCURRENTLY hat Einschränkungen:

• Nicht in Transaktionen erlaubt — muss als Standalone-Statement laufen, kein BEGIN; CREATE INDEX CONCURRENTLY ... ; COMMIT;
• Höhere Last während des Builds — Tabelle wird zweimal gescannt
• Längere Build-Zeit — typisch 2-3× länger als ein normaler Index-Build
• Kann fehlschlagen — z. B. bei Konflikten in einer UNIQUE-Constraint

Migrations-Tools brauchen oft Spezial-Handling: golang-migrate und Co. wickeln Migrationen sonst in Transaktionen, was hier explizit deaktiviert werden muss.

Was passiert bei Abbruch?

Wenn der Build abstürzt — z. B. Verbindung weg, Server-Restart, Constraint-Konflikt — bleibt ein INVALID-Index zurück:

SELECT
    i.relname AS index_name,
    t.relname AS table_name
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_class t ON t.oid = ix.indrelid
WHERE NOT ix.indisvalid;

INVALID-Indexe werden vom Planner nicht genutzt — sind also ohne Wert. Sie müssen aufgeräumt werden, bevor ein erneuter Build versucht wird.

INVALID-Index aufräumen

DROP INDEX CONCURRENTLY orders_customer_id_idx;

Auch DROP INDEX CONCURRENTLY blockt nicht — sehr praktisch.

Danach: erneut versuchen.

CREATE INDEX CONCURRENTLY orders_customer_id_idx
ON orders (customer_id);

Wer Glück hat: läuft beim zweiten Mal durch. Wer Pech hat: muss erst die Ursache finden (Constraint-Konflikt, etc.) und Daten bereinigen.

REINDEX CONCURRENTLY

Bestehende Indexe neu bauen, ohne Lock — z. B. um Bloat aufzuräumen oder einen Index nach Postgres-Major-Upgrade zu refreshen:

REINDEX INDEX CONCURRENTLY orders_customer_id_idx;
REINDEX TABLE CONCURRENTLY orders;       -- alle Indexe der Tabelle
REINDEX SCHEMA CONCURRENTLY public;     -- alle Indexe des Schemas

Verfügbar ab PG 12. Vorher musste man ein temporäres Index erstellen, dann tauschen.

UNIQUE-Indexe und Konflikte

Wenn der Build einen UNIQUE-Constraint baut, der von Bestandsdaten verletzt wird:

CREATE UNIQUE INDEX CONCURRENTLY users_email_uniq
ON users (email);

-- ERROR: could not create unique index "users_email_uniq"
-- DETAIL: Key (email)=(hans@example.com) is duplicated.

Folge: INVALID-Index, der gedroppt werden muss. Nach Bereinigung der Duplikate erneut versuchen.

Pragmatisch: bei großen Tabellen vorher prüfen mit:

SELECT email, count(*)
FROM users
GROUP BY email
HAVING count(*) > 1;

Constraints, die nicht CONCURRENTLY gehen

PRIMARY KEY und UNIQUE als Constraints (nicht als Index) brauchen ACCESS EXCLUSIVE. Workaround: erst Index CONCURRENTLY bauen, dann als Constraint registrieren.

-- 1. Unique-Index ohne Lock
CREATE UNIQUE INDEX CONCURRENTLY users_email_uniq_idx
ON users (email);

-- 2. Existierenden Index als Constraint registrieren (kurzer Lock)
ALTER TABLE users
ADD CONSTRAINT users_email_uniq
UNIQUE USING INDEX users_email_uniq_idx;

Schritt 2 ist instant — Postgres muss nichts mehr scannen, der Index ist schon da.

Weiterführende Ressourcen

Externe Quellen

• Building Indexes Concurrently
• REINDEX
• pg_index – System Catalog

Verwandte Artikel

• B-tree-Index
• ALTER TABLE ohne Downtime
• Unique-Constraint