GIN-Indizes in PostgreSQL

Wenn du aus der SQL-Server-Welt kommst (so wie ich), fühlt sich die Indexierung in PostgreSQL zunächst vertraut an – es gibt B-Tree-Indizes, zusammengesetzte Indizes oder Covering-Indizes. Aber urplötzlich stößt du auf Abfragen wie diese:

WHERE payload @> '{"type":"payment","status":"failed"}'

Oder diese:

WHERE tsv @@ plainto_tsquery('postgresql')

Spätestens an diesem Punkt stellen sich die meisten SQL-Server-Entwickler zwei Fragen: Was sind das für Operatoren? Und warum benötigt PostgreSQL dafür einen völlig anderen Indextyp?

Dieser Artikel beantwortet beides – und zeigt, weshalb es GIN-Indizes gibt, welche Probleme sie lösen und wie sie sich im Vergleich zu modernem SQL Server (inklusive SQL Server 2025 mit nativen JSON-Indizes) verhalten.

Zuerst das Wichtigste: PostgreSQL-Indizes sind operatorgetrieben

PostgreSQL verfolgt eine grundlegend andere Index-Philosophie als SQL Server. In SQL Server sind Indizes spalten- und wertebasiert und optimiert für Gleichheit, Bereiche und Sortierung.

In PostgreSQL hingegen sind Indizes operatorbasiert und um die Art der Abfrage herum entworfen – und nicht nur um die Speicherung der Daten. Deshalb verwendet PostgreSQL Operatoren, die ungewohnt aussehen, aber sehr explizit sind.

Die zwei entscheidenden Operatoren verstehen

Gehen wir, bevor wir über GIN sprechen, auf diese Operatoren ein und fokussieren auf die beiden wichtigsten. So hat der Operator @> die Bedeutung „enthält“, wie im folgenden Beispiel:

payload @> '{"type":"payment"}'

Das bedeutet: „Enthält das JSON-Dokument in payload mindestens dieses Schlüssel-Wert-Paar?“ Nicht erforderlich sind dabei identisches JSON, eine identische Reihenfolge sowie eine identische Struktur über die angegebenen Schlüssel hinaus.

Das unterscheidet sich deutlich von den klassischen JSON-Funktionen in SQL Server, die historisch eine explizite Extraktion erfordern. Vor SQL Server 2025 schrieb man typischerweise

JSON_VALUE(payload, '$.type') = 'payment'

und indexierte dies über persistierte berechnete Spalten oder gefilterte Indizes. SQL Server 2025 verbessert dies nun durch einen nativen JSON-Datentyp und JSON-Indizes. Diese Indizes bleiben jedoch pfad- und wertorientiert, nicht strukturell. PostgreSQL beantwortet mit @> also eine höherwertige Frage: „Ist diese Struktur logisch im Dokument enthalten?“

Der zweite bedeutsame Operator @@ wird für Volltextsuche verwendet:

tsv @@ plainto_tsquery('postgresql')

Das bedeutet: „Passt der Token-Vektor dieses Dokuments zu dieser Textabfrage?“ Das entspricht keinem LIKE und keinem String-Vergleich, aber einem linguistischen Matching.

SQL-Server-Entwickler sollten das mit Full-Text-Search vergleichen und nicht mit LIKE '%text%'.

Warum GIN-Indizes notwendig sind

B-Tree-Indizes funktionieren gut, wenn eine Zeile genau einen Indexwert hat, Vergleiche auf Gleichheit oder einem Bereich basieren und wenn die Sortierung relevant ist.

Einen GIN-Index (Generalized Inverted Index) muss man sich nun wie einen invertierten Index vorstellen. Statt

Zeile → Wert

speichert er

Wert → viele Zeilen

Jedes durchsuchbare Element innerhalb einer Spalte wird somit zu einem eigenen Indexschlüssel. Deshalb eignet sich GIN ideal für JSONB, Arrays, Tags oder Volltext-Tokens.

Ein konkretes Beispiel

Erstellen wir eine einfache Tabelle, in der Informationen zur Zahlungsabwicklung gespeichert werden:

CREATE TABLE Events
 (
 ID BIGINT GENERATED ALWAYS AS IDENTITY,
 OccurredAt TIMESTAMPTZ NOT NULL,
 Payload JSONB NOT NULL
 );
 
 

