B-Tree vs GIN vs GiST-Indizes: Ein praktischer PostgreSQL‑Leitfaden

Was du wirklich auswählst, wenn du einen Index wählst

Die meisten PostgreSQL‑Index‑Probleme fangen gleich an: Eine Listenansicht fühlt sich bei 1.000 Zeilen schnell an und zieht sich bei 1.000.000. Oder ein Suchfeld, das in Tests flott war, steht in Produktion plötzlich für Sekunden. Dann ist es verlockend zu fragen: „Welcher Index ist am besten?“ Besser ist die Frage: „Was bittet dieser Screen die Datenbank eigentlich zu tun?“

Die gleiche Tabelle kann verschiedene Index‑Typen brauchen, weil verschiedene Screens unterschiedlich auf die Daten zugreifen. Ein View filtert nach einem Status und sortiert nach created_at. Ein anderer macht Full‑Text‑Suche. Ein weiterer prüft, ob ein JSON‑Feld einen Schlüssel enthält. Ein anderer findet Elemente in der Nähe eines Punktes auf einer Karte. Das sind unterschiedliche Zugriffsmuster, weshalb ein einziger Indextyp nicht überall gewinnt.

Darum wählst du einen Index: wie die App die Daten liest. Machst du überwiegend exakte Treffer, Bereiche und Sortierungen? Durchsuchst du Dokumente oder Arrays? Fragst du „was ist in der Nähe dieses Standorts“ oder „was überlappt mit diesem Bereich“? Die Antwort entscheidet, ob B‑Tree, GIN oder GiST passt.

B‑Tree, GIN und GiST in einfachen Worten

Die Wahl des Index hängt weniger vom Spaltentyp ab als davon, was deine Queries damit tun. PostgreSQL wählt Indizes basierend auf Operatoren wie =, <, @> oder @@, nicht danach, ob die Spalte „text“ oder „json“ heißt. Deshalb kann dasselbe Feld auf verschiedenen Screens unterschiedliche Indizes brauchen.

B‑Tree: schnell bei geordneten Suchen

B‑Tree ist der Standard und die gebräuchlichste Wahl. Er glänzt, wenn du nach exakten Werten filterst, nach Bereichen suchst oder Ergebnisse in einer bestimmten Reihenfolge brauchst.

Ein typisches Beispiel ist eine Admin‑Liste, die nach Status filtert und nach created_at sortiert. Ein B‑Tree‑Index auf (status, created_at) kann sowohl beim Filtern als auch beim Sortieren helfen. B‑Tree ist auch üblich für Unique‑Constraints.

GIN: schnell, wenn jede Zeile viele durchsuchbare Schlüssel hat

GIN ist für Fragen gebaut wie „enthält diese Zeile diesen Term/Wert?“, wobei eine Zeile viele Schlüssel matchen kann. Häufige Beispiele sind Full‑Text‑Suche (ein Dokument enthält Wörter) und JSONB/Array‑Membership (JSON enthält einen Schlüssel/Wert).

Denk an einen Kunden‑Datensatz mit einem JSONB‑preferences‑Objekt und einem Screen, der Nutzer filtert, deren Preferences { "newsletter": true } enthält. Das ist ein typischer GIN‑Lookup.

GiST: flexibel für Bereiche, Geo und Ähnlichkeit

GiST ist ein allgemeines Framework für Datentypen, die sich nicht einfach ordnen lassen. Es passt gut zu Bereichen (Überlappung, Enthält), geometrischen und geographischen Abfragen (nahe, innerhalb) und zu einigen Ähnlichkeitssuchen.

Wenn du zwischen B‑Tree, GIN und GiST wählst, schreibe zuerst die Operatoren auf, die deine meistgenutzten Screens verwenden. Dann wird die richtige Indexwahl meist klarer.

Entscheidungstabelle für gängige Screens (Filter, Suche, JSON, Geo)

Die meisten Apps brauchen nur ein paar Index‑Muster. Der Trick ist, das Verhalten des Screens mit den Operatoren der Queries abzugleichen.

Zwei Indizes können koexistieren, wenn Endpunkte unterschiedliche Abfrageformen haben. Beispiel: Eine Admin‑Liste braucht vielleicht (tenant_id, created_at) für schnelles Sortieren, während eine Suchseite einen GIN‑Index für @@ braucht. Behalte beide nur, wenn beide Abfrageformen häufig sind.

