Array-Indexierung und Operatoren (`@>`, `<@`, `&&`) für schnelle Suche

Arrays in PostgreSQL ermöglichen es dir, mehrere Werte in einer einzigen Tabellenzelle zu speichern. Das ist mega praktisch, wenn du zusammengehörige Daten gruppieren willst, zum Beispiel eine Liste von Tags für einen Artikel oder Kategorien für ein Produkt. Aber sobald du anfängst, Arrays zu durchsuchen, zu filtern oder zu vergleichen, kann die Performance ziemlich leiden. Genau deshalb ist die Indexierung von Arrays ein echter Gamechanger. Indizes machen solche Operationen viel schneller, wie zum Beispiel:

• prüfen, ob ein Array ein bestimmtes Element enthält,
• Arrays finden, die bestimmte Elemente enthalten,
• prüfen, ob Arrays Überschneidungen haben.

Operatoren für die Arbeit mit Arrays

Bevor wir uns anschauen, wie man Indizes baut, lass uns die wichtigsten Operatoren für Arrays durchgehen:

@> (enthält) — prüft, ob ein Array alle Elemente eines anderen Arrays enthält.

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Hier suchen wir nach Kursen, die das Tag "SQL" enthalten.

<@ (ist enthalten in) — prüft, ob ein Array in einem anderen Array enthalten ist.

SELECT *
FROM courses
WHERE ARRAY['PostgreSQL', 'SQL'] <@ tags;

Hier suchen wir nach Kursen, deren Tags alle Elemente aus dem Array ARRAY['PostgreSQL', 'SQL'] enthalten.

&& (Überschneidung) — prüft, ob Arrays Überschneidungen haben.

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Diese Abfrage findet Kurse, die mindestens eines der Tags "NoSQL" oder "Big Data" enthalten.

Wie hilft die Indexierung?

Stell dir vor, du hast eine Tabelle courses mit Millionen von Einträgen und du führst eine Abfrage mit einem der oben genannten Operatoren aus. Ohne Index muss PostgreSQL jede Zeile einzeln prüfen – das kann ewig dauern (vor allem, wenn du so viel Geduld hast wie ein Dev beim Warten auf den Build).

Mit Indizes kannst du dir das sparen. PostgreSQL bietet zwei Index-Typen, die für Arrays geeignet sind:

1. GIN (Generalized Inverted Index) — die beste Wahl für Arrays.
2. BTREE — wird für den Vergleich ganzer Arrays genutzt.

Beispiel: Einen Index für Arrays anlegen

Lass uns eine kleine Tabelle mit Arrays anlegen, um das Ganze praktisch zu testen.

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] NOT NULL
);

Wir fügen ein paar Einträge hinzu:

INSERT INTO courses (name, tags)
VALUES
    ('SQL Grundlagen', ARRAY['SQL', 'PostgreSQL', 'Datenbanken']),
    ('Arbeiten mit Big Data', ARRAY['Hadoop', 'Big Data', 'NoSQL']),
    ('Python Entwicklung', ARRAY['Python', 'Web', 'Daten']),
    ('PostgreSQL Kurs', ARRAY['PostgreSQL', 'Advanced', 'SQL']);

So sieht die Tabelle dann aus:

Ohne Index: langsame Suche

Stell dir vor, wir wollen alle Kurse finden, die das Tag SQL enthalten.

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Die Abfrage läuft zwar, aber bei vielen Daten wird sie extrem langsam. PostgreSQL macht dann einen sogenannten Sequential Scan, also prüft jede einzelne Zeile der Tabelle.

Ungefähr so sieht das Ergebnis aus:

GIN-Index anlegen

Um die Suche zu beschleunigen, legen wir einen GIN-Index an:

CREATE INDEX idx_courses_tags
ON courses USING GIN (tags);

Jetzt führen wir die gleiche Abfrage nochmal aus:

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Jetzt nutzt PostgreSQL den GIN-Index, was die Abfragezeit deutlich verkürzt.

Früher gab es einen Sequential Scan (Seq Scan), jetzt siehst du im Ausführungsplan einen Bitmap Index Scan:

