Tworzenie indeksów ( CREATE INDEX) i parametry indeksowania ( UNIQUE, CONCURRENTLY)

Już nie raz gadaliśmy o tym, jak indeksy przyspieszają wyszukiwanie i pomagają bazie nie przeglądać wszystkiego po kolei. Teraz czas ogarnąć, jak dokładnie się je tworzy, jakie są parametry komendy CREATE INDEX i kiedy warto użyć opcji takich jak UNIQUE albo CONCURRENTLY. To wszystko jest ważne, jeśli chcesz nie tylko korzystać z indeksów, ale też dobrze nimi zarządzać.

Składnia CREATE INDEX

Indeks możesz stworzyć za pomocą komendy CREATE INDEX. Oto jej podstawowa składnia:

CREATE INDEX index_name
ON table_name (column_name);

• index_name — Nazwa indeksu. Najlepiej, żeby od razu było wiadomo, do czego służy, np. idx_users_email dla indeksu na kolumnę email w tabeli users.
• table_name — Nazwa tabeli, dla której tworzysz indeks.
• column_name — Kolumna, która będzie indeksowana.

Prosty przykład. Załóżmy, że mamy tabelę users:

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

Chcemy przyspieszyć wyszukiwanie użytkowników po polu email. Tworzymy indeks:

CREATE INDEX idx_users_email
ON users (email);

Teraz, kiedy będziesz robić zapytania typu:

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

PostgreSQL użyje indeksu idx_users_email, żeby szybko znaleźć odpowiedni wiersz.

Indeksy unikalne (UNIQUE)

Unikalny indeks to gwarancja, że wartości w danej kolumnie (albo kolumnach) będą unikalne. Jeśli spróbujesz wstawić duplikat, PostgreSQL ci na to nie pozwoli.

Indeksy unikalne często stosuje się dla kluczy, takich jak email, username czy inne identyfikatory, które nie mogą się powtarzać.

Składnia tworzenia unikalnego indeksu

Składnia dla unikalnego indeksu jest prawie taka sama jak dla zwykłego, tylko dodajesz słowo UNIQUE:

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

Załóżmy, że w naszej tabeli users pole email musi być unikalne, żeby nie było dwóch użytkowników z tym samym adresem. Robimy to tak:

CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);

Teraz, jeśli spróbujesz wykonać na przykład:

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

PostgreSQL wyrzuci błąd, bo email musi być unikalny.

Tworzenie indeksów z parametrem CONCURRENTLY

Wyobraź sobie, że pracujesz z ogromną tabelą na produkcji, na której ciągle coś się dzieje (np. dodawane są nowe dane). Tworzenie indeksu w standardowym trybie (CREATE INDEX) blokuje tę tabelę, nie pozwalając innym zapytaniom na wstawianie, aktualizowanie czy usuwanie danych. To może być katastrofa dla działającego systemu. Żeby tego uniknąć, możesz stworzyć indeks "asynchronicznie" za pomocą parametru CONCURRENTLY.

Składnia

CREATE INDEX CONCURRENTLY index_name
ON table_name (column_name);

Słowo kluczowe CONCURRENTLY mówi PostgreSQL, że indeks ma być tworzony równolegle, bez blokowania tabeli.

Załóżmy, że mamy tabelę orders, która ma miliony rekordów i ciągle pojawiają się nowe zamówienia:

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

Chcesz stworzyć indeks, żeby przyspieszyć wyszukiwanie po order_date, ale bez blokowania tabeli:

CREATE INDEX CONCURRENTLY idx_orders_order_date
ON orders (order_date);

Teraz baza danych stworzy indeks bez blokowania tabeli i twoi użytkownicy nawet tego nie zauważą.

Wśród cech CONCURRENTLY warto wymienić takie rzeczy:

1. Indeks tworzy się wolniej niż w zwykłym trybie, bo PostgreSQL robi to w kilku etapach.
2. Jeśli podczas tworzenia indeksu pojawią się błędy (np. przez duplikaty), trzeba go usunąć ręcznie i stworzyć od nowa.

Dodatkowe parametry indeksowania

PostgreSQL pozwala dodać dodatkowe parametry przy tworzeniu indeksów. Na przykład możesz zrobić indeks na kilku kolumnach naraz. To przydatne, gdy często robisz zapytania z filtrowaniem po kilku polach.

CREATE INDEX idx_users_name_email
ON users (name, email);

Teraz zapytania z warunkami WHERE name = 'John' AND email = 'john@example.com' będą działać szybciej.

Dwa indeksy po jednej kolumnie to nie to samo, co indeks po dwóch kolumnach! Indeks na kilku kolumnach przyspiesza właśnie wyszukiwanie, gdzie w WHERE są wszystkie te kolumny.

Przykłady błędów i ich rozwiązania

Podczas tworzenia indeksów możesz natknąć się na różne błędy. Oto najczęstsze z nich:

Błąd wstawiania duplikatów przy tworzeniu unikalnego indeksu. Jeśli w tabeli już są zduplikowane wiersze, PostgreSQL nie utworzy unikalnego indeksu. W takim przypadku najpierw musisz usunąć albo poprawić duplikaty.

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

Błąd blokady przy tworzeniu indeksów. Jeśli używasz zwykłego tworzenia indeksu w działającej bazie, użytkownicy mogą zauważyć opóźnienia albo błędy. Użyj parametru CONCURRENTLY, żeby tego uniknąć.

Wyobraź sobie, że pracujesz w firmie i powierzono ci optymalizację bazy z milionami rekordów. Możesz użyć indeksów, żeby znaleźć wąskie gardła i przyspieszyć działanie dla użytkowników. Na przykład, dodając odpowiedni indeks, skrócisz czas zapytania z 10 sekund do kilku milisekund. Fajnie, nie?