PostgreSQL-Indizes: Welcher Typ wann? (10-Mio-Zeilen-Benchmark)

Früher oder später tippt jedes Postgres-Team in Eile CREATE INDEX, und die meisten tippen denselben. B-tree ist der richtige Standard, aber nicht immer die richtige Antwort. Auf einer Tabelle mit 10 Millionen Zeilen fand ich einen Index, der 3.000-mal kleiner und nur 3-mal langsamer war, einen Index, der gar nichts bewirkte, und einen „abdeckenden“ Index, der Abfragen beschleunigte, bis ein ganz normales UPDATE den Vorteil wieder auffraß.

Das ist Teil 1 von Postgres in Production, einer Serie über die Entscheidungen, die zählen, sobald eine Datenbank das Tutorial verlässt: Indizes, VACUUM und Bloat, Connection Pooling, Partitionierung und JSONB. Jeder Teil folgt einer Regel: erst messen, dann erklären. Die Zahlen unten stammen von PostgreSQL 18.6 und einem Open-Source-Benchmark, den du selbst ausführen kannst.

Welcher PostgreSQL-Index-Typ passt: die kurze Antwort

Ein PostgreSQL-Index ist eine eigene Datenstruktur, mit der die Datenbank Zeilen findet, ohne die ganze Tabelle zu lesen. Der Typ bestimmt, welche Fragen er beantworten kann und wie viel Platz und Schreibzeit er kostet.

Wie ein Index eine Zeile findet und was ein Scan kostet

Ohne Index liest PostgreSQL die Tabelle Seite für Seite und prüft jede Zeile. Die Testtabelle ist 1,6 GB groß, 208.376 Seiten à 8 KB, und ein Sequential Scan hat jede einzelne gezählt. Ein B-tree-Index auf derselben Tabelle ist ein drei bis vier Ebenen tiefer Baum, ein Schlüssel kostet also eine Handvoll Seiten: Die Abfrage unten las 14 Buffer statt 208.376.

Das ist die ganze Idee, alles andere sind Abwägungen. Ein größerer Index beantwortet mehr Arten von Fragen, kostet aber Platz, Aufbauzeit und Schreibzeit. Der Planer wählt anhand geschätzter Kosten zwischen Scan und Index, deshalb wird ein vorhandener Index nicht immer benutzt (mehr dazu unten). Was wirklich passiert ist, zeigt EXPLAIN (ANALYZE, BUFFERS). Seit PostgreSQL 18 ist BUFFERS bei ANALYZE automatisch dabei.

Der Benchmark: eine Tabelle, 10 Millionen Zeilen, und was jeder Index kostet

Eine Tabelle events mit bigint-Primärschlüssel, user_id (zufällig, etwa 10 Zeilen pro Nutzer), status (70 % done, 20 % active, 9 % pending, 0,85 % failed, 0,15 % archived), created_at (eine Zeile alle 3 Sekunden, wächst also mit id), einer Spalte tags text[] und einer Spalte payload jsonb. Die Tabelle ist 1,6 GB groß, der Primärschlüssel 214 MiB. Die Daten kommen aus einem festen Zufalls-Seed, jeder Lauf erzeugt also dieselben Zeilen.

Jede Abfrage lief einmal zum Aufwärmen und danach dreimal, die Tabellen zeigen den Median. Punkt- und Bereichsabfragen wurden für mehrere Parameterwerte wiederholt. Jede gemessene Abfrage lief außerdem mit deaktivierten Index-Scans, und die Antworten mussten übereinstimmen, sonst bricht das Skript ab. PostgreSQL 18.6, warmer Cache, autovacuum aus, damit die Visibility Map unter Kontrolle bleibt (eine Produktionsdatenbank sollte so nicht laufen).

B-tree: der Standard aus gutem Grund

B-tree ist ein balancierter Baum aus sortierten Schlüsseln. Weil die Schlüssel sortiert sind, beantwortet eine Struktur =, <, >, BETWEEN, ORDER BY und IS NULL, und er ist das, was CREATE INDEX baut, solange du nichts anderes verlangst.

CREATE INDEX ix_user ON events (user_id);
SELECT * FROM events WHERE user_id = 424242;

