Jak zoptymalizować bazę PostgreSQL po wzroście liczby użytkowników

Po zwiększeniu liczby użytkowników aplikacja zaczyna odpowiadać z opóźnieniem. Czasem problem pojawia się tylko w godzinach szczytu, innym razem pojedynczy endpoint potrafi zablokować działanie całego systemu. Pierwszą reakcją bywa zwiększenie pamięci serwera albo liczby procesorów, ale taki ruch nie zawsze rozwiązuje przyczynę.

Optymalizacja PostgreSQL powinna przebiegać od pomiaru do zmiany. Najpierw trzeba ustalić, czy ograniczeniem są wolne zapytania SQL, blokady, operacje dyskowe, nadmiar połączeń, nieaktualne statystyki, konfiguracja pamięci czy sposób korzystania z bazy przez aplikację. Dopiero potem można zdecydować, czy potrzebna jest korekta indeksów, strojenie parametrów, pula połączeń, replikacja albo większy serwer. ^[4]

Najważniejsza zasada brzmi: nie optymalizuj PostgreSQL na podstawie samego wrażenia, że baza jest wolna. Ten sam objaw może wynikać z kilku różnych przyczyn, a niewłaściwa zmiana potrafi pogorszyć sytuację.

Najpierw potwierdź źródło problemu

Wzrost ruchu nie zawsze oznacza problem z samą bazą

Większa liczba użytkowników zwiększa obciążenie całego łańcucha: przeglądarki, aplikacji, usług zewnętrznych, sieci, serwera bazodanowego i systemu przechowywania danych. PostgreSQL może działać poprawnie, podczas gdy aplikacja czeka na odpowiedź z innego serwisu albo wyczerpała własną pulę połączeń.

Podobne objawy występują przy przeciążeniu procesora, małej ilości pamięci RAM, wysokich opóźnieniach dysku, błędnej konfiguracji kontenera, ograniczeniach chmurowych oraz nieefektywnym kodzie aplikacji. Sam wzrost czasu odpowiedzi endpointu nie dowodzi jeszcze, że trzeba zmienić indeksy lub parametry PostgreSQL.

Dobrym początkiem jest rozbicie czasu odpowiedzi na etapy. Trzeba sprawdzić, ile trwa obsługa żądania w aplikacji, ile czasu zajmuje oczekiwanie na połączenie, jak długo wykonywane jest zapytanie SQL i czy po jego zakończeniu aplikacja nie czeka jeszcze na kolejną usługę.

Rozpoznaj rodzaj przeciążenia

Wysokie użycie CPU zwykle oznacza kosztowne obliczenia, sortowanie, agregacje, kompilowanie planów lub zbyt dużą liczbę równoległych operacji. Nie jest to jednak jednoznaczny sygnał, że trzeba zwiększyć liczbę rdzeni. Jeden źle napisany raport wykonywany jednocześnie przez wiele sesji może zużyć cały procesor.

Wysokie oczekiwanie na I/O wskazuje na intensywne odczyty lub zapisy. Przyczyną może być sekwencyjne skanowanie dużych tabel, brak indeksu, zbyt mała pamięć podręczna, sortowanie na dysku albo duża liczba operacji autovacuum. Gdy dysk jest wolny, zwiększenie CPU nie przyniesie istotnej poprawy.

Blokady objawiają się tym, że zapytania trwają długo, choć same operacje nie wymagają dużej mocy obliczeniowej. W takiej sytuacji kolejne sesje mogą ustawiać się w kolejce za jedną transakcją, która przetrzymuje blokadę. Z kolei wyczerpanie limitu połączeń może powodować błędy w aplikacji nawet wtedy, gdy PostgreSQL ma jeszcze wolne CPU.

Zbuduj punkt odniesienia

Przed zmianą konfiguracji trzeba zebrać dane z okresu normalnego działania i z godzin największego ruchu. Przydatne są: liczba aktywnych sesji, liczba transakcji, średnie i wysokie percentyle czasu odpowiedzi, wykorzystanie CPU, odczyty i zapisy dyskowe, wolne miejsce, rozmiar bazy oraz opóźnienie replikacji, jeśli replika jest używana.

