Postgres-Indextypen: Welchen wann verwenden

Veröffentlicht

2. September 2026

Autor

Faizan Nadeem

Die Grundlagen: Was ein Index kostet

Jeder Index ist ein Trade-off, kein kostenloser Gewinn. Beim Lesen verwandelt ein gut passender Index einen vollständigen Tabellenscan in einen gezielten Lookup. Beim Schreiben muss jedes INSERT, UPDATE oder DELETE, das eine indexierte Spalte berührt, auch jeden Index aktualisieren, der diese Spalte abdeckt — mehr Indizes bedeuten langsamere Schreibvorgänge, mehr Speicherplatz und mehr Arbeit für autovacuum. Deshalb ist „einfach einen Index hinzufügen“ keine universelle Antwort; es ist eine Wette darauf, dass die Leseeinsparung die Schreibkosten überwiegt, und diese Wette fällt je nach Tabelle und ihrem Lese-/Schreibverhältnis unterschiedlich aus.

Die Views pg_indexes und pg_stat_user_indexes von Postgres sind die ehrliche Quelle der Wahrheit dafür, ob sich diese Wette ausgezahlt hat:

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan;

Ein Index, der nach Wochen von Produktions-Traffic noch bei idx_scan = 0 steht, ist reiner Schreib-Overhead ohne jeden Nutzen beim Lesen — ein starker Kandidat zum Löschen. Bevor man zu einem neuen Indextyp greift, lohnt es sich, einen Plan genau genug zu lesen, um zu verstehen, welche Form von Scan tatsächlich stattfindet; der EXPLAIN ANALYZE guide zeigt, wie man einen sequentiellen Scan, der nach einem Index schreit, von einem unterscheidet, der tatsächlich die günstigere Option ist.

B-tree: Der Standard, und warum

B-tree ist das, was CREATE INDEX erzeugt, sofern man nichts anderes angibt, und er ist der richtige Standard, weil er die einzige Struktur ist, die Gleichheits- und geordnete Bereichsabfragen effizient behandelt — =, <, >, BETWEEN, ORDER BY und IN — auf skalaren Typen wie Integers, Text, Timestamps und UUIDs.

CREATE INDEX idx_orders_created_at ON orders (created_at);

-- Uses the index for both of these:
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;

Ein zusammengesetzter B-tree-Index — also einer über mehrere Spalten — ist zuerst nach seiner führenden Spalte sortiert, dann nach der nächsten und so weiter. Das bedeutet, dass die Spaltenreihenfolge in der CREATE INDEX-Anweisung nicht bloß kosmetisch ist. Ein Index auf (customer_id, status) bedient Abfragen, die nur nach customer_id filtern, oder nach customer_id AND status zusammen, aber er bringt nichts für eine Abfrage, die nur nach status filtert, weil der Index auf oberster Ebene nicht nach status sortiert ist. Setze die Spalte mit dem selektivsten Wert, nach dem am häufigsten allein gefiltert wird, an die erste Stelle.

B-tree ist in dem Moment keine gute Wahl mehr, in dem die Bedingung nicht mehr „liegt dieser skalare Wert in diesem Bereich“ ist — Containment (@>, ?), Full-Text (@@), geometrische Überlappung und Ähnlichkeit sind keine Bereichsfragen, und ein B-tree kann sie buchstäblich nicht beantworten, ohne zu einem vollständigen Scan zu degenerieren.

GIN: Containment und Full-Text Search

Ein GIN-Index (Generalized Inverted Index) ist für die entgegengesetzte Frage zu einem B-tree gebaut: statt „wo befindet sich dieser einzelne Wert in sortierter Reihenfolge“ beantwortet er „welche Zeilen enthalten dieses Element innerhalb eines zusammengesetzten Werts“. Genau das ist die Form einer JSONB-Containment-Prüfung, eines Array-Mitgliedschaftstests oder einer Full-Text-Search.

-- JSONB containment
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

-- Array containment
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgres'];

-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', body));
SELECT * FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('index & performance');

