BRIN-Indizes in PostgreSQL

Wenn SQL-Server-Experten ernsthaft mit PostgreSQL arbeiten, fällt ihnen der Einstieg meist leicht. Die Tabellen verhalten sich wie erwartet, die Transaktionen sind vertraut und die B-Tree-Indizes ähneln denen, die sie seit Jahren in SQL Server verwenden. Dann aber stoßen sie auf BRIN-Indizes.

Auf den ersten Blick scheinen diese fast leichtsinnig zu sein: keine Zeilenzeiger, keine präzise Navigation und eine explizite Akzeptanz von Fehlalarmen. Und doch liefern BRIN-Indizes bei sehr großen Tabellen oft Leistungssteigerungen, für die in SQL Server Clustered Indizes, Partitionierung oder sogar Columnstore-Indizes erforderlich wären. 

Um zu verstehen, warum das so ist, müssen wir uns nicht nur ansehen, was BRIN tut, sondern auch, wie es intern funktioniert.

Von Präzision zu Wahrscheinlichkeit

Die Indizierung in SQL Server basiert auf Präzision. Ein nicht geclusterter Index ordnet Schlüssel Zeilenlokalisatoren zu. Ein Clustered-Index definiert das physische Layout der Tabelle selbst. Bei der Leistungsoptimierung geht es darum, die richtige Schlüsselreihenfolge zu wählen und die Fragmentierung unter Kontrolle zu halten.

PostgreSQL kann all das ebenfalls. Aber es verfolgt auch einen anderen Ansatz: Manchmal muss man nicht wissen, wo sich eine Zeile befindet – es reicht zu wissen, wo Zeilen nicht sein können. BRIN, kurz für Block Range Index, ist die Verkörperung dieses Ansatzes.

PostgreSQL speichert Tabellen als Sammlungen von 8-KB-Heap-Seiten. Ein BRIN-Index gruppiert diese Seiten in Blockbereiche, typischerweise 128 Heap-Seiten pro Bereich (etwa 1 MB Daten). Für jeden Bereich speichert PostgreSQL nur zusammenfassende Informationen, meist den Minimal- und Maximalwert der indizierten Spalte. Entscheidend ist, dass ein BRIN-Index keine Seiten-IDs oder Zeilenzeiger speichert.

Dies ist ein grundlegender Unterschied zu den B-Tree-Indizes und nicht gruppierten Indizes von SQL Server. Es gibt keine Liste von Heap-Seiten, die einem Bereich zugeordnet sind. Stattdessen ist die Beziehung zwischen einem BRIN-Indexeintrag und der Tabelle implizit. Die Position eines BRIN-Index-Tupels ist die Adresse des Blockbereichs.

• Bereich 0 entspricht den Heap-Seiten 0 – 127
• Bereich 1 entspricht den Heap-Seiten 128 – 255
• Bereich N entspricht den Heap-Seiten N × Seiten_pro_Bereich bis (N+1) × Seiten_pro_Bereich – 1

Es werden keine Zeiger gespeichert, da diese nicht benötigt werden. PostgreSQL leitet die Heap-Seiten mathematisch ab. Diese Designentscheidung ist einer der Hauptgründe dafür, dass BRIN-Indizes so klein und so schnell sind.

Was passiert während der Ausführung einer Abfrage?

Wenn eine Abfrage mit einem Prädikat wie einem Zeitstempelbereich eingeht, durchsucht PostgreSQL den BRIN-Index sequenziell. Für jeden Blockbereich vergleicht es das Abfrageprädikat mit den gespeicherten Minimal- und Maximalwerten. Liegt das Prädikat vollständig außerhalb des Bereichs, kann PostgreSQL den gesamten Blockbereich überspringen, ohne eine einzige Heap-Seite zu lesen. Wenn das Prädikat möglicherweise übereinstimmt, wird der Bereich als Kandidat markiert und nur diese Heap-Seiten werden gescannt.

Falsch-positive Ergebnisse sind zu erwarten und akzeptabel. Die Kosten für die Überprüfung einiger zusätzlicher Seiten sind im Vergleich zum Scannen der gesamten Tabelle vernachlässigbar. Da die Zuordnung zwischen Indexeintrag und Heap-Seiten implizit ist, ist der Overhead minimal: keine Zeigerverfolgung, keine Baumdurchquerung und ausgezeichnete Cache-Lokalität.

Ein konkretes Beispiel: Große Zeitreihendaten

Betrachten wir eine klassische Protokoll- oder Ereignistabelle: nur anhängbar, nach Zeit geordnet und sehr groß. Genau für diese Art von Workload wurde BRIN entwickelt.

-- Erstellen einer einfachen Tabelle
 CREATE TABLE Events
 (
 ID BIGINT GENERATED ALWAYS AS IDENTITY,
 Ts TIMESTAMPTZ NOT NULL,
 DeviceID INT NOT NULL,
 Payload TEXT NOT NULL
 );
 
 -- Einfügen einiger Testdaten
 INSERT INTO Events (Ts, DeviceID, Payload)
 SELECT
 TIMESTAMPTZ ‚2025-01-01 00:00:00+00‘ + MAKE_INTERVAL(secs => g),
 (g % 1000) + 1,
 MD5(g::TEXT)
 FROM GENERATE_SERIES(1, 20000000) AS g;
 
 -- Statistiken aktualisieren
 ANALYZE Events;

Mit 20 Millionen Zeilen, die in streng aufsteigender Zeitstempelreihenfolge eingefügt wurden, passt das physische Layout der Tabelle perfekt zu zeitbasierten Abfragen. Eine Abfrage, die eine einzelne Stunde filtert, muss dennoch einen großen Teil der Tabelle ohne Index untersuchen. Die folgende Abfrage dauert auf meinem System etwa 200 ms (Bild 1):

