PostgreSQL od lat jest jedną z najczęściej wybieranych baz danych. Jest stabilny, elastyczny i dobrze radzi sobie z dużym obciążeniem. W wielu projektach działa bez większych problemów przez długi czas.
Czy wiesz, że z PostgreSQL korzystają między innymi Instagram, Reddit czy Spotify?
Te platformy przetwarzają ogromne ilości danych i miliony zapytań dziennie, dlatego wybierają rozwiązania, które są stabilne, skalowalne i dobrze sprawdzają się w dużych systemach.
Nie oznacza to jednak, że problemy z wydajnością nigdy się nie pojawiają. Najczęściej zaczynają się w momencie, gdy aplikacja rośnie. Przybywa użytkowników, zwiększa się liczba operacji, a baza zaczyna przechowywać coraz więcej danych. Wtedy wydajność PostgreSQL zaczyna zależeć przede wszystkim od tego, jak zaprojektowane są zapytania, indeksy i struktura tabel.
W IQ widzimy to bardzo często podczas audytów infrastruktury. Baza danych działa poprawnie przez pierwsze miesiące projektu, a później pojawiają się spowolnienia, rosnące obciążenie serwera i zapytania, które wykonują się kilka razy dłużej niż wcześniej.
Na szczęście w wielu przypadkach rozwiązanie jest prostsze, niż się wydaje. Zamiast zmieniać technologię, wystarczy dobrze przeprowadzona optymalizacja bazy danych PostgreSQL. Czasem wystarczy poprawić jedno zapytanie. Innym razem, potrzebne są nowe indeksy albo zmiana konfiguracji serwera.
W dalszej części pokażemy ci, na które elementy warto zwrócić uwagę, gdy baza danych zaczyna wpływać na wydajność całej aplikacji.
Na początku projektu baza danych zwykle działa bardzo szybko. Liczba rekordów jest niewielka, zapytania przetwarzają małe zestawy danych, a serwer ma dużo wolnych zasobów. Z czasem sytuacja zaczyna się zmieniać. I w pewnym sensie jest to dobra wiadomość, bo zwykle oznacza to przecież, że aplikacja się rozwija, przybywa użytkowników, a system obsługuje coraz więcej danych.
Rośnie liczba wierszy w tabelach, pojawiają się kolejne funkcje aplikacji, zwiększa się liczba zapytań wykonywanych przez użytkowników. Każda operacja na bazie oznacza odczyt lub zapis danych, a to generuje kolejne operacje dyskowe i dodatkowe obciążenie pamięci. Nawet pozornie proste zapytania mogą powodować przetwarzanie całej tabeli. W dużych bazach danych takie operacje bardzo szybko stają się wąskim gardłem.
Bardzo często problem wynika z kilku powtarzających się błędów:
Widzieliśmy środowiska, w których jedna tabela przechowywała kilkadziesiąt milionów rekordów, a aplikacja przy każdym wyszukiwaniu skanowała całą tabelę. Przy niewielkiej liczbie użytkowników było to praktycznie niezauważalne. Gdy ruch w systemie wzrósł, baza danych bardzo szybko zaczęła być głównym źródłem problemów z wydajnością.
Dlatego pierwszym krokiem powinna być analiza tego, gdzie dokładnie pojawiają się największe obciążenia. PostgreSQL oferuje do tego bardzo dobre narzędzia, od statystyk zapytań po analizę planów wykonania.
Zanim zaczniesz zmieniać indeksy albo przepisywać zapytania, zatrzymaj się na chwilę i sprawdź, co faktycznie obciąża bazę. Optymalizacja PostgreSQL bez monitorowania wydajności bardzo często kończy się zgadywaniem.
Dlatego najpierw warto zobaczyć, jak baza zachowuje się pod obciążeniem.
Jakie zapytania wykonują się najczęściej? Które zajmują najwięcej czasu? Ile operacji odczytu trafia w pamięć podręczną, a ile kończy się odczytem z dysku? PostgreSQL udostępnia kilka mechanizmów, które bardzo w tym pomagają. Jednym z najczęściej używanych jest rozszerzenie pg_stat_statements. Zbiera ono statystyki zapytań wykonywanych w bazie i pozwala zobaczyć, które z nich generują największe obciążenie.
To często moment, w którym pojawiają się pierwsze zaskoczenia. Zapytanie, które wygląda niewinnie w kodzie aplikacji, potrafi odpowiadać za sporą część całego ruchu w bazie.
Warto też zwrócić uwagę na podstawowe statystyki dotyczące tabel i indeksów.
PostgreSQL gromadzi informacje o liczbie wierszy w tabelach, liczbie operacji odczytu, zapisów oraz wykorzystaniu indeksów. Te dane pomagają zrozumieć, czy baza korzysta z indeksów tak, jak powinna.
Kolejnym ważnym obszarem jest obserwacja zasobów serwera.
Baza danych działa w konkretnym środowisku, na serwerze z określoną ilością pamięci RAM, przestrzeni dyskowej i możliwościami systemu operacyjnego. Jeśli pamięć podręczna jest zbyt mała albo operacje dyskowe są zbyt częste, nawet dobrze napisane zapytania mogą działać wolno.
W dużych środowiskach przydatne są również zewnętrzne narzędzia monitorujące.
To one dają możliwość śledzenia obciążeń serwera, zużycia pamięci, liczby transakcji czy czasu odpowiedzi zapytań w dłuższym okresie. W ten sposób można zauważyć momenty, w których wydajność zaczyna spadać.
Dopiero mając taki obraz sytuacji, można przejść do kolejnego kroku: analizy konkretnych zapytań SQL.
Jeżeli pracujesz z PostgreSQL trochę dłużej, prędzej czy później trafisz na polecenie EXPLAIN ANALYZE. To jedno z podstawowych narzędzi używanych przy optymalizacji zapytań. Każde zapytanie SQL, które trafia do bazy, musi zostać wykonane według określonego planu. PostgreSQL decyduje między innymi:
z których tabel pobrać dane;
czy skorzystać z indeksu;
w jaki sposób połączyć dane z kilku tabel;
jakie operacje wykonać po drodze.
Ten zestaw decyzji nazywany jest planem zapytania. Polecenie EXPLAIN wyświetla taki plan jeszcze przed uruchomieniem zapytania. Widać w nim, w jaki sposób PostgreSQL zamierza odczytać dane z tabel, czy wykorzysta indeksy i jakie operacje pojawią się w kolejnych krokach.
EXPLAIN ANALYZE działa inaczej.
Zapytanie zostaje wykonane naprawdę, a baza zwraca dodatkowo rzeczywiste czasy trwania poszczególnych etapów. Ta różnica jest bardzo istotna. Plan zapytania może bowiem wyglądać poprawnie, ale dopiero rzeczywiste wykonanie ujawnia miejsca, w których baza traci najwięcej czasu.
W planie zapytania zobaczysz między innymi, czy PostgreSQL czyta całą tabelę, czy korzysta z indeksu oraz w jaki sposób łączy dane z innych tabel. Jeżeli zauważysz sekwencyjny odczyt dużej tabeli, a zapytanie filtruje dane po konkretnym polu, najczęściej oznacza to brak odpowiedniego indeksu.
Zdarza się też inna sytuacja. Plan zapytania wygląda dobrze, ale rzeczywisty czas wykonania jest znacznie dłuższy, niż wskazywały statystyki. W takim przypadku należy sprawdzić, czy statystyki tabel są aktualne i czy baza została niedawno przeanalizowana poleceniem ANALYZE.
Analiza planów zapytań bardzo szybko prowadzi do miejsc, w których baza danych zaczyna się dławić. Czasem wystarczy drobna zmiana zapytania albo dodanie jednego indeksu, żeby skrócić czas wykonania z kilku sekund do kilkudziesięciu milisekund
Załóżmy prostą sytuację. W aplikacji działa zapytanie, które przez długi czas nie sprawia żadnych problemów. W tabeli znajduje się kilka tysięcy wierszy, wszystko wykonuje się bardzo szybko i użytkownicy nawet nie zauważają, że baza coś liczy w tle.
Z czasem aplikacja zaczyna jednak przechowywać coraz więcej danych. Liczba rekordów rośnie, pojawiają się nowe funkcje, zwiększa się liczba użytkowników. Nagle okazuje się, że to samo zapytanie, które wcześniej wykonywało się w ułamku sekundy, potrzebuje kilku sekund. W tym momencie zalecamy sprawdzić plan zapytania. Bardzo często okazuje się, że baza musi przetworzyć znacznie więcej danych, niż wynikałoby to z samej logiki zapytania.
Częstą przyczyną jest brak odpowiedniego indeksu albo zapytanie, które zmusza PostgreSQL do przetwarzania dużej liczby wierszy.
Podobny problem pojawia się przy operacjach sortowania lub agregacji wykonywanych na dużych tabelach. Jeżeli baza musi przetworzyć miliony rekordów, każda operacja sortowania oznacza dodatkowe obciążenie pamięci i operacji dyskowych.
Zwróć też uwagę na zapytania, które łączą wiele tabel. PostgreSQL musi wtedy wybrać sposób wykonania takiego połączenia, a przy dużych zestawach danych może to generować znaczące zużycie pamięci i zasobów serwera.
Zdarza się też sytuacja znacznie prostsza. Zapytanie pobiera dużo więcej danych, niż naprawdę potrzebuje aplikacja. Zamiast kilku kolumn odczytywana jest cała tabela, a większość tych danych i tak nie jest później wykorzystywana.
Dlatego przy analizie zapytań warto zadać sobie kilka pytań:
czy zapytanie korzysta z odpowiednich indeksów
czy liczba wierszy zwracanych przez zapytanie jest rzeczywiście potrzebna
czy operacje sortowania i agregacji wykonywane są na możliwie najmniejszym zestawie danych
Często już niewielka zmiana zapytania potrafi znacząco poprawić wydajność PostgreSQL.
Indeksy potrafią diametralnie zmienić wydajność PostgreSQL. Dobrze dobrany indeks gwarantuje, że zapytanie będzie wykonane w milisekundach. Bez niego baza często musi przeszukać całą tabelę. Jeśli masz do czynienia z większymi tabelami, bardzo szybko zobaczysz tę różnicę.
Musisz jednak pamiętać, że indeksy mają swoją cenę. Każdy z nich zajmuje miejsce na dysku i musi być aktualizowany przy każdej operacji zapisu. Gdy tabela często przyjmuje nowe dane, zbyt duża liczba indeksów spowalnia działanie bazy, zamiast je przyspieszać. Dlatego powinno się je dodawać tylko tam, gdzie rzeczywiście mają sens. Zwróć uwagę przede wszystkim na kolumny używane w filtrowaniu, sortowaniu i łączeniu tabel. To dokładnie w tych miejscach indeksy najczęściej przynoszą największy efekt.
Na koniec jedna ważna rzecz. Samo utworzenie indeksu nie gwarantuje jeszcze poprawy wydajności. PostgreSQL korzysta z niego tylko wtedy, gdy plan zapytania uzna go za najlepsze rozwiązanie. Dlatego po dodaniu indeksu najlepiej sprawdzić plan zapytania i upewnić się, że baza rzeczywiście z niego korzysta.
Jeżeli tabela zaczyna przechowywać dziesiątki milionów rekordów, sama optymalizacja zapytań i indeksów może przestać wystarczać. W takiej sytuacji pojawia się temat partycjonowania danych. Zamiast przechowywać wszystko w jednej dużej tabeli, dane dzieli się na mniejsze fragmenty. PostgreSQL kieruje zapytania tylko do tych części, w których znajdują się potrzebne rekordy.
Jeśli Twoja baza przechowuje dane czasowe, na przykład logi, zdarzenia aplikacji albo historię operacji, partycjonowanie bardzo szybko zaczyna mieć sens. Tabela może być podzielona na miesiące lub lata. Zapytanie dotyczące ostatnich dni nie musi wtedy przeszukiwać całej historii danych. Przy dużych bazach oznacza to znacznie mniej operacji dyskowych i wyraźnie lepszą wydajność.
Gdy zapytania są już zoptymalizowane, a indeksy dobrane poprawnie, czas spojrzeć na samą konfigurację bazy. PostgreSQL ma wiele parametrów, które decydują o tym, jak wykorzystywana jest pamięć, dysk i zasoby serwera.
Jednym z najważniejszych obszarów jest pamięć RAM.
To w niej PostgreSQL przechowuje część danych, indeksów i wyników zapytań. Im więcej danych mieści się w pamięci podręcznej, tym rzadziej baza musi sięgać do dysku. Przy dużych bazach danych różnica między odczytem z RAM a operacjami dyskowymi potrafi być ogromna.
Drugim elementem są statystyki, które PostgreSQL wykorzystuje przy planowaniu zapytań.
Na ich podstawie baza decyduje, czy użyć indeksu, czy przetworzyć całą tabelę. Jeżeli statystyki są zbyt ogólne, plan zapytania może być niedopasowany do rzeczywistej struktury danych. W niektórych przypadkach pomaga zwiększenie wartości parametru set statistics dla wybranych kolumn.
Nie można też zapominać o samym systemie operacyjnym i konfiguracji dysku.
Szybkość operacji I/O, sposób działania systemu plików czy dostępna przepustowość dysku bezpośrednio przekładają się na czas wykonywania zapytań.
Jeżeli baza pracuje pod dużym obciążeniem, konfigurację PostgreSQL, serwera i systemu operacyjnego trzeba traktować jako jeden wspólny obszar optymalizacji.
Optymalizacja bazy danych PostgreSQL nie kończy się w momencie, gdy zapytania zaczynają działać szybciej. Baza rozwija się razem z Twoją aplikacją. Dlatego musisz spojrzeć na bazę z szerszej perspektywy.
Jednym z podstawowych mechanizmów jest polecenie ANALYZE, które aktualizuje statystyki tabel. PostgreSQL wykorzystuje je przy planowaniu zapytań. Jeżeli statystyki są nieaktualne, baza może wybierać plan zapytania, który nie odpowiada rzeczywistej strukturze danych.
Zwróć też uwagę na fragmentację danych w tabelach. Przy dużej liczbie operacji zapisu i aktualizacji struktura danych zaczyna się rozpraszać. W takich sytuacjach reorganizacja tabel lub operacje takie jak ALTER TABLE pomagają uporządkować dane i zmniejszyć liczbę niepotrzebnych operacji dyskowych.
Istotna jest również obserwacja obciążenia bazy i liczby transakcji. W systemach pracujących pod stałym ruchem użytkowników transakcje mogą wpływać zarówno na wydajność, jak i na spójność danych.
Gdy regularnie monitorujesz bazę, aktualizujesz statystyki i kontrolujesz obciążenie, PostgreSQL może działać stabilnie nawet przy bardzo dużej liczbie danych.
W IQ w projektach infrastrukturalnych bardzo często spotykamy się z sytuacją, w której baza danych zaczyna ograniczać wydajność całej aplikacji. Zwykle dzieje się to w momencie, gdy system zaczyna obsługiwać większą liczbę użytkowników albo przetwarzać znacznie więcej danych niż na początku projektu.
W takich sytuacjach pierwszym krokiem jest analiza środowiska. Sprawdzamy, jakie zapytania generują największe obciążenie, jak wygląda wykorzystanie zasobów serwera i czy baza korzysta z indeksów w sposób, który rzeczywiście poprawia wydajność. Często okazuje się, że problem nie leży w samej bazie, lecz w zależnościach pomiędzy aplikacją, zapytaniami i konfiguracją infrastruktury.
W kolejnych krokach analizujemy plany zapytań, strukturę tabel, indeksów oraz sposób przechowywania danych. Jeżeli jest taka potrzeba, wprowadzamy zmiany w konfiguracji serwera, mechanizmach pamięci podręcznej albo architekturze samej bazy.
W środowiskach produkcyjnych bardzo ważna jest także kwestia wysokiej dostępności. Baza danych powinna być odporna na awarie. Dlatego optymalizacja często obejmuje również projektowanie replikacji, backupów i mechanizmów zapewniających ciągłość działania usług.
W takim podejściu optymalizacja PostgreSQL nie kończy się na pojedynczym zapytaniu, czy jednej tabeli. Dotyczy całego środowiska, w którym pracuje Twoja aplikacja.