Nie wystarczy średnia. Dziewięćdziesiąty piąty lub dziewięćdziesiąty dziewiąty percentyl często pokazuje problem lepiej, ponieważ pojedyncze bardzo wolne zapytania są niewidoczne w zwykłej średniej. Dla użytkownika znaczenie ma nie tylko przeciętny czas odpowiedzi, ale także to, czy co pewien czas strona ładuje się przez kilkanaście sekund.

Trzeba też porównać dane z momentem wdrożenia zmian. Jeśli problem pojawił się bezpośrednio po dodaniu funkcji raportowej, zmianie ORM albo włączeniu nowego filtra, przyczyną może być konkretny fragment kodu, a nie ogólny wzrost liczby użytkowników.

Odizoluj przypadki szczególne

Nagły wzrost czasu odpowiedzi endpointu może wynikać z jednego raportowego zapytania uruchamianego równolegle przez wielu użytkowników. Pozostała część aplikacji może korzystać z bazy bez problemów. Wtedy zwiększenie serwera dla całego PostgreSQL jest mniej trafne niż ograniczenie raportu, utworzenie właściwego indeksu lub przeniesienie ciężkiej analizy do osobnej bazy.

Inny przypadek to długie transakcje pozostawione przez proces, który utracił połączenie z aplikacją. Mogą one blokować usuwanie starych wersji wierszy i powodować narastanie tabel. Objawy pojawią się dopiero po pewnym czasie, dlatego analiza musi obejmować nie tylko bieżące zapytania, ale też historię obciążenia.

Zbuduj widoczność: metryki, logi i statystyki PostgreSQL

Metryki pokazujące realne obciążenie

Monitoring bazy powinien odpowiadać na trzy pytania: co wykonuje PostgreSQL, na co czeka i jaki zasób ogranicza dalszą pracę. W praktyce obserwuje się aktywne oraz oczekujące połączenia, liczbę transakcji, czas ich trwania, blokady, wykorzystanie CPU, pamięć, I/O i zajętość dysku.

Istotny jest stosunek aktywnych sesji do wszystkich sesji. Wysokie max_connections nie oznacza, że baza rzeczywiście wykonuje więcej pracy. Część procesów może tylko czekać na połączenie z usługą, blokadę lub dostęp do dysku.

Przydatne są również informacje o trafieniach w pamięć podręczną. Wysoki cache hit ratio bywa dobrym sygnałem, ale nie powinien być traktowany jako samodzielny cel. Jeśli zapytanie odczytuje ogromną liczbę niepotrzebnych stron, nawet wysoki udział odczytów z cache nie oznacza dobrej wydajności.

Monitorowanie musi obejmować hosta, na którym działa PostgreSQL. Należy sprawdzać presję pamięci, swap, średnie obciążenie, opóźnienia dysku, przepustowość sieci i limity kontenera lub maszyny wirtualnej. Czasem system operacyjny ogranicza proces bardziej niż ustawienia samej bazy.

Pik czy trwałe przeciążenie?

Krótkotrwały pik ruchu nie zawsze wymaga zmiany architektury. Jeżeli obciążenie trwa kilka minut, a po jego zakończeniu kolejki znikają i baza wraca do normalnego stanu, wystarczające może być ograniczenie liczby równoległych zadań, buforowanie wyników albo obsługa żądań przez kolejkę.

Trwałe przeciążenie wygląda inaczej. Opóźnienia rosną z tygodnia na tydzień, liczba aktywnych sesji utrzymuje się blisko limitu, autovacuum nie nadąża, a wolne miejsce szybko znika. Wtedy sama optymalizacja pojedynczego zapytania może nie wystarczyć.

Pomiar powinien być skorelowany z ruchem aplikacyjnym. Wzrost liczby użytkowników, transakcji na sekundę i liczby zapytań na żądanie trzeba zestawić z opóźnieniem oraz użyciem zasobów. Jeśli użytkowników przybyło o połowę, ale liczba zapytań wykonywanych na jedno żądanie wzrosła kilkukrotnie, problem leży prawdopodobnie w sposobie korzystania z bazy.

