Kategorien:	credativ® Inside

Zu viele Indizes in PostgreSQL® verlangsamen die Datenbank, weil jeder Index bei Schreiboperationen wie INSERT, UPDATE und DELETE aktualisiert werden muss. Das erhöht den I/O-Aufwand, belegt zusätzlichen Speicherplatz und kann den Query Planner in seiner Entscheidungsfindung beeinträchtigen. Dieser Artikel beantwortet die wichtigsten Fragen rund um den Index-Overhead in PostgreSQL und zeigt, wie Sie Ihre Datenbankstruktur gezielt optimieren.

Ab wann werden Indizes in PostgreSQL zum Problem?

Indizes werden in PostgreSQL zum Problem, sobald ihr Wartungsaufwand den Lesegewinn übersteigt. Das ist typischerweise der Fall, wenn Tabellen viele Schreiboperationen erfahren, Indizes selten oder gar nicht vom Query Planner genutzt werden oder wenn mehrere Indizes dieselben Spalten in ähnlicher Kombination abdecken. Es gibt keine universelle Grenze, ab der Indizes schaden, aber das Verhältnis von Lese- zu Schreiblast ist der entscheidende Faktor.

In schreibintensiven Umgebungen, zum Beispiel bei Logging-Tabellen, Event-Queues oder OLTP-Systemen mit hohem Transaktionsvolumen, kann bereits eine Handvoll überflüssiger Indizes zu messbaren Performance-Einbußen führen. Auch der Autovacuum-Prozess wird durch viele Indizes verlangsamt, da er bei der Bereinigung toter Tupel jeden Index einzeln durchlaufen muss. Wer die PostgreSQL-Datenbankoptimierung ernst nimmt, sollte Indizes daher regelmäßig auf ihre Notwendigkeit prüfen.

Wie verlangsamen zu viele Indizes Schreiboperationen?

Jeder Index in PostgreSQL muss bei jedem Schreibvorgang auf der zugehörigen Tabelle aktualisiert werden. Bei einem INSERT wird der neue Datensatz in alle vorhandenen Indizes eingetragen. Bei einem UPDATE werden betroffene Einträge in jedem relevanten Index gelöscht und neu geschrieben. Bei einem DELETE müssen die Einträge aus allen Indizes entfernt werden. Je mehr Indizes existieren, desto mehr Schreibvorgänge fallen pro Transaktion an.

Dieser Overhead wirkt sich auf mehreren Ebenen aus. Erstens steigt die CPU-Last, weil jedes Index-Update eine eigene Verarbeitungslogik erfordert. Zweitens erhöht sich der I/O-Aufwand, weil Indexseiten aus dem Speicher gelesen, modifiziert und zurückgeschrieben werden müssen. Drittens verlängern sich Transaktionen, was bei hohem gleichzeitigem Zugriff zu Lock-Konflikten führen kann. In der Praxis zeigt sich dieser Effekt besonders deutlich bei Bulk-Inserts oder Batch-Updates, die eigentlich schnell abgewickelt werden sollten.

Welche Arten von Indizes verursachen den größten Overhead?

Nicht alle Indextypen belasten PostgreSQL gleich stark. GIN-Indizes und GiST-Indizes, die häufig für Volltextsuche oder geometrische Daten eingesetzt werden, sind bei der Aktualisierung deutlich aufwendiger als einfache B-Tree-Indizes. Partial Indexes und Funktionsindizes können ebenfalls teuer sein, wenn die zugrunde liegende Bedingung oder Funktion bei jedem Schreibvorgang neu ausgewertet werden muss.

Besonders problematisch sind Indizes auf Spalten mit hoher Schreibfrequenz und niedriger Selektivität, also Spalten, die wenige unterschiedliche Werte enthalten, wie Status-Felder oder Boolean-Spalten. Solche Indizes verursachen erheblichen Wartungsaufwand, bieten dem Query Planner aber kaum Mehrwert, weil er bei wenigen unterschiedlichen Werten ohnehin einen Sequential Scan bevorzugt. Mehrspaltigen Indizes sollte man ebenfalls kritisch begegnen, wenn die führende Spalte selten in WHERE-Klauseln vorkommt.

Wie findet man ungenutzte oder redundante Indizes in PostgreSQL?

PostgreSQL bietet mit der System-View pg_stat_user_indexes eine direkte Möglichkeit, ungenutzte Indizes zu identifizieren. Die Spalte idx_scan zeigt, wie oft ein Index seit dem letzten Statistik-Reset verwendet wurde. Indizes mit einem Wert von null oder nahe null über einen repräsentativen Beobachtungszeitraum sind Kandidaten für die Entfernung.

Für redundante Indizes, also Indizes, deren Spaltenabdeckung von einem anderen Index vollständig übernommen wird, hilft eine Abfrage auf pg_index kombiniert mit pg_attribute. Dabei lässt sich prüfen, ob ein Index nur eine Teilmenge der Spalten eines umfassenderen Indexes abdeckt. Praktische Abfragen dafür sind in der PostgreSQL-Dokumentation beschrieben und können als Teil eines regelmäßigen Datenbankmonitorings automatisiert werden.

