Fehlerbehebung bei defekten Indizes: So erstellen und reparieren Sie PostgreSQL-Indizes neu

Indizes sind normalerweise der Grund, warum PostgreSQL eine Abfrage in Millisekunden beantworten kann, anstatt eine ganze Tabelle zu lesen. Sie sind auch leicht zu vergessen, bis einer aufgebläht, ungültig oder verdächtigt wird, beschädigt zu sein. Dann sehen die Symptome zunächst wie normale Leistungsprobleme aus: Eine Abfrage wird langsamer, die Datenträgerauslastung steigt, eine Tabelle, die früher ruhig war, wird teuer, oder ein Abfrageplan ergibt keinen Sinn mehr.

Einen Index neu zu erstellen ist nicht schwierig. Zu wissen, wann man ihn neu erstellen sollte, ist der schwierigere Teil. Ein aufgeblähter Index kann mit REINDEX repariert werden, aber die Ursache könnten schwache Autovacuum-Einstellungen oder eine Arbeitslast sein, die den ganzen Tag dieselben Zeilen aktualisiert. Ein beschädigter Index muss möglicherweise dringend repariert werden, aber Sie sollten auch fragen, warum es überhaupt zu einer Beschädigung gekommen ist: Speicher, Arbeitsspeicher, Kernel-Fehler, unsichere Hardware-Einstellungen oder ein seltener Softwarefehler.

Dieser Leitfaden konzentriert sich auf praktische PostgreSQL-Befehle: wie man verdächtige Indizes erkennt, wie man sie mit und ohne Ausfallzeit neu erstellt und was man überprüfen sollte, bevor man Wartungsarbeiten an einer Produktionsdatenbank durchführt.

Grundlegendes zu PostgreSQL-Indizes und ihren häufigsten Problemen

PostgreSQL-Indizes, am häufigsten B-Baum-Indizes, sind Nachschlagestrukturen, die dem Planer helfen, das Scannen jeder Zeile zu vermeiden. Wenn ein Index gesund und selektiv ist, kann PostgreSQL zu dem kleinen Teil der Tabelle springen, den es benötigt. Wenn der Index aufgebläht oder ungültig ist, verwendet der Planer ihn möglicherweise trotzdem, aber die Datenbank leistet zusätzliche Arbeit, um das gleiche Ergebnis zu erzielen.

Indizes können hauptsächlich aus zwei Gründen problematisch werden: Bloat und Beschädigung.

Index-Bloat

Index-Bloat bezieht sich auf die Ansammlung von "toten Tupeln" (veraltete Datenversionen) innerhalb einer Indexstruktur. In PostgreSQL werden bei Aktualisierung oder Löschung von Zeilen die alten Versionen der Daten (und ihre entsprechenden Indexeinträge) nicht sofort entfernt. Stattdessen werden sie als "tot" markiert und schließlich vom VACUUM-Prozess zurückgewonnen. Wenn VACUUM nicht häufig oder effektiv genug läuft oder wenn eine hohe Aktualisierungs-/Löschrate vorliegt, können sich diese toten Tupel ansammeln und den Index größer als nötig machen. Ein aufgeblähter Index belegt mehr Speicherplatz, erfordert mehr E/A-Operationen zum Scannen und kann sogar weniger effektiv bei der Beschleunigung von Abfragen werden.

Index-Beschädigung

Index-Beschädigung ist ein schwerwiegenderes Problem, bei dem die interne Struktur eines Index logisch inkonsistent oder physisch beschädigt wird. Dies kann durch verschiedene Faktoren verursacht werden, darunter:

• Hardwarefehler: Datenträgerfehler, Speicherprobleme oder Stromausfälle.
• Softwarefehler: Seltene, aber mögliche Defekte in PostgreSQL selbst oder zugrunde liegenden Betriebssystemkomponenten.
• Plötzliche Systemabstürze: Abrupte Beendigung des PostgreSQL-Servers ohne ordnungsgemäße Herunterfahrverfahren.

Beschädigte Indizes können zu falschen Abfrageergebnissen, Fehlern wie "Index enthält unerwartete Daten" führen oder sogar verhindern, dass Abfragen abgeschlossen werden. Die Identifizierung und Behebung von Beschädigungen ist entscheidend für die Datenintegrität und Datenbankstabilität.

