Array-Indexierung: GIN- und BTREE-Indizes erstellen

Stell dir vor, du hast eine Tabelle mit Millionen von Einträgen, und eine der Spalten speichert Arrays. Zum Beispiel gibt es die Tabelle products, und jedes Produkt kann zu mehreren Kategorien gehören:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    categories TEXT[] -- Array von Strings für die Speicherung der Produktkategorien
);

Angenommen, du willst alle Produkte finden, die zur Kategorie electronics gehören. Wenn du einfach den Operator @> für die Suche verwendest, kann das zu einem kompletten Table-Scan führen:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

Ein kompletter Scan (Seq Scan) — das ist langsam. Besonders wenn die Tabelle riesig ist. Indizes kommen ins Spiel, um daraus eine viel schnellere Suche zu machen.

Index-Typen für Arrays

PostgreSQL unterstützt zwei Haupttypen von Indizes, die du für Arrays nutzen kannst:

1. GIN (Generalized Inverted Index) — perfekt, wenn du schnell Elemente im Array suchen oder Überschneidungen prüfen willst.
2. BTREE (Binary Tree) — geeignet für andere Operationen, zum Beispiel für den exakten Vergleich von Arrays.

Schauen wir uns beide genauer an.

1. GIN-Index: blitzschnell

GIN (Generalized Inverted Index) ist ein Index, der super für Operatoren wie diese ist:

• @> (Array enthält ein Element oder ein anderes Array),
• <@ (Array ist in einem anderen Array enthalten),
• && (Arrays überschneiden sich).

So kannst du einen GIN-Index für unsere Spalte categories erstellen:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Nach dem Erstellen des Indexes werden Queries deutlich schneller. Zum Beispiel diese Query:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

wird jetzt deinen GIN-Index nutzen.

Fun Fact: Ein GIN-Index funktioniert wie eine invertierte Liste — er speichert, welche Elemente (zum Beispiel Strings) in welchen Einträgen vorkommen. Das ist wie ein Rückverweis in Büchern, mit dem du ein Thema über die Seitenzahl findest. Ziemlich praktisch, oder?

2. BTREE-Index: wenn die Reihenfolge zählt

BTREE (Binary Tree) ist der Standard-Index, den die meisten Datenbanken nutzen. Er ist gut für Operationen, bei denen Arrays exakt verglichen werden, zum Beispiel:

• Prüfen auf Array-Gleichheit =,
• Vergleich von Arrays nach Reihenfolge der Elemente (>, <).

Einen BTREE-Index für ein Array kannst du so erstellen:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Beispiel für eine Query, die den BTREE-Index nutzen kann:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Beachte aber: BTREE-Indizes sind nicht geeignet für Operatoren wie @> oder <@. Dafür ist GIN besser.

Beispiele für die Nutzung von Indizes

Kombinieren wir jetzt Theorie und Praxis und schauen uns ein paar Beispiele an.

1. Suche nach Überschneidung von Arrays

Angenommen, wir wollen alle Produkte finden, die mit den Kategorien electronics und smartphones verbunden sind, mit dem Operator && (Array-Überschneidung):

SELECT *
FROM products
WHERE categories && ARRAY['electronics', 'smartphones'];

Dafür ist der GIN-Index, den du schon erstellt hast, ideal:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Mit so einem Index läuft die Query viel schneller, weil die invertierte Liste genutzt wird.

2. Vergleich von Arrays auf Gleichheit

Wenn du Produkte finden willst, die nur zu den Kategorien electronics und gadgets gehören (in genau dieser Reihenfolge), dann ist ein BTREE-Index besser:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Erstelle dazu den passenden Index:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Performance von Indizes

Indizes machen Queries schneller, aber sie haben auch ihre Schattenseiten. Zum Beispiel:

• Index-Erstellung braucht Zeit und Ressourcen. Wenn deine Tabelle riesig ist, kann das Erstellen eines Indexes ziemlich lange dauern.
• Tabellen-Updates. Immer wenn du neue Zeilen einfügst oder bestehende Daten änderst, werden auch die Indizes aktualisiert. Das kann INSERT und UPDATE langsamer machen.

Aber meistens überwiegt der Vorteil von schnellen Queries diese Nachteile locker.

Wie wählen: GIN oder BTREE?

Hier eine kleine Tabelle, die dir hilft, den passenden Index für deine Aufgabe zu wählen: