# Indizes in PostgreSQL - Ein Überblick Stefan Keller, 2022, aktualisiert Dezember 2025 ## Einleitung Indizes sind eine gängige Methode zur Verbesserung der Datenbank-Performanz. Ein Index ermöglicht es dem Datenbankserver, bestimmte Zeilen schneller zu finden und abzurufen, als dies ohne einen Index möglich wäre. Indizes verursachen jedoch auch einen zusätzlichen Aufwand für das gesamte Datenbanksystem, weshalb sie sinnvoll eingesetzt werden sollten. Die PostgreSQL-Standard-Installation enthält bereits mehrere Indexmethoden (B-Tree, Hash, GiST, SP-GiST, GIN, BRIN); der Default ist B-Tree. Weitere Indizes können als Erweiterungen mit "CREATE EXTENSION" installiert werden. Wieder andere Indexmethoden müssen zuerst von separaten Repositories heruntergeladen und installiert werden. Experimentelle Indexe schliesslich verlangen oft, dass man sie selber kompiliert. Dieses Dokument ist eine Zusammenstellung von in PostgreSQL verfügbaren Indizes und ihren Eigenschaften. Die Idee des Dokuments ist, dass man sich das Anwendungsfeld (Query-Typ) und die Datentypen anschaut und mit dem eigenen Schema vergleicht, um dann Hinweise zu erhalten, welche Indizes in Frage kommen. Die gewählten Indizes können parametrisiert werden (mit kombinierten Attributen, mit Ausdrücken, mit zusätzlichen "Operator Classes" etc.), wie in der PostgreSQL-Dokumentation zu "Index Types" beschrieben. ## Eigenschaften von Indizes Ein Index kann aufgrund seines Algorithmus einer Kategorie zugeordnet werden und aufgrund seiner Implementierung einem Anwendungsfeld (Query-Typ, Datentyp). Folgende Query-Typen werden u.a. unterschieden: Equality-Query, Range-Query, Point-Query. ## Nutzung von Indizes (Syntax) Die allgemeine Syntax zur Erstellung eines Index lautet: ```sql CREATE INDEX [index_name] ON table_name USING index_method (column_name [operator_class]); ``` ### Beispiel 1: Einfachste Syntax mit B-Tree-Defaults Die einfachste Form erstellt einen B-Tree-Index (Standard) ohne explizite Angabe der Index-Methode: ```sql CREATE INDEX ON users (email); ``` PostgreSQL generiert automatisch einen Index-Namen (z.B. `users_email_idx`) und verwendet B-Tree als Standard-Index-Methode. ### Beispiel 2: Syntax mit explizitem Index-Namen Bei wichtigen oder komplexen Indizes empfiehlt sich die explizite Angabe eines aussagekräftigen Index-Namens: ```sql CREATE INDEX idx_users_created_at ON users USING btree (created_at); ``` Hier wird explizit B-Tree als Index-Methode angegeben, was bei B-Tree jedoch optional ist. ### Beispiel 3: Syntax mit Operator-Klasse für JSONB-Datentypen Bei JSONB-Datentypen können verschiedene Operator-Klassen verwendet werden, abhängig von den Query-Anforderungen: ```sql CREATE INDEX idx_events_data ON events USING gin (data); ``` Dieser Index verwendet die Standard-Operator-Klasse für JSONB und unterstützt alle JSONB-Operatoren (`@>`, `?`, `?&`, `?|`). Für spezialisierte Enthält-Queries (`@>`) ist die `jsonb_path_ops` Operator-Klasse effizienter: ```sql CREATE INDEX idx_events_data_path ON events USING gin (data jsonb_path_ops); ``` Dieser Index ist ca. 30% kleiner, unterstützt aber nur den `@>` Operator. Für Queries auf spezifische JSON-Pfade können Expression-Indizes erstellt werden: ```sql CREATE INDEX idx_events_user_id ON events USING btree ((data->>'user_id')); ``` Hier wird ein B-Tree-Index auf den extrahierten `user_id`-Wert aus dem JSON-Dokument erstellt, nützlich für `WHERE data->>'user_id' = '123'` Queries. Gruppierung der Indizes von PostgreSQL in Kategorien (siehe PostgreSQL-Dokumentation "Index Types"): - Baumindizes: B-Tree (built-in), GiST (Generalized Search Tree, built-in), SP-GiST (built-in) - Hash-Indizes: Hash (built-in, seit PG 10 WAL-logged und crash-safe) - Inverted Indizes: GIN (Generalized Inverted Index, built-in), RUM (experimentell) - Block-Range-Indizes: BRIN (Block Range Index, built-in) - Spezialisierte / probabilistische Indizes: Bloom-Index (Bloom-Filter, Extension), Vektor-Indizes (pgvector: IVFFlat, HNSW, Extension), weitere Machine-Learning-Indizes (experimentell, Extension) ## Indizes-Eigenschaften in tabellarischer Übersicht | Name | Kategorie | Anwendungsfeld | Erstellzeit | Diskplatz | Operatoren (Op.) | |------|-----------|----------------|-------------|-----------|------------------| | B-Tree | (balancierter) Baumindex | Equality-Queries, Range-Queries, Sortierung, Pattern-Matching (mit passenden Operator-Klassen). Unterstützt INCLUDE-Klausel für Covering Indexes (seit PG 11). | mässig | mittel | Equality, Vergleichsoperatoren, Pattern-Matching. | | GiST | (balancierter) Baumindex | Neuere Datentypen wie Geometrie, Text, binäre Datentypen, Bereiche, Ähnlichkeitssuche. | mässig bis langsam | mittel bis gross | Verschiedene. Framework zur Entwicklung eigener Indizes. | | SP-GiST | (nicht balancierter, space-partitioned) Baumindex | Alternative zu GiST für hierarchisch/raumbezogen partitionierbare Daten (z.B. IP-Präfixe, Quadtrees, Tries). | mässig bis langsam | mittel | Verschiedene. | | Hash | Hash | Equality- / Point-Queries auf einer Spalte. Seit PG 10 WAL-logged und produktionsreif. | schnell bis mässig | klein bis mittel | Nur Equality-Operator, keine Range-Queries. | | GIN | Inverted Index | Für Text-Datentypen. Abfragen nach Werten innerhalb zusammengesetzter Datentypen wie hstore, array, JSONB, tsvector oder range-Datentypen. | langsam | gross | Spezielle Op.-Klassen für Arrays, JSONB/JSONPath (inkl. jsonb_path_ops), tsvector, sowie weitere; Framework zur Entwicklung eigener Indizes. | | RUM | Inverted Index (Extension) | Optimierung des GIN für Text-Datentypen und für Queries mit zusammengesetzten Attributen; Relevanz-Ranking. | langsam bis sehr langsam | gross bis sehr gross | Spezielle Op. ergänzend zu den Op. von GIN für Datentypen Arrays und tsvector/tsquery. | | BRIN | Block-Range-Index | Platzsparend bei grossen Datenmengen (>1M Tupel), natürlich-sequenzielle Daten (v.a. Zeit, ID). Gut bei INSERTs und Queries mit grossen Ergebnismengen. Verschiedene Op.-Klassen: minmax, inclusion, bloom (seit PG 14). | sehr schnell | sehr klein | Vergleichsoperatoren; Min/Max- und Inklusionsoperatoren (abhängig vom Datentyp und Op.-Klasse). | | Bloom* | Bloom-Filter-Index (Extension) | Platzsparend und performant für das Prüfen, ob eine Kombination von Werten existiert. Geeignet für Tabellen mit vielen Attributen. | schnell | klein | Equality-Operatoren und Operator-Klassen für Datentypen int4 und text. | | pgvector (IVFFlat/HNSW) | Vektorindex (ANN, Extension) | Vektor-Embeddings, Ähnlichkeitssuche (k-nächste Nachbarn), KI-Anwendungen (z.B. RAG, Recommendations). Bei kleinen Datensätzen (<10k Vektoren) oft sequentieller Scan ausreichend. | mässig bis langsam (HNSW tendenziell langsamer im Aufbau, aber schneller bei Queries) | mittel bis gross | Distanz-/Ähnlichkeitsoperatoren (<->, <#>, <=>) mit Operator-Klassen vector_l2_ops, vector_ip_ops, vector_cosine_ops. | \* Bloom-Indizes sind als contrib-Modul verfügbar und werden mit "CREATE EXTENSION bloom;" installiert. pgvector-Indizes werden über die Extension "vector" bereitgestellt ("CREATE EXTENSION vector;"). ## Spezielle Index-Varianten ### Covering Indexes (INCLUDE-Klausel) Seit PostgreSQL 11 unterstützen B-Tree-Indizes die INCLUDE-Klausel, mit der zusätzliche Spalten im Index gespeichert werden können, ohne dass sie Teil des Index-Keys sind. Dies ermöglicht Index-Only-Scans für Queries, die auch auf nicht-indexierte Spalten zugreifen. **Syntax:** `CREATE INDEX idx ON table (indexed_col) INCLUDE (additional_col);` **Use Case:** E-Commerce-Produkttabelle mit Index auf `category_id` und INCLUDE `(price, name)` ermöglicht schnelle Kategorieabfragen ohne Tabellenzugriff. ### Partielle Indizes (Partial Indexes) Ein partieller Index enthält nur Zeilen, die eine WHERE-Bedingung erfüllen. Dies reduziert die Indexgrösse und verbessert die Performance für Queries auf dieser Teilmenge. **Syntax:** `CREATE INDEX idx ON table (col) WHERE condition;` **Use Case:** Index auf `status = 'active'` in einer Benutzertabelle, wo 95% der Nutzer inaktiv sind und Queries fast immer nur aktive Nutzer betreffen. ### Expression Indexes (Funktionsindizes) Expression Indexes indexieren das Ergebnis einer Funktion oder eines Ausdrucks statt direkt eine Spalte. Sie sind nützlich für häufig verwendete Transformationen. **Syntax:** `CREATE INDEX idx ON table (LOWER(email));` oder `CREATE INDEX idx ON table (EXTRACT(YEAR FROM created_at));` **Use Case:** Case-insensitive Suche in E-Mail-Adressen oder Filterung nach Jahr in Datumsfeldern ohne wiederholte Funktionsaufrufe in jeder Query. ## Datentypen mit passenden Indizes Es gibt folgende Gruppen von Datentypen in PostgreSQL: - Numerische Datentypen - Zeichenketten-Datentypen (text, varchar, tsvector/tsquery) - Binäre Datentypen (bytea) - Date/Time-Datentypen - Boolean-Datentypen - Aufzähl-Datentypen - Arrays - Währungs-Datentypen (MONEY, NUMERIC) - Key-Value-Datentypen (hstore) - Dokument-Datentypen (JSON/JSONB/JSONPath) - Raumbezogene Datentypen (PostGIS geometry/geography) - Embedding-Datentypen (Vektoren, z.B. vector mittels pgvector-Extension) ### 1. Numerische Datentypen Integer, Floating-Point Numbers => B-Tree (Standard), Hash (nur Equality), BRIN (für grosse, natürlich sortierte Tabellen). **Use Case:** BRIN-Index auf `order_id` in append-only Order-Tabelle mit Milliarden Zeilen spart 99% Speicherplatz gegenüber B-Tree. ### 2. Zeichenketten-Datentypen Char, Text, Varchar => B-Tree (Equality, Sortierung), GIN/GiST (mit pg_trgm für Fuzzy-Suche und LIKE-Queries mit '%pattern%'), Bloom (Multi-Spalten-Gleichheit). **Use Case:** GIN-Index mit `gin_trgm_ops` für Produktnamen ermöglicht schnelles `WHERE name ILIKE '%such%'` auch bei Millionen Produkten. Tsvector, Tsquery => GIN (Standard, schnelle Suche), RUM (Relevanz-Ranking mit Positionsinformationen), GiST (Alternative, besser bei häufigen Updates). **Use Case:** GIN-Index auf tsvector für Volltextsuche in Dokumenten-Datenbank mit Millionen Artikeln. ### 3. Binäre Datentypen Bytea. Direkte Indizierung nur eingeschränkt sinnvoll (Grössen-Limit der Indexeinträge). Typisch: Ausdrucksindex auf Hash (z.B. `md5(bytea)` oder `sha256(bytea)`) => B-Tree oder Hash. **Use Case:** Expression-Index `CREATE INDEX ON files (md5(content))` für Deduplizierung von Datei-Uploads. ### 4. Date/Time-Datentypen Date, Time, Timestamp, Timestamptz, Interval => B-Tree (Standard, für Range-Queries), BRIN (grosse Zeitreihen-Tabellen mit chronologischer Einfügereihenfolge), Hash (nur Equality, selten nötig). **Use Case:** BRIN-Index auf `created_at` in IoT-Sensor-Tabelle mit append-only Log-Daten. ### 5. Boolean-Datentypen Boolean. Eigener Index meist nicht sinnvoll (nur zwei Werte, geringe Selektivität). Eher: partieller Index, z.B. `WHERE active = true`, wenn eine Ausprägung selten ist. **Use Case:** Partieller Index `WHERE deleted = false` in Soft-Delete-Tabelle, wo 98% der Zeilen nicht gelöscht sind. ### 6. Aufzähl-Datentypen Benutzerdefinierter Typ mit `CREATE TYPE name AS ENUM ('value1', 'value2')`. => B-Tree (Standard), oft mit partiellem Index auf seltenen Enum-Werten. **Use Case:** Partieller Index `WHERE order_status = 'pending'` bei Order-Status-Enum, wo die meisten Orders 'completed' oder 'cancelled' sind. ### 7. Arrays Array => GIN (Enthält-Abfragen mit `@>`, `<@`, `&&` Operatoren für Überschneidung), B-Tree (Gleichheit des ganzen Arrays, selten verwendet). **Use Case:** GIN-Index auf `tags[]` in Blog-Post-Tabelle für schnelles `WHERE tags @> ARRAY['postgresql', 'performance']`. ### 8. Währungs-Datentypen PostgreSQL kennt einen eigenen Datentyp MONEY, doch empfohlen wird z.B. NUMERIC(10,2), oder aber BIGINT (in Rappen/Cent) => B-Tree. **Use Case:** B-Tree-Index auf `price` (als NUMERIC) für E-Commerce-Preisfilter mit Range-Queries. ### 9. Key-Value-Datentypen hstore => GIN (Standard für Enthält-Queries mit `?`, `@>` Operatoren), GiST (Alternative bei sehr häufigen Updates). **Use Case:** GIN-Index auf hstore-Spalte `attributes` für Produkt-Metadaten mit dynamischen Schlüsseln. ### 10. Dokument-Datentypen JSON, JSONB, JSONPath. JSONB => GIN (Standard-Op.-Klasse für allgemeine Enthält-Queries, oder `jsonb_path_ops` für `@>` Operator - 30% kleiner aber nur ein Operator), B-Tree (Gleichheit des ganzen Dokuments), Expression-Index auf spezifische Pfade (z.B. `CREATE INDEX ON table ((data->>'email'))`), BRIN (zusätzlich auf Zeit/ID-Spalte), Bloom (Multi-Spalten-Filtern). **Use Case:** GIN-Index mit `jsonb_path_ops` auf `metadata` für API-Response-Cache mit `WHERE metadata @> '{"user_id": 123}'`. JSON => meist vorher in strukturierte Spalten oder JSONB transformieren, da JSON als Text gespeichert wird und nicht effizient indexierbar ist. ### 11. Raumbezogene Datentypen Die in PostgreSQL mitgelieferten Geometrie-Typen (Point, Line, Lseg, Box, Polygon, Circle, Path) werden selten verwendet. De-facto-Standard ist die PostGIS-Extension. Geometry, Geography (PostGIS) => GiST (Standard, R-Tree-ähnlich für räumliche Operatoren wie `ST_Intersects`, `ST_Contains`), SP-GiST (alternative räumliche Indexvarianten wie Quad-Tree). **Use Case:** GiST-Index auf `location` (PostGIS geometry) für Umkreissuche in Restaurant-App mit `ST_DWithin(location, :point, :radius)`. ### 12. Embedding-Datentypen (Vektoren) vector(n) (pgvector-Extension). Vektor-Embeddings (Text, Bild, Audio) => pgvector-Indizes (IVFFlat - schnellerer Aufbau, HNSW - bessere Query-Performance aber langsamer im Aufbau und mehr Speicher). Kleine Tabellen (<10k Vektoren) => sequentielle Suche mit `ORDER BY embedding <-> :query LIMIT k` oft ausreichend schnell ohne Index. **Use Case:** HNSW-Index auf `text_embedding` für semantische Suche in Dokumenten-Datenbank (RAG-System) mit Millionen Embeddings. ## Third-Party Index-Erweiterungen (Extensions) Neben den im Core vorhandenen Indexmethoden (B-Tree, Hash, GiST, GIN, SP-GiST, BRIN) stellt PostgreSQL zahlreiche Erweiterungen bereit, die über "CREATE EXTENSION" aktiviert werden. Im Folgenden einige wichtige Index-nahe Extensions. ### Bloom-Index (Extension "bloom") Die Extension "bloom" implementiert Bloom-Filter-basierte Indizes für effiziente Multi-Spalten-Filter mit Gleichheitsbedingungen. Sie ist besonders geeignet für Tabellen mit vielen Attributen und wechselnden Abfragemustern, wo traditionelle Multi-Column-B-Tree-Indizes zu gross oder zu spezifisch wären. **Use Case:** Bloom-Index auf 10 Produkt-Attributen in E-Commerce-Katalog, wo Nutzer beliebige Kombinationen filtern. ### pgvector mit ANN-Indextypen (IVFFlat, HNSW) Die Extension "vector" (pgvector) erweitert PostgreSQL um den Datentyp vector(n) für Embeddings sowie approximate-nearest-neighbor-Indizes IVFFlat und HNSW. HNSW bietet sehr geringe Query-Latenz (oft 10-100x schneller als IVFFlat) bei höherem Speicherverbrauch und tendenziell längerer Aufbauzeit als IVFFlat. Beide Indextypen unterstützen Distanz-Operatoren wie `<->` (L2-Distanz), `<#>` (Inner Product) oder `<=>` (Cosine-Distanz). HNSW wurde später hinzugefügt und ist seit pgvector 0.5.0 verfügbar. **Use Case:** HNSW-Index für k-nearest-neighbor-Suche in Bild-Embeddings einer Foto-Sharing-Plattform mit Sub-Millisekunden-Latenz. ### Weitere spezialisierte Index-Erweiterungen #### pg_trgm für Textsuche Extension "pg_trgm" für trigramm-basierte Textähnlichkeit und Pattern-Matching. Typisch in Kombination mit GIN- oder GiST-Indizes (`gin_trgm_ops`, `gist_trgm_ops`). Ermöglicht effiziente LIKE-Queries mit führenden Wildcards (`%pattern%`) und Fuzzy-Matching. **Syntax:** `CREATE EXTENSION pg_trgm; CREATE INDEX idx ON table USING GIN (col gin_trgm_ops);` **Use Case:** Autovervollständigung in Suchleiste mit `WHERE title ILIKE '%eingabe%'` auf Millionen Artikeln. #### HypoPG für Index-Analyse Extension "hypopg" für hypothetische (virtuelle) Indizes. Damit lassen sich Query-Pläne mit virtuellen Indizes testen, ohne dass die Indizes physisch angelegt werden müssen. Nützlich für Index-Optimierung und Kapazitätsplanung. **Use Case:** Testen verschiedener Index-Strategien auf Produktions-Replikat ohne Schreiblast und Speicherverbrauch. #### pg_repack für Index-Maintenance Extension "pg_repack" für Online-Reorganisation von Tabellen und Indizes (Beseitigung von Bloat) mit minimalen Locks. Eignet sich für Wartungsarbeiten in OLTP-Systemen. **Use Case:** Nächtliche Bloat-Beseitigung in stark fragmentierten Indizes ohne Downtime in 24/7-System. ## Wichtige Hinweise zur Nutzung ### Clustered vs. Non-Clustered Indexes PostgreSQL kennt im Gegensatz zu anderen Datenbanksystemen (z.B. SQL Server, MySQL InnoDB) keine integrierten clustered indexes, bei denen die Tabellendaten permanent in der Reihenfolge eines Index physisch gespeichert werden. Die einzige Ausnahme bildet der B-Tree-Index mit INCLUDE-Klausel (Covering Index), der zusätzliche Spalten im Index speichert. PostgreSQL bietet jedoch das `CLUSTER`-Kommando, um Tabellendaten temporär nach einem Index zu sortieren: ```sql CLUSTER angestellter USING angestellter_pkey; ``` Dies ordnet die Tabelle `angestellter` physisch nach dem Index `angestellter_pkey` und kann die Performance von Range-Scans erheblich verbessern. **Wichtig:** Diese physische Sortierung ist nicht persistent - bei nachfolgenden INSERTs, UPDATEs oder DELETEs wird die Clustering-Ordnung nicht aufrechterhalten. Das CLUSTER-Kommando muss periodisch wiederholt werden (z.B. in nächtlichen Wartungsfenstern), um die physische Sortierung beizubehalten. Während des CLUSTER-Vorgangs wird ein exklusiver Lock auf die Tabelle gesetzt. ### Concurrent Index Creation Bei der Erstellung von Indizes in Produktionssystemen sollte die `CONCURRENTLY`-Option verwendet werden, um Schreibblockaden zu vermeiden: ```sql CREATE INDEX CONCURRENTLY idx_name ON table (column); ``` Dies dauert zwar länger, vermeidet aber das Blockieren von INSERTs, UPDATEs und DELETEs während der Index-Erstellung. ### Index-Nutzung überprüfen Die System-View `pg_stat_user_indexes` zeigt, wie oft jeder Index tatsächlich verwendet wird. Ungenutzte Indizes sollten entfernt werden, da sie Speicher verschwenden und Schreiboperationen verlangsamen. ## Danksagung, Kontakt, Lizenz Teile dieses Dokuments sind der Seminararbeit "Übersicht über Datenbank-Index-Typen in bekannten Datenbanksystemen mit PostgreSQL-Benchmark" von Vladimir Brazhnik (2021) entnommen: Kudos. Kontakt: Institut für Software, Departement Informatik, OST Campus Rapperswil, E-Mail. Lizenz CC BY 3.0 CH.