Intern speichert ein GIN-Index eine Zuordnung von jedem einzelnen Element (jedes JSON-Schlüssel/Wert-Paar, jedes Array-Element, jedes Lexem) zurück zu den Zeilen, die es enthalten — ein invertierter Index, dieselbe Struktur, die auch eine Suchmaschine verwendet. Diese Struktur macht Containment schnell, und sie ist zugleich der Grund, warum GIN-Indizes beim Schreiben merklich teurer in der Pflege sind als B-tree: Das Einfügen einer einzigen Zeile kann bedeuten, Dutzende einzelner Einträge zu aktualisieren, wenn das JSONB-Dokument oder Array dieser Zeile Dutzende Elemente enthält.

Der fastupdate-Mechanismus von Postgres (standardmäßig aktiviert) bündelt diese Aktualisierungen in einer Pending List, statt sie bei jedem Insert direkt in den Indexbaum zu schreiben. Das tauscht Hintergrund-Wartungskosten gegen geringere Latenz pro Schreibvorgang. Auf einer schreibintensiven Tabelle mit GIN-Index sollte man pg_stat_user_tables im Blick behalten, was die autovacuum-Häufigkeit angeht — eine Tabelle mit vielen GIN-Indizes braucht oft aggressivere autovacuum-Einstellungen, als die Standardwerte liefern.

GIN unterstützt außerdem mehrspaltige Indizes seit Postgres 9.4, was für ein häufiges Muster wichtig ist: Filtern auf eine JSONB-Spalte und eine normale skalare Spalte in derselben Abfrage.

CREATE EXTENSION IF NOT EXISTS btree_gin;

CREATE INDEX idx_products_multi ON products
USING GIN (tenant_id, attributes);

SELECT * FROM products
WHERE tenant_id = 42 AND attributes @> '{"in_stock": true}';

Die Extension btree_gin ermöglicht es einer skalaren Spalte wie tenant_id, zusammen mit einer jsonb- oder Array-Spalte an einem GIN-Index teilzunehmen, sodass ein einzelner Index einen kombinierten Filter bedienen kann, statt den Planner zu zwingen, zwischen einem B-tree auf tenant_id und einem GIN-Index auf attributes zu wählen und die beiden Ergebnismengen anschließend per Bitmap-AND zusammenzuführen. Ob der kombinierte Index oder zwei getrennte Indizes schneller sind, hängt von der Selektivität ab — miss beides mit EXPLAIN (ANALYZE, BUFFERS), statt Annahmen zu treffen.

GiST: Überlappung, Nähe und Exclusion

GiST (Generalized Search Tree) sieht oberflächlich GIN ähnlich — beide behandeln nicht-skalare Daten — aber die Fragen, die sie beantworten, sind unterschiedlich. GIN beantwortet „enthält diese Zeile dieses exakte Element“. GiST beantwortet „überlappt der Wert dieser Zeile mit diesem anderen Wert, schneidet ihn oder liegt in seiner Nähe“, was eine grundlegend andere, ungenauere Struktur ist, die auf Bounding-Regionen statt auf exakten Element-Lookups basiert.

-- Geometric proximity (with the earthdistance/cube or PostGIS extension)
CREATE INDEX idx_venues_location ON venues USING GIST (location);
SELECT name FROM venues
WHERE ST_DWithin(location, ST_MakePoint(-122.42, 37.77), 5000);

-- Range overlap
CREATE INDEX idx_bookings_period ON bookings USING GIST (during);
SELECT * FROM bookings WHERE during && tsrange('2026-08-01', '2026-08-05');

-- Exclusion constraints (no overlapping bookings for the same room)
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING GIST (room_id WITH =, during WITH &&);

Das letzte Beispiel verdient besondere Beachtung — GiST ist die einzige dieser vier Strukturen, die eine EXCLUDE-Constraint trägt. So erzwingt Postgres auf Datenbankebene „keine zwei Zeilen dürfen für denselben Schlüssel überlappende Bereiche haben“, statt das dem Anwendungscode zu überlassen. Das ist mit Row-Level-Locking und reiner Anwendungslogik tatsächlich schwer sauber umzusetzen, und die Constraint auf Datenbankebene schließt Race Conditions, die Anwendungscode unter Parallelität zuverlässig übersieht.