{
 "type": "payment",
 "status": "failed",
 "user_id": 4711,
 "tags": ["stripe", "europe"]
 }
 
 

-- Insert 1mio rows
 INSERT INTO Events (OccurredAt, Payload)
 SELECT
 now() - (random() * interval '30 days'),
 jsonb_build_object(
 'type',
 CASE
 WHEN r < 0.80 THEN 'payment'
 WHEN r < 0.95 THEN 'login'
 ELSE 'signup'
 END,
 'status',
 CASE
 WHEN r < 0.80 THEN
 CASE WHEN random() < 0.002 THEN 'failed' ELSE 'success' END
 ELSE
 CASE WHEN random() < 0.01  THEN 'failed' ELSE 'success' END
 END,
 'user_id', (random() * 5000000)::int,
 'region',  CASE WHEN random() < 0.6 THEN 'eu' ELSE 'us' END,
 'provider',CASE WHEN random() < 0.5 THEN 'stripe' ELSE 'paypal' END,
 'tags',
 CASE
 WHEN random() < 0.20 THEN jsonb_build_array('retry','europe')
 WHEN random() < 0.40 THEN jsonb_build_array('stripe','europe')
 WHEN random() < 0.60 THEN jsonb_build_array('paypal','us')
 ELSE jsonb_build_array('mobile','web')
 END
 )
 FROM (
 SELECT random() AS r
 FROM generate_series(1, 1000000)
 ) s;
 
 -- Update Statistics
 ANALYZE events;

Das ergibt die folgende Abfrage, die wir anschließend noch optimieren wollen:

-- Execution Time: around 60ms
 EXPLAIN (ANALYZE, BUFFERS)
 SELECT COUNT(*)
 FROM Events
 WHERE Payload @> '{"type":"payment","status":"failed"}'::JSONB;

Ohne Index läuft diese Abfrage auf meinem System etwa 60 ms lang (Bild 1).

Nun legen wir einen GIN-Index an:

-- Create a GIN index
 CREATE INDEX idx_EventsPayload_GIN
 ON Events
 USING GIN(Payload);
 
 -- Update Statistics
 ANALYZE events;

Diese einzelne Anweisung indiziert jeden JSON-Schlüssel, jeden JSON-Wert und jedes Array-Element. Wir erkennen gleichzeitig, dass SQL Server JSON-Indizes – selbst in SQL Server 2025 – nicht auf die gleiche Weise modelliert, da sie weiterhin Pfad- statt Containment-zentriert sind.

Führen wir die Abfrage nun erneut aus, verwendet der Abfrageplaner den GIN-Index, und die Abfrage wird deutlich schneller abgeschlossen, nämlich in etwa 18 ms (Bild 2).

Allerdings haben GIN-Indizes auch ein paar negative Begleiterscheinungen, so zum Beispiel:

• Sie sind größer als B-Trees.
• Sie verlangsamen Inserts und Updates.
• Sie unterstützen keine Sortierung.

Vor allem der letzte Punkt ist kritisch zu bewerten – und führt uns direkt zu RUM.

Was ist RUM?

RUM ist eine Erweiterung und kein eingebauter Indextyp. RUM erweitert GIN um Token-Positionen, Ranking-Metadaten und optionale Sortierhinweise. Sehen wir uns das kurz an:

CREATE EXTENSION rum;
 
 CREATE INDEX idx_Documents_RUM
 ON Documents
 USING RUM (tsv rum_tsvector_ops);

Mithilfe von RUM kann PostgreSQL schneller filtern, ranken und Ergebnisse nach zusätzlichen Informationen wie Zeitstempeln sortieren.

Zusammenfassung

SQL Server 2025 verbessert die JSON-Unterstützung deutlich. Aber beim GIN-Modell in PostgreSQL ging nie allein um JSON, sondern vielmehr um das Indexieren von Relevanz und nicht einfach nur von Werten. GIN existiert, weil Daten heutzutage mehrwertig und semistrukturiert sind und semantisch abgefragt werden. RUM hingegen existiert, weil Relevanz wichtig ist, sobald das Filtern kostengünstig ist. Wenn Sie an PostgreSQL mit dieser Einstellung herangehen, erscheinen GIN-Indizes nicht mehr „irgendwie seltsam“, sondern werden unverzichtbar.