Wenn du unsicher bist, schau zuerst auf den Operator. Indizes helfen, wenn die Datenbank damit große Teile der Tabelle überspringen kann.

Filter und Sortierung: wo B‑Tree meist gewinnt

Für die meisten Alltags‑Screens ist B‑Tree die langweilige Wahl, die funktioniert. Wenn deine Query lautet „nimm Zeilen, wo eine Spalte gleich einem Wert ist, sortiere dann und zeige Seite 1“, ist B‑Tree meist der erste Versuch.

Gleichheitsfilter sind der klassische Fall. Spalten wie status, user_id, account_id, type oder tenant_id tauchen ständig in Dashboards und Admin‑Panels auf. Ein B‑Tree‑Index kann direkt zu den passenden Werten springen.

Bereichsfilter passen ebenfalls gut zu B‑Tree. Bei zeitlichen oder numerischen Bereichen hilft die Ordnungsstruktur: created_at >= ..., price BETWEEN ..., id > .... Wenn dein UI „Letzte 7 Tage“ oder „$50 bis $100“ anbietet, macht B‑Tree genau das, was du brauchst.

Sortierung und Pagination sparen dir mitunter die meiste Arbeit. Wenn die Indexreihenfolge zu deinem ORDER BY passt, kann PostgreSQL Zeilen oft bereits sortiert zurückgeben, statt einen großen Satz im Speicher zu sortieren.

-- Ein häufiger Screen: "Meine offenen Tickets, neueste zuerst"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

Bei zusammengesetzten Indizes gilt eine einfache Regel: PostgreSQL kann meist nur den führenden Teil des Index effizient nutzen. Denk „von links nach rechts“. Bei (user_id, status, created_at) profitieren Abfragen, die nach user_id filtern (und optional status). Eine Abfrage, die nur nach status filtert, hilft normalerweise nicht.

Partielle Indizes sind ein starker Gewinn, wenn dein Screen nur eine Scheibe der Daten betrachtet. Gängige Scheiben sind „nur aktive Zeilen“, „nicht soft‑gelöscht“ oder „aktuelle Aktivität“. Sie halten den Index kleiner und schneller.

Hoch‑kardinale Spalten und die Kosten zusätzlicher Indizes

Hoch‑kardinale Spalten haben viele einzigartige Werte wie user_id, order_id, email oder created_at mit Sekundenauflösung. Indizes lohnen sich hier, weil ein Filter schnell auf einen sehr kleinen Teil der Tabelle eingrenzt.

Geringe Kardinalität ist das Gegenteil: Booleans und kleine Enums wie is_active, status IN ('open','closed') oder plan IN ('free','pro'). Ein Index darauf enttäuscht oft, weil jeder Wert einen großen Block Zeilen trifft. PostgreSQL wählt dann manchmal korrekt einen Sequenzscan.

Ein weiterer subtiler Kostenpunkt ist das Nachladen der Zeilen. Selbst wenn ein Index IDs schnell findet, muss die DB oft die Tabelle für die restlichen Spalten besuchen. Wenn deine Query nur wenige Felder braucht, hilft ein Covering‑Index, aber der macht den Index auch größer und teurer zu pflegen.

Jeder zusätzliche Index hat einen Schreibe‑Preis. Inserts müssen in jeden Index schreiben. Updates, die indizierte Spalten ändern, müssen die Indexeinträge anpassen. Indizes „nur für alle Fälle“ zu ergänzen kann die ganze App verlangsamen, nicht nur einen Screen.

Praktische Orientierung:

• Starte mit 1–2 Arbeitspferd‑Indizes pro stark genutzter Tabelle, basierend auf realen Filtern und Sortierungen.
• Bevorzuge hoch‑kardinale Spalten in WHERE und ORDER BY.
• Sei vorsichtig mit Indizes auf Booleans und kleinen Enums, außer sie kombinieren mit einer selektiven Spalte.
• Füge einen neuen Index erst hinzu, wenn du die genaue Query benennen kannst, die er beschleunigen soll.

Beispiel: Eine Support‑Ticket‑Liste gefiltert nach assignee_id (hoch‑kardinal) profitiert von einem Index, während is_archived = false allein oft nicht hilft.

Such‑Screens: Full‑Text, Präfixe und "contains"