GiST und GIN unterstützen für manche Typen dieselben Operator-Klassen (etwa jsonb), und wenn beide in Frage kommen, ist GIN beim Abfragen meist schneller, aber langsamer im Aufbau und größer auf der Platte, während GiST schneller gebaut wird und kleiner bleibt — auf Kosten etwas geringerer Abfragegeschwindigkeit. Prüfe im Grenzfall beide, wenn die Wahl nicht ohnehin dadurch vorgegeben ist, dass EXCLUDE oder geometrische Operatoren benötigt werden.

BRIN: Nahezu kostenlos auf riesigen, sortierten Tabellen

BRIN (Block Range Index) verfolgt einen völlig anderen Ansatz: Statt einzelne Zeilen zu indexieren, speichert er den Minimal- und Maximalwert für jeden physischen Blockbereich (standardmäßig 128 Seiten). Das macht ihn dramatisch kleiner als einen B-tree — oft zwei bis drei Größenordnungen kleiner — zum Preis deutlich geringerer Präzision: Ein BRIN-Index kann nur sagen, welche Blockbereiche möglicherweise eine passende Zeile enthalten, nicht welche exakten Zeilen es sind. Deshalb muss Postgres Kandidatenzeilen weiterhin gegen die tatsächliche Bedingung nachprüfen.

CREATE INDEX idx_events_logged_at ON events USING BRIN (logged_at);

Das ist genau dann das richtige Werkzeug, wenn eine Spalte stark mit der physischen Einfügereihenfolge korreliert — ein created_at- oder logged_at-Timestamp auf einer Append-Only-Tabelle ist der klassische Fall, weil Zeilen, die ungefähr zur selben Zeit eingefügt wurden, tatsächlich nahe beieinander auf der Platte liegen. Eine Time-Series- oder Event-Log-Tabelle mit hundert Millionen Zeilen kann einen BRIN-Index bekommen, der nur ein paar hundert Kilobyte groß ist, während ein B-tree auf derselben Spalte schnell in die Hunderte von Megabytes geht — bei einer Abfrageleistung, die für Workloads mit vielen Range-Scans nah genug dran ist, um den Trade-off lohnend zu machen.

BRIN ist die falsche Wahl, sobald die indexierte Spalte nicht mehr mit der physischen Zeilenreihenfolge korreliert — etwa eine status-Spalte, die verteilt über die Tabelle hinweg in place aktualisiert wird. Das untergräbt die ganze Grundannahme, weil das Min/Max pro Blockbereich nichts Nützliches mehr aussagt.

Partial-Indizes: Nur das indexieren, was zählt

Ein Partial-Index fügt direkt an CREATE INDEX selbst eine WHERE-Klausel an, sodass überhaupt nur Zeilen indexiert werden, die dieser Bedingung entsprechen. Das ist mit Abstand das am wenigsten genutzte Werkzeug in dieser Liste, und es beantwortet direkt die Frage, wann man einen postgres partial index statt eines vollständigen verwenden sollte: immer dann, wenn eine Spalte stark zu einem Wert hin verzerrt ist, nach dem die Abfragen in der Praxis nie wirklich filtern.

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Wenn 95 % der Orders completed oder cancelled sind und die Anwendung auf diesem Pfad immer nur nach pending fragt, verschwendet ein vollständiger Index auf status den Großteil seines Platzes damit, Zeilen abzudecken, die niemand auf diese Weise sucht. Der obige Partial-Index ist nur ein Bruchteil so groß wie ein vollständiger Index auf derselben Spalte, günstiger in der Pflege bei jedem Schreibvorgang auf eine nicht-pending Zeile (weil diese Writes diesen Index überhaupt nicht berühren) und — weil er kleiner ist — wahrscheinlicher dauerhaft im Cache. Dasselbe Muster gilt für Soft-Deletes (WHERE deleted_at IS NULL), Feature Flags und jede enum-artige Spalte mit einem dominanten, selten abgefragten Wert.

