Optymalizacja funkcji do pracy z dużymi ilościami danych

Kiedy gadamy o optymalizacji funkcji w PostgreSQL, zwykle mamy na myśli dwa kluczowe elementy: indeksowanie i partycjonowanie. Te dwie techniki pomagają szybciej ogarniać duże ilości danych, eliminując zbędne obliczenia i zapewniając dostęp do danych "prosto do celu". No to rozkminiamy szczegóły.

Indeksy w świecie baz danych działają tak samo jak indeksy w książkach. Jak szukasz info w książce, nie czytasz wszystkiego po kolei. Otwierasz indeks, znajdujesz temat i lecisz od razu na odpowiednią stronę. Tak samo robią indeksy w PostgreSQL.

Tworzenie indeksów

Indeksy tworzy się komendą CREATE INDEX. Oto prosty przykład:

-- Tworzymy indeks na kolumnie id tabeli users, żeby przyspieszyć wyszukiwanie
CREATE INDEX idx_users_id ON users (id);

Teraz, jeśli odpalisz zapytanie typu:

SELECT * FROM users WHERE id = 42;

PostgreSQL użyje utworzonego indeksu, żeby szybko znaleźć odpowiedni wiersz.

Przykład: Optymalizacja funkcji z użyciem indeksów

Załóżmy, że mamy funkcję, która wybiera dane o zamówieniach z tabeli orders dla użytkownika:

CREATE OR REPLACE FUNCTION get_user_orders(user_id INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE user_id = user_id;
END; 
$$ LANGUAGE plpgsql;

Jeśli w tabeli orders są miliony wierszy, wykonanie tej funkcji będzie wolne. Rozwiązanie? Tworzymy indeks na user_id:

CREATE INDEX idx_orders_user_id ON orders (user_id);

Teraz zapytanie w środku funkcji będzie dużo szybsze, bo PostgreSQL użyje indeksu do wyszukania wierszy.

Rodzaje indeksów

PostgreSQL obsługuje kilka typów indeksów, ale najpopularniejsze to B-TREE i GIN. Krótkie porównanie:

Jak chcesz zgłębić temat indeksów, zerknij do oficjalnej dokumentacji PostgreSQL.

Partycjonowanie danych

Jeśli indeksy to przyspieszenie wyszukiwania, to partycjonowanie to metoda, która pozwala podzielić tabelę na mniejsze "kawałki" (partycje). To przydatne, gdy masz ogromne ilości danych w jednej tabeli.

Wyobraź sobie, że masz tabelę orders, która trzyma zamówienia z ostatnich 10 lat. Jeśli robisz zapytanie, żeby znaleźć zamówienia z ostatniego miesiąca, PostgreSQL i tak będzie przeglądał całą tabelę, co jest kosztowne. Partycjonowanie rozwiązuje ten problem, dzieląc dane np. na lata.

Tworzenie tabeli partycjonowanej

Tak możesz stworzyć tabelę partycjonowaną:

-- Tworzymy tabelę orders jako rodzica partycji
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    user_id INT NOT NULL
) PARTITION BY RANGE (order_date);

-- Tworzymy tabele-córki dla każdego roku
CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2022 PARTITION OF orders FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Teraz, jak odpalisz zapytanie typu:

SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01';

PostgreSQL od razu ogarnie, że musi szukać tylko w tabeli orders_2023, zamiast sprawdzać całą tabelę.

Użycie partycjonowania w funkcjach

Wyobraź sobie, że mamy funkcję, która wybiera zamówienia z konkretnego roku. Dzięki partycjonowaniu zapytania w środku funkcji będą szybsze, bo PostgreSQL będzie pracował z konkretną tabelą-córką.

CREATE OR REPLACE FUNCTION get_orders_by_year(year INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE order_date >= make_date(year, 1, 1) 
      AND order_date < make_date(year + 1, 1, 1);
END;
$$ LANGUAGE plpgsql;

Praktyczne case'y

1. Case'y indeksowania

Wyszukiwanie po stringach: jeśli masz tabelę z produktami i często szukasz po nazwie, stwórz indeks na polu name:

CREATE INDEX idx_products_name ON products (name);

Przyspieszenie sortowania: jeśli w zapytaniach często sortujesz po dacie, stwórz indeks:

CREATE INDEX idx_orders_date ON orders (order_date);

2. Case'y partycjonowania

Dane historyczne: jeśli tabela ma dane z timestampem, partycjonowanie po dniach, miesiącach albo latach mocno przyspieszy zapytania.

Dane geograficzne: jeśli tabela ma dane po krajach, stwórz partycje dla każdego kraju.

Potencjalne błędy i jak je ogarnąć

Wielu devów popełnia błąd, tworząc za dużo indeksów. To powoduje spadek wydajności przy insertach i update'ach, bo PostgreSQL musi za każdym razem aktualizować indeksy, gdy zmienia się tabela. Rada: twórz indeksy tylko na tych polach, po których często masz warunki albo sortowanie.

Kolejny typowy błąd — złe partycjonowanie. Jak zrobisz za dużo małych partycji (np. po dniach zamiast po miesiącach), możesz mieć narzut na zarządzanie tymi tabelami.