Wie funktionieren Operatoren mit Indizes?

Beispiel 1: Operator @>

Abfrage:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Der GIN-Index ist für diesen Operator optimal. Postgres checkt blitzschnell, welche Zeilen das gesuchte Element enthalten, und gibt das Ergebnis zurück.

Ergebnis der Abfrage:

Beispiel 2: Operator &&

Abfrage:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Dieser Operator prüft die Überschneidung von Arrays: Er gibt Zeilen zurück, bei denen das Array tags mindestens ein Element aus dem übergebenen Array enthält.

Der GIN-Index wirkt hier wieder wie Magie – die Suche ist auch bei großen Datenmengen schnell.

Ergebnis der Abfrage:

Indexierung und Optimierung

Wenn du mit Arrays arbeitest, halte dich an diese Tipps:

1. Nutze GIN-Indizes für die Suche in Arrays. Sie sind viel schneller als ein Sequential Scan.
2. Lege Indizes nur auf Spalten an, die wirklich oft in Abfragen genutzt werden. Indizes brauchen Platz und verlangsamen Inserts, also nicht einfach alles indexieren.
3. Profilier deine Abfragen mit EXPLAIN und EXPLAIN ANALYZE, um zu checken, ob dein Index wirklich genutzt wird.

Beispiele: Indizes für Arrays anlegen

Schauen wir uns an, wie man Indizes für bestimmte Array-Operationen baut und warum das in der Praxis Sinn macht.

Index für den Operator @>

Angenommen, wir haben schon so eine Tabelle courses:

Um Abfragen mit dem Operator @> (Array enthält Element) zu beschleunigen, legen wir einen GIN-Index an:

CREATE INDEX idx_courses_tags_gin
ON courses USING GIN (tags);

Jetzt führen wir die Abfrage aus:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Ergebnis:

Index für die Operatoren @>, <@ und &&

Die Ausgangstabelle ist wie im vorherigen Beispiel.

Da die Operatoren @>, <@ und && alle mit GIN-Indizes super funktionieren, reicht ein universeller Index, um Abfragen mit allen diesen Operatoren zu beschleunigen:

CREATE INDEX idx_tags
ON courses USING GIN (tags);

Beispiele für Abfragen und ihre Ergebnisse:

• @> — prüft, ob das Array die angegebenen Elemente enthält:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

---

• <@ — prüft, ob das Array in einem anderen Array enthalten ist:

SELECT *
FROM courses
WHERE tags <@ ARRAY['SQL', 'PostgreSQL', 'Advanced', 'Big Data', 'NoSQL', 'Python'];

---

• && — prüft die Überschneidung von Arrays:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Lass uns was Komplexeres probieren

Wir bauen eine Abfrage, die alle Kurse zurückgibt, bei denen es mindestens eine Überschneidung mit der Liste ['Python', 'SQL', 'NoSQL'] gibt:

SELECT *
FROM courses
WHERE tags && ARRAY['Python', 'SQL', 'NoSQL'];

Ausgabe:

Mit dem GIN-Index läuft so eine Abfrage blitzschnell, selbst wenn die Tabelle Millionen von Einträgen hat.

Typische Fehler bei der Arbeit mit Arrays

Index wird nicht genutzt: Wenn du im EXPLAIN-Output Seq Scan siehst, check, ob der Index wirklich existiert und ob der Operator, den du nutzt, Indexierung unterstützt.

Array-Spalte wird selten genutzt: Wenn die Array-Spalte kaum in Abfragen vorkommt oder oft aktualisiert wird, kann der Index unnötig Platz verbrauchen und bringt keinen echten Vorteil.

Zu viele Indizes: Indizes brauchen Speicherplatz und verlangsamen Schreiboperationen, also leg nur die an, die du wirklich brauchst und die in Abfragen genutzt werden.

Jetzt hast du alles, was du brauchst, um mit Arrays in PostgreSQL effizient zu arbeiten – beschleunige deine Abfragen mit den Operatoren @>, <@, && und GIN-Indizes. Probier das ruhig mal mit deinen eigenen Daten aus!