Optimierung von analytischen Funktionen für große Datenmengen: Indexierung und Partitionierung

Wenn die Datenmenge groß wird (so wie die Nachrichten über Deadlines in Firmen-Chats), werden Abfragen zum Auswählen und Verarbeiten langsamer. Hier sind die Hauptgründe:

1. Fehlende Indizes. Wenn PostgreSQL für eine Abfrage die ganze Tabelle scannen muss (das nennt sich "Seq Scan" — sequentielles Scannen), kann die Abfrage spürbar länger dauern.
2. Ineffiziente SQL-Abfragen. Wenn deine Abfragen ohne Optimierung gebaut sind, kannst du auch mit Indizes auf Produktionsprobleme stoßen. Zum Beispiel hast du vergessen, wichtige Bedingungen im WHERE zu nutzen? Dann kannst du dich auf eine lange Ausführungszeit einstellen.
3. Große Datenmengen in einer Tabelle. Wenn du zum Beispiel versuchst, Verkäufe für alle Jahre auf einmal zu analysieren, helfen manchmal selbst Indizes nicht mehr.

Aber keine Panik, wir haben zwei bewährte Methoden, um das zu lösen: Indexierung und Partitionierung.

Indizes nutzen, um Abfragen zu beschleunigen

Hier ein einfaches Beispiel, wie man einen Index erstellt:

CREATE INDEX idx_sales_date ON sales(transaction_date);

• Hier ist idx_sales_date der Name des Index (du kannst ihn nennen, wie du willst, aber ein sinnvoller Name ist besser).
• ON sales(transaction_date) — gibt an, für welche Tabelle und welche Spalte der Index erstellt wird.

Dieser Index ist besonders nützlich, wenn du oft nach dem Feld transaction_date filterst.

Ein Beispiel für eine Abfrage, die davon profitiert:

SELECT *
FROM sales
WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31';

Indexierung von zusammengesetzten Schlüsseln

Wenn deine Abfragen oft eine Kombination aus mehreren Feldern nutzen, zum Beispiel region und product_id, solltest du einen zusammengesetzten Index anlegen:

CREATE INDEX idx_sales_region_product ON sales(region, product_id);

Jetzt laufen Abfragen wie diese viel schneller:

SELECT *
FROM sales
WHERE region = 'Nordamerika' AND product_id = 42;

Verwendung von Unique-Indizes

Unique-Indizes beschleunigen nicht nur die Suche, sondern garantieren auch die Einzigartigkeit der Werte in einer Spalte. Zum Beispiel:

CREATE UNIQUE INDEX idx_unique_customer_email ON customers(email);

Jetzt kannst du nicht mehr versehentlich zwei Kunden mit derselben E-Mail-Adresse anlegen.

Indexierung für analytische Funktionen

Einige Analysefunktionen wie SUM, COUNT oder AVG können einen Index nutzen, um Werte schneller zu berechnen. Hier ein Beispiel:

CREATE INDEX idx_sales_amount ON sales(amount);

Abfrage:

SELECT SUM(amount)
FROM sales 
WHERE transaction_date >= '2023-01-01';

wird dank des Index schneller ausgeführt.

Tabellenpartitionierung für große Datenmengen

Tabellenpartitionierung ist der Prozess, eine große Tabelle in kleinere logische Teile zu teilen, die Partitionen genannt werden. Zum Beispiel kannst du die Tabelle sales nach Jahren partitionieren: sales_2021, sales_2022 usw.

Du denkst, das ist kompliziert? Eigentlich macht PostgreSQL das einfacher, als es aussieht.

Arten der Partitionierung

1. Range Partitioning (Range Partitioning). Die Daten werden nach einem Bereich aufgeteilt, zum Beispiel nach Datum.
2. List Partitioning (List Partitioning). Die Daten werden nach exakten Werten aufgeteilt, zum Beispiel nach Regionen.
3. Hash Partitioning (Hash Partitioning). Nutzt eine Hash-Funktion, um die Daten zu verteilen (wird seltener manuell verwendet).

Erstellen einer partitionierten Tabelle

Lass uns eine Verkaufstabelle mit Partitionierung nach Jahr erstellen.

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    transaction_date DATE NOT NULL,
    amount NUMERIC,
    region TEXT
) PARTITION BY RANGE (transaction_date);

Jetzt erstellen wir Partitionen für verschiedene Jahre:

CREATE TABLE sales_2021 PARTITION OF sales
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE sales_2022 PARTITION OF sales
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Abfragen, die nach Datum filtern, arbeiten automatisch nur mit der passenden Partition. Das kannst du leicht mit dem Befehl EXPLAIN überprüfen.

Beispiel mit Partitionierung

So würde eine Abfrage aussehen, um die Verkäufe nur für 2021 zu summieren:

SELECT SUM(amount)
FROM sales
WHERE transaction_date BETWEEN '2021-01-01' AND '2021-12-31';

Wie du siehst, arbeitet PostgreSQL nur mit der nötigen Partition sales_2021 und scannt nicht die ganze Tabelle.

Beispiel: Optimierung der Metrikberechnung nach Regionen

Angenommen, du willst die Gesamtsumme der Verkäufe nach Regionen berechnen. Ohne Indizes und Partitionen dauert das ewig. Zuerst erstellen wir einen Index für die Spalte region:

CREATE INDEX idx_sales_region ON sales(region);

Deine Abfrage:

SELECT region, SUM(amount)
FROM sales
GROUP BY region;

Jetzt läuft die Verarbeitung dank des Index schneller.

Beispiel: Partitionierung von Zeitdaten

Für Zeitdaten wie Transaktionen oder Logs solltest du Partitionen nach Monaten anlegen. Zum Beispiel:

CREATE TABLE sales_monthly PARTITION BY RANGE (transaction_date);

CREATE TABLE sales_jan_2023 PARTITION OF sales_monthly
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

Abfrage:

SELECT SUM(amount)
FROM sales_monthly
WHERE transaction_date >= '2023-01-01' AND transaction_date < '2023-02-01';

wird schneller ausgeführt, weil PostgreSQL nur die Partition sales_jan_2023 liest.

Beispiel: Kombination von Indexierung und Partitionierung

Du kannst Indexierung und Partitionierung kombinieren, um maximale Performance zu erreichen. Zum Beispiel kannst du Indizes innerhalb jeder Partition erstellen. Hier ein Beispiel:

CREATE INDEX idx_sales_amount_jan_2023 ON sales_jan_2023(amount);

Wie man typische Fehler vermeidet

Viele Performance-Probleme hängen mit falscher Nutzung von Indizes und Partitionierung zusammen. Zum Beispiel:

• Zu viele Indizes können das Einfügen von Daten verlangsamen.
• Partitionen sollten so gestaltet sein, dass sie gleichmäßig gefüllt sind; zu kleine oder zu große Partitionen verschlechtern die Performance.
• Wenn du die Performance-Analyse (EXPLAIN ANALYZE) vor der Optimierung vergisst — das ist wie ein Auto zu reparieren, ohne unter die Haube zu schauen.

Prüfe immer, ob deine Optimierungen wirklich einen Geschwindigkeitsvorteil bringen, und hab keine Angst zu experimentieren.