pg_stat_activity jako obraz bieżącej sytuacji

Widok pg_stat_activity pokazuje sesje, ich stan, wykonywane zapytania, czas rozpoczęcia oraz oczekiwanie. Proste zapytanie diagnostyczne może wyglądać tak:

SELECT pid,
       usename,
       application_name,
       client_addr,
       state,
       wait_event_type,
       wait_event,
       query_start,
       now() - query_start AS duration,
       query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

Warto zwrócić uwagę na sesje w stanie idle in transaction. Oznaczają połączenie, które rozpoczęło transakcję, ale nie zakończyło jej ani nie wykonało kolejnej operacji. Taka sesja może długo utrzymywać blokady i zatrzymywać sprzątanie nieaktualnych wersji wierszy.

Nie należy automatycznie zabijać każdej długiej sesji. Długie zapytanie może być legalnym raportem, migracją lub operacją administracyjną. Najpierw trzeba ustalić właściciela procesu, znaczenie zapytania i to, czy jego przerwanie nie uszkodzi pracy aplikacji.

pg_stat_statements i logi wolnych zapytań

Rozszerzenie pg_stat_statements agreguje statystyki zapytań. Pozwala znaleźć zapytania o największym łącznym czasie, największej liczbie wywołań, najwyższym średnim czasie oraz dużej liczbie odczytanych bloków. ^[2]

SELECT calls,
       total_exec_time,
       mean_exec_time,
       rows,
       shared_blks_hit,
       shared_blks_read,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Najdroższe zapytanie w ujęciu łącznym nie musi być najwolniejsze pojedynczo. Zapytanie trwające kilka milisekund, wykonywane setki tysięcy razy, może zużywać więcej zasobów niż pojedynczy raport trwający kilka sekund. Dlatego analizuje się zarówno koszt jednostkowy, jak i częstotliwość.

Logowanie wolnych zapytań pomaga znaleźć operacje, które nie występują regularnie w statystykach albo są ważne tylko w określonych godzinach. Parametry takie jak log_min_duration_statement należy dobierać ostrożnie, ponieważ zbyt szczegółowe logi mogą zwiększyć obciążenie i szybko zająć dysk.

Minimalny zestaw alarmów powinien obejmować:

• długie transakcje i sesje w stanie idle in transaction,
• gwałtowny wzrost liczby połączeń lub sesji oczekujących,
• blokady utrzymujące się ponad ustalony czas,
• opóźnienie replikacji, jeśli używane są repliki,
• małą ilość wolnego miejsca i rosnący rozmiar tabel,
• zapytania przekraczające przyjęty czas odpowiedzi.

Przyspiesz zapytania, zanim zwiększysz zasoby serwera

Analiza planu wykonania

Do analizy konkretnego zapytania używa się zwykle EXPLAIN (ANALYZE, BUFFERS). Samo EXPLAIN pokazuje plan przewidywany przez optymalizator, ale nie mówi, jak zapytanie rzeczywiście wykonało się na danych produkcyjnych. ^[1]

Warto uruchamiać je na reprezentatywnych parametrach i danych zbliżonych do produkcyjnych. Plan dla pustej tabeli albo środowiska testowego może różnić się od planu dla tabeli zawierającej miliony wierszy.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, status
FROM orders
WHERE customer_id = 1842
  AND created_at >= TIMESTAMP '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;

Wynik należy czytać od dołu do góry, ponieważ niższe węzły przekazują dane do operacji znajdujących się wyżej. Szczególnie istotne są różnice między wartościami estimated rows i actual rows. Jeżeli optymalizator spodziewał się kilku wierszy, a znalazł setki tysięcy, mógł wybrać niewłaściwą metodę dostępu.

Jeśli chcesz pogłębić ten wątek, sprawdź też: Jak zarządzać wirtualną pamięcią w systemie?.