Das ist etwa 1.400-mal schneller für einen zusätzlichen Index von 88,5 MiB, aufgebaut in 1,4 Sekunden auf einer 1,6-GB-Tabelle. Der Index ist klein, weil B-tree jeden doppelten Schlüssel nur einmal speichert und eine Liste von Zeilenzeigern führt (Deduplizierung, seit PostgreSQL 13). Derselbe Index auf einer Zeitspalte, in der jeder Schlüssel einzigartig ist, ist 214 MiB groß.

Wenn eine Spalte im WHERE, JOIN oder ORDER BY einer häufigen Abfrage steht, die wenige Zeilen liefert, ist ein B-tree fast immer die Antwort. Die anderen Typen gibt es für das, was B-tree schlecht kann.

Hash-Index: nicht besser als B-tree

Ein Hash-Index speichert einen Hash des Schlüssels und unterstützt nur Gleichheit. Die Theorie sagt, er müsse bei Punktabfragen schneller sein. In diesem Test war er es nicht:

Gleiche Geschwindigkeit, dreifacher Platz, neunfache Aufbauzeit, und keine Bereiche oder Sortierung. Hash-Indizes sind seit PostgreSQL 10 absturzsicher, gefährlich sind sie also nicht, aber bei vielen doppelten Schlüsseln haben sie keinen Vorteil gegenüber einem B-tree, der dedupliziert. Das ist ein Datensatz, kein Gesetz: Bei langen String-Schlüsseln kann Hashing Platz sparen, miss also, bevor du ihn abschreibst.

BRIN: ein 72-KiB-Index für zeitlich geordnete Daten

Ein BRIN (Block Range Index) speichert für jeden Bereich von Tabellenseiten eine Zusammenfassung, normalerweise Minimum und Maximum. Eine Abfrage überspringt jeden Bereich, dessen Zusammenfassung nicht passen kann. Er ist winzig, funktioniert aber nur, wenn die Werte der Spalte der physischen Reihenfolge der Tabelle folgen, und ein Append-only-created_at tut genau das: pg_stats meldet hier eine Korrelation von 1,0 für created_at und etwa 0 für user_id.

CREATE INDEX ix_ts_brin ON events USING brin (created_at);

Die Abfrage: ein Tag Ereignisse, 28.800 Zeilen von 10 Millionen.

B-tree ist dreimal schneller, und BRIN ist etwa 3.000-mal kleiner. Bei einer Tabelle, die um Milliarden Zeilen wächst, ist das der Unterschied zwischen einem Index, der in den Speicher passt, und einem, der es nicht tut, und ein BRIN-Index lässt sich in Sekundenbruchteilen neu bauen. Wenn deine Abfragen immer „der letzte Tag“ oder „der letzte Monat“ lauten, ist BRIN die günstige Wahl. Wenn eine einzelne Suche unter einer Millisekunde antworten muss, nimm B-tree. Ein kleineres pages_per_range macht den Index etwas größer und schärfer.

Partielle Indizes: nur die Zeilen indizieren, die zählen

Ein partieller Index hat eine WHERE-Klausel und enthält nur die passenden Zeilen. Er ist die günstigste Optimierung in diesem Artikel, weil viele Abfragen einen kleinen, klar definierten Ausschnitt betreffen: unverarbeitete Jobs, fehlgeschlagene Zahlungen, aktive Sessions.

CREATE INDEX ix_failed_ts ON events (created_at) WHERE status = 'failed';

SELECT id, created_at FROM events
WHERE status = 'failed' ORDER BY created_at DESC LIMIT 50;

Fehlgeschlagene Ereignisse sind 0,85 % der Zeilen. Der partielle Index beantwortet die Abfrage so schnell wie der volle und ist etwa 160-mal kleiner, baut in einem Bruchteil der Zeit und kostet beim Schreiben fast nichts, weil 99 % der Inserts ihn nie berühren. Die Regel: Das WHERE der Abfrage muss das WHERE des Index implizieren, schreibe die Bedingung also in beiden gleich. Ein Parameter wie status = $1 kann den Planer in einem generischen Plan daran hindern, den Treffer zu beweisen, prüfe mit EXPLAIN, ob der partielle Index genutzt wird.

Abdeckende Indizes: schneller, aber nur auf einer sauberen Tabelle

