BRIN steht für „Block Range INdex" — statt jede Zeile zu indizieren, speichert er pro Block-Range nur Min/Max-Werte. Das macht ihn winzig (oft nur Kilobyte für Millionen Zeilen), aber er funktioniert nur, wenn die Daten physikalisch sortiert auf der Disk liegen — typischerweise bei Zeitreihen oder Append-only-Logs.
Wie BRIN funktioniert
Postgres speichert Tabellen-Daten in 8 KB großen Pages. Ein BRIN-Index merkt sich pro Page-Range (default: 128 Pages = 1 MB) den Min- und Max-Wert der indizierten Spalte.
Bei einem Lookup:
- Postgres prüft alle Page-Ranges, ob der gesuchte Wert in der Min-Max-Spanne liegt.
- Pages, deren Range den Wert nicht enthalten kann, werden übersprungen.
- Pages, die den Wert enthalten könnten, werden gelesen und zeilenweise geprüft.
Das ist nur dann ein Gewinn, wenn die Daten gut korreliert auf der Disk liegen — sonst muss Postgres fast alle Page-Ranges prüfen.
Erstellen
CREATE TABLE measurements (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sensor_id bigint NOT NULL,
measured_at timestamptz NOT NULL,
value numeric NOT NULL
);
-- Sehr günstig auf großer Tabelle
CREATE INDEX measurements_time_idx
ON measurements USING brin (measured_at);Bei einer Tabelle mit 100 Mio. Zeilen ist der B-tree mehrere GB groß; der BRIN nur ein paar MB.
Wann BRIN gut ist
Voraussetzungen:
- Sehr große Tabellen (100 Mio.+ Zeilen) — sonst sind die Vorteile minimal
- Natürliche Sortierung auf Disk: Append-only-Daten, deren neue Zeilen ans Ende kommen
- Korrelierte Spalte: typisch
created_at,measured_at,event_time
Klassische Use-Cases:
- IoT-Sensor-Daten (Zeitreihen)
- Server-Logs
- Audit-Logs
- ETL-Output-Tabellen, die nur appended werden
NICHT gut:
- Tabellen mit häufigen UPDATEs / DELETEs (zerstören die Korrelation)
- Spalten, die nicht mit der Reihen-Reihenfolge korrelieren
Korrelation prüfen
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'measurements';correlation zwischen -1 und 1:
- Nahe 1 oder -1: Spalte ist gut sortiert auf Disk → BRIN sinnvoll
- Nahe 0: keine Korrelation → BRIN bringt fast nichts
Faustregel: BRIN nur, wenn correlation über 0.9 (oder unter -0.9) liegt.
pages_per_range tunen
Default ist 128 Pages pro Range. Kleinere Werte = präziser, aber größerer Index.
CREATE INDEX measurements_time_idx
ON measurements USING brin (measured_at)
WITH (pages_per_range = 32);Pragmatisch:
- Default (128) für Tabellen ab Hunderten Millionen Zeilen
- 32 oder 16 für mittlere Tabellen, wo Selektivität wichtiger als Speicher ist
BRIN warten
BRIN wird nicht automatisch aktualisiert wie B-tree. Beim Insert wird der Index zwar mitgepflegt, aber unsauber organisierte Bereiche bleiben — brin_summarize_new_values bringt's wieder in Form:
SELECT brin_summarize_new_values('measurements_time_idx');Bei Tabellen mit kontinuierlichem Append: regelmäßig aufrufen (per Cron oder Scheduler).
Mit Auto-Summarization (PG 10+) kann das automatisch passieren:
ALTER INDEX measurements_time_idx
SET (autosummarize = on);Größenvergleich praktisch
Bei einer Beispiel-Tabelle mit 100 Mio. Zeilen, indiziert auf created_at:
| Index-Typ | Typische Größe |
|---|---|
| B-tree | 3-5 GB |
| BRIN (default) | 5-50 MB |
BRIN (pages_per_range=32) | 20-200 MB |
Faktor 100-1000 kleiner als B-tree — bei richtiger Verwendung. Falsche Verwendung (unsortiert) macht ihn nutzlos.
Besonderheiten
BRIN braucht physikalisch sortierte Daten — sonst nutzlos.
Der Index speichert nur Min/Max pro Block-Range. Wenn die Daten zufällig verteilt sind, überlappen sich alle Ranges, und Postgres muss alle Pages prüfen. Korrelation in pg_stats über 0.9 ist die Daumenregel.
UPDATEs und DELETEs zerstören die Korrelation langsam.
Bei Hot-Update-Tabellen wird die Sortierung über die Zeit chaotisch — BRIN verliert seine Effizienz. Daher: BRIN für Append-only-Workloads. Bei gemischten Workloads B-tree nehmen.
autosummarize = on spart manuelle Wartung.
Ohne Autosummarize wird der Index zwar bei Inserts grob mitgepflegt, neuer Bereiche werden aber nicht sofort summarisiert. Bei kontinuierlich wachsenden Tabellen lohnt sich autosummarize.
Range-Queries gewinnen am meisten.
BRIN's Stärke ist nicht Single-Row-Lookup, sondern „alle Zeilen mit created_at BETWEEN X AND Y". Da kann Postgres ganze Page-Ranges überspringen. Bei WHERE id = 42 ist B-tree immer schneller.
CLUSTER oder pg_repack kann Korrelation herstellen.
Wenn du nachträglich BRIN auf eine bestehende Tabelle willst und die Korrelation schlecht ist: CLUSTER tabelle USING (b-tree_index) schreibt die Tabelle in der Index-Reihenfolge neu. Ein-Mal-Aktion, kein dauerhaftes Cluster.
BRIN auf Partitionen kombinieren mit Partition-Pruning.
Klassisches Pattern: Tabelle nach Monat partitionieren + BRIN auf created_at pro Partition. Partition-Pruning grenzt grob ein, BRIN feinmaschiger. Sehr effektiv für riesige Zeitreihen.