Böyük həcmli məlumatlarla işləmək üçün funksiyaların optimallaşdırılması

PostgreSQL-də funksiyaların optimallaşdırılmasından danışanda, adətən iki əsas komponent nəzərdə tutulur: indeksləmə və partitioning. Bu iki texnika böyük həcmdə məlumatı daha tez işləməyə kömək edir, artıq hesablama işlərini aradan qaldırır və məlumatlara "dəqiq hədəf"lə çıxış verir. Gəlin detallı baxaq.

Verilənlər bazasında index-lər kitabdakı index-lər kimi işləyir. Kitabda məlumat axtaranda, bütün səhifələri ardıcıl oxumursan. Index-i açırsan, lazım olan mövzunu tapırsan və birbaşa həmin səhifəyə keçirsən. PostgreSQL-dəki index-lər də təxminən belə işləyir.

İndex-lərin yaradılması

Index-lər CREATE INDEX komandası ilə yaradılır. Sadə bir nümunə:

-- users cədvəlində id sütununda axtarışı sürətləndirmək üçün index yaradırıq
CREATE INDEX idx_users_id ON users (id);

İndi, əgər belə bir sorğu işlədərsənsə:

SELECT * FROM users WHERE id = 42;

PostgreSQL yaradılmış index-dən istifadə edəcək və lazım olan sətri tez tapacaq.

Nümunə: İndex-lərlə funksiyanın optimallaşdırılması

Tutaq ki, bizdə bir funksiya var, hansı ki, orders cədvəlindən istifadəçiyə görə sifarişləri seçir:

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;

Əgər orders cədvəlində milyonlarla sətir varsa, funksiyanın işləmə sürəti yavaş olacaq. Həll? user_id üçün index yaradırıq:

CREATE INDEX idx_orders_user_id ON orders (user_id);

İndi funksiyanın içindəki sorğu xeyli sürətlənəcək, çünki PostgreSQL axtarış üçün index-dən istifadə edəcək.

İndex növləri

PostgreSQL bir neçə index növünü dəstəkləyir, amma ən məşhurları — B-TREE və GIN-dir. Qısa müqayisə belədir:

Əgər index-ləri daha dərindən öyrənmək istəyirsənsə, PostgreSQL-in rəsmi sənədlərinə bax.

Məlumatların partitioning-i

Əgər index-lər axtarışı sürətləndirirsə, partitioning — cədvəli daha kiçik "parçalara" (partition) bölmək üsuludur. Bu, bir cədvəldə çoxlu məlumat olduqda faydalıdır.

Təsəvvür elə ki, səndə orders cədvəli var və orada son 10 ilin sifarişləri saxlanılır. Əgər son ayın sifarişlərini tapmaq üçün sorğu göndərsən, PostgreSQL yenə də bütün cədvəli yoxlayacaq, bu isə baha başa gəlir. Partitioning bu problemi həll edir, məsələn, məlumatları illərə görə bölür.

Partitioned cədvəlin yaradılması

Partitioned cədvəli belə yarada bilərsən:

-- orders cədvəlini parent partition kimi yaradırıq
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    user_id INT NOT NULL
) PARTITION BY RANGE (order_date);

-- Hər il üçün child cədvəllər yaradırıq
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');

İndi, əgər belə bir sorğu işlədərsənsə:

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

PostgreSQL dərhal başa düşəcək ki, yalnız orders_2023 cədvəlində axtarmaq lazımdır, bütün cədvəli yoxlamağa ehtiyac yoxdur.

Funksiyalarda partitioning-dən istifadə

Təsəvvür elə ki, bizdə bir funksiya var, hansı ki, müəyyən il üçün sifarişləri seçir. Partitioning sayəsində funksiyanın içindəki sorğular daha sürətli işləyəcək, çünki PostgreSQL konkret child cədvəllə işləyəcək.

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;

Praktiki case-lər

1. İndeksləmə case-ləri

Sətirlər üzrə axtarış: əgər səndə məhsullar olan cədvəl var və tez-tez məhsul adlarına görə axtarış edirsənsə, name sütununda index yarat:

CREATE INDEX idx_products_name ON products (name);

Sıralamanı sürətləndirmək: əgər sorğularda tez-tez tarixə görə sıralama istifadə olunursa, index yarat:

CREATE INDEX idx_orders_date ON orders (order_date);

2. Partitioning case-ləri

Tarixi məlumatlar: əgər cədvəldə timestamp-lı məlumatlar varsa, günlərə, aylara və ya illərə görə partitioning sorğuları xeyli sürətləndirəcək.

Coğrafi məlumatlar: əgər cədvəldə ölkələr üzrə məlumatlar varsa, hər ölkə üçün ayrıca partition yarat.

Potensial səhvlər və onların həlli

Çox developer səhvən həddindən artıq çox index yaradır. Bu isə insert və update əməliyyatlarının sürətini azaldır, çünki PostgreSQL hər dəfə cədvəl dəyişəndə index-ləri də yeniləməlidir. Məsləhət: yalnız tez-tez şərt və ya sıralama üçün istifadə olunan sütunlarda index yarat.

Daha bir tipik səhv — partitioning-in düzgün olmamasıdır. Əgər çox xırda partition-lar yaradırsansa (məsələn, aylar əvəzinə günlər üzrə), bu, cədvəllərin idarə olunmasında əlavə xərclərə səbəb ola bilər.