Śledzenie aktywnych transakcji w czasie rzeczywistym z pg_stat_activity

pg_stat_activity to w zasadzie okno na żywo, które pozwala ogarnąć, co się dzieje w twojej bazie danych właśnie teraz. W poprzednim wykładzie ogarnęliśmy podstawy, teraz wbijamy głębiej w szczegóły pracy z tym potężnym narzędziem.

Przykład podstawowego zapytania do pg_stat_activity:

SELECT * 
FROM pg_stat_activity;

To zapytanie wyświetli wszystkie aktywne połączenia i aktualne zapytania. Super! Ale tych danych będzie masa i można przepaść, przeglądając je godzinami. Dlatego warto przefiltrować najważniejsze info.

Podstawowe pola w pg_stat_activity

Rzućmy okiem na kluczowe pola, które ci się przydadzą oprócz tych już znanych. query_start pokazuje czas rozpoczęcia zapytania, co jest mega ważne, żeby wyłapać długie operacje. pid to identyfikator procesu połączenia — przydaje się do zarządzania (np. kończenia) połączeniem. state_change pokazuje, kiedy zostało ustawione obecne stanie połączenia, co jest szczególnie przydatne do analizy długo wiszących problematycznych stanów.

Przykład selekcji aktywnych procesów:

SELECT pid, usename, state, query, query_start 
FROM pg_stat_activity
WHERE state = 'active';

Jak wyłapać długie zapytania?

Wyobraź sobie, że jesteś adminem bazy i nagle obciążenie serwera wystrzeliło w kosmos. Co robić? Najpierw trzeba ogarnąć, które zapytanie zjada wszystkie zasoby. Używamy pg_stat_activity, żeby znaleźć takie „żarłoczne” zapytania.

SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '10 seconds';

To zapytanie pokaże wszystkie zapytania, które lecą dłużej niż 10 sekund. Dostosuj wartość interwału według swoich potrzeb.

Kończenie problematycznych zapytań

Rozkminimy teraz, jak pozbyć się zapytań, które już za długo się wykonują i blokują bazę. Użyj funkcji pg_terminate_backend(), żeby wymusić zakończenie procesu.

Przykład zakończenia procesu o konkretnym PID:

SELECT pg_terminate_backend(12345);

Gdzie 12345 to identyfikator procesu (pole pid) z pg_stat_activity.

Ważne: Zakończenie procesu może wywołać rollback dla niepoprawnie zakończonej transakcji, więc uważaj z tym.

Jeśli chcesz automatycznie zakończyć wszystkie „zawieszone” procesy, np. idle-transakcje, możesz odpalić taki blok PL/pgSQL. Skoro już ogarniasz programowanie, to pętle (ang. loop) nie są ci obce — to konstrukcja, która powtarza wykonanie instrukcji, dopóki spełniony jest warunek lub nie skończy się przetwarzanie danych:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'idle in transaction' 
          AND (now() - state_change) > interval '5 minutes'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

To dynamiczne rozwiązanie pozwala czyścić system z problematycznych transakcji. Pętla FOR przechodzi po każdym rekordzie z wyniku zapytania i wykonuje zakończenie procesu dla każdego znalezionego PID.

Już niedługo zaczniemy ogarniać PL/pgSQL, jeszcze chwila cierpliwości :P

Filtrowanie po stanie transakcji

Czasem nie chcesz tylko znaleźć aktywne zapytanie, ale też ogarnąć, które połączenia są w specjalnym stanie, np. idle albo idle in transaction. To może pomóc wyłapać potencjalne problemy, zanim zrobi się grubo.

Przykład zapytania do wykrycia transakcji w stanie idle in transaction:

SELECT pid, usename, query, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Pole state_change pokazuje, kiedy to stanie zostało ustawione. Dzięki temu możesz znaleźć długo wiszące transakcje, które nic nie robią, ale mogą blokować zasoby bazy.

Zastosowanie w praktyce

Monitoring długich zapytań na produkcji: możesz ustawić regularny monitoring zapytań przekraczających określony próg czasu i wysyłać powiadomienia przez Slacka, Telegrama czy inny tool. Dzięki temu szybko zareagujesz na problemy z wydajnością.

Analiza zapytań podczas incydentów: jeśli serwer zaczyna mulić, najpierw patrz w pg_stat_activity, żeby znaleźć przyczynę. To powinien być twój standardowy protokół reagowania na problemy z wydajnością.

Obsługa bazy danych: regularna analiza pg_stat_activity pomoże ci wyłapać nieefektywne zapytania i zoptymalizować je (np. dodając indeksy albo przepisując zapytania).

Przy monitoringu łatwo o błędy przez złe filtrowanie lub interpretację danych. Na przykład, jeśli filtrujesz po stanie active, możesz przegapić zapytania w stanie idle in transaction, a one też potrafią blokować zasoby. Kolejny błąd to zbyt agresywne kończenie procesów, co może wywołać niechciane rollbacki i utratę danych. Zawsze analizuj kontekst, zanim podejmiesz radykalne kroki.

Dodatkowe techniki monitoringu

Dla bardziej zaawansowanego monitoringu możesz tworzyć złożone zapytania pokazujące statystyki po użytkownikach, bazach czy typach zapytań. Na przykład możesz sprawdzić, ile średnio czasu każdy user spędza na wykonywaniu zapytań albo znaleźć bazy z największą liczbą aktywnych połączeń.

Warto też ustawić automatyczne logowanie długich zapytań do logów PostgreSQL, używając parametrów konfiguracyjnych log_min_duration_statement i log_statement. To pomoże ci analizować problemy z wydajnością po fakcie i wyłapywać wzorce w zachowaniu aplikacji.