Index-Änderungen ohne Ausfall in PostgreSQL: Ein sicheres Playbook

Warum Index-Änderungen Ausfall verursachen (und wie man das vermeidet)

Index-Arbeiten klingen harmlos. Man fügt "nur" eine Hilfsstruktur hinzu. In PostgreSQL können das Erstellen, Löschen oder Austauschen eines Index jedoch Sperren verursachen, die andere Sessions blockieren. Ist die Tabelle stark ausgelastet, häufen sich diese Wartezeiten und die App wirkt defekt.

Ausfallzeiten sehen selten wie ein klarer Wartungsbanner aus. Meistens äußert es sich als hängende Seiten, hinterherfallende Hintergrundjobs und eine wachsende Warteschlange von Anfragen, die auf die Datenbank warten. Jemand klickt auf "Suche" und bekommt einen Timeout, während Support-Tools und Admin-Oberflächen plötzlich träge reagieren, weil einfache Abfragen die benötigte Sperre nicht bekommen.

"Führ es nachts aus" scheitert aus zwei typischen Gründen. Viele Systeme sind nie wirklich ruhig (globale Nutzer, Batch-Jobs, ETL, Backups). Und Index-Operationen können länger dauern als erwartet, weil sie viele Daten lesen und um CPU und Festplatte konkurrieren. Wenn das Fenster während des Builds endet, stehen Sie vor der Wahl: länger warten oder die Arbeit abbrechen.

Index-Änderungen ohne Ausfall sind kein Zauber. Es geht darum, die am wenigsten blockierende Methode zu wählen, Schutzmechanismen zu setzen (Timeouts und Festplattenchecks) und die Datenbank während des Laufs zu beobachten.

Dieses Playbook konzentriert sich auf praktische Produktionsgewohnheiten:

• Bevorzugen Sie Concurrent-Builds, wenn Lese- und Schreibzugriffe weiterlaufen müssen.
• Überwachen Sie Sperren und Build-Fortschritt, damit Sie früh reagieren können.
• Haben Sie einen Rollback-Pfad, falls die Änderung Regressionen verursacht oder zu lange dauert.

Was nicht abgedeckt wird: tiefe Index-Design-Theorie, allgemeines Query-Tuning oder Schema-Refactorings, die viele Daten umschreiben.

Das einfache Sperrmodell hinter Index-Arbeiten

PostgreSQL verwendet Sperren, um Daten korrekt zu halten, wenn viele Sessions dieselbe Tabelle berühren. Eine Sperre ist einfach eine Regel, die sagt, wer momentan ein Objekt lesen oder schreiben darf und wer warten muss.

Meistens bemerken Sie Sperren nicht, weil PostgreSQL leichte Modi nutzt, die normale Abfragen erlauben. DDL ist anders. Beim Erstellen oder Löschen eines Index muss PostgreSQL genug Kontrolle über die Tabelle haben, um Katalog und Daten konsistent zu halten. Je mehr Kontrolle nötig ist, desto mehr andere Sessions können gezwungen werden zu warten.

Einen Index bauen vs. einen Index benutzen

Die Benutzung eines Index ist aus Sicht der Sperren meist günstig. SELECT-, UPDATE- und DELETE-Abfragen können Indizes lesen oder pflegen, während andere Sessions das ebenfalls tun.

Das Erstellen eines Index ist anders. PostgreSQL muss die Tabelle scannen, Schlüssel sortieren oder hashen und eine neue Struktur auf die Festplatte schreiben. Diese Arbeit braucht Zeit, und Zeit ist das, was "kleine Sperren" in der Produktion zu "großen Problemen" macht.

Was CONCURRENTLY ändert (und was nicht)

Ein normaler CREATE INDEX nimmt eine starke Sperre, die Schreibvorgänge für die Dauer blockiert. CREATE INDEX CONCURRENTLY ist so konzipiert, dass normale Lese- und Schreibzugriffe während des Builds weiterlaufen.

"Concurrent" heißt aber nicht "keine Sperren". Es gibt immer noch kurze Sperrfenster am Anfang und Ende, und der Build kann fehlschlagen oder warten, wenn etwas anderes inkompatible Sperren hält.

