Einführung in PostgreSQL-Indizes

 (dlt.github.io)
30 Punkte von GN⁺ 2026-01-26 | 1 Kommentare | Auf WhatsApp teilen
  • PostgreSQL-Indizes sind eine zentrale Struktur zur Beschleunigung des Datenzugriffs und verbessern die Query-Performance, indem sie die Menge der von der Festplatte zu lesenden Daten reduzieren
  • Indizes stehen in verschiedenen Formen wie Btree, Hash, BRIN, GIN, GiST, SP-GiST zur Verfügung und sind jeweils für unterschiedliche Dateneigenschaften und Query-Muster optimiert
  • Indizes bringen verschiedene Kosten mit sich, darunter Festplattenspeicher, Schreib-Performance, Komplexität des Query-Planers und Speichernutzung
  • Mit erweiterten Funktionen wie partiellen Indizes, Mehrspaltenindizes, Covering-Indizes und Ausdrucksindizes lässt sich die Effizienz in bestimmten Situationen maximieren
  • Die richtige Auswahl und Verwaltung von Indizes wird als Schlüsselelement der PostgreSQL-Performance-Optimierung hervorgehoben

Grundkonzept von Indizes

  • Ein Index ist eine Struktur, mit der die Datenbank die von der Festplatte gelesene Datenmenge reduziert und so Queries beschleunigt
    • Auch Primärschlüssel, Unique Keys und Exclusion Constraints werden über Indizes umgesetzt
    • Ein Index ist effektiv, wenn das Query-Ergebnis weniger als 15–20 % der gesamten Tabelle umfasst; darüber kann ein Sequential Scan effizienter sein
  • PostgreSQL stellt standardmäßig 6 Indextypen bereit; über Erweiterungen können weitere Typen verwendet werden
    • Jeder Index verknüpft Schlüsselwerte mit der zugehörigen Datenposition (TID)

Auf der Festplatte gespeicherte Datenstruktur

  • PostgreSQL-Tabellen werden als Heap-Dateien gespeichert und bestehen aus 8-KB-Seiten
  • Jede Zeile (Tuple) wird ohne feste Reihenfolge gespeichert, und ihre interne Adresse wird durch ctid (current tuple id) identifiziert
    • Beispiel: (0,1) bedeutet das erste Tuple auf Seite 0
  • Indizes verknüpfen diese Heap-Positionen (ctid) in einer Baumstruktur und ermöglichen so eine schnelle Suche

Wie Indizes den Datenzugriff beschleunigen

  • Ohne Index führt PostgreSQL einen Sequential Scan aus und liest alle Seiten
    • Im Beispiel-Query zum Finden von name='Ronaldo' werden 6272 Seiten gelesen, was 265 ms dauert
  • Nach dem Hinzufügen eines Indexes wird auf einen Index Scan umgestellt, der nur 4 Seiten liest und in 0,077 ms abgeschlossen ist
    • Der Index ordnet Werte ctid zu und findet so schnell nur die benötigten Zeilen
  • Die Größe einer Indexdatei kann der Tabellengröße ähneln (z. B. 30 MB Tabelle → 30 MB Index)

Kostenfaktoren von Indizes

  • Neben der Leistungssteigerung bringen Indizes auch verschiedene Belastungen mit sich

Festplattenspeicher

  • Indizes belegen separaten Speicherplatz und können größer als die Tabelle werden
    • Zusätzliche Kosten entstehen bei Backup, Replikation und Disaster Recovery
    • Die Speichereffizienz kann durch partielle Indizes, Mehrspaltenindizes, BRIN usw. verbessert werden

Schreibvorgänge

  • Bei UPDATE, INSERT und DELETE entsteht Overhead für die Indexaktualisierung, wenn sich indexierte Spalten ändern

Query-Planer

  • Je mehr Indizes vorhanden sind, desto mehr Optionen muss der Planer berücksichtigen, was die Zeit für die Erstellung des Query-Plans erhöht

Speichernutzung

  • Indexseiten werden in den shared buffers geladen und gecacht; je mehr Indizes vorhanden sind, desto höher die Speicherbelastung
  • Aufgrund der Größenbeschränkung von Btree-Knoten nimmt bei größeren Spalten die Baumtiefe zu
  • Auch bei Sortierung, Mehrspalten-Scans, Vacuum, Reindex usw. wird zusätzlich work memory verwendet

Wichtige Indextypen

