Когда вы разрабатываете процедуры, они часто становятся "сердцем" вашей базы данных, выполняя множество операций. Однако эти же процедуры могут быть "узким местом", особенно если:
- Они выполняют ненужные операции (например, часто обращаются к одним и тем же данным).
- Они неэффективно используют индексы.
- Выполняют слишком много операций внутри одной транзакции.
Как сказал один мудрый разработчик: "Ускорить плохо написанный код — как просить ленивого друга бегать быстрее". Поэтому оптимизация процедур — это не просто улучшение скорости, это улучшение самой основы!
Минимизация количества операций внутри транзакции
Каждая транзакция в PostgreSQL создает накладные расходы для обслуживания своих операций. Чем крупнее транзакция, тем дольше она удерживает блокировки и тем больше шансов на блокировки других пользователей. Чтобы минимизировать эти эффекты:
- Не объединяйте в одну транзакцию слишком много операций.
- Используйте
EXCEPTION END, чтобы локально ограничивать изменения. Это полезно, если только часть операций требует отката. - Разделяйте крупные транзакции на несколько меньших (если логика вашего приложения это позволяет).
Пример: разделение массовой вставки данных на "пакеты":
-- Пример: Процедура для пакетной загрузки с поэтапным коммитом
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;
будут работать гораздо быстрее, избегая сканирования всей таблицы.
Важно: при создании процедур убедитесь, что используемые поля участвуют в индексах, особенно в условиях фильтров, сортировок и соединений.
Анализ производительности с EXPLAIN ANALYZE
EXPLAIN показывает план выполнения запроса (как PostgreSQL собирается его выполнить), а ANALYZE добавляет реальную статистику выполнения (например, сколько времени ушло на выполнение). Вот типичный пример:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
Как его использовать внутри процедуры?
Вы можете "разложить" сложные запросы вашей процедуры, выполнив их отдельно с EXPLAIN ANALYZE:
DO $$
BEGIN
RAISE NOTICE 'Query Plan: %',
(
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, и вы не потеряете весь контекст.
Основные правила оптимизации и устойчивости процедур
- Используйте индексы для запросов внутри процедур.
- Разделяйте большие операции на небольшие пачки (batch), делайте поэтапную обработку.
- Умейте логировать ошибки — делайте отдельную таблицу для логов ошибок массовых операций.
- Для "частичных" откатов используйте только вложенные блоки с
EXCEPTION. - Не используйте
ROLLBACK TO SAVEPOINTвнутри PL/pgSQL — это вызовет ошибку синтаксиса. - В процедурах используйте COMMIT/SAVEPOINT только при вызове в autocommit-режиме соединения!
- Анализируйте план выполнения тяжелых запросов (
EXPLAIN ANALYZE) вне процедур, до интеграции.
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