Optimización de procedimientos teniendo en cuenta transacciones: análisis de rendimiento y rollbacks

Cuando desarrollas procedimientos, muchas veces se convierten en el "corazón" de tu base de datos, ejecutando un montón de operaciones. Pero esos mismos procedimientos pueden ser el "cuello de botella", sobre todo si:

1. Hacen operaciones innecesarias (por ejemplo, acceden muchas veces a los mismos datos).
2. No usan bien los índices.
3. Hacen demasiadas operaciones dentro de una sola transacción.

Como dijo un desarrollador sabio: "Acelerar código mal escrito es como pedirle a tu colega vago que corra más rápido". Así que optimizar procedimientos no es solo mejorar la velocidad, ¡es mejorar la base de todo!

Minimizar la cantidad de operaciones dentro de una transacción

Cada transacción en PostgreSQL crea un overhead para gestionar sus operaciones. Cuanto más grande es la transacción, más tiempo mantiene los locks y más posibilidades hay de bloquear a otros usuarios. Para minimizar estos efectos:

1. No metas demasiadas operaciones en una sola transacción.
2. Usa EXCEPTION END para limitar los cambios localmente. Esto es útil si solo una parte de las operaciones necesita rollback.
3. Divide las transacciones grandes en varias más pequeñas (si la lógica de tu app lo permite).

Ejemplo: dividir una inserción masiva de datos en "paquetes":

-- Ejemplo: Procedimiento para carga por lotes con commit por etapas
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
                -- Registramos el error; los cambios de este elemento serán revertidos
                INSERT INTO load_errors(msg) VALUES (SQLERRM);
        END;
        IF batch_cnt >= 1000 THEN
            COMMIT; -- confirmamos cada 1000 operaciones
            batch_cnt := 0;
        END IF;
    END LOOP;
    COMMIT; -- commit final
END;
$$;

Consejo: no olvides que cada COMMIT confirma los cambios, así que asegúrate de antemano que dividir la transacción no va a romper la integridad de los datos.

Uso de índices para acelerar consultas

Supón que tienes una tabla orders con un millón de registros, y consultas mucho por customer_id. Sin un índice, la consulta va a escanear todas las filas:

CREATE INDEX idx_customer_id ON orders(customer_id);

Ahora las consultas como esta:

SELECT * FROM orders WHERE customer_id = 42;

van a ir mucho más rápido, evitando escanear toda la tabla.

Importante: cuando crees procedimientos, asegúrate de que los campos usados estén indexados, sobre todo en filtros, ordenamientos y joins.

Análisis de rendimiento con EXPLAIN ANALYZE

EXPLAIN muestra el plan de ejecución de la consulta (cómo PostgreSQL planea ejecutarla), y ANALYZE añade estadísticas reales de ejecución (por ejemplo, cuánto tiempo tardó). Aquí tienes un ejemplo típico:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

¿Cómo usarlo dentro de un procedimiento?

Puedes "descomponer" las consultas complejas de tu procedimiento, ejecutándolas aparte con EXPLAIN ANALYZE:

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

Ejemplo de análisis y mejora

Procedimiento original (lento):

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é pasa aquí? Para cada fila de la tabla sales se ejecuta el subquery SUM(amount), lo que genera un montón de operaciones. Es lento.

Versión mejorada:

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;

Ahora el subquery con SUM se ejecuta una sola vez y todos los datos se actualizan de golpe.

Rollbacks de datos en caso de errores

Si algo sale mal dentro del procedimiento, puedes hacer rollback solo de una parte de la transacción. Por ejemplo:

BEGIN
    -- Insertamos datos
    INSERT INTO inventory(product_id, quantity) VALUES (1, -5);
EXCEPTION
    WHEN OTHERS THEN
        -- Este bloque es como un rollback a un savepoint interno
        RAISE WARNING 'Error al actualizar datos: %', SQLERRM;
END;

Práctica: implementación de un procedimiento resistente para procesar pedidos

Supón que tu tarea es procesar un pedido. Si ocurre un error (por ejemplo, no hay suficiente stock), el pedido se cancela y el error se registra en logs.

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

    BEGIN
        IF v_in_stock < 1 THEN
            RAISE EXCEPTION 'No hay stock disponible';
        END IF;
        UPDATE products SET stock = stock - 1 WHERE id = p_order_id;
        -- ... otras operaciones
    EXCEPTION
        WHEN OTHERS THEN
            -- ¡Todos los cambios en este bloque se revierten!
            INSERT INTO order_logs(order_id, log_message)
                VALUES (p_order_id, 'Error de procesamiento: ' || SQLERRM);
            RAISE NOTICE 'Error al procesar pedido: %', SQLERRM;
    END;

    -- El resto del código sigue si no hubo errores
    -- Puedes registrar: pedido procesado con éxito
END;
$$;

• Incluso si hay error, el pedido no se procesa y el log aparece en la tabla order_logs.
• Si hay error, se activa un savepoint interno y no pierdes todo el contexto.

Reglas básicas para optimizar y hacer resistentes los procedimientos

1. Usa índices en las consultas dentro de los procedimientos.
2. Divide operaciones grandes en lotes pequeños (batch), haz procesamiento por etapas.
3. Sé capaz de registrar errores — crea una tabla aparte para logs de errores de operaciones masivas.
4. Para rollbacks "parciales" usa solo bloques anidados con EXCEPTION.
5. No uses ROLLBACK TO SAVEPOINT dentro de PL/pgSQL — eso da error de sintaxis.
6. En procedimientos, usa COMMIT/SAVEPOINT solo si llamas en modo autocommit de la conexión.
7. Analiza el plan de ejecución de consultas pesadas (EXPLAIN ANALYZE) fuera de los procedimientos, antes de integrarlas.