Covering-Indizes und Index-Only-Scans

Ein normaler Index-Scan muss die zugrunde liegende Tabelle (den „Heap“) weiterhin besuchen, um Spalten zu holen, die nicht im Index selbst enthalten sind. Ein Covering-Index, gebaut mit INCLUDE, speichert zusätzliche Spalten in den Blattseiten des Index allein zu dem Zweck, dass die Abfrage nur aus dem Index beantwortet werden kann und der Heap vollständig übersprungen wird — ein Index-Only-Scan.

CREATE INDEX idx_orders_customer_covering
ON orders (customer_id, status) INCLUDE (total, created_at);

EXPLAIN (ANALYZE, BUFFERS)
SELECT total, created_at FROM orders
WHERE customer_id = 4821 AND status = 'pending';
-- Index Only Scan using idx_orders_customer_covering

Die INCLUDE-Spalten sind nicht Teil des Sortierschlüssels des Index — sie helfen weder beim Filtern noch beim Sortieren, sie werden nur mitgeführt, damit der Heap-Lookup für Abfragen unnötig wird, die nur diese Spalten zurückgeben müssen. Das tauscht Indexgröße (die enthaltenen Spalten duplizieren Daten, die bereits in der Tabelle liegen) gegen Lesegeschwindigkeit, und es zahlt sich nur dann vollständig aus, wenn die Visibility Map der Tabelle von autovacuum gut gepflegt wird — ein Index-Only-Scan muss die Heap-Sichtbarkeit für Seiten, die nicht kürzlich vacuumed wurden, weiterhin prüfen. Eine Tabelle mit hoher Änderungsrate ohne ausreichendes autovacuum-Tuning wird also nicht den vollen Nutzen sehen.

Auswahl nach Abfrageform

Gleichheit oder Bereich auf einer skalaren Spalte, Sortierung oder Standard für allgemeine Zwecke: B-tree.
„Enthält dieses JSONB/Array X“ oder Full-Text-Search: GIN.
Geometrische Überlappung, Nähe, Bereichsüberlappung oder eine EXCLUDE-Constraint: GiST.
Riesige, überwiegend append-lastige Tabelle, bei der die Spalte mit der Einfügereihenfolge korreliert: BRIN.
Eine verzerrte Spalte, bei der Abfragen sich nur für den seltenen Wert interessieren: ein Partial-Index, kombiniert mit dem Basis-Typ, der zur Spalte passt.
Eine häufig ausgeführte Abfrage, die nur einige wenige Spalten zurückgeben muss: ein Covering-Index mit INCLUDE, zusätzlich zu dem Typ, der zum eigentlichen Filter passt.

Diese Optionen schließen sich nicht gegenseitig aus — ein Partial-GIN-Index oder ein Covering-B-tree mit einer WHERE-Klausel sind beide völlig gültig und oft die beste Antwort, sobald man sowohl die Form der Abfrage als auch die Verzerrung in den Daten erkannt hat.

Indextyp	Beantwortet	Relative Build-/Schreibkosten	Relative Größe	Typischer Einsatz
B-tree	Gleichheit, Bereich, Sortierung	niedrig	mittel	Primärschlüssel, Fremdschlüssel, `ORDER BY`-Spalten
GIN	Containment, Full-Text	hoch	mittel bis groß	JSONB, Arrays, `tsvector`
GiST	Überlappung, Nähe, Exclusion	mittel	mittel	Geometrie, Bereiche, `EXCLUDE`-Constraints
BRIN	korrelierte Bereichseingrenzung	sehr niedrig	winzig	Append-Only-Time-Series, riesige Log-Tabellen

Behandle diese Tabelle als Ausgangspunkt, nicht als Urteil — der einzige Weg herauszufinden, welcher Index einer konkreten Abfrage tatsächlich hilft, ist, ihn zu bauen und EXPLAIN (ANALYZE, BUFFERS) davor und danach zu vergleichen, da die reale Selektivität in deinen tatsächlichen Daten den Trade-off in beide Richtungen verschieben kann.