Zu den Symptomen problematischer Indizes gehören oft eine plötzliche Verlangsamung bestimmter Abfragen, erhöhte E/A-Aktivität ohne ersichtlichen Grund oder Fehlermeldungen im Zusammenhang mit dem Index-Scannen.

Identifizieren problematischer Indizes

Bevor Sie einen Index reparieren können, müssen Sie identifizieren, welche Probleme verursachen. PostgreSQL bietet mehrere Möglichkeiten, dies zu tun.

Überprüfen auf ungenutzte oder ineffiziente Indizes

Die Ansicht pg_stat_user_indexes liefert Statistiken über die Indexnutzung. Sie können sie abfragen, um Indizes zu finden, die selten oder nie verwendet werden und möglicherweise Kandidaten für die Entfernung oder Neubewertung sind.

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0 -- Indizes, die noch nie gescannt wurden
    AND schemaname = 'public'
ORDER BY
    pg_relation_size(indexrelid) DESC;

Obwohl ein idx_scan von 0 auf einen ungenutzten Index hindeuten kann, ist es wichtig zu bedenken, dass einige Indizes für Constraints (z. B. UNIQUE, PRIMARY KEY) oder selten aufgerufene Berichte verwendet werden. Untersuchen Sie immer, bevor Sie löschen.

Erkennen von Index-Bloat

Bloat ist schwer direkt zu erkennen, aber eine unverhältnismäßig große Indexgröße im Vergleich zu seiner Tabelle oder ein Index, der übermäßig wächst, ohne entsprechendes Datenwachstum, kann auf Bloat hindeuten. Sie können die Größe von Tabellen und ihren Indizes vergleichen:

SELECT
    relname AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM
    pg_stat_user_tables
ORDER BY
    pg_total_relation_size(relid) DESC;

Für eine fortgeschrittenere Bloat-Erkennung könnten Sie die Verwendung von Community-Skripten oder Erweiterungen wie pg_repack oder pgstattuple in Betracht ziehen (die Bloat durch Betrachtung der Tupeldichte schätzen können).

Identifizieren langsamer Abfragen mit EXPLAIN ANALYZE

Wenn eine bestimmte Abfrage langsam wird, ist EXPLAIN ANALYZE Ihr bester Freund. Es zeigt den Abfrageausführungsplan und die tatsächlichen Laufzeitstatistiken, einschließlich der Verwendung (oder Nichtverwendung) von Indizes.

EXPLAIN ANALYZE
SELECT * FROM your_table WHERE your_column = 'some_value';

Wenn der Plan sequentielle Scans zeigt, wo ein Index-Scan erwartet wurde, oder wenn ein Index-Scan ungewöhnlich lange dauert, könnte dies auf einen ineffizienten oder problematischen Index hindeuten.

Überprüfen auf Index-Beschädigung

Index-Beschädigung äußert sich oft in Fehlern in den PostgreSQL-Protokollen oder wenn Abfragen unerwartet fehlschlagen. Achten Sie auf Meldungen mit Phrasen wie corruption, unexpected data, invalid page oder bad block. Es gibt keinen einzigen integrierten SQL-Befehl, der beweist, dass jeder Index in einer Datenbank gesund ist. Für tiefere Überprüfungen verwenden Teams oft die amcheck-Erweiterung von PostgreSQL, insbesondere bt_index_check und bt_index_parent_check für B-Baum-Indizes, während Wartungsfenstern.

CREATE EXTENSION IF NOT EXISTS amcheck;

SELECT bt_index_check('public.idx_products_name'::regclass);

amcheck ist ein Diagnosetool, kein Reparaturtool. Wenn es ein Problem meldet, erstellen Sie ein Backup, falls Sie noch kein aktuelles haben, überprüfen Sie die PostgreSQL- und Systemprotokolle und planen Sie eine Neuerstellung.

Tipp: Überwachen Sie regelmäßig Ihre PostgreSQL-Protokolle auf Fehlermeldungen. Die frühzeitige Erkennung von Beschädigungen kann größere Probleme verhindern.

Der REINDEX-Befehl: Ihr primäres Werkzeug

Der REINDEX-Befehl ist das primäre Werkzeug zum Neuerstellen von PostgreSQL-Indizes. Er rekonstruiert einen Index von Grund auf, behebt effektiv Bloat durch Entfernen toter Tupel und repariert Beschädigungen durch den Aufbau einer neuen, gültigen Struktur basierend auf den aktuellen Daten der Tabelle.