Suchfelder wirken simpel, aber Nutzer erwarten viel: mehrere Wörter, unterschiedliche Wortformen und sinnvolle Rangfolge. In PostgreSQL ist das meist Full‑Text‑Suche: du speicherst ein tsvector (vorbereiteter Text) und fragst mit einem tsquery (aus dem eingegebenen Text geparst).

Für Full‑Text ist GIN der übliche Default, weil er schnell beantwortet, ob ein Dokument bestimmte Begriffe enthält. Der Kompromiss sind höhere Schreibkosten: Inserts und Updates sind teurer.

GiST kann auch für Full‑Text genutzt werden. Er ist oft kleiner und günstiger beim Aktualisieren, aber meist langsamer bei Lesezugriffen als GIN. Wenn deine Daten sehr veränderlich sind (z. B. Event‑Tables), kann dieses Lese‑Schreib‑Verhältnis wichtig werden.

Präfix‑Suche ist keine Full‑Text‑Suche

Präfix‑Suche heißt „beginnt mit“, z. B. Kunden nach Email‑Präfix suchen. Dafür ist Full‑Text nicht gebaut. Für Präfixe kann ein B‑Tree helfen (oft mit dem passenden Operator‑Klassen), weil er mit der String‑Ordnung übereinstimmt.

Für "contains"‑Suchen wie ILIKE '%error%' hilft B‑Tree meist nicht. Hier kommen Trigram‑Indizes oder andere Suchansätze ins Spiel.

Wenn Nutzer Filter plus Textsuche wollen

Die meisten realen Screens kombinieren Suche mit Filtern: Status, Assignee, Datumsbereich, Tenant usw. Ein praktisches Setup ist:

• Ein GIN (oder manchmal GiST) Index für die tsvector‑Spalte.
• B‑Tree‑Indizes für die selektivsten Filter (z. B. account_id, status, created_at).
• Eine strikte Regel: so minimal wie möglich bleiben, denn zu viele Indizes verlangsamen Schreiboperationen.

Beispiel: Ein Support‑Ticket‑Screen sucht „refund delayed“ und filtert status = 'open' und ein bestimmtes account_id. Full‑Text liefert relevante Zeilen, B‑Tree hilft, die passende Account/Status‑Menge schnell einzugrenzen.

JSONB‑Felder: GIN oder gezielte B‑Tree‑Indizes?

JSONB ist praktisch für flexible Daten, kann aber zu langsamen Queries führen, wenn du es wie eine normale Spalte behandelst. Die Kernentscheidung ist einfach: suchst du „überall im JSON“ oder filterst du häufig auf ein paar bestimmten Pfaden?

Für Containment‑Abfragen wie metadata @> '{"plan":"pro"}' ist ein GIN‑Index meist die erste Wahl. Er ist für „enthält diese Struktur?“ gebaut und unterstützt auch Key‑Existenzprüfungen wie ?, ?| und ?&.

Wenn deine App meist nach ein oder zwei JSON‑Feldern filtert, ist ein gezielter B‑Tree‑Expression‑Index oft schneller und kleiner. Er hilft auch bei Sortierung oder numerischen Vergleichen extrahierter Werte.

-- Breit anwendbar für Containment und Key‑Checks
CREATE INDEX ON customers USING GIN (metadata);

-- Zielgerichtete Filter und Sortierung auf einem JSON‑Pfad
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

Faustregeln:

• Nutze GIN, wenn Nutzer viele Keys, Tags oder verschachtelte Strukturen durchsuchen.
• Nutze B‑Tree‑Expressions, wenn du regelmäßig spezifische Pfade abfragst.
• Indiziere, was auf echten Screens vorkommt, nicht alles.
• Wenn die Performance von ein paar JSON‑Keys abhängt, zieh in Betracht, sie in echte Spalten zu überführen.

Beispiel: Ein Support‑Screen filtert Tickets nach metadata->>'priority' und sortiert nach created_at. Indiziere den JSON‑Priority‑Pfad und die normale created_at‑Spalte. Ein breiter GIN‑Index ist nur nötig, wenn Nutzer auch Tags oder verschachtelte Attribute durchsuchen.

Geo‑ und Range‑Abfragen: wo GiST am besten passt

Geo‑ und Range‑Screens sind typische GiST‑Anwendungsfälle. GiST ist für Fragen gebaut wie „überlappt das?“ oder „ist das nahe dran?“ statt „ist dieser Wert gleich jenem?"