Die wichtigsten möglichen Ergebnisse:

• Nicht-concurrent Builds können INSERTs, UPDATEs und DELETEs auf der Tabelle blockieren.
• Concurrent Builds erlauben in der Regel Lese- und Schreibzugriffe, können aber durch lange Transaktionen verlangsamt oder gestoppt werden.
• Die abschließenden Schritte brauchen trotzdem kurze Sperren, sodass sehr stark belastete Systeme kurze Wartezeiten sehen können.

Die richtige Vorgehensweise wählen: concurrent oder nicht

Sie haben zwei Hauptoptionen bei Index-Änderungen: den Index normal erstellen (schnell, aber blockierend) oder ihn mit CONCURRENTLY erstellen (meist nicht-blockierend für Anwendungstraffic, aber langsamer und empfindlich gegenüber langen Transaktionen).

Wann CONCURRENTLY die richtige Wahl ist

Verwenden Sie CREATE INDEX CONCURRENTLY, wenn die Tabelle echten Traffic bedient und Sie Schreibvorgänge nicht anhalten können. Es ist typischerweise die sicherere Wahl, wenn:

• Die Tabelle groß genug ist, dass ein normaler Build Minuten oder Stunden dauern könnte.
• Die Tabelle konstante Schreibzugriffe hat, nicht nur Leseströme.
• Sie kein echtes Wartungsfenster planen können.
• Sie zuerst bauen, verifizieren und dann später einen alten Index entfernen müssen.

Wann ein normaler Index-Build akzeptabel ist

Ein normaler CREATE INDEX kann in Ordnung sein, wenn die Tabelle klein ist, der Traffic gering ist oder Sie ein kontrolliertes Fenster haben. Er ist oft schneller und einfacher durchzuführen.

Erwägen Sie den normalen Weg, wenn der Build in Staging konsistent schnell ist und Sie Schreibzugriffe vorübergehend stoppen können (auch kurz).

Wenn Sie eine Eindeutigkeit brauchen, entscheiden Sie früh. CREATE UNIQUE INDEX CONCURRENTLY funktioniert, schlägt aber fehl, wenn Duplikate existieren. In vielen Produktionen ist das Finden und Beheben von Duplikaten das eigentliche Projekt.

Preflight-Checks vor Änderungen in Produktion

Die meisten Probleme entstehen, bevor der Befehl überhaupt startet. Ein paar Checks helfen, zwei große Überraschungen zu vermeiden: unerwartetes Blocking und ein Index-Build, der viel länger läuft (oder mehr Platz braucht) als geplant.

1. Stellen Sie sicher, dass Sie sich nicht innerhalb einer Transaktion befinden. CREATE INDEX CONCURRENTLY schlägt fehl, wenn Sie es nach BEGIN ausführen, und manche GUI-Tools wickeln Statements stillschweigend in eine Transaktion. Wenn Sie unsicher sind, öffnen Sie eine frische Session und führen Sie dort nur den Index-Befehl aus.

2. Setzen Sie Erwartungen für Zeit und Festplattenplatz. Concurrent-Builds dauern in der Regel länger als normale Builds und benötigen zusätzlichen Arbeitsraum. Planen Sie für den neuen Index plus temporären Overhead und vergewissern Sie sich, dass Sie genügend freien Festplattenspeicher haben.

3. Setzen Sie Timeouts, die zu Ihrem Ziel passen. Sie wollen, dass der Build schnell fehlschlägt, wenn er keine Sperre bekommt, aber nicht, dass die Session mitten im Build wegen eines zu aggressiven statement_timeout stirbt.

4. Erfassen Sie eine Basislinie. Sie wollen Beweise, dass die Änderung geholfen hat, und eine schnelle Möglichkeit, Regressionen zu erkennen. Notieren Sie einen Vorher-Snapshot: langsame Query-Zeiten, ein repräsentatives EXPLAIN (ANALYZE, BUFFERS) und einen schnellen Blick auf CPU, IO, Verbindungen und freien Speicher.

