Optymalizacja procedur z uwzględnieniem transakcji: analiza wydajności i cofanie zmian

Kiedy tworzysz procedury, często stają się one "sercem" Twojej bazy danych, robiąc masę operacji. Ale te same procedury mogą być "wąskim gardłem", szczególnie jeśli:

1. Robią niepotrzebne operacje (np. często sięgają po te same dane).
2. Nieefektywnie używają indeksów.
3. Robią za dużo operacji w jednej transakcji.

Jak powiedział jeden mądry programista: "Przyspieszyć źle napisany kod — to jak prosić leniwego kumpla, żeby szybciej biegał". Dlatego optymalizacja procedur to nie tylko poprawa szybkości, to poprawa samej podstawy!

Minimalizacja liczby operacji w jednej transakcji

Każda transakcja w PostgreSQL generuje narzuty na obsługę swoich operacji. Im większa transakcja, tym dłużej trzyma blokady i tym większa szansa na blokady dla innych userów. Żeby to zminimalizować:

1. Nie wrzucaj za dużo operacji do jednej transakcji.
2. Używaj EXCEPTION END, żeby lokalnie ograniczać zmiany. To się przydaje, jeśli tylko część operacji wymaga cofnięcia.
3. Dziel duże transakcje na kilka mniejszych (jeśli logika Twojej apki na to pozwala).

Przykład: dzielenie masowego insertu na "paczki":

-- Przykład: Procedura do ładowania paczkami z etapowym commitem
CREATE PROCEDURE batch_load()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
    batch_cnt INT := 0;
BEGIN
    FOR r IN SELECT * FROM staging_table LOOP
        BEGIN
            INSERT INTO target_table (col1, col2) VALUES (r.col1, r.col2);
            batch_cnt := batch_cnt + 1;
        EXCEPTION
            WHEN OTHERS THEN
                -- Logujemy błąd; zmiany dla tego elementu będą cofnięte
                INSERT INTO load_errors(msg) VALUES (SQLERRM);
        END;
        IF batch_cnt >= 1000 THEN
            COMMIT; -- commit co 1000 operacji
            batch_cnt := 0;
        END IF;
    END LOOP;
    COMMIT; -- ostatni commit
END;
$$;

Tip: pamiętaj, że każdy COMMIT zatwierdza zmiany, więc upewnij się wcześniej, że dzielenie transakcji nie rozwali spójności danych.

Używanie indeksów do przyspieszania zapytań

Załóżmy, że mamy tabelę orders z milionem rekordów i często robisz zapytania po customer_id. Bez indeksu zapytanie będzie skanować wszystkie wiersze:

CREATE INDEX idx_customer_id ON orders(customer_id);

Teraz zapytania typu:

SELECT * FROM orders WHERE customer_id = 42;

będą działać dużo szybciej, bez skanowania całej tabeli.

Ważne: pisząc procedury, upewnij się, że używane pola są w indeksach, szczególnie w warunkach filtrów, sortowania i joinów.

Analiza wydajności z EXPLAIN ANALYZE

EXPLAIN pokazuje plan wykonania zapytania (jak PostgreSQL zamierza je wykonać), a ANALYZE dorzuca realne statystyki wykonania (np. ile czasu poszło na wykonanie). Typowy przykład:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Jak tego użyć w procedurze?

Możesz "rozłożyć" złożone zapytania z procedury, wykonując je osobno z EXPLAIN ANALYZE:

DO $$
BEGIN
    RAISE NOTICE 'Plan zapytania: %',
    (
        SELECT query_plan
        FROM pg_stat_statements
        WHERE query = 'SELECT * FROM orders WHERE customer_id = 42'
    );
END $$;

Przykład analizy i poprawy

Początkowa procedura (wolna):

CREATE OR REPLACE FUNCTION update_total_sales()
RETURNS VOID AS $$
BEGIN
    UPDATE sales
    SET total = (
        SELECT SUM(amount)
        FROM orders
        WHERE orders.sales_id = sales.id
    );
END $$ LANGUAGE plpgsql;

Co się dzieje? Dla każdego wiersza z tabeli sales odpala się podzapytanie SUM(amount), co daje masę operacji. To jest wolne.

Lepsza wersja:

CREATE OR REPLACE FUNCTION update_total_sales()
RETURNS VOID AS $$
BEGIN
    UPDATE sales as s
    SET total = o.total_amount
    FROM (
        SELECT sales_id, SUM(amount) as total_amount
        FROM orders
        GROUP BY sales_id
    ) o
    WHERE o.sales_id = s.id;
END $$ LANGUAGE plpgsql;

Teraz podzapytanie z SUM wykona się raz i wszystkie dane od razu się zaktualizują.

Cofanie danych przy błędach

Jeśli coś pójdzie nie tak w procedurze, możesz cofnąć tylko część transakcji. Przykład:

BEGIN
    -- Wstawiamy dane
    INSERT INTO inventory(product_id, quantity) VALUES (1, -5);
EXCEPTION
    WHEN OTHERS THEN
        -- Ten blok to jak cofnięcie do wewnętrznego savepointa!
        RAISE WARNING 'Błąd przy aktualizacji danych: %', SQLERRM;
END;

Praktyka: odporna procedura obsługi zamówień

Załóżmy, że masz zadanie: obsłużyć zamówienie. Jeśli w trakcie pojawi się błąd (np. brak towaru), zamówienie jest anulowane, a błąd logowany.

CREATE OR REPLACE PROCEDURE process_order(p_order_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_in_stock INT;
BEGIN
    -- Sprawdzamy stan magazynu
    SELECT stock INTO v_in_stock FROM products WHERE id = p_order_id;

    BEGIN
        IF v_in_stock < 1 THEN
            RAISE EXCEPTION 'Brak towaru na magazynie';
        END IF;
        UPDATE products SET stock = stock - 1 WHERE id = p_order_id;
        -- ... inne operacje
    EXCEPTION
        WHEN OTHERS THEN
            -- Wszystkie zmiany w tym bloku są cofane!
            INSERT INTO order_logs(order_id, log_message)
                VALUES (p_order_id, 'Błąd obsługi: ' || SQLERRM);
            RAISE NOTICE 'Błąd obsługi zamówienia: %', SQLERRM;
    END;

    -- Reszta kodu leci dalej, jeśli nie było błędów
    -- Możesz logować: zamówienie obsłużone poprawnie
END;
$$;

• Nawet przy błędzie zamówienie nie zostanie obsłużone, a log pojawi się w tabeli order_logs.
• Przy błędzie zadziała wewnętrzny savepoint i nie stracisz całego kontekstu.

Podstawowe zasady optymalizacji i odporności procedur

1. Używaj indeksów w zapytaniach w procedurach.
2. Dziel duże operacje na mniejsze batch'e, rób etapową obsługę.
3. Loguj błędy — zrób osobną tabelę na logi błędów masowych operacji.
4. Do "częściowych" rollbacków używaj tylko zagnieżdżonych bloków z EXCEPTION.
5. Nie używaj ROLLBACK TO SAVEPOINT w PL/pgSQL — to wywali błąd składni.
6. W procedurach używaj COMMIT/SAVEPOINT tylko jak wywołujesz w autocommit-mode połączenia!
7. Analizuj plan wykonania ciężkich zapytań (EXPLAIN ANALYZE) poza procedurami, zanim je zintegrujesz.