- PostgreSQL-Indizes sind eine zentrale Struktur zur Beschleunigung des Datenzugriffs und verbessern die Query-Performance, indem sie die Menge der von der Festplatte zu lesenden Daten reduzieren
- Indizes stehen in verschiedenen Formen wie Btree, Hash, BRIN, GIN, GiST, SP-GiST zur Verfügung und sind jeweils für unterschiedliche Dateneigenschaften und Query-Muster optimiert
- Indizes bringen verschiedene Kosten mit sich, darunter Festplattenspeicher, Schreib-Performance, Komplexität des Query-Planers und Speichernutzung
- Mit erweiterten Funktionen wie partiellen Indizes, Mehrspaltenindizes, Covering-Indizes und Ausdrucksindizes lässt sich die Effizienz in bestimmten Situationen maximieren
- Die richtige Auswahl und Verwaltung von Indizes wird als Schlüsselelement der PostgreSQL-Performance-Optimierung hervorgehoben
Grundkonzept von Indizes
- Ein Index ist eine Struktur, mit der die Datenbank die von der Festplatte gelesene Datenmenge reduziert und so Queries beschleunigt
- Auch Primärschlüssel, Unique Keys und Exclusion Constraints werden über Indizes umgesetzt
- Ein Index ist effektiv, wenn das Query-Ergebnis weniger als 15–20 % der gesamten Tabelle umfasst; darüber kann ein Sequential Scan effizienter sein
- PostgreSQL stellt standardmäßig 6 Indextypen bereit; über Erweiterungen können weitere Typen verwendet werden
- Jeder Index verknüpft Schlüsselwerte mit der zugehörigen Datenposition (TID)
Auf der Festplatte gespeicherte Datenstruktur
- PostgreSQL-Tabellen werden als Heap-Dateien gespeichert und bestehen aus 8-KB-Seiten
- Jede Zeile (Tuple) wird ohne feste Reihenfolge gespeichert, und ihre interne Adresse wird durch ctid (current tuple id) identifiziert
- Beispiel:
(0,1) bedeutet das erste Tuple auf Seite 0
- Indizes verknüpfen diese Heap-Positionen (
ctid) in einer Baumstruktur und ermöglichen so eine schnelle Suche
Wie Indizes den Datenzugriff beschleunigen
- Ohne Index führt PostgreSQL einen Sequential Scan aus und liest alle Seiten
- Im Beispiel-Query zum Finden von
name='Ronaldo' werden 6272 Seiten gelesen, was 265 ms dauert
- Nach dem Hinzufügen eines Indexes wird auf einen Index Scan umgestellt, der nur 4 Seiten liest und in 0,077 ms abgeschlossen ist
- Der Index ordnet Werte
ctid zu und findet so schnell nur die benötigten Zeilen
- Die Größe einer Indexdatei kann der Tabellengröße ähneln (z. B. 30 MB Tabelle → 30 MB Index)
Kostenfaktoren von Indizes
- Neben der Leistungssteigerung bringen Indizes auch verschiedene Belastungen mit sich
Festplattenspeicher
- Indizes belegen separaten Speicherplatz und können größer als die Tabelle werden
- Zusätzliche Kosten entstehen bei Backup, Replikation und Disaster Recovery
- Die Speichereffizienz kann durch partielle Indizes, Mehrspaltenindizes, BRIN usw. verbessert werden
Schreibvorgänge
- Bei
UPDATE, INSERT und DELETE entsteht Overhead für die Indexaktualisierung, wenn sich indexierte Spalten ändern
Query-Planer
- Je mehr Indizes vorhanden sind, desto mehr Optionen muss der Planer berücksichtigen, was die Zeit für die Erstellung des Query-Plans erhöht
Speichernutzung
- Indexseiten werden in den shared buffers geladen und gecacht; je mehr Indizes vorhanden sind, desto höher die Speicherbelastung
- Aufgrund der Größenbeschränkung von Btree-Knoten nimmt bei größeren Spalten die Baumtiefe zu
- Auch bei Sortierung, Mehrspalten-Scans, Vacuum, Reindex usw. wird zusätzlich work memory verwendet
Wichtige Indextypen
Btree
- Die Standard-Indexstruktur von PostgreSQL, ein universeller Index, der in den meisten DBMS verwendet wird
- Unterstützt schnelle Suche mit einer Zeitkomplexität von O(log n)
- Balancierte Baumstruktur, bei der alle Blattknoten dieselbe Tiefe haben
- Vorteilhaft für ORDER BY- und JOIN-Operationen und wird für Primärschlüssel- und Unique-Key-Constraints verwendet
- Interne Knoten speichern Zeiger auf untergeordnete Knoten, Blattknoten speichern Schlüssel und Heap-Zeiger
- Über linke und rechte Knotenzeiger ist eine bidirektionale Traversierung möglich
Nutzung mehrerer Indizes
- PostgreSQL kann mehrere Indizes über Bitmap-AND/OR-Operationen kombinieren, um zusammengesetzte Bedingungen zu verarbeiten
- Beispiel: Bei der Bedingung
age=30 AND login_count=100 werden die Bitmaps zweier Indizes kombiniert
Mehrspaltenindizes
- Mehrere Spalten können in einem einzigen Index gebündelt werden, was Speicher spart und die Geschwindigkeit erhöht
- Allerdings ist die Spaltenreihenfolge wichtig, und der Index kann nur für Bedingungen genutzt werden, die von links beginnend übereinstimmen
Partielle Indizes
- Mit einer Bedingung werden nur bestimmte Zeilen indiziert
- Geringere Indexgröße, bessere RAM-Eignung und schnellere Abfragen
- Beispiel:
create index on rules(status) where status='enabled';
- Nützlich bei unausgewogener Werteverteilung (
status <> 'TODO' usw.)
Covering-Indizes
- Wenn alle für eine Query benötigten Spalten im Index enthalten sind, kann das Ergebnis ohne Heap-Zugriff zurückgegeben werden (index-only scan)
create index abc_cov_idx on bar(a, b) including c;- Speichereffizienter als ein Mehrspaltenindex
Ausdrucksindizes
- Es werden nicht Spaltenwerte selbst, sondern Funktions- oder Ausdrucksergebnisse indiziert
- Beispiel:
CREATE INDEX idx_lower_name ON customers (lower(name));
- Nützlich für Suchen nach transformierten Werten wie
LOWER(name)
- Es können nur immutable Funktionen verwendet werden
Hash
- Ein auf einer Hashmap-Struktur basierender Index, der bei langen Strings oder UUIDs speichereffizient ist
- Speichert 32-Bit-Hashcodes und reduziert dadurch die Größe
- Unterstützt nur Gleichheitsvergleiche (=); Sortierung oder Mehrspaltenindizes sind nicht möglich
- Bei gleichmäßiger Hash-Verteilung kann eine schnellere Leseleistung als Btree möglich sein
- Laut offizieller Dokumentation reduzieren Hash-Indizes durch direkten Zugriff auf Bucket-Seiten den I/O bei großen Tabellen
BRIN (Block Range Index)
- Ein Index, der pro Blockbereich nur Minimal- und Maximalwerte speichert
- Sehr kompakt und cache-freundlich
- Geeignet für große, append-only und Time-Series-Daten
- Wenn Zeilen häufig aktualisiert werden, sinkt die Effizienz durch MVCC-bedingte Duplikatspeicherung
- Mit der Einstellung
pages_per_range lässt sich der Trade-off zwischen Genauigkeit und Größe anpassen
GIN (Generalized Inverted Index)
- Ein Index, der sich für die Suche in zusammengesetzten Daten eignet
- Unterstützt die Suche nach bestimmten Elementen in Text, Arrays, JSONB usw.
- Verwendet typspezifische Strategien (opclass)
- Für JSON wird die Verwendung mit JSONB-Spalten, für Text mit tsvector oder der Erweiterung pg_trgm empfohlen
GiST & SP-GiST
- Generalized Search Tree (GiST) und Space-Partitioned GiST (SP-GiST) sind Frameworks zur Implementierung von Indizes für bestimmte Datentypen
- GiST unterstützt balancierte Bäume, SP-GiST unausgeglichene Strukturen
- Einsatz bei Geodaten, inet, Bereichen und Textvektoren
- GIN bietet schnelle Abfragen, GiST hat geringere Kosten für Aufbau und Pflege
- Bei der Volltextsuche sollte je nach Anforderungen zwischen beiden Ansätzen gewählt werden
Fazit
- Indizes sind ein Kernelement der PostgreSQL-Performance-Optimierung, und das Gleichgewicht zwischen schnellerem Lesen sowie Schreib- und Speicherkosten ist entscheidend
- Wenn der passende Indextyp entsprechend den Dateneigenschaften und Query-Mustern gewählt wird, ist ein schneller und effizienter Datenbankbetrieb möglich
- Ein geeignetes Indexdesign ist ein wesentlicher Faktor für die Skalierbarkeit und Stabilität großer Systeme
Noch keine Kommentare.