Ein Index-Only-Scan beantwortet eine Abfrage allein aus dem Index, ohne die Tabelle zu besuchen. INCLUDE (ab PostgreSQL 11) fügt dem Index genau dafür zusätzliche Spalten hinzu.

CREATE INDEX ix_user_cov ON events (user_id) INCLUDE (created_at, status);
SELECT created_at, status FROM events WHERE user_id = 424242;

Zwei Lehren. Erstens: Der Gewinn ist echt, aber bescheiden (1,4-fach), während der Index 4,4-mal größer ist: Für eine Abfrage mit zehn Ergebniszeilen ist ein einfacher B-tree schon gut. Abdeckende Indizes lohnen sich in der Theorie mehr, wenn eine Abfrage viele Zeilen aus einem Indexbereich liefert und jeder Heap-Zugriff zählt; miss das auf deinen eigenen Daten. Zweitens: Ein Index-Only-Scan prüft weiterhin die Visibility Map, um zu sehen, ob eine Seite vollständig sichtbar ist, und ein UPDATE löscht diese Bits. Nachdem 1 % der Zeilen angefasst wurden, verteilt über jede Seite, war der Vorteil weg, bis VACUUM wieder lief. Autovacuum übernimmt das normalerweise; der Benchmark schaltet es ab, um den Effekt sichtbar zu machen.

Mehrspaltige Indizes und Skip Scan in PostgreSQL 18

Ein mehrspaltiger B-tree sortiert nach der ersten Spalte, dann innerhalb jedes ersten Werts nach der zweiten, die Reihenfolge der Spalten zählt also. Vor PostgreSQL 18 konnte eine Abfrage, die nur nach der zweiten Spalte filterte, so einen Index nicht effizient nutzen. PostgreSQL 18 bringt Skip Scan, der durch die verschiedenen Werte der führenden Spalte springt.

CREATE INDEX ix_status_ts ON events (status, created_at);
-- gar kein Filter auf status:
SELECT count(*) FROM events
WHERE created_at >= '2025-02-12' AND created_at < '2025-02-12 01:00';

EXPLAIN meldet jetzt Index Searches: 11 Abstiege durch den Index statt einem, was immer noch winzig ist. Skip Scan macht einen Index nützlicher, den du ohnehin hast. Er funktioniert, wenn die übersprungene Spalte wenige verschiedene Werte hat (hier fünf Status). Bei Millionen verschiedener führender Werte lässt er nach, ersetzt also keinen eigenen Index auf die Spalte, nach der du filterst.

GIN: wenn eine Zeile viele Werte enthält

Ein GIN (Generalized Inverted Index) bildet jedes Element auf die Zeilen ab, die es enthalten, wie das Register hinten in einem Buch. Er ist der Index für Arrays, jsonb, Volltextsuche (tsvector) und, mit der Erweiterung pg_trgm, für LIKE '%text%'.

CREATE INDEX ix_tags ON events USING gin (tags);
CREATE INDEX ix_payload ON events USING gin (payload jsonb_path_ops);

SELECT count(*) FROM events WHERE tags @> ARRAY['rare'];
SELECT count(*) FROM events WHERE payload @> '{"beta": true}';

Drei Dinge zum Mitnehmen:

• Die Selektivität entscheidet alles. GIN glänzt, wenn der Treffer selten ist (129-mal schneller), und hilft kaum, wenn der Treffer 2,5 % der Tabelle sind, weil die Heap-Seiten trotzdem gelesen werden müssen. Eine unselektive Enthält-Abfrage ist ein Hinweis auf das Datenmodell, kein Indexproblem.
• Probiere bei jsonb jsonb_path_ops. Er ist etwa halb so groß und bei @>-Abfragen schneller, unterstützt dafür aber die Operatoren zur Schlüsselprüfung (?, ?|, ?&) nicht.
• Parallele Builds sind neu. PostgreSQL 18 kann einen GIN-Index mit parallelen Workern bauen: 3,8 s mit vier Workern gegenüber 14,4 s seriell beim tags-Index, 3,8-mal schneller, was beim Neuaufbau großer Tabellen zählt.

GiST und SP-GiST: für Formen und Bereiche

