Optimierung von Prozeduren mit Transaktionen: Performance-Analyse und Rollbacks

Wenn du Prozeduren entwickelst, werden sie oft zum "Herzstück" deiner Datenbank und führen viele Operationen aus. Aber genau diese Prozeduren können auch zum "Flaschenhals" werden, besonders wenn:

1. Sie unnötige Operationen ausführen (z.B. immer wieder auf die gleichen Daten zugreifen).
2. Sie Indizes nicht effizient nutzen.
3. Zu viele Operationen innerhalb einer einzigen Transaktion laufen.

Wie ein weiser Entwickler mal sagte: "Schlechten Code schneller machen ist wie einen faulen Freund bitten, schneller zu rennen." Deshalb ist Prozedur-Optimierung nicht nur Speed-Tuning, sondern ein Upgrade des Fundaments!

Minimierung der Anzahl von Operationen innerhalb einer Transaktion

Jede Transaktion in PostgreSQL erzeugt Overhead für die Verwaltung ihrer Operationen. Je größer die Transaktion, desto länger hält sie Locks und desto höher die Chance auf Blockaden für andere User. Um das zu minimieren:

1. Pack nicht zu viele Operationen in eine Transaktion.
2. Nutze EXCEPTION END, um Änderungen lokal zu begrenzen. Das ist praktisch, wenn nur ein Teil der Operationen zurückgesetzt werden soll.
3. Teile große Transaktionen in mehrere kleinere auf (wenn es die Logik deiner App zulässt).

Beispiel: Aufteilen eines Masseneinfügens in "Pakete":

-- Beispiel: Prozedur für Batch-Loading mit schrittweisem Commit
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
                -- Fehler loggen; Änderungen für dieses Element werden zurückgesetzt
                INSERT INTO load_errors(msg) VALUES (SQLERRM);
        END;
        IF batch_cnt >= 1000 THEN
            COMMIT; -- jede 1000 Operationen committen
            batch_cnt := 0;
        END IF;
    END LOOP;
    COMMIT; -- finaler Commit
END;
$$;

Tipp: Vergiss nicht, dass jedes COMMIT Änderungen festschreibt – also prüfe vorher, ob das Aufteilen der Transaktion die Datenintegrität nicht gefährdet.

Indizes nutzen, um Abfragen zu beschleunigen

Angenommen, wir haben eine Tabelle orders mit einer Million Einträgen und du fragst oft nach customer_id. Ohne Index scannt die Abfrage alle Zeilen:

CREATE INDEX idx_customer_id ON orders(customer_id);

Jetzt laufen Abfragen wie diese:

SELECT * FROM orders WHERE customer_id = 42;

viel schneller, weil kein Full Table Scan mehr nötig ist.

Wichtig: Achte beim Schreiben von Prozeduren darauf, dass die verwendeten Felder indiziert sind – vor allem bei Filtern, Sortierungen und Joins.

Performance-Analyse mit EXPLAIN ANALYZE

EXPLAIN zeigt dir den Ausführungsplan einer Abfrage (wie PostgreSQL sie ausführen will), und ANALYZE liefert echte Ausführungsstatistiken (z.B. wie lange es gedauert hat). Typisches Beispiel:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Wie nutzt man das in einer Prozedur?

Du kannst komplexe Abfragen aus deiner Prozedur "herauslösen" und sie einzeln mit EXPLAIN ANALYZE testen:

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

Beispiel für Analyse und Verbesserung

Ursprüngliche Prozedur (langsam):

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;

Was passiert hier? Für jede Zeile in der Tabelle sales wird ein Subquery SUM(amount) ausgeführt – das sind viele Operationen und dauert lange.

Besser so:

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;

Jetzt läuft der Subquery mit SUM nur einmal und alle Daten werden direkt aktualisiert.

Rollbacks bei Fehlern

Wenn in einer Prozedur was schiefgeht, kannst du auch nur einen Teil der Transaktion zurücksetzen. Zum Beispiel:

BEGIN
    -- Daten einfügen
    INSERT INTO inventory(product_id, quantity) VALUES (1, -5);
EXCEPTION
    WHEN OTHERS THEN
        -- Dieser Block entspricht einem internen Savepoint!
        RAISE WARNING 'Fehler beim Aktualisieren der Daten: %', SQLERRM;
END;

Praxis: Robuste Prozedur zur Bestellverarbeitung

Angenommen, deine Aufgabe ist: Eine Bestellung verarbeiten. Wenn ein Fehler auftritt (z.B. nicht genug Ware), wird die Bestellung abgebrochen und der Fehler geloggt.

CREATE OR REPLACE PROCEDURE process_order(p_order_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_in_stock INT;
BEGIN
    -- Bestand prüfen
    SELECT stock INTO v_in_stock FROM products WHERE id = p_order_id;

    BEGIN
        IF v_in_stock < 1 THEN
            RAISE EXCEPTION 'Kein Produkt auf Lager';
        END IF;
        UPDATE products SET stock = stock - 1 WHERE id = p_order_id;
        -- ... weitere Operationen
    EXCEPTION
        WHEN OTHERS THEN
            -- Alle Änderungen in diesem Block werden zurückgesetzt!
            INSERT INTO order_logs(order_id, log_message)
                VALUES (p_order_id, 'Fehler bei der Verarbeitung: ' || SQLERRM);
            RAISE NOTICE 'Fehler bei der Bestellverarbeitung: %', SQLERRM;
    END;

    -- Der restliche Code läuft weiter, wenn kein Fehler aufgetreten ist
    -- Du kannst loggen: Bestellung erfolgreich verarbeitet
END;
$$;

• Auch bei Fehlern wird die Bestellung nicht verarbeitet, aber ein Log-Eintrag landet in der Tabelle order_logs.
• Bei Fehlern greift ein interner Savepoint und du verlierst nicht den ganzen Kontext.

Grundregeln für die Optimierung und Robustheit von Prozeduren

1. Nutze Indizes für Abfragen in Prozeduren.
2. Teile große Operationen in kleine Batches auf, arbeite schrittweise.
3. Fehler loggen – leg eine eigene Tabelle für Fehler-Logs bei Massenoperationen an.
4. Für "partielle" Rollbacks nutze nur verschachtelte Blöcke mit EXCEPTION.
5. Verwende ROLLBACK TO SAVEPOINT nicht in PL/pgSQL – das gibt einen Syntaxfehler.
6. In Prozeduren nutze COMMIT/SAVEPOINT nur, wenn du im Autocommit-Modus arbeitest!
7. Analysiere den Ausführungsplan schwerer Abfragen (EXPLAIN ANALYZE) außerhalb der Prozeduren, bevor du sie integrierst.