Оптимізація процедур з урахуванням транзакцій: аналіз продуктивності та відкати

Коли ти розробляєш процедури, вони часто стають "серцем" твоєї бази даних, виконуючи купу операцій. Але ці ж процедури можуть бути "вузьким місцем", особливо якщо:

1. Вони виконують зайві операції (наприклад, часто звертаються до одних і тих самих даних).
2. Вони неефективно використовують індекси.
3. Виконують забагато операцій всередині однієї транзакції.

Як сказав один мудрий розробник: "Прискорити погано написаний код — це як просити лінивого друга бігати швидше". Тому оптимізація процедур — це не просто про швидкість, це про покращення самої основи!

Мінімізація кількості операцій всередині транзакції

Кожна транзакція в PostgreSQL створює накладні витрати для обслуговування своїх операцій. Чим більша транзакція, тим довше вона тримає блокування і тим більше шансів на блокування інших користувачів. Щоб мінімізувати ці ефекти:

1. Не об'єднуй в одну транзакцію забагато операцій.
2. Використовуй EXCEPTION END, щоб локально обмежувати зміни. Це корисно, якщо тільки частина операцій потребує відкату.
3. Діли великі транзакції на кілька менших (якщо логіка твого застосунку це дозволяє).

Приклад: поділ масової вставки даних на "пакети":

-- Приклад: Процедура для пакетного завантаження з поетапним комітом
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
                -- Логуємо помилку; зміни по цьому елементу будуть відкочені
                INSERT INTO load_errors(msg) VALUES (SQLERRM);
        END;
        IF batch_cnt >= 1000 THEN
            COMMIT; -- фіксуємо кожні 1000 операцій
            batch_cnt := 0;
        END IF;
    END LOOP;
    COMMIT; -- фінальний коміт
END;
$$;

Порада: не забувай, що кожен COMMIT фіксує зміни, тому переконайся заздалегідь, що поділ транзакції не порушить цілісність даних.

Використання індексів для прискорення запитів

Припустимо, у нас є таблиця orders з мільйоном записів, і ти часто робиш запити по customer_id. Без індексу запит буде сканувати всі рядки:

CREATE INDEX idx_customer_id ON orders(customer_id);

Тепер запити типу:

SELECT * FROM orders WHERE customer_id = 42;

будуть працювати набагато швидше, уникаючи сканування всієї таблиці.

Важливо: при створенні процедур переконайся, що використовувані поля беруть участь в індексах, особливо в умовах фільтрів, сортувань і join-ів.

Аналіз продуктивності через EXPLAIN ANALYZE

EXPLAIN показує план виконання запиту (як PostgreSQL збирається його виконати), а ANALYZE додає реальну статистику виконання (наприклад, скільки часу пішло на виконання). Ось типовий приклад:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Як це використати всередині процедури?

Ти можеш "розкласти" складні запити своєї процедури, виконавши їх окремо з EXPLAIN ANALYZE:

DO $$
BEGIN
    RAISE NOTICE 'План запиту: %',
    (
        SELECT query_plan
        FROM pg_stat_statements
        WHERE query = 'SELECT * FROM orders WHERE customer_id = 42'
    );
END $$;

Приклад аналізу та покращення

Початкова процедура (повільна):

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;

Що відбувається? Для кожного рядка з таблиці sales виконується підзапит SUM(amount), що призводить до купи операцій. Це повільно.

Покращений варіант:

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;

Тепер підзапит з SUM виконається один раз, і всі дані одразу оновляться.

Відкати даних при помилках

Якщо щось пішло не так всередині процедури, ти можеш відкочувати тільки частину транзакції. Наприклад:

BEGIN
    -- Вставляємо дані
    INSERT INTO inventory(product_id, quantity) VALUES (1, -5);
EXCEPTION
    WHEN OTHERS THEN
        -- Цей блок еквівалентний відкату до внутрішнього savepoint!
        RAISE WARNING 'Помилка при оновленні даних: %', SQLERRM;
END;

Практика: реалізація стійкої процедури обробки замовлень

Припустимо, твоє завдання: обробити замовлення. Якщо в процесі виникла помилка (наприклад, товару не вистачає), замовлення скасовується, а помилка логується.

CREATE OR REPLACE PROCEDURE process_order(p_order_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_in_stock INT;
BEGIN
    -- Перевіряємо залишки
    SELECT stock INTO v_in_stock FROM products WHERE id = p_order_id;

    BEGIN
        IF v_in_stock < 1 THEN
            RAISE EXCEPTION 'Немає товару на складі';
        END IF;
        UPDATE products SET stock = stock - 1 WHERE id = p_order_id;
        -- ... інші операції
    EXCEPTION
        WHEN OTHERS THEN
            -- Всі зміни в цьому блоці відкочуються!
            INSERT INTO order_logs(order_id, log_message)
                VALUES (p_order_id, 'Помилка обробки: ' || SQLERRM);
            RAISE NOTICE 'Помилка обробки замовлення: %', SQLERRM;
    END;

    -- Решта коду продовжується, якщо не було помилок
    -- Можна логувати: замовлення успішно оброблене
END;
$$;

• Навіть при помилці замовлення не обробиться, а лог з'явиться в таблиці order_logs.
• При помилці спрацює внутрішній savepoint, і ти не втратиш весь контекст.

Основні правила оптимізації та стійкості процедур

1. Використовуй індекси для запитів всередині процедур.
2. Діли великі операції на невеликі пачки (batch), роби поетапну обробку.
3. Вмій логувати помилки — створи окрему таблицю для логів помилок масових операцій.
4. Для "часткових" відкатів використовуй тільки вкладені блоки з EXCEPTION.
5. Не використовуй ROLLBACK TO SAVEPOINT всередині PL/pgSQL — це викличе синтаксичну помилку.
6. У процедурах використовуй COMMIT/SAVEPOINT тільки при виклику в autocommit-режимі з'єднання!
7. Аналізуй план виконання важких запитів (EXPLAIN ANALYZE) поза процедурами, до інтеграції.