Sichere Session-Einstellungen, die viele Teams als Startpunkt nutzen (passen Sie sie an Ihre Regeln an):

-- Run in the same session that will build the index
SET lock_timeout = '2s';
SET statement_timeout = '0';

Schritt für Schritt: einen Index mit CONCURRENTLY erstellen

Verwenden Sie CREATE INDEX CONCURRENTLY, wenn der Anwendungstraffic weiterlaufen muss und Sie eine längere Build-Zeit in Kauf nehmen können.

Entscheiden Sie zuerst genau, was Sie bauen:

• Seien Sie spezifisch in der Spaltenreihenfolge (das ist wichtig).
• Überlegen Sie, ob ein partieller Index ausreicht. Wenn die meisten Abfragen auf "aktive" Zeilen filtern, kann ein partieller Index kleiner, schneller und günstiger in der Pflege sein.

Ein sicherer Ablauf sieht so aus: schreiben Sie das Ziel und den Indexnamen auf, führen Sie den Build außerhalb eines Transaction Blocks aus, überwachen Sie ihn bis zum Abschluss und verifizieren Sie dann, dass der Planner ihn verwenden kann, bevor Sie etwas anderes entfernen.

-- Example: speed up searches by email for active users
CREATE INDEX CONCURRENTLY idx_users_active_email
ON public.users (email)
WHERE status = 'active';

-- Validate it exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Check the plan can use it
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM public.users
WHERE status = 'active' AND email = '[email protected]';

Für Fortschrittsnotizen (nützlich für Audits) protokollieren Sie Startzeit, Endzeit und etwaige Wartezeiten. Während des Builds können Sie in einer anderen Session pg_stat_progress_create_index abfragen.

Validierung ist nicht nur „der Index existiert.“ Bestätigen Sie, dass der Planner ihn auswählen kann, und beobachten Sie echte Query-Zeiten nach Deployment. Wenn der neue Index nicht verwendet wird, eilen Sie nicht, den alten zu löschen. Verbessern Sie zuerst die Abfrage oder die Index-Definition.

Schritt für Schritt: Indizes ersetzen oder entfernen ohne Blocking

Das sicherste Muster ist: zuerst hinzufügen, dem Traffic Zeit geben, sich an den neuen Index zu gewöhnen, und erst danach den alten zu entfernen. So behalten Sie eine funktionierende Rückfalloption.

Alten Index gegen neuen tauschen (sichere Reihenfolge)

1. Erstellen Sie den neuen Index mit CREATE INDEX CONCURRENTLY.

2. Verifizieren Sie, dass er verwendet wird. Prüfen Sie EXPLAIN für die langsamen Abfragen, die Ihnen wichtig sind, und beobachten Sie die Index-Nutzung über die Zeit.

3. Erst danach den alten Index concurrent löschen. Ist das Risiko hoch, behalten Sie beide Indizes über einen kompletten Geschäftszyklus, bevor Sie etwas entfernen.

Indizes löschen: wann CONCURRENTLY funktioniert (und wann nicht)

Für einen normalen Index, den Sie selbst erstellt haben, ist DROP INDEX CONCURRENTLY in der Regel die richtige Wahl. Zwei Fallstricke: Es darf nicht in einem Transaction Block laufen, und es braucht trotzdem kurze Sperren am Anfang und Ende, sodass lange Transaktionen Verzögerungen verursachen können.

Wenn der Index wegen eines PRIMARY KEY oder UNIQUE Constraints existiert, können Sie ihn in der Regel nicht direkt löschen. Sie müssen das Constraint mit ALTER TABLE ändern, was stärkere Sperren bedeuten kann. Behandeln Sie das als separaten, geplanten Wartungsschritt.

Indizes umbenennen für Klarheit

ALTER INDEX ... RENAME TO ... ist meist schnell, aber vermeiden Sie es, wenn Tools oder Migrationen auf Indexnamen referenzieren. Eine sicherere Gewohnheit ist, von Anfang an einen eindeutigen Namen zu wählen.

Falls der alte Index noch gebraucht wird

