Blog

Jedną z wielu trosk Administratorów Baz Danych korzystających z PostgreSQL na całym świecie są indeksy. Planujemy je z rozwagą, dobieramy właściwy typ, analizujemy wiele zmiennych. Ostatecznie tworzymy je i – kiedy spełniają pokładane w nich nadzieje – jesteśmy z nich dumni. Niestety, gdy już „zrobią swoje”, zdarza nam się o nich zapomnieć. Tymczasem w tym wielkim i niebezpiecznym świecie czekają na nie zagrożenia.

Choroby indeksów są podstępne – rozwijają się najczęściej powoli i rzadko dają specyficzne objawy, utrudniając poprawną diagnostykę. Co ważne, im później poprawnie zdiagnozujemy, tym dłużej trwa leczenie i zazwyczaj pod większą presją się odbywa. Najbardziej zagrożone są indeksy założone na tabelach, które poddawane są dużemu¹ obciążeniu, związanemu z operacjami zapisu – konkretniej aktualizacji lub usunięcia danych. Co zatem może się przytrafić naszym indeksom?

Spuchnięcie indeksu w PostgreSQL (bloated index)

Jest to najczęściej spotykane schorzenie – smutna, lecz naturalna przypadłość pojawiająca się zazwyczaj u schyłku życia indeksów. Stają się one wówczas nienormalnie duże, przerośnięte². Prędkość wyszukiwania po indeksowanych kolumnach spada – jednak bardzo rzadko jest nagła zmiana i niestety, na ogół orientujemy się dopiero w stadium zaawansowanym.

Aby zapewnić przetwarzanie współbieżne danych, mechanizm MVCC przechowuje w rekordzie specjalne metadane – w kolumnie xmin identyfikator transakcji, która utworzyła rekord, a w xmax tej, która usunęła rekord³. Na ogół bieżącym czyszczeniem fizycznych plików na dysku z martwych krotek (dead tuples) zajmuje się proces autovacuum. Zdarza się jednak, że mogą one zalegać w plikach. Dzieje się tak, gdy nagle pojawi się ich bardzo dużo lub gdy autovacuum jest nieagresywnie skonfigurowany (co z kolei może być celowym działaniem administratora, np. na bazach z dużą ilością operacji zapisu).

Choroba ta bardzo często dotyka także tabele. Zatem przy decyzji leczenia konieczna jest analiza – leczymy bowiem albo przebudową indeksu, albo przebudową całej tabeli.

Rozdwojenie jaźni (duplicated indexes)

Mówimy o duplikacji, gdy stwierdzimy dwa (lub więcej) indeksy, które różnią się wyłącznie nazwą – są tego samego typu, dotyczą tych samych kolumn, używają tych samych funkcji etc. Ludowa mądrość uczy, że od przybytku głowa nie boli, jednak nie w wypadku indeksów – każdy nich powinien być niepowtarzalnym bytem.
Choroba ta występuje bardzo często jako konsekwencja ryzykownych zachowań w kwestii bezpieczeństwa – innymi słowy wówczas, gdy uprawnienia są nadawane zbyt szeroko (i niekoniecznie kompetentnym osobom).

Bagatelizowanie tego schorzenia prowadzi nie tylko do niepotrzebnego zajęcia powierzchni dyskowej, ale istotnie spowalnia działanie bazy:

• przy odczycie proces planera traci czas na analizę,
• przy operacjach zapisu, aktualizacji podlegają wszystkie indeksy.

Leczenie tej przypadłości wydawać się może bardzo proste – wystarczy usunąć nadmiarowe obiekty. W rzeczywistości tak to się odbywa, kluczowe jednak jest poprawne zdiagnozowanie, który z indeksów jest używany przy zapytaniach SELECT.

Ważne: rekonwalescencja powinna się odbywać pod wzmożoną obserwacją.

Upośledzenie (invalid index)

Gdy używamy polecenia CREATE INDEX {name} ON… budowanie indeksu przebiega w transakcji – nasza operacja wykona się w całości lub wcale. Na tabeli zostanie założony twardy LOCK, natomiast w efekcie dostaniemy albo spójny indeks, albo nic (zostaną wycofane wszystkie zmiany).

Jeśli nie możemy zablokować operacji zapisu do indeksowanej tabeli, możemy podjąć decyzję o rezygnacji z ochrony, jaką daje nam transakcja. Jeśli jednak wówczas coś pójdzie nie tak… cóż, wówczas, parafrazując klasyka, nasz indeks zostanie inwalidą.

Prosimy o wyrozumiałość względem zamieszczania tak drastycznych zdjęć – służą one celom edukacyjnym.

Leczenie niestety nie jest możliwe – a jako że uporczywa terapia jest nieetyczna, konieczne jest usunięcie obiektu i utworzenie nowego. Przed wykonaniem tego zabiegu należy zdiagnozować przyczynę, ze względu na którą utworzenie indeksu nie było możliwe.

Zalecenia ogólne i profilaktyka

Nie chcielibyśmy, aby nasi czytelnicy pod wpływem tego artykułu doszli do wniosku, że z indeksami jest tak dużo kłopotów, że lepiej ich nie używać. Nic bardziej mylnego, brak indeksów (missing indexes) to jedna z najpaskudniejszych chorób, które mogą się przytrafić – są one niezbędne dla zdrowia bazy, co więcej ich brak może prowadzić do problemów całego klastra.

O indeksy trzeba jednak dbać i to w sposób możliwie ciągły – lepiej zapobiegać, niż leczyć, to oczywista prawda.

¹ Pod pojęciem „duże obciążenie” mamy na myśli albo częstotliwość takich operacji (ciągłe modyfikacje pojedynczych rekordów, np. związane ze zmiana stany obiektów), albo zakres danych, którego dotyczą (np. usuwanie jednorazowo dużej ilości rekordów).

² Dość trudno to stwierdzić „gołym okiem”, przy analizie wielkości i stopnia „spuchniecia” indeksu należy uwzględnić przede wszystkim jego algorytm oraz ilość i typ kolumn, na których został utworzony. Przykładowo, indeks na kolumnie tekstowej może być nawet większy niż sama tabela, nawet gdy jest zupełnie zdrowy (choć często w takich sytuacjach jest jednocześnie niezbyt zasadny).

³ Modyfikacja rekordu jest na poziomie pliku równoznaczna z usunięciem rekordy i utworzeniem nowego, ze zmodyfikowanymi wartościami.