Otimização de procedimentos considerando transações: análise de performance e rollbacks

Quando você desenvolve procedimentos, eles geralmente viram o "coração" do seu banco de dados, fazendo um monte de operações. Mas esses mesmos procedimentos podem ser o "gargalo", principalmente se:

1. Fazem operações desnecessárias (tipo, ficam buscando os mesmos dados toda hora).
2. Não usam índices de forma eficiente.
3. Executam coisa demais dentro de uma única transação.

Como já disse um dev sábio: "Tentar acelerar código mal escrito é igual pedir pro amigo preguiçoso correr mais rápido". Então, otimizar procedimentos não é só deixar mais rápido, é melhorar a base de tudo!

Minimizando a quantidade de operações dentro da transação

Cada transação no PostgreSQL gera um overhead pra gerenciar suas operações. Quanto maior a transação, mais tempo ela segura locks e maior a chance de travar outros usuários. Pra minimizar isso:

1. Não junta operações demais numa transação só.
2. Usa EXCEPTION END pra limitar mudanças localmente. Isso é útil quando só parte das operações precisa de rollback.
3. Divide transações grandes em várias menores (se a lógica do seu app permitir).

Exemplo: dividir um insert massivo de dados em "pacotes":

-- Exemplo: Procedimento pra carga em lote com commit por etapa
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
                -- Logando erro; mudanças desse item vão ser revertidas
                INSERT INTO load_errors(msg) VALUES (SQLERRM);
        END;
        IF batch_cnt >= 1000 THEN
            COMMIT; -- faz commit a cada 1000 operações
            batch_cnt := 0;
        END IF;
    END LOOP;
    COMMIT; -- commit final
END;
$$;

Dica: não esquece que cada COMMIT salva as mudanças, então garante antes que dividir a transação não vai ferrar a integridade dos dados.

Usando índices pra acelerar queries

Imagina que temos uma tabela orders com um milhão de registros, e você sempre faz query por customer_id. Sem índice, a query vai escanear tudo:

CREATE INDEX idx_customer_id ON orders(customer_id);

Agora, queries tipo essa:

SELECT * FROM orders WHERE customer_id = 42;

vão rodar bem mais rápido, sem precisar escanear a tabela inteira.

Importante: quando criar procedimentos, garante que os campos usados estão indexados, principalmente nos filtros, ordenações e joins.

Analisando performance com EXPLAIN ANALYZE

EXPLAIN mostra o plano de execução da query (como o PostgreSQL vai rodar ela), e ANALYZE adiciona estatísticas reais (tipo quanto tempo levou pra executar). Olha um exemplo:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Como usar isso dentro do procedimento?

Você pode "desmembrar" queries complexas do seu procedimento, rodando elas separadas com EXPLAIN ANALYZE:

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

Exemplo de análise e melhoria

Procedimento 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;

O que rola aqui? Pra cada linha da tabela sales ele faz um subquery SUM(amount), ou seja, um monte de operação. Isso é lento.

Versão melhorada:

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;

Agora o subquery com SUM roda só uma vez e já atualiza tudo de uma vez.

Rollbacks de dados em caso de erro

Se der ruim dentro do procedimento, você pode reverter só parte da transação. Por exemplo:

BEGIN
    -- Inserindo dados
    INSERT INTO inventory(product_id, quantity) VALUES (1, -5);
EXCEPTION
    WHEN OTHERS THEN
        -- Esse bloco é tipo um rollback pra um savepoint interno!
        RAISE WARNING 'Erro ao atualizar dados: %', SQLERRM;
END;

Prática: implementando um procedimento resistente de processamento de pedidos

Imagina que sua missão é: processar um pedido. Se der erro (tipo, não tem produto suficiente), o pedido é cancelado e o erro é logado.

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

    BEGIN
        IF v_in_stock < 1 THEN
            RAISE EXCEPTION 'Sem produto no estoque';
        END IF;
        UPDATE products SET stock = stock - 1 WHERE id = p_order_id;
        -- ... outras operações
    EXCEPTION
        WHEN OTHERS THEN
            -- Todas as mudanças desse bloco são revertidas!
            INSERT INTO order_logs(order_id, log_message)
                VALUES (p_order_id, 'Erro no processamento: ' || SQLERRM);
            RAISE NOTICE 'Erro no processamento do pedido: %', SQLERRM;
    END;

    -- O resto do código continua se não teve erro
    -- Pode logar: pedido processado com sucesso
END;
$$;

• Mesmo se der erro, o pedido não é processado e o log aparece na tabela order_logs.
• Se der erro, o savepoint interno entra em ação e você não perde o contexto todo.

Regras principais pra otimizar e deixar procedimentos resistentes

1. Usa índices nas queries dentro dos procedimentos.
2. Divide operações grandes em batches menores, faz processamento por etapa.
3. Sabe logar erros — cria uma tabela só pra logs de erro de operações em massa.
4. Pra rollbacks "parciais", usa só blocos aninhados com EXCEPTION.
5. Não usa ROLLBACK TO SAVEPOINT dentro do PL/pgSQL — isso vai dar erro de sintaxe.
6. Nos procedimentos, usa COMMIT/SAVEPOINT só se a conexão estiver em modo autocommit!
7. Analisa o plano de execução de queries pesadas (EXPLAIN ANALYZE) fora dos procedimentos, antes de integrar.