Przykładowo plan może zawierać Seq Scan na dużej tabeli, mimo że zapytanie filtruje po kolumnie używanej często w aplikacji. Nie oznacza to automatycznie błędu. Przy zwracaniu znacznej części tabeli skan sekwencyjny może być tańszy niż wielokrotne przechodzenie przez indeks i odczytywanie wierszy z różnych miejsc dysku. Problem pojawia się wtedy, gdy zapytanie powinno zwrócić kilkadziesiąt rekordów, a odczytuje miliony.

Opcja BUFFERS pokazuje między innymi liczbę stron odczytanych z pamięci podręcznej oraz z dysku. Wysokie shared_blks_read oznacza rzeczywiste odczyty, natomiast shared_blks_hit wskazuje strony znalezione w pamięci PostgreSQL. Połączenie tych danych z czasem wykonania pomaga odróżnić problem planu od problemu przepustowości pamięci masowej.

Najczęstsze przyczyny złego planu

Jedną z przyczyn są nieaktualne statystyki. PostgreSQL na ich podstawie szacuje liczność wyników i wybiera sposób wykonania. Po dużych zmianach danych warto wykonać analizę tabeli:

ANALYZE orders;

Jeśli zmiany zachodzą często, lepiej sprawdzić konfigurację autovacuum i automatycznego analizowania niż regularnie wykonywać polecenia ręcznie. Dla kolumn o nierównomiernym rozkładzie wartości można zwiększyć dokładność statystyk:

ALTER TABLE orders
ALTER COLUMN customer_id SET STATISTICS 500;

ANALYZE orders;

Nie należy jednak ustawiać wysokiej wartości dla wszystkich kolumn bez potrzeby. Dokładniejsze statystyki wymagają więcej pracy podczas analizy i zajmują dodatkowe miejsce w katalogach systemowych.

Innym problemem są wyrażenia uniemożliwiające wykorzystanie zwykłego indeksu. Zapytanie filtrujące po LOWER(email) nie skorzysta automatycznie z indeksu założonego na email. Można albo przechowywać dane w formie odpowiedniej do wyszukiwania, albo utworzyć indeks na wyrażeniu. Podobnie należy uważać na niejawne konwersje typów, funkcje wykonywane na kolumnach oraz warunki budowane przez ORM.

Duże znaczenie ma też ilość danych pobieranych przez aplikację. Zapytanie zwracające wszystkie kolumny i tysiące rekordów może być wolne nawet wtedy, gdy filtr działa poprawnie. Zamiast SELECT * warto wskazać konkretne kolumny, stosować paginację i ograniczać zakres raportu. Dla dużych tabel paginacja oparta na kluczu lub znaczniku czasu bywa wydajniejsza niż wysokie wartości OFFSET, ponieważ baza nie musi przechodzić przez wszystkie pominięte rekordy.

Ogranicz koszt zapytań w kodzie aplikacji

Po wzroście liczby użytkowników często ujawnia się problem N+1. Aplikacja najpierw pobiera listę obiektów, a następnie wykonuje osobne zapytanie dla każdego elementu. Dla dziesięciu rekordów może to być niezauważalne, lecz dla tysiąca oznacza tysiąc dodatkowych operacji i narzut połączeń.

Lepszym rozwiązaniem może być pojedyncze zapytanie z JOIN, pobranie danych w partii albo świadome użycie mechanizmu eager loading w ORM. Trzeba sprawdzić plan i liczbę zwracanych wierszy, ponieważ samo połączenie tabel nie gwarantuje poprawy. Czasem konieczne jest wcześniejsze ograniczenie zbioru, agregacja po stronie bazy lub rozdzielenie zapytania na dwa mniejsze etapy.

W przypadku raportów uruchamianych przez użytkowników warto rozważyć cache, materializowane widoki albo okresowe generowanie wyników. Raport nie musi być obliczany od początku przy każdym otwarciu strony, jeśli dane mogą mieć opóźnienie kilku minut. Takie rozwiązanie zmniejsza jednocześnie koszt CPU, liczbę odczytów i liczbę konkurujących sesji.

Dobierz indeksy do rzeczywistych ścieżek dostępu do danych