Wie REINDEX funktioniert

Wenn REINDEX ausgeführt wird, erstellt PostgreSQL den Index aus den aktuellen Tabellendaten neu. Das Ergebnis ist eine neue, kompakte Indexstruktur. Bei Bloat bedeutet dies, dass toter Speicherplatz innerhalb des Index entfernt wird. Bei vielen Index-Beschädigungen auf Indexebene erhält PostgreSQL eine frische Struktur, die aus der Tabelle aufgebaut wurde.

REINDEX-Syntax und Verwendung

REINDEX kann auf verschiedenen Granularitätsebenen angewendet werden:

1. Neuerstellen eines bestimmten Index:

   REINDEX INDEX index_name;
   

   Dies ist der häufigste Anwendungsfall, der auf einen einzelnen problematischen Index abzielt.

2. Neuerstellen aller Indizes einer Tabelle:

   REINDEX TABLE table_name;
   

   Nützlich, wenn eine Tabelle mehrere aufgeblähte oder beschädigte Indizes hat.

3. Neuerstellen aller Indizes in einer Datenbank:

   REINDEX DATABASE database_name;
   

   Dies ist eine drastischere Maßnahme, die typischerweise in Situationen eingesetzt wird, in denen weit verbreitete Beschädigung oder Bloat vermutet wird. Dies kann zu erheblichen Ausfallzeiten führen.

4. Neuerstellen von Systemkatalogen in einer Datenbank:

   REINDEX SYSTEM database_name;
   

   Dies erstellt alle Indizes für Systemkatalogtabellen in einer bestimmten Datenbank neu. Dies sollte mit äußerster Vorsicht und nur dann verwendet werden, wenn Sie Probleme mit Systemkatalogindizes vermuten, da es die Funktionalität der gesamten Datenbank beeinträchtigen kann und exklusiven Zugriff erfordert.

Warnung: Das Ausführen von REINDEX ohne CONCURRENTLY sperrt stärker und kann den normalen Anwendungsverkehr auf den betroffenen Objekten blockieren. Behandeln Sie es als Ausfallzeit-Operation, es sei denn, Sie haben den genauen Befehl und das Sperrverhalten für Ihre PostgreSQL-Version und Ihren Objekttyp getestet.

Minimierung von Ausfallzeiten mit REINDEX CONCURRENTLY

Für Produktionssysteme, bei denen Ausfallzeiten nicht akzeptabel sind, ist REINDEX CONCURRENTLY eine unschätzbare Option. Es ermöglicht die Neuerstellung von Indizes, ohne gleichzeitige Lese- und Schreiboperationen auf der Tabelle zu blockieren.

Wie REINDEX CONCURRENTLY funktioniert:

1. Es erstellt eine neue Indexdefinition gleichzeitig mit normalen Operationen.
2. Es erhält eine kurze SHARE UPDATE EXCLUSIVE-Sperre auf der Tabelle, die DDL (wie ALTER TABLE) blockiert, aber DML (INSERT, UPDATE, DELETE) und SELECT-Anweisungen zulässt.
3. Es scannt dann die Tabelle, um den neuen Index zu erstellen.
4. Nach dem anfänglichen Aufbau erhält es eine weitere, sehr kurze SHARE UPDATE EXCLUSIVE-Sperre, um Änderungen anzuwenden, die während des Aufbauprozesses aufgetreten sind.
5. Schließlich ersetzt es den alten Index durch den neuen und löscht den alten Index.

Syntax:

REINDEX INDEX CONCURRENTLY index_name;

Wichtige Überlegungen zu REINDEX CONCURRENTLY:

• Langsamere Ausführung: Da es gleichzeitige Änderungen verarbeiten muss, ist REINDEX CONCURRENTLY im Allgemeinen langsamer als ein nicht-gleichzeitiges REINDEX.
• Speicherplatz: Es erfordert vorübergehend Speicherplatz sowohl für die alte als auch für die neue Indexstruktur.
• Keine Transaktionsunterstützung: REINDEX CONCURRENTLY kann nicht innerhalb eines Transaktionsblocks ausgeführt werden.
• Fehlerbehandlung: Wenn REINDEX CONCURRENTLY fehlschlägt (z. B. aufgrund einer Verletzung einer Eindeutigkeitsbeschränkung bei einem eindeutigen Index), hinterlässt es einen ungültigen Index. Sie müssen diesen ungültigen Index mit DROP löschen und dann den REINDEX CONCURRENTLY-Befehl erneut ausführen.

Praktische Beispiele für die Neuerstellung von Indizes

Nehmen wir an, wir haben eine Tabelle products mit einem Index idx_products_name.

Neuerstellen eines einzelnen Index (mit Ausfallzeit)

Wenn Sie sich eine kurze Unterbrechung für den betroffenen Index leisten können:

REINDEX INDEX idx_products_name;

Neuerstellen eines einzelnen Index (gleichzeitig, minimale Ausfallzeit)

Für Produktionssysteme, bei denen die Tabelle products zugänglich bleiben muss:

-- Für einen B-Baum-Index:
REINDEX INDEX CONCURRENTLY idx_products_name;

-- Für einen Primärschlüssel- oder Eindeutigkeits-Constraint-Index (erfordert oft spezielle Handhabung, obwohl REINDEX CONCURRENTLY dies handhabt):
-- Wenn Sie einen Primärschlüssel- oder Eindeutigkeits-Constraint-Index neu erstellen müssen, erstellen Sie normalerweise den zugrunde liegenden Index neu.
-- Wenn beispielsweise 'products_pkey' der Primärschlüsselindex ist:
REINDEX INDEX CONCURRENTLY products_pkey;

Neuerstellen aller Indizes einer Tabelle

Wenn Sie vermuten, dass mehrere Indizes der Tabelle products problematisch sind:

-- Dies erhält eine ACCESS EXCLUSIVE-Sperre auf der Tabelle 'products'.
REINDEX TABLE products;

Moderne PostgreSQL-Versionen unterstützen die gleichzeitige Neuerstellung von Tabellenindizes:

REINDEX TABLE CONCURRENTLY products;

Das ist normalerweise einfacher, als jeden Index manuell neu zu erstellen, verbraucht aber dennoch E/A, CPU und temporären Speicherplatz. Bei älteren PostgreSQL-Versionen, die diese Syntax nicht unterstützen, identifizieren Sie die Indizes der Tabelle und erstellen Sie jeden mit REINDEX INDEX CONCURRENTLY neu.

Identifizieren Sie zunächst alle Indizes für die Tabelle:

SELECT indexname FROM pg_indexes WHERE tablename = 'products';

Für manuelle Kontrolle listen Sie zuerst die Indizes auf:

SELECT indexname
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'products'
ORDER BY indexname;

Neuerstellen aller Indizes in einer Datenbank

Dies ist eine letzte Möglichkeit und erfordert erhebliche Ausfallzeiten. Es sollte nur während geplanter Wartungsfenster durchgeführt werden.

REINDEX DATABASE your_database_name;

Alternativ können Sie auf unterstützten PostgreSQL-Versionen REINDEX DATABASE CONCURRENTLY your_database_name; verwenden. Es vermeidet das schlimmste Blockierungsverhalten, ist aber dennoch eine große Wartungsoperation und kann nicht innerhalb eines Transaktionsblocks ausgeführt werden.

Verwandte Wartungsbefehle und Best Practices

Die Neuerstellung von Indizes ist oft Teil einer umfassenderen Wartungsstrategie. Andere Befehle spielen eine wichtige Rolle bei der Vermeidung von Indexproblemen.

VACUUM und VACUUM FULL

• VACUUM: Gibt von toten Tupeln belegten Speicherplatz frei und macht ihn für die Wiederverwendung verfügbar. Es verkleinert die Tabellen- oder Indexdateien auf der Festplatte nicht, ist aber entscheidend, um Bloat zu verhindern. Der autovacuum-Daemon erledigt dies normalerweise automatisch.

  VACUUM your_table;
  

• VACUUM FULL: Schreibt die gesamte Tabelle und ihre zugehörigen Indizes in eine neue Datenträgerdatei um, gewinnt maximalen Speicherplatz zurück und beseitigt Bloat. Es erhält jedoch eine ACCESS EXCLUSIVE-Sperre auf der Tabelle, blockiert alle Operationen und sollte mit äußerster Vorsicht verwendet werden. REINDEX wird oft für Index-Bloat bevorzugt.

  VACUUM FULL your_table;
  

ANALYZE

