Optymalizacja funkcji analitycznych dla dużych ilości danych: indeksowanie i partycjonowanie

Kiedy danych robi się dużo (zupełnie jak wiadomości o deadlinie na firmowych czatach), zapytania do pobierania i przetwarzania zaczynają zwalniać. Oto główne powody:

1. Brak indeksów. Gdy PostgreSQL musi przeskanować całą twoją tabelę, żeby wykonać zapytanie (to się nazywa "Seq Scan" — sekwencyjne skanowanie), zapytanie może zająć znacznie więcej czasu.
2. Nieefektywne zapytania SQL. Jeśli zapytania są zbudowane bez myślenia o optymalizacji, to nawet z indeksami możesz mieć produkcyjne problemy. Na przykład, zapomniałeś użyć kluczowych warunków w WHERE? Szykuj się na długie czekanie.
3. Duże ilości danych w jednej tabeli. Na przykład, gdy próbujesz analizować sprzedaż ze wszystkich lat naraz, nawet indeksy mogą nie pomóc.

Spokojnie, mamy dwa sprawdzone sposoby na to: Indeksowanie i Partycjonowanie.

Używanie indeksów do przyspieszania zapytań

Oto prosty przykład tworzenia indeksu:

CREATE INDEX idx_sales_date ON sales(transaction_date);

• Tutaj idx_sales_date — to nazwa indeksu (możesz ją nazwać jak chcesz, ale lepiej użyć czegoś sensownego).
• ON sales(transaction_date) — wskazuje, dla której tabeli i której kolumny tworzysz indeks.

Ten indeks jest szczególnie przydatny, jeśli często filtrujesz zapytania po polu transaction_date.

Przykład zapytania, które skorzysta z tego indeksu:

SELECT *
FROM sales
WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31';

Indeksowanie kluczy złożonych

Jeśli twoje zapytania często używają kombinacji kilku pól, na przykład region i product_id, rozważ stworzenie indeksu złożonego:

CREATE INDEX idx_sales_region_product ON sales(region, product_id);

Teraz zapytania tego typu działają dużo szybciej:

SELECT *
FROM sales
WHERE region = 'North America' AND product_id = 42;

Używanie indeksów unikalnych

Indeksy unikalne nie tylko przyspieszają wyszukiwanie, ale też gwarantują unikalność wartości w kolumnie. Na przykład:

CREATE UNIQUE INDEX idx_unique_customer_email ON customers(email);

Teraz nie uda ci się przypadkowo dodać dwóch klientów z tym samym mailem.

Indeksowanie dla funkcji analitycznych

Niektóre funkcje analityczne, takie jak SUM, COUNT czy AVG, mogą użyć indeksu, żeby szybciej policzyć wartości. Oto przykład:

CREATE INDEX idx_sales_amount ON sales(amount);

Zapytanie:

SELECT SUM(amount)
FROM sales 
WHERE transaction_date >= '2023-01-01';

będzie wykonywać się szybciej dzięki indeksowi.

Partyjconowanie tabel dla pracy z dużymi ilościami danych

Partyjconowanie tabel — to proces dzielenia dużej tabeli na mniejsze logiczne części, zwane partycjami. Na przykład, możesz podzielić tabelę sales na partycje według lat: sales_2021, sales_2022 itd.

Myślisz, że to trudne? W rzeczywistości PostgreSQL robi to prościej, niż się wydaje.

Rodzaje partycjonowania

1. Partyjconowanie po zakresie (Range Partitioning). Dane są dzielone na podstawie zakresu, na przykład po dacie.
2. Partyjconowanie po liście (List Partitioning). Dane są dzielone na podstawie konkretnych wartości, na przykład po regionach.
3. Partyjconowanie po hash (Hash Partitioning). Używa funkcji hash do podziału danych (rzadziej stosowane ręcznie).

Tworzenie tabeli partycjonowanej

Stwórzmy tabelę sprzedaży z partycjonowaniem po roku.

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    transaction_date DATE NOT NULL,
    amount NUMERIC,
    region TEXT
) PARTITION BY RANGE (transaction_date);

Teraz tworzymy partycje dla różnych lat:

CREATE TABLE sales_2021 PARTITION OF sales
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE sales_2022 PARTITION OF sales
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Zapytania filtrujące po dacie będą automatycznie działać tylko na odpowiedniej partycji. To łatwo sprawdzić komendą EXPLAIN.

Przykład z partycjonowaniem

Tak wyglądałoby zapytanie do sumowania sprzedaży tylko za 2021 rok:

SELECT SUM(amount)
FROM sales
WHERE transaction_date BETWEEN '2021-01-01' AND '2021-12-31';

Jak widzisz, PostgreSQL pracuje tylko z potrzebną partycją sales_2021, a nie skanuje całej tabeli.

Przykład: optymalizacja liczenia metryk po regionach

Załóżmy, że chcesz policzyć sumę sprzedaży po regionach. Bez indeksów i partycji trwa to wieczność. Najpierw stwórz indeks dla kolumny region:

CREATE INDEX idx_sales_region ON sales(region);

Twoje zapytanie:

SELECT region, SUM(amount)
FROM sales
GROUP BY region;

Teraz przetwarzanie przyspiesza dzięki indeksowi.

Przykład: partycjonowanie danych czasowych

Dla danych czasowych, takich jak transakcje czy logi, twórz partycje po miesiącach. Na przykład:

CREATE TABLE sales_monthly PARTITION BY RANGE (transaction_date);

CREATE TABLE sales_jan_2023 PARTITION OF sales_monthly
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

Zapytanie:

SELECT SUM(amount)
FROM sales_monthly
WHERE transaction_date >= '2023-01-01' AND transaction_date < '2023-02-01';

będzie działać szybciej, bo PostgreSQL czyta tylko partycję sales_jan_2023.

Przykład: łączenie indeksowania i partycjonowania

Indeksowanie i partycjonowanie można łączyć, żeby osiągnąć maksymalną wydajność. Na przykład, możesz tworzyć indeksy wewnątrz każdej partycji. Oto przykład:

CREATE INDEX idx_sales_amount_jan_2023 ON sales_jan_2023(amount);

Jak unikać typowych błędów

Wiele błędów wydajnościowych wynika z nieprawidłowego użycia indeksów i partycjonowania. Na przykład:

• Zbyt dużo indeksów może spowolnić operacje INSERT.
• Partycje powinny być zaprojektowane tak, żeby były równomiernie wypełnione; zbyt małe i zbyt duże partycje pogarszają wydajność.
• Zapominanie o analizie wydajności (EXPLAIN ANALYZE) przed wdrożeniem optymalizacji — to jak próba naprawy auta bez zaglądania pod maskę.

Zawsze sprawdzaj, czy twoje optymalizacje naprawdę przyspieszają zapytania i nie bój się eksperymentować.