Optimisation des procédures avec transactions : analyse de perf et rollbacks

Quand tu développes des procédures, elles deviennent souvent le "cœur" de ta base de données, en faisant plein d'opérations. Mais ces mêmes procédures peuvent devenir un "goulot d'étranglement", surtout si :

1. Elles font des opérations inutiles (genre, elles vont chercher tout le temps les mêmes données).
2. Elles utilisent mal les indexes.
3. Elles font trop d'opérations dans une seule transaction.

Comme l'a dit un dev malin : "Optimiser du mauvais code, c'est comme demander à ton pote flemmard de courir plus vite". Donc optimiser les procédures, ce n'est pas juste aller plus vite, c'est améliorer la base même !

Minimiser le nombre d'opérations dans une transaction

Chaque transaction dans PostgreSQL crée un surcoût pour gérer ses opérations. Plus la transaction est grosse, plus elle garde les verrous longtemps et plus tu risques de bloquer d'autres utilisateurs. Pour limiter ces effets :

1. N'empile pas trop d'opérations dans une seule transaction.
2. Utilise EXCEPTION END pour limiter localement les changements. C'est utile si seulement une partie des opérations doit être rollback.
3. Découpe les grosses transactions en plusieurs plus petites (si la logique de ton appli le permet).

Exemple : découper un insert massif de données en "paquets" :

-- Exemple : Procédure pour un chargement batch avec commit étape par étape
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
                -- On log l'erreur ; les changements pour cet élément seront rollback
                INSERT INTO load_errors(msg) VALUES (SQLERRM);
        END;
        IF batch_cnt >= 1000 THEN
            COMMIT; -- on valide chaque 1000 opérations
            batch_cnt := 0;
        END IF;
    END LOOP;
    COMMIT; -- commit final
END;
$$;

Astuce : n'oublie pas que chaque COMMIT valide les changements, donc assure-toi que découper la transaction ne va pas casser l'intégrité des données.

Utiliser les indexes pour accélérer les requêtes

Imaginons qu'on a une table orders avec un million de lignes, et tu fais souvent des requêtes sur customer_id. Sans index, la requête va scanner toutes les lignes :

CREATE INDEX idx_customer_id ON orders(customer_id);

Maintenant, les requêtes comme celle-ci :

SELECT * FROM orders WHERE customer_id = 42;

vont tourner beaucoup plus vite, sans scanner toute la table.

Important : quand tu crées des procédures, assure-toi que les champs utilisés sont indexés, surtout dans les filtres, les tris et les jointures.

Analyser la perf avec EXPLAIN ANALYZE

EXPLAIN montre le plan d'exécution de la requête (comment PostgreSQL compte la faire), et ANALYZE ajoute les vraies stats d'exécution (genre combien de temps ça a pris). Voilà un exemple typique :

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Comment l'utiliser dans une procédure ?

Tu peux "découper" les requêtes complexes de ta procédure, les exécuter à part avec EXPLAIN ANALYZE :

DO $$
BEGIN
    RAISE NOTICE 'Plan de requête : %',
    (
        SELECT query_plan
        FROM pg_stat_statements
        WHERE query = 'SELECT * FROM orders WHERE customer_id = 42'
    );
END $$;

Exemple d'analyse et d'amélioration

Procédure initiale (lente) :

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;

Qu'est-ce qui se passe ? Pour chaque ligne de la table sales, on fait un sous-select SUM(amount), donc plein d'opérations. C'est lent.

Version améliorée :

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;

Maintenant, le sous-select avec SUM s'exécute une seule fois, et toutes les données sont mises à jour d'un coup.

Rollbacks de données en cas d'erreur

Si un truc se passe mal dans la procédure, tu peux rollback juste une partie de la transaction. Par exemple :

BEGIN
    -- On insère des données
    INSERT INTO inventory(product_id, quantity) VALUES (1, -5);
EXCEPTION
    WHEN OTHERS THEN
        -- Ce bloc équivaut à un rollback vers un savepoint interne !
        RAISE WARNING 'Erreur lors de la mise à jour des données : %', SQLERRM;
END;

Pratique : faire une procédure robuste de traitement de commande

Imaginons que ta mission est de traiter une commande. Si une erreur arrive (genre, pas assez de stock), la commande est annulée et l'erreur est loguée.

CREATE OR REPLACE PROCEDURE process_order(p_order_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_in_stock INT;
BEGIN
    -- On vérifie le stock
    SELECT stock INTO v_in_stock FROM products WHERE id = p_order_id;

    BEGIN
        IF v_in_stock < 1 THEN
            RAISE EXCEPTION 'Pas de produit en stock';
        END IF;
        UPDATE products SET stock = stock - 1 WHERE id = p_order_id;
        -- ... autres opérations
    EXCEPTION
        WHEN OTHERS THEN
            -- Tous les changements dans ce bloc sont rollback !
            INSERT INTO order_logs(order_id, log_message)
                VALUES (p_order_id, 'Erreur de traitement : ' || SQLERRM);
            RAISE NOTICE 'Erreur de traitement de la commande : %', SQLERRM;
    END;

    -- Le reste du code continue si pas d'erreur
    -- Tu peux logger : commande traitée avec succès
END;
$$;

• Même en cas d'erreur, la commande ne sera pas traitée, et un log apparaîtra dans la table order_logs.
• En cas d'erreur, un savepoint interne s'active, donc tu ne perds pas tout le contexte.

Règles de base pour optimiser et fiabiliser les procédures

1. Utilise des indexes pour les requêtes dans les procédures.
2. Découpe les grosses opérations en petits batchs, traite-les étape par étape.
3. Sache logger les erreurs — fais une table à part pour les logs d'erreurs des opérations massives.
4. Pour les rollbacks "partiels", utilise seulement des blocs imbriqués avec EXCEPTION.
5. N'utilise pas ROLLBACK TO SAVEPOINT dans PL/pgSQL — ça va faire une erreur de syntaxe.
6. Dans les procédures, utilise COMMIT/SAVEPOINT seulement si tu es en mode autocommit sur la connexion !
7. Analyse le plan d'exécution des requêtes lourdes (EXPLAIN ANALYZE) en dehors des procédures, avant de les intégrer.