Noch etwas, das man sich früh angewöhnen sollte: Keiner dieser Indextypen sollte auf einer Tabelle, die bereits Produktions-Traffic bedient, mit einem nackten CREATE INDEX gebaut werden. Ein normales CREATE INDEX setzt ein Lock, das Schreibvorgänge auf der Tabelle für die gesamte Dauer des Builds blockiert, was bei einer großen Tabelle Minuten blockierter INSERT/UPDATE/DELETE-Statements bedeuten kann. CREATE INDEX CONCURRENTLY baut denselben Index — B-tree, GIN, GiST oder BRIN, die Option gilt für alle vier — ohne dieses Lock zu halten, zum Preis eines langsameren Builds und einer kleinen Chance, bei einer Unterbrechung auf halbem Weg neu ansetzen zu müssen.

Häufige Fallstricke

Fehler: einen B-tree-Index hinzufügen und erwarten, dass er eine JSONB-Containment-Abfrage beschleunigt. Ein B-tree auf einer jsonb-Spalte kann Gleichheit auf dem gesamten Dokument unterstützen, aber nicht @>-Containment. Lösung: Verwende GIN für Containment, Full-Text und Array-Mitgliedschaft.

Fehler: fastupdate-GIN-Indizes auf einer Tabelle mit hohem Schreibaufkommen unüberwacht lassen. Die Pending List kann so groß werden, dass autovacuum Mühe hat, mitzuhalten, und die Abfragelatenz dadurch langsam ansteigt. Lösung: Beobachte pg_stat_user_tables und tune autovacuum-Schwellenwerte speziell für Tabellen mit vielen GIN-Indizes.

Fehler: BRIN auf einer Spalte ohne Korrelation zur physischen Reihenfolge verwenden. Der Index sieht so aus, als existiere er, grenzt aber kaum etwas ein, und Abfragen fallen trotzdem auf das Scannen der meisten Kandidaten-Blockbereiche zurück. Lösung: Prüfe vor der Wahl von BRIN die correlation der Spalte in pg_stats.

Fehler: einen zusammengesetzten B-tree-Index mit Spalten in der falschen Reihenfolge bauen. Ein Index auf (status, customer_id) bedient eine Abfrage, die nur auf customer_id filtert, nicht so gut wie (customer_id, status). Lösung: Führe mit der Spalte, die am häufigsten isoliert verwendet wird oder die höchste Selektivität hat.

Fehler: niemals pg_stat_user_indexes auf ungenutzte Indizes prüfen. Jeder Index, der nicht real genutzt wird, ist reiner Schreib-Overhead. Lösung: Überprüfe idx_scan regelmäßig und lösche Indizes, die nach einem vollständigen Traffic-Zyklus noch auf null stehen.

Zum Abschluss

Es gibt nicht den einen „richtigen“ Postgres-Index — es gibt den richtigen Index für die konkrete Form der Abfrage, die du tatsächlich ausführst, und Postgres gibt dir vier strukturell unterschiedliche Werkzeuge, weil skalare Bereichs-Lookups, Containment, Überlappung und Korrelation auf riesigen Tabellen vier wirklich unterschiedliche Probleme sind. B-tree deckt den Großteil dessen ab, was ein typisches Schema braucht; GIN und GiST decken die JSONB-, Array-, Full-Text- und geometrischen Fälle ab, die ein B-tree überhaupt nicht anfassen kann; BRIN bringt enorme Platzeinsparungen auf riesigen, natürlich sortierten Tabellen; und Partial- und Covering-Indizes sind Modifikatoren, die jedes der genannten Werkzeuge günstiger oder schneller machen, sobald du die tatsächliche Verzerrung und das Zugriffsmuster deiner Daten verstehst.

Wenn du das nächste Mal zu CREATE INDEX greifst: Passt du ihn an die tatsächliche Form der Abfrage an, oder greifst du aus Gewohnheit standardmäßig zu B-tree?