Indeks powinien wynikać z zapytania

Indeks tworzy się dla konkretnego sposobu filtrowania, sortowania lub łączenia danych, a nie tylko dlatego, że kolumna wydaje się ważna. Przed jego dodaniem należy sprawdzić najczęstsze zapytania, ich selektywność, rozmiar tabeli oraz koszt utrzymania dodatkowej struktury.

Dla zapytania:

SELECT id, created_at, status
FROM orders
WHERE customer_id = 1842
  AND created_at >= TIMESTAMP '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;

przydatny może być indeks złożony:

CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC);

Najpierw znajduje on rekordy klienta, a następnie pomaga obsłużyć zakres czasu i sortowanie. Kolejność kolumn ma znaczenie. Indeks na samym created_at może być mniej użyteczny, jeśli większość zapytań dodatkowo filtruje po customer_id.

Indeksy złożone nie powinny jednak powstawać według jednej uniwersalnej reguły. Ich skuteczność zależy od konkretnych warunków, rozkładu wartości i tego, czy zapytania wykorzystują początkowy fragment indeksu. Indeks zaczynający się od (customer_id, created_at) dobrze pasuje do wyszukiwania po kliencie, ale nie musi pomagać w zapytaniu filtrującym wyłącznie po dacie.

Indeksy częściowe i pokrywające

Jeżeli aplikacja niemal zawsze pobiera tylko aktywne rekordy, można ograniczyć rozmiar indeksu warunkiem:

CREATE INDEX CONCURRENTLY idx_tasks_open_assignee
ON tasks (assignee_id, due_at)
WHERE status = 'open';

Taki indeks jest mniejszy i tańszy w utrzymaniu niż indeks obejmujący wszystkie statusy. Zapytanie musi jednak zawierać warunek zgodny z predykatem indeksu, aby optymalizator mógł go wykorzystać.

W niektórych przypadkach warto użyć klauzuli INCLUDE:

CREATE INDEX CONCURRENTLY idx_products_category
ON products (category_id)
INCLUDE (name, price);

Dodatkowe kolumny mogą pozwolić na wykonanie zapytania wyłącznie na podstawie indeksu, bez odczytywania pełnych wierszy z tabeli. Nie jest to gwarantowane, ponieważ PostgreSQL musi jeszcze uwzględnić widoczność wersji wierszy. Kolumny w INCLUDE zwiększają też rozmiar indeksu, dlatego należy stosować je dla rzeczywiście często wykonywanych odczytów.

Koszt indeksowania i bezpieczne wdrożenie

Każdy indeks przyspiesza wybrane odczyty, ale zwiększa koszt operacji INSERT, UPDATE i DELETE. Zajmuje również miejsce oraz musi być aktualizowany podczas zmian danych. Nadmiar indeksów może więc pogorszyć działanie systemu, zwłaszcza w tabelach intensywnie modyfikowanych.

Przed utworzeniem indeksu warto sprawdzić istniejące struktury:

SELECT indexname,
       indexdef
FROM pg_indexes
WHERE tablename = 'orders';

Do oceny wykorzystania można użyć statystyk indeksów:

SELECT schemaname,
       relname,
       indexrelname,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

Niski licznik użyć nie zawsze oznacza, że indeks jest zbędny. Rzadko wykonywana operacja administracyjna może być ważna, a statystyki mogą zostać zresetowane. Przed usunięciem indeksu trzeba sprawdzić jego definicję, zależności i zachowanie aplikacji w dłuższym okresie.

Na dużych tabelach indeks produkcyjny zwykle tworzy się z opcją CONCURRENTLY. Ogranicza ona blokowanie zapisów, ale operacja trwa dłużej, wykonuje więcej pracy i nie może być uruchomiona wewnątrz zwykłej transakcji. Po utworzeniu indeksu należy ponownie przeanalizować plan zapytania i sprawdzić wpływ zmiany na zapis oraz wykorzystanie dysku.

Uporządkuj połączenia, pamięć i podstawową konfigurację serwera