Geo‑Daten sind Punkte (Store‑Standort), Linien (Route) oder Polygone (Lieferzone). Gängige Screens: „Stores in der Nähe“, „Jobs innerhalb 10 km“, „Zeige Items in diesem Kartenbereich“ oder „liegt diese Adresse in unserem Einzugsgebiet?“ Ein GiST‑Index (meist über PostGIS geometry/geography) beschleunigt diese Spatial‑Operatoren, damit die DB die meisten Shapes überspringen kann.

Ranges sind ähnlich. PostgreSQL hat daterange, int4range etc., und die typische Frage ist Überlappung: „kollidiert diese Buchung mit einer bestehenden?“ oder „zeige Abonnements, die in dieser Woche aktiv sind“. GiST unterstützt Überlappungs‑ und Enthält‑Operatoren effizient.

B‑Tree bleibt relevant: Viele Seiten filtern zuerst nach Tenant, Status oder Zeit, dann die räumliche Bedingung und sortieren. Beispiel: „Nur Lieferungen meiner Firma, der letzten 7 Tage, nächstes zuerst.“ GiST macht den räumlichen Teil, B‑Tree hilft bei selektiven Filtern und Sortierungen.

Schritt‑für‑Schritt: wie du einen Index wählst

Die Wahl des Index hängt meist vom Operator ab, nicht vom Spaltennamen. Dieselbe Spalte braucht je nach Verwendung =, >, LIKE 'prefix%', Full‑Text, JSON‑Containment oder Geo‑Distance‑Indizes.

Lies die Query wie eine Checkliste: WHERE entscheidet, welche Zeilen qualifizieren, JOIN wie Tabellen verbunden werden, ORDER BY die Ausgabe‑Reihenfolge und LIMIT, wie viele Zeilen du tatsächlich brauchst. Der beste Index hilft dir oft, die ersten 20 Zeilen schnell zu finden.

Ein einfacher Prozess, der für die meisten Screens funktioniert:

1. Schreibe die genauen Operatoren auf, die dein Screen nutzt (z. B. status =, created_at >=, name ILIKE, meta @>, ST_DWithin).
2. Beginne mit einem Index, der den selektivsten Filter oder die Standard‑Sortierung abdeckt. Wenn der Screen nach created_at DESC sortiert, fang dort an.
3. Füge zusammengesetzte Indizes nur hinzu, wenn dieselben Filter regelmäßig zusammen auftreten. Setze Gleichheitsspalten zuerst, dann Bereichsspalten, dann die Sortierspalte.
4. Nutze partielle Indizes, wenn du immer auf eine Untermenge filterst (z. B. status = 'open'). Verwende Expressionsindizes, wenn du auf berechnete Werte fragst (z. B. lower(email) für case‑insensitive Lookups).
5. Validere mit EXPLAIN ANALYZE. Behalte den Index, wenn er Ausführungszeit deutlich reduziert und die gelesenen Zeilen stark verringert.

Konkretes Beispiel: Ein Support‑Dashboard filtert Tickets nach status und sortiert nach Neuheit. Ein B‑Tree auf (status, created_at DESC) ist ein guter erster Versuch. Wenn derselbe Screen außerdem nach einem JSONB‑Flag wie meta @> '{"vip": true}' filtert, ist das ein anderer Operator und braucht meist einen eigenen JSON‑fokussierten Index.

Häufige Fehler, die Zeit und Schreibleistung kosten

Ein häufiger Frust ist, den „richtigen“ Indextyp für den falschen Operator zu wählen. PostgreSQL nutzt einen Index nur, wenn die Query das beantwortet, wofür der Index gebaut wurde. Wenn deine App ILIKE '%term%' nutzt, bleibt ein normaler B‑Tree auf der Textspalte ungenutzt und die Tabelle wird gescannt.

Eine weitere Falle sind riesige Multi‑Spalten‑Indizes „für alle Fälle“. Sie wirken sicher, sind aber teuer in der Pflege und passen oft nicht zu realen Query‑Mustern. Wenn die linke(n) Spalte(n) im Index nicht in der Abfrage vorkommen, hilft der Rest meist nicht.

Low‑Selectivity‑Spalten sind leicht zu überindizieren. Ein B‑Tree auf is_active oder einem Status mit wenigen Werten ist oft nutzlos, es sei denn, du machst daraus einen partiellen Index.