Wichtig: Statistiken werden beim Neustart des PostgreSQL-Servers zurückgesetzt. Ein aussagekräftiger Beobachtungszeitraum von mindestens mehreren Wochen, besser einem vollständigen Geschäftszyklus, ist Voraussetzung für verlässliche Aussagen über die tatsächliche Indexnutzung.

Wann sollte man einen Index in PostgreSQL entfernen?

Ein Index sollte entfernt werden, wenn er nachweislich nicht genutzt wird, einen anderen Index vollständig dupliziert oder wenn der Schreiboverhead seinen Lesegewinn überwiegt. Vor der Entfernung empfiehlt sich ein zweistufiges Vorgehen: Zunächst den Index mit CREATE INDEX CONCURRENTLY als Test-Szenario beobachten oder ihn mit ALTER INDEX … DISABLE (in PostgreSQL über Umwege realisierbar) temporär außer Betrieb nehmen, bevor er endgültig gelöscht wird.

PostgreSQL bietet seit Version 12 die Möglichkeit, Indizes als INVALID zu markieren, ohne sie sofort zu löschen. Eine sichere Vorgehensweise ist es, den Index zunächst umzubenennen und über einen definierten Zeitraum zu beobachten, ob Abfragen langsamer werden. Erst wenn keine negativen Auswirkungen festgestellt werden, sollte der Index mit DROP INDEX CONCURRENTLY entfernt werden, um laufende Transaktionen nicht zu blockieren.

Wie viele Indizes pro Tabelle sind in PostgreSQL sinnvoll?

Eine pauschale Zahl gibt es nicht, aber als Orientierung gilt: Für die meisten OLTP-Tabellen sind drei bis fünf Indizes ein vernünftiger Rahmen. Dazu zählen der automatisch erstellte Primary-Key-Index sowie Indizes auf Foreign Keys und häufig gefilterten Spalten. Tabellen mit rein lesenden Zugriffen, wie Data-Warehouse-Tabellen, können mehr Indizes sinnvoll nutzen, während schreibintensive Tabellen mit möglichst wenigen Indizes auskommen sollten.

Entscheidend ist nicht die absolute Anzahl, sondern die Qualität und Ausrichtung jedes einzelnen Indexes. Ein Index, der gezielt auf die häufigsten Abfragemuster zugeschnitten ist, leistet mehr als fünf Indizes, die sich überschneiden oder selten genutzt werden. Die Faustregel lautet: Jeder Index sollte eine konkrete Frage beantworten, nämlich welche Abfrage er beschleunigt und wie oft diese Abfrage tatsächlich ausgeführt wird. Wer seine Abfragemuster kennt und regelmäßig analysiert, trifft fundierte Entscheidungen zur PostgreSQL-Performance-Optimierung.

Wie credativ® bei der PostgreSQL-Indexoptimierung unterstützt

Übermäßige oder falsch konfigurierte Indizes gehören zu den häufigsten Ursachen für Performance-Probleme in produktiven PostgreSQL-Umgebungen. Wir bei credativ® unterstützen Unternehmen dabei, ihre PostgreSQL-Instanzen gezielt zu analysieren und dauerhaft leistungsfähig zu halten. Unser PostgreSQL® Competence Center bietet dafür konkrete Leistungen:

Index-Analyse und Bereinigung: Wir identifizieren ungenutzte, redundante und schädliche Indizes auf Basis realer Nutzungsstatistiken Ihrer Umgebung.
Query-Tuning: Wir analysieren langsame Abfragen und erarbeiten gezielte Indexstrategien, die Lese- und Schreiblast in Balance bringen.
Monitoring-Umgebungen: Wir richten maßgeschneiderte Monitoring-Lösungen ein, die Index-Overhead und Datenbankperformance kontinuierlich im Blick behalten.
Workshops und Schulungen: Wir vermitteln Ihren Teams das notwendige Know-how, um Indexentscheidungen eigenständig und fundiert zu treffen.
Operative Unterstützung: Von der einmaligen Beratung bis zur vollständigen Übernahme des PostgreSQL-Betriebs bieten wir flexible Modelle.

Möchten Sie wissen, welche Indizes in Ihrer PostgreSQL-Umgebung tatsächlich helfen und welche bremsen? Kontaktieren Sie uns für eine unverbindliche Ersteinschätzung durch unsere PostgreSQL-Spezialisten.

PostgreSQL® ist eine Marke der PostgreSQL Community Association of Canada. credativ® ist Competence Center für PostgreSQL®. Die Nennung dient ausschließlich der sachlichen Beschreibung von Dienstleistungen von credativ®. Es besteht keine geschäftliche Verbindung zu den genannten Markeninhabern über die beschriebene Partnerschaft hinaus.

Wie wirken sich zu viele Indizes auf PostgreSQL aus?