Böyük həcmli məlumatlar üçün analitik funksiyaların optimallaşdırılması: indeksasiya və partisionlaşdırma

Məlumatlar çox olanda (elə bil korporativ çatlarda deadline barədə mesajlar kimi), select və emal sorğuları yavaşımağa başlayır. Əsas səbəblər bunlardır:

1. İndekslərin olmaması. PostgreSQL sorğunu icra etmək üçün bütün cədvəli skan etməli olanda (buna "Seq Scan" — ardıcıl skanlama deyilir), sorğu xeyli çox vaxt apara bilər.
2. Effektsiz SQL-sorğular. Əgər sorğular optimallaşdırmanı nəzərə almadan yazılıbsa, hətta indekslər olsa belə, sən production-da çətinliklərlə qarşılaşa bilərsən. Məsələn, WHERE şərtlərini unutdun? Hazır ol, sorğu uzun çəkəcək.
3. Bir cədvəldə çoxlu məlumat. Məsələn, bütün illərin satışlarını bir yerdə analiz edirsən, bəzən indekslər də kömək etmir.

Amma narahat olma, bunun üçün iki sınanmış yolumuz var: İndeksləmə və Partisionlaşdırma.

Sorğuları sürətləndirmək üçün indekslərdən istifadə

İndeks yaratmağın sadə nümunəsi:

CREATE INDEX idx_sales_date ON sales(transaction_date);

• Burada idx_sales_date — indeksin adıdır (istədiyin kimi adlandıra bilərsən, amma mənalı ad seçmək yaxşıdır).
• ON sales(transaction_date) — hansı cədvəl və sütun üçün indeks yaradıldığını göstərir.

Bu indeks xüsusilə faydalıdır, əgər tez-tez transaction_date sahəsinə görə filtr edirsənsə.

Bu indeksdən faydalanacaq sorğu nümunəsi:

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

Birləşmiş açarlar üçün indeksasiya

Əgər sorğuların tez-tez bir neçə sahənin birləşməsindən istifadə edirsə, məsələn region və product_id, birləşmiş indeks yaratmağı düşün:

CREATE INDEX idx_sales_region_product ON sales(region, product_id);

İndi bu kimi sorğular daha sürətli işləyəcək:

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

Unikal indekslərdən istifadə

Unikal indekslər təkcə axtarışı sürətləndirmir, həm də sütunda dəyərlərin unikal olmasına zəmanət verir. Məsələn:

CREATE UNIQUE INDEX idx_unique_customer_email ON customers(email);

İndi təsadüfən iki müştəriyə eyni email vermək mümkün olmayacaq.

Analitik funksiyalar üçün indeksasiya

Bəzi data analiz funksiyaları, məsələn SUM, COUNT və ya AVG, indeksdən istifadə edərək dəyərləri daha tez hesablaya bilər. Nümunə:

CREATE INDEX idx_sales_amount ON sales(amount);

Sorğu:

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

indeks sayəsində daha tez icra olunacaq.

Böyük həcmli məlumatlarla işləmək üçün cədvəllərin partisionlaşdırılması

Cədvəllərin partisionlaşdırılması — böyük bir cədvəli daha kiçik məntiqi hissələrə bölmək prosesidir, bunlara partisiya deyilir. Məsələn, sales cədvəlini illərə görə bölə bilərsən: sales_2021, sales_2022 və s.

Düşünürsən ki, bu çətindir? Əslində PostgreSQL bunu düşündüyündən daha asan edir.

Partisionlaşdırma növləri

1. Aralıq üzrə partisionlaşdırma (Range Partitioning). Məlumatlar aralığa görə bölünür, məsələn tarixə görə.
2. Siyahı üzrə partisionlaşdırma (List Partitioning). Məlumatlar konkret dəyərlərə görə bölünür, məsələn regionlara görə.
3. Hash üzrə partisionlaşdırma (Hash Partitioning). Məlumatları bölmək üçün hash-funksiyadan istifadə edir (əksər hallarda əl ilə tətbiq olunmur).

Partisionlaşdırılmış cədvəl yaratmaq

Gəlin satış cədvəlini il üzrə partisionlaşdırılmış şəkildə yaradaq.

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

İndi müxtəlif illər üçün partisiya yaradaq:

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');

Tarixə görə filtr edən sorğular avtomatik olaraq yalnız lazımi partisiya ilə işləyəcək. Bunu EXPLAIN komandası ilə asanlıqla yoxlaya bilərsən.

Partisionlaşdırma nümunəsi

Yalnız 2021-ci il üçün satışların cəmini hesablamaq üçün sorğu belə görünərdi:

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

Gördüyün kimi, PostgreSQL yalnız sales_2021 partisiya ilə işləyir, bütün cədvəli skan etmir.

Nümunə: regionlar üzrə metriklərin hesablanmasının optimallaşdırılması

Tutaq ki, regionlar üzrə ümumi satış məbləğini hesablamaq istəyirsən. İndekslər və partisiyalar olmadan bu, əbədiyyət çəkir. Əvvəlcə region sütunu üçün indeks yaradaq:

CREATE INDEX idx_sales_region ON sales(region);

Sənin sorğun:

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

İndi indeks sayəsində emal daha sürətli olur.

Nümunə: zaman məlumatlarının partisionlaşdırılması

Zaman tipli məlumatlar üçün, məsələn tranzaksiyalar və ya loglar, aylar üzrə partisiya yarat. Məsələn:

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');

Sorğu:

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

daha sürətli işləyəcək, çünki PostgreSQL yalnız sales_jan_2023 partisiya oxuyacaq.

Nümunə: indeksasiya və partisionlaşdırmanın birləşdirilməsi

İndeksləmə və partisionlaşdırmanı birləşdirib maksimum performans əldə edə bilərsən. Məsələn, hər partisiya daxilində indeks yarada bilərsən. Nümunə:

CREATE INDEX idx_sales_amount_jan_2023 ON sales_jan_2023(amount);

Tipik səhvlərdən necə qaçmaq olar

Çox vaxt performans problemləri indeks və partisionlaşdırmadan düzgün istifadə etməməkdən yaranır. Məsələn:

• Çoxlu indeks olması insert əməliyyatlarını yavaşıda bilər.
• Partisiyalar elə dizayn edilməlidir ki, onlar bərabər dolsun; çox kiçik və ya çox böyük partisiya performansı pisləşdirir.
• Performans analizini (EXPLAIN ANALYZE) optimallaşdırmadan əvvəl unutmaq — elə bil maşını təmir edirsən, amma kapotu açmırsan.

Həmişə yoxla ki, optimallaşdırmalar həqiqətən sürət artımı verir, və eksperiment etməkdən qorxma.