Diese habe ich auf diesen Daten nicht gemessen, weil die Testtabelle keine Geometrie und keine Bereiche enthält, und ein erfundener Benchmark würde nur mein Schema messen. GiST ist ein Rahmen für baumbasierte Indizes über Daten ohne lineare Ordnung: geometrische Typen (PostGIS nutzt ihn), Bereichstypen und Nächste-Nachbarn-Suche (ORDER BY location <-> point). Er stützt auch Exclusion Constraints wie „zwei Buchungen desselben Raums dürfen sich nicht überschneiden“. SP-GiST unterstützt raumpartitionierte Strukturen wie Quad-Trees und Radix-Tries, nützlich für unbalancierte Daten wie IP-Adressen oder Telefonvorwahlen. Wenn deine Abfrage &&, <-> oder @> auf Formen oder Bereichen nutzt, fang hier an.

Wenn der Planer deinen Index ignoriert

Der häufigste Index-Fehler ist nicht der falsche Typ, sondern eine Abfrage, die der Index nicht bedienen kann. Zwei Fälle, gemessen auf derselben Tabelle:

Eine indizierte Spalte in eine Funktion oder einen Cast zu packen macht den Index unbrauchbar. Schreibe die Bedingung also als Bereich um oder indiziere den Ausdruck. Beachte, dass timestamptz::date von der Zeitzone der Session abhängt und sich deshalb nicht direkt indizieren lässt: Der Ausdrucks-Index nutzt AT TIME ZONE 'UTC'. Und wenn eine Abfrage den Großteil der Tabelle trifft, hat der Planer recht, den Index zu überspringen: 70 % einer Tabelle über einen Index zu lesen ist langsamer als ein Sequential Scan.

Die Schreibsteuer: Jeder Index kostet bei jedem Insert

Lesen ist nur die halbe Rechnung. Jeder Insert muss in jedem Index der Tabelle einen Eintrag ergänzen. Ich habe 1 Million Zeilen in eine leere Tabelle mit Primärschlüssel und dann 0, 1, 3 und 5 weiteren Indizes eingefügt:

Der Sprung von 3 auf 5 Indizes, mehr als eine Verdopplung der Zeit, sind die beiden GIN-Indizes: Sie sind am teuersten in der Pflege, und PostgreSQL puffert GIN-Updates standardmäßig in einer Pending-Liste, um das abzumildern. Indizes verlangsamen auch UPDATE (wenn sich eine indizierte Spalte ändert) und VACUUM, und sie brauchen Speicher und Platte. Ein Index, den du nicht nutzt, ist reine Kostenstelle.

Zufällige UUID-Schlüssel: versteckte Indexkosten

Ein Primärschlüssel ist ebenfalls ein Index, und sein Typ bestimmt, wie der B-tree wächst. Eine zufällige UUIDv4 landet irgendwo im Baum, Inserts berühren also Seiten im ganzen Baum und spalten sie. Eine UUIDv7 beginnt mit einem Zeitstempel, neue Schlüssel kommen also der Reihe nach am rechten Rand an, wie bei einer Sequenz. PostgreSQL 18 hat dafür ein eingebautes uuidv7(). Ich habe 5 Millionen Zeilen in eine Tabelle mit je einer Art von Primärschlüssel eingefügt:

Der zeitlich geordnete Schlüssel fügte 1,3-mal schneller ein und erzeugte einen etwa ein Fünftel kleineren Index. Diese Tabellen passen in den Speicher, der Abstand dürfte also wachsen, sobald der Index nicht mehr hineinpasst, weil zufällige Schlüssel dann bei jedem Insert den Cache verfehlen. UUIDv7 verrät außerdem die Erstellungszeit in der ID, was zählt, wenn die ID öffentlich ist. Eine bigint-Sequenz ist noch kleiner (8 gegen 16 Byte).

So findest du ungenutzte und fehlende Indizes

Rate nicht, frag die Statistik. Indizes, die seit dem letzten Zurücksetzen der Statistik nie gescannt wurden (pg_stat_database.stats_reset zeigt, wie lange die Zähler schon laufen):

SELECT s.relname AS table_name, s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size, s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

Lass eindeutige und Primärschlüssel-Indizes aus, sie erzwingen Constraints. Beobachte über einen vollen Geschäftszyklus (Monatsabschluss-Jobs), und denk daran, dass jedes Replikat eigene Zähler führt. Für fehlende Indizes schau auf Tabellen, bei denen Sequential Scans viele Zeilen lesen:

SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC LIMIT 10;

Finde dann die Abfragen dahinter mit pg_stat_statements und prüfe den Plan mit EXPLAIN (ANALYZE, BUFFERS). Auf einem Live-System baust du immer mit CREATE INDEX CONCURRENTLY, das Schreibzugriffe nicht blockiert (es kann nicht in einer Transaktion laufen, und ein fehlgeschlagener Build hinterlässt einen INVALID-Index, den du löschen musst), und entfernst Indizes mit DROP INDEX CONCURRENTLY. Ein Index, der nach vielen Updates und Deletes aufgebläht ist, lässt sich ohne Blockieren von Schreibzugriffen mit REINDEX INDEX CONCURRENTLY neu bauen.

Was sich bei Indizes in PostgreSQL 18 und 19 ändert

PostgreSQL 18, das aktuelle Major-Release, brachte vier indexbezogene Änderungen, die in diesem Artikel vorkommen: Skip Scan für mehrspaltige B-tree-Indizes, parallele GIN-Builds, Index Searches und automatisches BUFFERS bei EXPLAIN ANALYZE sowie das eingebaute uuidv7() für indexfreundliche Schlüssel. Es hat außerdem das neue asynchrone I/O-Subsystem eingeführt, das die Sequential und Bitmap Scans beschleunigt, gegen die diese Indizes antreten. PostgreSQL 19 ist im Release-Candidate-Stadium, und PostgreSQL 14 erreicht am 12. November 2026 das Support-Ende, jetzt ist also ein guter Zeitpunkt zu prüfen, welche Version deine eigene Datenbank nutzt. Die offiziellen Release Notes haben die vollständige Liste, und wenn du dich noch für eine Datenbank entscheidest, vergleicht Postgres vs. MySQL 2026 die beiden.

Eine praktische Checkliste

1. Schreibe zuerst die Abfrage und sieh dir ihren Plan mit EXPLAIN (ANALYZE, BUFFERS) an.
2. Fang mit einem B-tree auf den Spalten in WHERE und JOIN an, Gleichheitsspalten zuerst.
3. Wenn nur ein kleiner Ausschnitt der Zeilen zählt, mach ihn partiell.
4. Wenn die Tabelle riesig, append-only und zeitlich geordnet ist, probiere BRIN vor B-tree.
5. Für Arrays, jsonb und Textsuche nimm GIN und prüfe, ob die Treffer selektiv sind.
6. Füge INCLUDE nur für heiße Abfragen hinzu, die viele Zeilen liefern, und lass VACUUM laufen.
7. Baue mit CREATE INDEX CONCURRENTLY und prüfe pg_stat_user_indexes jedes Quartal.
8. Lege für die referenzierende Spalte jedes Fremdschlüssels, nach der du joinst oder löschst, einen Index an: PostgreSQL erzeugt ihn nicht von selbst.
9. Nimm für UUID-Primärschlüssel unter PostgreSQL 18 lieber UUIDv7 als zufällige UUIDv4.

Für Gewohnheiten auf Abfrageebene, die dazu passen, siehe SQL Query Optimization 2026, und für Vektoren in Postgres Brauchst du eine Vektordatenbank?.

Das Fazit

Nimm standardmäßig B-tree und miss, bevor du zu etwas anderem greifst. Mach ihn partiell, wenn nur ein Ausschnitt der Zeilen zählt, nutze BRIN bei riesigen, zeitlich geordneten Tabellen und GIN, wenn du in Arrays und Dokumenten suchst und die Treffer selten sind. Sei vorsichtig mit dem Rest: Ein Hash-Index war nicht schneller, ein abdeckender Index war 4,4-mal größer für einen 1,4-fachen Gewinn, und jeder zusätzliche Index verlangsamte Inserts. Der beste Index ist der kleinste, der deine Abfrage beantwortet, und der zweitbeste ist der, den du gelöscht hast.

Indizes sind keine Geschwindigkeit zum Nulltarif. Jeder ist ein Tausch zwischen Lesezeit, Platte, Speicher und Schreibzeit, wähle den Typ also nach der Form der Abfrage und behalte nur die, die laut Statistik genutzt werden.

Postgres, PostgreSQL und das Slonik-Logo sind Marken oder eingetragene Marken der PostgreSQL Community Association of Canada.