Manchmal benötigen zwei Abfragemuster zwei verschiedene Indizes. Wenn wichtige Abfragen weiterhin auf den alten angewiesen sind, behalten Sie ihn. Überlegen Sie, den neuen Index anzupassen (Spaltenreihenfolge, Partial-Bedingung), statt den alten zu erzwingen zu löschen.

Sperren und Fortschritt während des Builds überwachen

Selbst mit CREATE INDEX CONCURRENTLY sollten Sie beobachten, was in Echtzeit passiert. Die meisten Überraschungsfälle entstehen durch eine von zwei Ursachen: eine blockierende Session, die Sie nicht bemerkt haben, oder eine lang laufende Transaktion, die den Build festhält.

Blockierende Sessions finden (wer blockiert wen)

Fangen Sie damit an, Sessions zu finden, die auf Locks warten:

SELECT
  a.pid,
  a.usename,
  a.application_name,
  a.state,
  a.wait_event_type,
  a.wait_event,
  now() - a.xact_start AS xact_age,
  left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY xact_age DESC;

Wenn Sie den genauen Blockierer brauchen, folgen Sie blocked_pid zu blocking_pid:

SELECT
  blocked.pid  AS blocked_pid,
  blocking.pid AS blocking_pid,
  now() - blocked.xact_start AS blocked_xact_age,
  left(blocked.query, 80)  AS blocked_query,
  left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Build-Fortschritt und "feststeckende" Signale beobachten

PostgreSQL stellt den Fortschritt eines Index-Builds zur Verfügung. Wenn Sie über lange Zeit keine Bewegung sehen, suchen Sie nach einer langen Transaktion (oft eine idle-Session, die einen alten Snapshot hält).

SELECT
  pid,
  phase,
  lockers_total,
  lockers_done,
  blocks_total,
  blocks_done,
  tuples_total,
  tuples_done
FROM pg_stat_progress_create_index;

Achten Sie außerdem auf Systemdruck: Festplatten-IO, Replikationsverzug und steigende Query-Zeiten. Concurrent-Builds sind freundlicher zur Verfügbarkeit, lesen aber trotzdem viele Daten.

Einfache Regeln, die in der Praxis gut funktionieren:

• Warten Sie, wenn Fortschritt sichtbar ist und die Nutzerbeeinträchtigung gering.
• Abbrechen und neu planen, wenn der Build hinter einer langen Transaktion steckt, die Sie nicht sicher beenden können.
• Während Spitzenzeiten pausieren, wenn IO kundenrelevante Queries beeinträchtigt.
• Nur als letzten Ausweg abbrechen, und erst nachdem Sie bestätigt haben, was die Session tut.

Für die Teamkommunikation halten Sie Updates kurz: Startzeit, aktuelle Phase, was blockiert (falls etwas) und wann Sie wieder prüfen.

Rollback-Plan: wie man sicher zurückgeht

Index-Änderungen bleiben nur dann risikoarm, wenn Sie den Exit planen, bevor Sie starten. Der sicherste Rollback ist oft kein dramatisches Rückgängigmachen, sondern einfach das Stoppen der neuen Arbeit und das Beibehalten des alten Index. So bleibt der Dienst verfügbar.

Häufige Gründe, warum Index-Arbeiten fehlschlagen

Die meisten Produktionsfehler sind vorhersehbar: Der Build trifft auf ein Timeout, jemand bricht ihn während eines Incidents ab, der Server bekommt wenig Festplattenspeicher oder der Build konkurriert mit Normaltraffic so stark, dass die Latenz für Nutzer steigt.

Bei CREATE INDEX CONCURRENTLY ist ein Abbruch für die App meist sicher, weil Abfragen weiterlaufen. Der Kompromiss ist die Aufräumarbeit: Ein abgebrochener oder fehlgeschlagener concurrent Build kann einen ungültigen Index hinterlassen.

Sichere Regeln für Abbruch und Aufräumen

Das Abbrechen eines concurrent Builds rollt nicht wie eine normale Transaktion zurück. PostgreSQL kann einen Index zurücklassen, der existiert, aber für den Planner nicht gültig ist.

-- Cancel the session building the index (use the PID you identified)
SELECT pg_cancel_backend(<pid>);

