考慮交易的程序優化：效能分析與回滾

當你在寫程序的時候，這些程序常常變成你資料庫的「核心」，負責一堆操作。但這些程序也很容易變成「瓶頸」，尤其是：

1. 它們做了沒必要的操作（像是一直重複查同一筆資料）。
2. index 沒用好。
3. 在同一個交易裡做太多事。

有個很懂的工程師說過：「要讓寫很爛的 code 跑更快，就像叫很懶的朋友去跑步一樣。」所以優化程序不只是加速，而是把底層打好！

減少交易裡的操作數量

每個 PostgreSQL 的交易都會有一些額外負擔來處理自己的操作。交易越大，鎖住的時間越久，也越容易卡到別人。想要減少這些問題：

1. 不要把太多操作塞進同一個交易。
2. 用 EXCEPTION END 來局部限制變更。如果只有部分操作需要回滾，這超好用。
3. 把大交易拆成幾個小的（如果你的應用邏輯允許的話）。

範例：把大量 insert 拆成「批次」：

-- 範例：分批載入資料，每批 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
                -- 記錄錯誤；這筆資料的變更會被回滾
                INSERT INTO load_errors(msg) VALUES (SQLERRM);
        END;
        IF batch_cnt >= 1000 THEN
            COMMIT; -- 每 1000 筆 commit 一次
            batch_cnt := 0;
        END IF;
    END LOOP;
    COMMIT; -- 最後再 commit 一次
END;
$$;

小提醒：每個 COMMIT 都會把變更寫死，所以要先確定這樣拆交易不會破壞資料完整性。

用 index 加速查詢

假設你有一個 orders 表有一百萬筆資料，而且你常常用 customer_id 查。沒 index 的話，查詢會掃整張表：

CREATE INDEX idx_customer_id ON orders(customer_id);

現在像這樣的查詢：

SELECT * FROM orders WHERE customer_id = 42;

就會快很多，不用全表掃描。

重點：寫程序時要確定你用到的欄位有 index，尤其是 filter、排序、join 用到的。

用 EXPLAIN ANALYZE 分析效能

EXPLAIN 會顯示查詢的執行計畫（PostgreSQL 打算怎麼跑），ANALYZE 則會加上實際執行的統計資料（像是花了多少時間）。來看個例子：

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

怎麼在程序裡用？

你可以把程序裡的複雜查詢單獨拿出來用 EXPLAIN ANALYZE 跑看看：

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

分析與優化範例

原本的程序（很慢）：

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;

發生什麼事？ 每一筆 sales 都會跑一次 SUM(amount) 子查詢，超多操作，當然慢。

優化後：

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;

現在 SUM 子查詢只跑一次，所有資料一次更新完。

發生錯誤時的資料回滾

如果程序裡有問題，你可以只回滾部分交易。例如：

BEGIN
    -- 插入資料
    INSERT INTO inventory(product_id, quantity) VALUES (1, -5);
EXCEPTION
    WHEN OTHERS THEN
        -- 這個區塊就像回到內部 savepoint！
        RAISE WARNING '更新資料時發生錯誤: %', SQLERRM;
END;

實戰：寫一個穩定的訂單處理程序

假設你的任務是處理訂單。如果過程中出錯（像是庫存不夠），訂單就要取消，錯誤要記錄下來。

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

    BEGIN
        IF v_in_stock < 1 THEN
            RAISE EXCEPTION '沒有庫存';
        END IF;
        UPDATE products SET stock = stock - 1 WHERE id = p_order_id;
        -- ... 其他操作
    EXCEPTION
        WHEN OTHERS THEN
            -- 這個區塊的變更都會被回滾！
            INSERT INTO order_logs(order_id, log_message)
                VALUES (p_order_id, '處理錯誤: ' || SQLERRM);
            RAISE NOTICE '訂單處理錯誤: %', SQLERRM;
    END;

    -- 如果沒錯誤，後面的程式會繼續跑
    -- 你可以記錄：訂單處理成功
END;
$$;

• 就算出錯，訂單不會被處理，log 也會寫進 order_logs 表。
• 有錯誤時會觸發內部 savepoint，不會丟掉全部上下文。

程序優化與穩定的重點守則

1. 查詢時記得用 index。
2. 大操作要拆成小批次（batch），分段處理。
3. 要會記錄錯誤 — 建一張專門記錄大量操作錯誤的表。
4. 「部分」回滾只能用巢狀 EXCEPTION 區塊。
5. 不要在 PL/pgSQL 裡用 ROLLBACK TO SAVEPOINT — 會 syntax error。
6. 只有在 autocommit 連線模式下，程序裡才用 COMMIT/SAVEPOINT！
7. 重的查詢先在程序外用 EXPLAIN ANALYZE 看計畫再整合進來。