-- Paralleler sequenzieller Scan
 -- Ausführungszeit: 200 ms
 EXPLAIN (ANALYZE, BUFFERS)
 SELECT COUNT(*) FROM Events
 WHERE
 Ts >= TIMESTAMPTZ ‚2025-06-01 10:00:00+00‘
 AND Ts < TIMESTAMPTZ ‚2025-06-01 11:00:00+00‘;

Fügen wir nun einen BRIN-Index hinzu:

-- BRIN-Index erstellen
 CREATE INDEX idx_Events_Ts_Brin
 ON Events
 USING BRIN (Ts)
 WITH (pages_per_range = 128);
 
 -- Statistiken aktualisieren
 ANALYZE Events;

Wenn wir dieselbe Abfrage erneut ausführen, wird ein anderer Ausführungsplan angezeigt. PostgreSQL verwendet den BRIN-Index, um eine kleine Anzahl relevanter Blockbereiche zu identifizieren, und liest nur diese Heap-Seiten. Die Pufferstatistiken machen den Vorteil deutlich: Es werden weit weniger Seiten angesprochen. Die Abfrage läuft nun etwa 2 ms (Bild 2):

-- Bitmap-Index/Heap-Scan
-- Ausführungszeit: 2 ms
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM Events
WHERE
Ts >= TIMESTAMPTZ ‚2025-06-01 10:00:00+00‘
AND Ts < TIMESTAMPTZ ‚2025-06-01 11:00:00+00‘;

Noch interessanter ist der Größenvergleich:

-- Der BRIN-Index ist sehr klein.
 SELECT
 pg_size_pretty(pg_relation_size(‚Events‘)) AS heap_size,
 pg_size_pretty(pg_relation_size(‚idx_Events_Ts‘)) AS brin_size;

Der BRIN-Index ist sehr klein und hat nur eine Größe von etwa 72 KB – im Vergleich zu 1611 MB der gesamten Heap-Tabelle. Bei einer Tabelle mit mehreren zehn Millionen Zeilen wird der BRIN-Index oft in Kilobyte oder wenigen Megabyte gemessen (Bild 3).

Warum dies für SQL-Server-Experten ungewohnt ist

Aus Sicht von SQL Server erscheint dieser Ansatz ungewöhnlich. Ein Clustered Index auf einer Zeitstempel-Spalte würde eine ähnliche Range-Scan-Leistung bieten, jedoch auf Kosten einer großen Indexstruktur und fortlaufender Wartung. Eine Partitionierung könnte den Scan-Umfang reduzieren, führt jedoch zu einer erhöhten Komplexität der Verwaltung. Columnstore-Indizes verwenden Metadaten auf Segmentebene mit MIN- und MAX-Werten, was konzeptionell BRIN ähnelt, aber sie verfügen über eine ganz andere Ausführungs-Engine und ein anderes Workload-Profil.

BRIN kann als automatisches, extrem leichtgewichtiges, tabelleninternes Partitions-Pruning betrachtet werden. Es erzielt einen Großteil der Vorteile dieser SQL-Server-Techniken mit einem Bruchteil des Overheads.

Die Kompromisse: Wenn BRIN versagt

Die Effizienz von BRIN hängt von der Datenkorrelation ab. Wenn die Werte zufällig verteilt sind, überschneiden sich die Minimal- und Maximalwerte der Blockbereiche stark. In diesem Fall kommen die meisten Bereiche infrage, und PostgreSQL scannt letztendlich ohnehin große Teile der Tabelle.

BRIN ist auch für Punktabfragen ungeeignet. Abfragen, die nach einer bestimmten ID suchen oder eine hohe Präzision erfordern, benötigen weiterhin B-Tree-Indizes. BRIN wurde entwickelt, um den Suchraum zu reduzieren, nicht um Zeilen zu lokalisieren.

Ein weiterer subtiler Nachteil ist die Kostenschätzung. Da BRIN von Natur aus grob ist, kann der Planer die Selektivität falsch einschätzen, insbesondere bei kleineren Tabellen. In diesen Fällen kann PostgreSQL zu Recht entscheiden, dass ein sequenzieller Scan kostengünstiger ist.

Die wichtigste Erkenntnis für SQL-Server-Experten ist, dass BRIN kein Ersatz für B-Tree-Indizes ist. Es handelt sich um ein ergänzendes Tool. In PostgreSQL ist es üblich – und leistungsstark –, präzise B-Baum-Indizes für den OLTP-Zugriff mit BRIN-Indizes für groß angelegte analytische Scans derselben Tabelle zu kombinieren. Jeder Index dient einem anderen Zweck, und zusammen übertreffen sie oft komplexere Designs.

Fazit

BRIN-Indizes verdeutlichen einen tieferen philosophischen Unterschied zwischen PostgreSQL und SQL Server. SQL Server legt Wert auf Präzision und sorgfältig entwickelte Indexstrukturen. PostgreSQL mit BRIN vertritt die Idee, dass ein Index unvollkommen sein kann, solange er kostengünstig und in großem Maßstab effektiv ist.

Sobald man das verinnerlicht hat, erscheint BRIN nicht mehr seltsam. Es erscheint eher wie eine pragmatische Optimierung, die Präzision gegen Einfachheit eintauscht – und gewinnt.

Für SQL-Server-Experten, die zu PostgreSQL wechseln, bedeutet das Verständnis von BRIN mehr als nur das Erlernen eines neuen Indextyps. Es bedeutet, eine neue Denkweise in Bezug auf Leistung zu erlernen.