Btree

  • Die Standard-Indexstruktur von PostgreSQL, ein universeller Index, der in den meisten DBMS verwendet wird
    • Unterstützt schnelle Suche mit einer Zeitkomplexität von O(log n)
    • Balancierte Baumstruktur, bei der alle Blattknoten dieselbe Tiefe haben
    • Vorteilhaft für ORDER BY- und JOIN-Operationen und wird für Primärschlüssel- und Unique-Key-Constraints verwendet
  • Interne Knoten speichern Zeiger auf untergeordnete Knoten, Blattknoten speichern Schlüssel und Heap-Zeiger
  • Über linke und rechte Knotenzeiger ist eine bidirektionale Traversierung möglich
Nutzung mehrerer Indizes
  • PostgreSQL kann mehrere Indizes über Bitmap-AND/OR-Operationen kombinieren, um zusammengesetzte Bedingungen zu verarbeiten
    • Beispiel: Bei der Bedingung age=30 AND login_count=100 werden die Bitmaps zweier Indizes kombiniert
Mehrspaltenindizes
  • Mehrere Spalten können in einem einzigen Index gebündelt werden, was Speicher spart und die Geschwindigkeit erhöht
    • Allerdings ist die Spaltenreihenfolge wichtig, und der Index kann nur für Bedingungen genutzt werden, die von links beginnend übereinstimmen
Partielle Indizes
  • Mit einer Bedingung werden nur bestimmte Zeilen indiziert
    • Geringere Indexgröße, bessere RAM-Eignung und schnellere Abfragen
    • Beispiel: create index on rules(status) where status='enabled';
    • Nützlich bei unausgewogener Werteverteilung (status <> 'TODO' usw.)
Covering-Indizes
  • Wenn alle für eine Query benötigten Spalten im Index enthalten sind, kann das Ergebnis ohne Heap-Zugriff zurückgegeben werden (index-only scan)
    • create index abc_cov_idx on bar(a, b) including c;
    • Speichereffizienter als ein Mehrspaltenindex
Ausdrucksindizes
  • Es werden nicht Spaltenwerte selbst, sondern Funktions- oder Ausdrucksergebnisse indiziert
    • Beispiel: CREATE INDEX idx_lower_name ON customers (lower(name));
    • Nützlich für Suchen nach transformierten Werten wie LOWER(name)
    • Es können nur immutable Funktionen verwendet werden

Hash

  • Ein auf einer Hashmap-Struktur basierender Index, der bei langen Strings oder UUIDs speichereffizient ist
    • Speichert 32-Bit-Hashcodes und reduziert dadurch die Größe
    • Unterstützt nur Gleichheitsvergleiche (=); Sortierung oder Mehrspaltenindizes sind nicht möglich
    • Bei gleichmäßiger Hash-Verteilung kann eine schnellere Leseleistung als Btree möglich sein
  • Laut offizieller Dokumentation reduzieren Hash-Indizes durch direkten Zugriff auf Bucket-Seiten den I/O bei großen Tabellen

BRIN (Block Range Index)

  • Ein Index, der pro Blockbereich nur Minimal- und Maximalwerte speichert
    • Sehr kompakt und cache-freundlich
    • Geeignet für große, append-only und Time-Series-Daten
  • Wenn Zeilen häufig aktualisiert werden, sinkt die Effizienz durch MVCC-bedingte Duplikatspeicherung
  • Mit der Einstellung pages_per_range lässt sich der Trade-off zwischen Genauigkeit und Größe anpassen

GIN (Generalized Inverted Index)

  • Ein Index, der sich für die Suche in zusammengesetzten Daten eignet
    • Unterstützt die Suche nach bestimmten Elementen in Text, Arrays, JSONB usw.
    • Verwendet typspezifische Strategien (opclass)
    • Für JSON wird die Verwendung mit JSONB-Spalten, für Text mit tsvector oder der Erweiterung pg_trgm empfohlen

GiST & SP-GiST

  • Generalized Search Tree (GiST) und Space-Partitioned GiST (SP-GiST) sind Frameworks zur Implementierung von Indizes für bestimmte Datentypen
    • GiST unterstützt balancierte Bäume, SP-GiST unausgeglichene Strukturen
    • Einsatz bei Geodaten, inet, Bereichen und Textvektoren
    • GIN bietet schnelle Abfragen, GiST hat geringere Kosten für Aufbau und Pflege
    • Bei der Volltextsuche sollte je nach Anforderungen zwischen beiden Ansätzen gewählt werden

