Indizes erstellen ( CREATE INDEX) und Indexierungs-Parameter ( UNIQUE, CONCURRENTLY)

Wir haben schon öfter darüber gesprochen, wie Indizes SELECTs beschleunigen und der Datenbank helfen, nicht alles durchzuscannen. Jetzt ist es Zeit, zu checken, wie genau sie erstellt werden, welche Parameter es beim CREATE INDEX-Befehl gibt und wann du Optionen wie UNIQUE oder CONCURRENTLY nutzen solltest. Das alles ist wichtig, wenn du nicht nur Indizes verwenden, sondern sie auch smart verwalten willst.

Syntax von CREATE INDEX

Einen Index erstellst du mit dem Befehl CREATE INDEX. Hier ist die Basic-Syntax:

CREATE INDEX index_name
ON table_name (column_name);

• index_name — Der Name des Index. Am besten so wählen, dass man gleich sieht, wofür er ist, z.B. idx_users_email für einen Index auf die Spalte email in der Tabelle users.
• table_name — Der Name der Tabelle, für die der Index erstellt wird.
• column_name — Die Spalte, die indexiert werden soll.

Hier ein einfaches Beispiel. Angenommen, wir haben eine Tabelle users:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    age INT
);

Wir wollen die Suche nach Usern über das Feld email beschleunigen. Also erstellen wir einen Index:

CREATE INDEX idx_users_email
ON users (email);

Jetzt, wenn du Abfragen wie diese machst:

SELECT * FROM users WHERE email = 'example@example.com';

Wird PostgreSQL den Index idx_users_email nutzen, um die passende Zeile schnell zu finden.

Einzigartige Indizes (UNIQUE)

Ein einzigartiger Index garantiert, dass die Werte in der angegebenen Spalte oder den Spalten einzigartig sind. Wenn du versuchst, einen doppelten Wert einzufügen, lässt PostgreSQL das nicht zu.

UNIQUE-Indizes werden oft für Keys wie email, username oder andere IDs genutzt, die nicht doppelt vorkommen dürfen.

Syntax für einen einzigartigen Index

Die Syntax für einen UNIQUE-Index ist fast wie bei einem normalen Index, nur mit dem Keyword UNIQUE:

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

Angenommen, in unserer users-Tabelle soll das Feld email einzigartig sein, damit es keine zwei User mit derselben Adresse gibt. So machen wir das:

CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);

Jetzt, wenn du zum Beispiel Folgendes versuchst:

INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 30);
INSERT INTO users (name, email, age) VALUES ('Jane', 'john@example.com', 25);

Wird PostgreSQL einen Fehler werfen, weil email einzigartig sein muss.

Indizes mit dem Parameter CONCURRENTLY erstellen

Stell dir vor, du arbeitest mit einer riesigen Tabelle in Production, auf der ständig was passiert (z.B. Inserts von neuen Daten). Einen Index im Standardmodus (CREATE INDEX) zu erstellen, blockiert diese Tabelle – andere Abfragen können dann keine Daten einfügen, updaten oder löschen. Das kann für ein laufendes System richtig übel sein. Um das zu vermeiden, kannst du den Index "asynchron" mit dem Parameter CONCURRENTLY erstellen.

Syntax

CREATE INDEX CONCURRENTLY index_name
ON table_name (column_name);

Das Keyword CONCURRENTLY sagt PostgreSQL, dass der Index parallel erstellt werden soll, ohne die Tabelle zu blockieren.

Nehmen wir an, wir haben eine Tabelle orders mit Millionen von Einträgen, die ständig mit neuen Bestellungen gefüllt wird:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL
);

Du willst einen Index für schnellere Suche nach order_date erstellen, aber ohne die Tabelle zu blockieren:

CREATE INDEX CONCURRENTLY idx_orders_order_date
ON orders (order_date);

Jetzt erstellt die Datenbank den Index ohne die Tabelle zu blockieren, und deine User merken davon gar nichts.

Zu den Besonderheiten von CONCURRENTLY gehört:

1. Der Index wird langsamer erstellt als im normalen Modus, weil PostgreSQL das Ganze in mehreren Schritten macht.
2. Wenn beim Erstellen Fehler auftreten (z.B. wegen doppelter Daten), musst du den Index manuell löschen und neu erstellen.

Weitere Indexierungs-Parameter

PostgreSQL erlaubt es, beim Erstellen von Indizes weitere Parameter zu setzen. Zum Beispiel kannst du mehrere Spalten gleichzeitig indexieren. Das ist praktisch, wenn du oft Abfragen mit Filtern auf mehreren Feldern machst.

CREATE INDEX idx_users_name_email
ON users (name, email);

Jetzt laufen Abfragen mit Bedingungen wie WHERE name = 'John' AND email = 'john@example.com' schneller.

Zwei Indizes auf je eine Spalte sind nicht das gleiche wie ein Index auf zwei Spalten! Ein Multi-Spalten-Index beschleunigt genau die Suche, bei der im WHERE alle diese Spalten vorkommen.

Beispiele für Fehler und deren Lösung

Beim Erstellen von Indizes kann es zu verschiedenen Fehlern kommen. Hier die häufigsten:

Fehler beim Einfügen von Duplikaten bei UNIQUE-Index. Wenn es in der Tabelle schon doppelte Zeilen gibt, kann PostgreSQL keinen UNIQUE-Index erstellen. In dem Fall musst du erst die Duplikate löschen oder korrigieren.

DELETE FROM users
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(email) > 1
);

Sperrfehler beim Erstellen von Indizes. Wenn du einen normalen Index in einer produktiven DB erstellst, können User Verzögerungen oder Fehler bemerken. Nutze den Parameter CONCURRENTLY, um das zu vermeiden.

Stell dir vor, du arbeitest in einer Firma und bekommst die Aufgabe, eine Datenbank mit Millionen von Einträgen zu optimieren. Mit Indizes kannst du Engpässe finden und das Nutzererlebnis beschleunigen. Zum Beispiel kannst du mit dem richtigen Index die Ausführungszeit einer Abfrage von 10 Sekunden auf ein paar Millisekunden senken. Ziemlich cool, oder?