JSONB hat eigene Fallstricke. Ein breiter GIN‑Index ist super für flexible Filter, aber viele JSON‑Pfadprüfungen sind schneller mit einem Expressionsindex auf den extrahierten Wert. Wenn dein Screen immer payload->>'customer_id' prüft, ist ein Expression‑Index kleiner und schneller als ein Index auf das ganze Dokument.

Zuletzt: Jeder zusätzliche Index belastet Schreibeinheiten. Bei häufig geänderten Tabellen (Tickets, Orders) muss jede Insert/Update alle Indizes aktualisieren.

Bevor du einen Index anlegst, frag dich:

• Passt der Index genau zu dem Operator, den die Query nutzt?
• Kannst du einen breiten Multi‑Spalten‑Index durch ein oder zwei fokussierte ersetzen?
• Sollte das ein partieller Index werden, um geringe Selektivität zu vermeiden?
• Für JSONB: wäre ein Expressionsindex passender?
• Ist die Tabelle so schreibintensiv, dass die Indexkosten die Lesegewinne überwiegen?

Schnelle Checks bevor du einen Index hinzufügst (oder behältst)

Bevor du einen neuen Index erstellst, sei konkret: was macht die App tatsächlich. Ein „nice to have“‑Index wird oft zu langsamen Schreibvorgängen und mehr Speicher mit geringem Nutzen.

Fang mit deinen drei wichtigsten Screens (oder API‑Endpoints) an und schreibe die exakte Query‑Form auf: Filter, Sortierung und was der Nutzer eingibt. Viele „Index‑Probleme“ sind eigentlich „unklare Query‑Probleme“, besonders wenn Leute B‑Tree vs GIN vs GiST diskutieren, ohne den Operator zu benennen.

Eine einfache Checkliste:

• Wähle 3 reale Screens und liste ihre genauen WHERE und ORDER BY‑Muster (inkl. Richtung und NULL‑Behandlung).
• Bestätige den Operator‑Typ: Gleichheit (=), Bereich (>, BETWEEN), Präfix, Containment, Überlappung oder Distanz.
• Wähle pro häufiges Screen‑Muster einen Index, teste ihn und behalte nur die, die Zeit oder Lesezugriffe messbar reduzieren.
• Wenn die Tabelle schreibintensiv ist, sei streng: zusätzliche Indizes vervielfachen Schreibkosten und erhöhen VACUUM‑Druck.
• Überprüfe nach Feature‑Änderungen erneut. Ein neuer Filter oder eine neue Standard‑Sortierung kann einen alten Index irrelevant machen.

Beispiel: Ein Dashboard fügt Standard‑Sort last_activity DESC hinzu. Wenn du nur status indiziert hast, bleibt das Filter schnell, aber die Sortierung kostet jetzt extra Arbeit.

Beispiel: reale Screens auf passende Indizes abbilden

Eine Entscheidungstabelle hilft nur, wenn du sie auf die Screens anwendest, die du auslieferst. Hier drei gängige Screens und die passenden Index‑Wahlen.

Praktische Anwendung: Starte an der UI:

• Liste alle Controls, die Ergebnisse eingrenzen (Filter).
• Notiere die Standard‑Sortierung.
• Sei spezifisch bei der Suchfunktion (Full‑Text vs Starts‑With vs Contains).
• Markiere „besondere“ Felder (JSONB, Geo, Ranges).

Nächste Schritte: mach das Indizieren zum Teil deines Entwicklungsprozesses

Gute Indizes folgen deinen Screens: den Filtern, die Leute klicken, der Sortierung, die sie erwarten, und dem Suchfeld, das sie wirklich nutzen. Behandle Indizierung als Gewohnheit während der Entwicklung und du vermeidest die meisten Performance‑Überraschungen später.

Halte es reproduzierbar: identifiziere 1–3 Queries pro Screen, füge den kleinsten Index hinzu, der sie unterstützt, teste mit realistischen Daten und entferne, was sich nicht bewährt.

Wenn du ein internes Tool oder ein Kundenportal baust, plane Index‑Bedarf früh, denn solche Apps wachsen oft durch zusätzliche Filter und Listen‑Screens. Wenn du mit AppMaster (appmaster.io) baust, hilft es, die Filter‑ und Sortierkonfiguration jedes Screens als konkrete Query‑Form zu behandeln und nur die Indizes hinzuzufügen, die zu diesen echten Klicks passen.