Fazit

  • Indizes sind ein Kernelement der PostgreSQL-Performance-Optimierung, und das Gleichgewicht zwischen schnellerem Lesen sowie Schreib- und Speicherkosten ist entscheidend
  • Wenn der passende Indextyp entsprechend den Dateneigenschaften und Query-Mustern gewählt wird, ist ein schneller und effizienter Datenbankbetrieb möglich
  • Ein geeignetes Indexdesign ist ein wesentlicher Faktor für die Skalierbarkeit und Stabilität großer Systeme

Verwandte Beiträge

1 Kommentare

 
GN⁺ 2026-01-26
Hacker-News-Kommentare

  • Die offizielle PostgreSQL-Dokumentation ist wirklich sehr gut geschrieben und macht sogar Spaß zu lesen, daher teile ich sie hier.
    Einführung in PostgreSQL-Indizes

  • Der Abschnitt zu Mehrspaltenindizes entspricht fast genau dem, was ich gelernt hatte.
    Ich habe mich aber gefragt, ob das auch in den neuesten PostgreSQL-Versionen noch genauso gilt.
    Früher habe ich bei einer Query ähnlich dem dritten Beispiel gesehen, dass ein bitmap index scan verwendet wurde, und seitdem habe ich die bisherige „Lehrmeinung“ noch einmal überdacht.
    Nebenbei: Beim Thema Indizes halte ich die Website und das Buch Use The Index, Luke für klassische Standardwerke, die das ganze Team lesen sollte.

    • In PostgreSQL 18 wurde index skip scan hinzugefügt, sodass nun auch nur mit nachgeordneten Spalten eines Mehrspaltenindex effizient gesucht werden kann.
      In früheren Versionen war das zwar auch möglich, erforderte aber einen vollständigen Index-Scan und war daher ineffizient.
      Passendes Video: YouTube-Link
    • Ein bitmap index scan grenzt die Seiten ein, auf denen sich wahrscheinlich Daten befinden, aber die eigentliche Bedingungsprüfung muss anschließend erneut erfolgen, weshalb die Leistung schlechter ist als bei einem normalen Index-Scan.

  • Ich fände es gut, wenn PostgreSQL incremental view maintenance nativ unterstützen würde.
    Das wäre ein Konzept, das sich wie ein Index automatisch aktualisiert, wenn sich die Basisdaten ändern, aber nicht auf bestimmte Views beschränkt ist, sondern auch auf beliebige Views angewendet werden kann.

    • Das ist ein ziemlich schwieriges Problem, weil Transaktionsverarbeitung damit verknüpft ist.
      Es gibt viele verwandte Projekte wie Noria, Materialize, Apache Flink, GCP Continuous Queries, Spark Streaming Tables, Delta Tables, ClickHouse streaming tables, TimescaleDB, ksqlDB und StreamSQL.
      In PostgreSQL hat kürzlich eine Erweiterung namens pg_ivm begonnen, dieses Problem anzugehen.
    • Bei Zeitreihendaten erfüllt die Funktion continuous aggregates von TimescaleDB bereits diese Rolle.

  • Die Diskussion B-tree vs. Hash-Indizes ist interessant.
    Viele Leute denken, dass für ID-Spalten Hash besser sei, aber tatsächlich ist der standardmäßige B-tree effizienter.
    Vor allem bei Einfügungen fast sequentieller Werte ist die Baumstruktur vorteilhafter.
    Allerdings hieß es in dem diesmal erwähnten Blogbeitrag umgekehrt, dass Hash im Benchmark gewonnen habe.

  • Das Timing dieses Artikels war gut.
    Die Leading-Column-Regel bei Mehrspaltenindizes war für mich immer verwirrend, aber dank bitmap index scan ist sie nicht mehr ganz so kritisch wie früher.
    Die Skip-Scan-Funktion in PostgreSQL 18 verändert das bisherige Verständnis deutlich, daher sollten Menschen, die das auf Basis älterer Versionen gelernt haben, ihr mentales Modell aktualisieren.

  • Ich finde, das ist ein wirklich großartiger Text als Material für PostgreSQL.
    Für B-tree-Indizes habe ich schon seit langem oft Use The Index, Luke zu Rate gezogen.

  • Ich halte das für Pflichtlektüre.
    Es geht weit über ein einfaches Einführungsniveau hinaus, ist tiefgehend und zugleich solange es nicht um die internen Strukturen geht gut lesbar.

  • Mir gefällt dieser einfache und bescheidene Schreibstil.
    Ich mag die Art, Wissen direkt zu vermitteln.