-- If the index exists but is invalid, remove it without blocking writes
DROP INDEX CONCURRENTLY IF EXISTS your_index_name;

Bevor Sie löschen, prüfen Sie genau:

SELECT
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'your_index_name';

Wenn indisvalid = false, wird er nicht verwendet und kann sicher entfernt werden.

Praktische Checkliste für Rollback beim Ersetzen eines Index:

• Behalten Sie den alten Index, bis der neue vollständig gebaut und gültig ist.
• Wenn der neue Build fehlschlägt oder abgebrochen wird, löschen Sie den ungültigen neuen Index concurrent.
• Wenn Sie den alten bereits gelöscht haben, erstellen Sie ihn mit CREATE INDEX CONCURRENTLY neu, um den vorherigen Zustand wiederherzustellen.
• Wenn Speicherdruck den Fehler verursacht hat, schaffen Sie zuerst Platz und versuchen Sie es dann erneut.
• Wenn Timeouts das Problem waren, planen Sie ein ruhigeres Fenster statt es zu erzwingen.

Beispiel: Sie starten einen neuen Index für eine Admin-Suche, er läuft 20 Minuten, dann schlagen Festplatten-Alerts an. Bauen Sie den Build ab, löschen Sie den ungültigen Index concurrent und lassen Sie den alten Index den Traffic bedienen. Später können Sie es nach dem Freimachen von Speicher erneut versuchen, ohne für Benutzer sichtbaren Ausfall.

Häufige Fehler, die überraschende Ausfälle erzeugen

Die meisten Ausfälle rund um Indizes entstehen nicht, weil PostgreSQL "langsam" ist. Sie passieren, weil ein kleines Detail eine sichere Änderung in eine blockierende verwandelt.

1) Einen concurrent Build in eine Transaktion packen

CREATE INDEX CONCURRENTLY kann nicht innerhalb eines Transaction Blocks laufen. Viele Migrationstools wickeln standardmäßig alle Änderungen in einer einzigen Transaktion ab. Das Ergebnis ist entweder ein harter Fehler (bestes Szenario) oder ein chaotischer Deploy mit Retries.

Vor der Migration sollten Sie sicherstellen, dass Ihr Tool ein Statement ohne äußere Transaktion ausführen kann, oder die Migration in einen speziellen nicht-transaktionalen Schritt aufteilen.

2) Während Spitzenverkehr starten

Concurrent Builds reduzieren Blockierungen, erhöhen aber dennoch die Last: mehr Lese- und Schreibzugriffe und mehr Druck auf autovacuum. Einen Build während eines Deploy-Fensters zu starten, in dem der Traffic gerade hochgeht, ist ein häufiger Weg, eine Verlangsamung zu erzeugen, die sich wie ein Ausfall anfühlt.

Wählen Sie eine ruhige Periode und behandeln Sie sie wie jede andere Produktionswartung.

3) Lange Transaktionen ignorieren

Eine einzige lange Transaktion kann die Aufräumphase eines concurrent Builds blockieren. Der Index kann Fortschritt zeigen, dann aber am Ende verharren, während auf alte Snapshots gewartet wird.

Gewöhnen Sie sich an: Vor dem Start nach langen Transaktionen suchen und erneut prüfen, wenn der Fortschritt stockt.

4) Das falsche Objekt löschen (oder eine Constraint brechen)

Teams löschen manchmal aus dem Gedächtnis per Namen einen Index oder entfernen einen Index, der eine Unique-Regel trägt. Löschen Sie das falsche Objekt, verlieren Sie möglicherweise eine Durchsetzung (Unique Constraints) oder verschlechtern sofort die Query-Performance.

Schnelle Sicherheits-Checkliste: Verifizieren Sie den Indexnamen im Katalog, prüfen Sie, ob er ein Constraint unterstützt, kontrollieren Sie Schema und Tabelle und trennen Sie "neuen Index erstellen" klar von "alten Index löschen". Halten Sie den Wiederherstellungsbefehl bereit, bevor Sie starten.

Realistisches Beispiel: eine Admin-Suche beschleunigen