Der ANALYZE-Befehl sammelt Statistiken über den Inhalt von Tabellen in der Datenbank und speichert sie in pg_statistic. Der PostgreSQL-Abfrageplaner verwendet diese Statistiken, um intelligente Entscheidungen darüber zu treffen, wie Abfragen ausgeführt werden, einschließlich der Frage, ob ein Index verwendet werden soll oder nicht. Das Ausführen von ANALYZE nach erheblichen Datenänderungen (oder nach der Neuerstellung von Indizes) stellt sicher, dass der Planer aktuelle Informationen hat.

ANALYZE your_table;
-- Oder analysieren Sie die gesamte Datenbank:
ANALYZE;

Überwachung von Auto-Vacuum

Stellen Sie sicher, dass der autovacuum-Daemon läuft und richtig konfiguriert ist. Er ist für die automatische Durchführung von VACUUM- und ANALYZE-Operationen verantwortlich, die entscheidend sind, um Bloat zu verhindern und Statistiken aktuell zu halten. Falsch konfiguriertes autovacuum ist eine häufige Ursache für Leistungseinbußen.

Regelmäßige Wartungspläne

Proaktive Indexwartung ist besser als reaktive Fehlerbehebung. Legen Sie einen Zeitplan fest für:

• Überwachung der Indexnutzung und -größe: Identifizieren Sie potenziellen Bloat oder ungenutzte Indizes.
• Ausführen von REINDEX CONCURRENTLY: Für häufig aktualisierte oder gelöschte Tabellen oder nach erheblichen Datenmigrationen.
• Überprüfen von autovacuum-Protokollen und -Einstellungen: Stellen Sie sicher, dass es mit der Datenbankaktivität Schritt hält.

Testen und Backup

• Immer testen: Bevor Sie größere Wartungsarbeiten an einer Produktionsdatenbank durchführen, testen Sie sie gründlich in einer Staging- oder Entwicklungsumgebung, die Ihre Produktionseinrichtung widerspiegelt.
• Immer sichern: Halten Sie ein aktuelles, zuverlässiges Backup Ihrer Datenbank bereit, bevor Sie REINDEX-Operationen starten, insbesondere nicht-gleichzeitige oder solche, die auf ganze Tabellen/Datenbanken abzielen. Obwohl REINDEX im Allgemeinen sicher ist, ist ein beschädigtes Datenbank-Backup nutzlos.

Tipps und Warnungen zur Fehlerbehebung

• Speicherplatz: REINDEX-Operationen (insbesondere CONCURRENTLY) benötigen erheblichen temporären Speicherplatz – möglicherweise bis zur doppelten Größe des neu zu erstellenden Index. Stellen Sie sicher, dass Ihr Datenbankserver über ausreichend freien Speicherplatz verfügt.
• Leistungsauswirkungen: Selbst REINDEX CONCURRENTLY verbraucht während seines Betriebs CPU- und E/A-Ressourcen. Überwachen Sie die Leistung Ihres Systems sorgfältig, während es läuft.
• Identifizieren Sie die Ursachen: Erstellen Sie Indizes nicht einfach wiederholt neu, ohne zu verstehen, warum die Indizes aufgebläht oder beschädigt werden. Untersuchen Sie zugrunde liegende Probleme wie ineffiziente VACUUM-Einstellungen, hohe Transaktionsraten oder Hardwareprobleme.
• Indexerstellung vs. Neuerstellung: CREATE INDEX CONCURRENTLY ist das Äquivalent zu REINDEX INDEX CONCURRENTLY zum Erstellen neuer Indizes ohne Blockierung. Es folgt ähnlichen Prinzipien und hat ähnliche Einschränkungen.

Gute Indexwartung ist teils Befehlswissen, teils Zurückhaltung. REINDEX CONCURRENTLY ist ein nützliches Reparaturwerkzeug, aber wiederholte Neuerstellungen ohne Verständnis der Arbeitslast bedeuten normalerweise, dass derselbe Bloat zurückkommt. Verwenden Sie die obigen Befehle, um das Problem zu bestätigen, erstellen Sie das kleinste betroffene Objekt neu, das Sie können, und überprüfen Sie dann Autovacuum, Aktualisierungsmuster, Datenträgerzustand und Abfragepläne, damit Sie nicht im nächsten Monat die gleiche Notfallreparatur durchführen müssen.