Pula połączeń zamiast niekontrolowanego mnożenia sesji

Każde połączenie PostgreSQL zużywa pamięć i wymaga obsługi przez proces serwera. Przy większym ruchu aplikacja powinna korzystać z puli połączeń, która utrzymuje ograniczoną liczbę sesji i udostępnia je kolejnym żądaniom.

Rozmiar puli należy dopasować do rzeczywistej równoległości pracy bazy, a nie do liczby wszystkich użytkowników aplikacji. Zbyt duża pula może powodować konkurencję o CPU, pamięć i blokady. Zbyt mała wydłuży oczekiwanie po stronie aplikacji, ale takie oczekiwanie bywa bezpieczniejsze niż doprowadzanie PostgreSQL do przeciążenia.

Jeżeli z jednej bazy korzysta kilka usług, trzeba uwzględnić sumę ich pul. Przykładowo trzy procesy aplikacji z pulą po 30 połączeń mogą wygenerować znacznie większe obciążenie niż zakładane początkowo 30 sesji. Przydatne jest także rozdzielenie użytkowników aplikacyjnych, zadań asynchronicznych i raportów, aby jeden rodzaj pracy nie zajął całego limitu.

Pamięć: zmieniaj ustawienia z uwzględnieniem rodzaju zapytań

shared_buffers określa część pamięci używaną przez współdzielony cache PostgreSQL, ale nie jest jedynym miejscem przechowywania danych. System operacyjny również buforuje odczyty, dlatego zwiększanie tej wartości bez obserwacji efektów może nie przynieść poprawy.

work_mem działa inaczej: jest przydzielane osobno dla operacji takich jak sortowanie lub haszowanie, a jedno zapytanie może użyć go wielokrotnie. Ustawienie zbyt wysokiej wartości przy dużej liczbie równoległych sesji może doprowadzić do gwałtownego wzrostu zużycia pamięci. Bezpieczniej jest zacząć od umiarkowanej wartości, sprawdzić sortowania wypadające na dysk i podnosić limit tylko tam, gdzie przynosi to mierzalną korzyść.

Warto obserwować logi informujące o sortowaniu lub haszowaniu wykonywanym poza pamięcią. Nie każde sortowanie na dysku jest problemem, ale powtarzalne operacje tego typu w często wywoływanych zapytaniach mogą uzasadniać zmianę zapytania, indeksu albo lokalnego work_mem dla konkretnego zadania.

Autovacuum, checkpointy i przestrzeń dyskowa

Po wzroście liczby aktualizacji i usunięć szczególnego znaczenia nabiera autovacuum. Jego zadaniem jest między innymi usuwanie nieaktualnych wersji wierszy oraz aktualizowanie statystyk. Jeśli nie nadąża, tabele i indeksy mogą rosnąć, a zapytania będą odczytywać więcej stron niż wcześniej. ^[3]

Dla intensywnie modyfikowanych tabel można dostosować progi autovacuum na poziomie tabeli, zamiast zmieniać je globalnie:

ALTER TABLE order_events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

Wartość trzeba dobrać do rozmiaru i charakteru tabeli. Niski próg uruchamia częstsze sprzątanie, ale zwiększa konkurencję o zasoby. Należy też sprawdzić, czy autovacuum nie jest blokowane przez długie transakcje.

Checkpointy oraz zapis zmienionych stron powinny być oceniane razem z opóźnieniem dysku i czasem odpowiedzi. Zbyt agresywne wymuszanie checkpointów może powodować krótkie skoki I/O, a zbyt duże opóźnianie zwiększa ilość pracy wykonywanej jednorazowo. Zmiany tych parametrów najlepiej wprowadzać stopniowo, z obserwacją obciążenia w godzinach szczytu.

Źródła

• [1] PostgreSQL Documentation – Using EXPLAIN ↑
• [2] PostgreSQL Documentation – pg_stat_statements — track statistics of SQL planning and execution ↑
• [3] PostgreSQL Documentation – Routine Vacuuming ↑
• [4] PostgreSQL Documentation – The Cumulative Statistics System ↑