Ottimizzazione delle procedure considerando le transazioni: analisi delle performance e rollback

Quando sviluppi delle procedure, spesso diventano il "cuore" del tuo database, eseguendo un sacco di operazioni. Però, queste stesse procedure possono diventare il "collo di bottiglia", soprattutto se:

1. Fanno operazioni inutili (tipo, accedono spesso agli stessi dati).
2. Non usano bene gli indici.
3. Fanno troppe operazioni dentro una sola transazione.

Come ha detto un saggio developer: "Velocizzare un codice scritto male è come chiedere a un amico pigro di correre più veloce". Quindi ottimizzare le procedure non è solo una questione di velocità, ma di migliorare proprio la base!

Minimizzare il numero di operazioni dentro una transazione

Ogni transazione in PostgreSQL crea un certo overhead per gestire le sue operazioni. Più è grande la transazione, più a lungo tiene i lock e più è facile che blocchi altri utenti. Per minimizzare questi effetti:

1. Non mettere troppe operazioni in una sola transazione.
2. Usa EXCEPTION END per limitare localmente le modifiche. È utile se solo una parte delle operazioni deve essere rollbackata.
3. Dividi le transazioni grosse in più piccole (se la logica della tua app lo permette).

Esempio: dividere un inserimento massivo di dati in "pacchetti":

-- Esempio: Procedura per il caricamento batch con commit a step
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
                -- Logghiamo l'errore; le modifiche per questo elemento saranno rollbackate
                INSERT INTO load_errors(msg) VALUES (SQLERRM);
        END;
        IF batch_cnt >= 1000 THEN
            COMMIT; -- facciamo commit ogni 1000 operazioni
            batch_cnt := 0;
        END IF;
    END LOOP;
    COMMIT; -- commit finale
END;
$$;

Consiglio: ricordati che ogni COMMIT salva le modifiche, quindi assicurati prima che dividere la transazione non rompa l'integrità dei dati.

Uso degli indici per velocizzare le query

Supponiamo di avere una tabella orders con un milione di record, e fai spesso query su customer_id. Senza indice, la query scansiona tutte le righe:

CREATE INDEX idx_customer_id ON orders(customer_id);

Ora le query tipo questa:

SELECT * FROM orders WHERE customer_id = 42;

andranno molto più veloci, evitando di scansionare tutta la tabella.

Importante: quando crei procedure, assicurati che i campi usati siano indicizzati, soprattutto nei filtri, ordinamenti e join.

Analisi delle performance con EXPLAIN ANALYZE

EXPLAIN mostra il piano di esecuzione della query (come PostgreSQL pensa di eseguirla), mentre ANALYZE aggiunge statistiche reali di esecuzione (tipo quanto tempo ci ha messo). Ecco un esempio classico:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Come usarlo dentro una procedura?

Puoi "scomporre" le query complesse della tua procedura, eseguendole separatamente con 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 $$;

Esempio di analisi e miglioramento

Procedura iniziale (lenta):

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;

Cosa succede? Per ogni riga della tabella sales viene eseguita una subquery SUM(amount), che genera un sacco di operazioni. È lento.

Versione migliorata:

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;

Ora la subquery con SUM viene eseguita una sola volta e tutti i dati vengono aggiornati subito.

Rollback dei dati in caso di errori

Se qualcosa va storto dentro una procedura, puoi fare rollback solo di una parte della transazione. Per esempio:

BEGIN
    -- Inseriamo i dati
    INSERT INTO inventory(product_id, quantity) VALUES (1, -5);
EXCEPTION
    WHEN OTHERS THEN
        -- Questo blocco equivale a un rollback a un savepoint interno!
        RAISE WARNING 'Errore durante l\'aggiornamento dei dati: %', SQLERRM;
END;

Pratica: implementare una procedura robusta per la gestione degli ordini

Supponiamo che il tuo compito sia: gestire un ordine. Se durante il processo c'è un errore (tipo, non c'è abbastanza merce), l'ordine viene annullato e l'errore viene loggato.

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

    BEGIN
        IF v_in_stock < 1 THEN
            RAISE EXCEPTION 'Non c\'è merce in magazzino';
        END IF;
        UPDATE products SET stock = stock - 1 WHERE id = p_order_id;
        -- ... altre operazioni
    EXCEPTION
        WHEN OTHERS THEN
            -- Tutte le modifiche in questo blocco vengono rollbackate!
            INSERT INTO order_logs(order_id, log_message)
                VALUES (p_order_id, 'Errore di gestione: ' || SQLERRM);
            RAISE NOTICE 'Errore nella gestione dell\'ordine: %', SQLERRM;
    END;

    -- Il resto del codice continua, se non ci sono stati errori
    -- Puoi loggare: ordine gestito con successo
END;
$$;

• Anche in caso di errore l'ordine non verrà gestito, ma il log apparirà nella tabella order_logs.
• Se c'è un errore, scatterà un savepoint interno e non perderai tutto il contesto.

Regole base per ottimizzare e rendere robuste le procedure

1. Usa gli indici per le query dentro le procedure.
2. Dividi le operazioni grosse in batch più piccoli, fai elaborazione a step.
3. Sii capace di loggare gli errori — crea una tabella separata per i log degli errori delle operazioni massive.
4. Per rollback "parziali" usa solo blocchi annidati con EXCEPTION.
5. Non usare ROLLBACK TO SAVEPOINT dentro PL/pgSQL — ti darà errore di sintassi.
6. Nelle procedure usa COMMIT/SAVEPOINT solo se sei in modalità autocommit della connessione!
7. Analizza il piano di esecuzione delle query pesanti (EXPLAIN ANALYZE) fuori dalle procedure, prima di integrarle.