Dlaczego tworzenie indeksu na dużej tabeli jest powolne?
Tworzenie nowego indeksu w przypadku utrzymania systemu może być dosyć częstą operacją.
Krótkie zapytanie i mamy gotowy indeks dzięki któremu nasz system może dostać niezłego "kopa".
CREATE INDEX nazwa_indeksu ON nazwa_tabeli (kolumna1, kolumna2, kolumna3);
Tworzenie indeksu w ten sposób ma jednak swoje następstwa, oznacza to konieczność:
- Przetworzenia wszystkich wierszy – PostgreSQL musi przeiterować przez całą tabelę i odczytać wartości z kolumn objętych indeksem.
- Sortowania danych – typowy indeks B-tree wymaga posortowania danych, co może oznaczać intensywne użycie pamięci RAM lub dysku.
- Zapisu na dysk – gotowy indeks musi zostać zapisany, co generuje koszt I/O proporcjonalny do rozmiaru danych.
- Braku równoległości (w domyślnej konfiguracji) – do wersji 11 PostgreSQL nie wspierał tworzenia indeksów wielowątkowo, a w nowszych wersjach trzeba to jawnie włączyć.
- Blokad – standardowe zapytanie CREATE INDEX nakłada blokadę ACCESS EXCLUSIVE, co powoduje pełną blokadę dostępu do tabeli.
Jak zapewnić dostępność tabeli podczas tworzenia indeksów?
Jak wspomniałem powyżej, standardowe zapytanie CREATE INDEX nakłada blokadę typu ACCESS EXCLUSIVE, czyli blokuje wszystkie operacje: SELECT, INSERT, UPDATE, DELETE, ALTER. Blokada ACCESS EXCLUSIVE trwa przez cały czas tworzenia indeksu.
Jeśli chcesz zapewnić dostępność tabeli podczas tworzenia indeksów wykorzystaj CONCURRENTLY.
CREATE INDEX CONCURRENTLY nazwa_indeksu
ON nazwa_tabeli (kolumna1, kolumna2, kolumna3);
Zalety i wady wykorzystania CONCURRENTLY podczas tworzenia indeksów
Zapytanie CREATE INDEX CONCURRENTLY nakłada na tabelę lżejsze blokady, a mianowicie: SHARE UPDATE EXCLUSIVE, ROW SHARE.
Dzięki czemu umożliwia odczyty i zapisy do tabeli, przez co nie blokuje działania aplikacji.
Minusem tego rozwiązania jest to, że cała operacja tworzenia indeksów trwa dłużej niż bez użycia CONCURRENTLY.
Uwaga! Opcja CONCURRENTLY dla tworzenia indeksów nie działa w transakcji!
Co w przypadku jeśli zapytanie z CONCURRENTLY zakończy się niepowodzeniem?
Jako, że z użyciem opcji CONCURRENTLY, indeks jest tworzony równolegle, to może on pozostać w niepełnym stanie. W takiej sytuacji indeks należy po prostu usunąć ręcznie.
DROP INDEX IF EXISTS nazwa_indeksu;
Usunięcie indeksu nie blokuje całej tabeli. Operacja ta zakłada co prawda blokadę typu ACCESS EXCLUSIVE, ale jedynie na usuwany indeks. Problem może więc wystąpić jedynie z zapytaniami które zaczęły już używać tego indeksu. W takim przypadku nastąpi rekompilacja planów zapytań, ale nie ma ona wpływu na dostępność tabeli.
Podsumowując
Jeśli tabela dla której chcesz utworzyć indeks jest małych rozmiarów, lub możesz sobie pozwolić na przerwy w zapytaniach do tej tabeli, to twórz indeksy w standardowy sposób. Natomiast jeśli posiadasz tabelę z setkami tysięcy bądź milionami rekordów, i nie możesz sobie pozwolić na jej niedostępność, to koniecznie skorzystaj podczas tworzenia indeksów z opcji CONCURRENTLY.