Ein typischer Schmerzpunkt ist eine Admin-Suche, die in Staging instant wirkt, aber in Produktion kriecht. Angenommen, Sie haben eine große tickets-Tabelle (zig Millionen Zeilen) hinter einem internen Admin-Panel und Agenten suchen oft nach "offenen Tickets für einen Kunden, neueste zuerst."

Die Abfrage sieht so aus:

SELECT id, customer_id, subject, created_at
FROM tickets
WHERE customer_id = $1
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

Ein vollständiger Index auf (customer_id, status, created_at) hilft, fügt aber Schreibaufwand für jedes Ticket-Update hinzu, auch für geschlossene. Wenn die meisten Zeilen nicht open sind, ist ein partieller Index oft die einfachere Lösung:

CREATE INDEX CONCURRENTLY tickets_open_by_customer_created_idx
ON tickets (customer_id, created_at DESC)
WHERE status = 'open';

Eine sichere Timeline in Produktion:

• Preflight: Bestätigen, dass die Abfrageform stabil ist und genug freier Speicher für einen neuen Index vorhanden ist.
• Build: Führen Sie CREATE INDEX CONCURRENTLY in einer separaten Session mit klaren Timeout-Einstellungen aus.
• Validate: Führen Sie ANALYZE tickets; aus und prüfen Sie, ob der Planner den neuen Index verwendet.
• Cleanup: Sobald Sie sicher sind, löschen Sie redundante alte Indizes mit DROP INDEX CONCURRENTLY.

Woran Erfolg aussieht:

• Die Admin-Suche fällt von Sekunden auf wenige zehn Millisekunden für typische Kunden.
• Normale Lese- und Schreibzugriffe laufen während des Builds weiter.
• CPU- und Festplatten-IO steigen während des Builds, bleiben aber innerhalb Ihrer Sicherheitsgrenzen.
• Sie können klare Vorher/Nachher-Zahlen vorweisen: Abfragezeit, gescannte Zeilen und Sperrverlauf.

Kurze Checkliste und nächste Schritte

Index-Arbeit ist am sichersten, wenn Sie sie wie ein kleines Produktions-Release behandeln: vorbereiten, beobachten, verifizieren und erst dann aufräumen.

Bevor Sie starten:

• Setzen Sie Timeouts, damit eine überraschende Sperre nicht ewig hängt.
• Bestätigen Sie genug freien Festplattenspeicher für den neuen Indexbuild.
• Suchen Sie nach langen Transaktionen, die den Build verlangsamen könnten.
• Wählen Sie ein Traffic-armes Fenster und definieren Sie, was "fertig" bedeutet.
• Schreiben Sie Ihren Rollback-Plan jetzt auf.

Während des Builds:

• Beobachten Sie Blockierungen und Sperr-Warteschlangen.
• Verfolgen Sie den Build-Fortschritt mit pg_stat_progress_create_index.
• Achten Sie auf App-Symptome: Error-Rates, Timeouts und langsame Endpunkte, die mit der Tabelle verbunden sind.
• Seien Sie bereit, abzubrechen, wenn Lock-Waits steigen oder Nutzer-Timeouts zunehmen.
• Protokollieren Sie, was passiert ist: Startzeit, Endzeit und Alerts.

Nach dem Abschluss: Bestätigen Sie, dass der Index gültig ist, führen Sie die ein oder zwei Schlüsselabfragen aus, um Plan und Timing zu prüfen, und entfernen Sie alte Indizes erst dann auf nicht-blockierende Weise.

Wenn Sie das öfter machen, machen Sie daraus einen wiederholbaren Bereitstellungs-Schritt: ein kleines Runbook, eine Generalprobe in Staging mit Produktions-ähnlichen Daten und eine klare Verantwortlichkeit für das Monitoring während des Builds.

Wenn Sie interne Tools oder Admin-Panels mit AppMaster (appmaster.io) bauen, hilft es, Datenbankänderungen wie Index-Builds als Teil derselben Release-Checkliste wie Ihre Backend-Updates zu behandeln: gemessen, überwacht und mit einem Rollback, das Sie schnell ausführen können.