CodeGym /행동 /SQL SELF /트랜잭션을 고려한 프로시저 최적화: 성능 분석과 롤백

트랜잭션을 고려한 프로시저 최적화: 성능 분석과 롤백

SQL SELF

레벨 54, 레슨 3

사용 가능

프로시저를 만들다 보면 얘네가 종종 데이터베이스의 "심장"이 되어서 여러 작업을 처리하게 돼. 근데 이런 프로시저가 "병목"이 되기도 쉬워, 특히 아래 같은 경우에:

쓸데없는 작업을 많이 함 (예: 똑같은 데이터에 자주 접근함).
인덱스를 제대로 안 씀.
한 트랜잭션 안에서 너무 많은 작업을 처리함.

어떤 똑똑한 개발자가 이런 말을 했어: "엉망으로 짠 코드를 빠르게 만들라는 건 게으른 친구한테 빨리 뛰라고 하는 거랑 똑같다". 그래서 프로시저 최적화는 단순히 속도만 올리는 게 아니라, 근본부터 개선하는 거야!

트랜잭션 안의 작업 수 최소화하기

PostgreSQL에서 트랜잭션 하나마다 작업을 관리하는 오버헤드가 생겨. 트랜잭션이 클수록 락을 오래 잡고, 다른 유저들도 더 많이 막힐 수 있어. 이런 문제를 줄이려면:

한 트랜잭션에 너무 많은 작업을 몰아넣지 마.
EXCEPTION END를 써서 변경 범위를 로컬하게 제한해. 일부 작업만 롤백이 필요할 때 유용해.
큰 트랜잭션은 여러 개의 작은 트랜잭션으로 쪼개 (앱 로직이 허락하면).

예시: 대량 데이터 insert를 "배치"로 나눠서 처리하기:

-- 예시: 단계별 커밋으로 배치 로딩하는 프로시저
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개마다 커밋
            batch_cnt := 0;
        END IF;
    END LOOP;
    COMMIT; -- 마지막 커밋
END;
$$;

꿀팁: COMMIT마다 변경사항이 확정되니까, 트랜잭션을 쪼갤 때 데이터 일관성이 깨지지 않는지 꼭 확인해!

인덱스로 쿼리 속도 올리기

예를 들어 orders 테이블에 백만 개의 데이터가 있고, customer_id로 자주 쿼리한다고 해보자. 인덱스가 없으면 전체 테이블을 다 뒤져야 해:

CREATE INDEX idx_customer_id ON orders(customer_id);

이제 이런 쿼리들은:

SELECT * FROM orders WHERE customer_id = 42;

훨씬 빨라지고, 테이블 전체 스캔을 피할 수 있어.

중요: 프로시저 만들 때 필터, 정렬, 조인에 쓰는 컬럼이 인덱스에 포함돼 있는지 꼭 확인해!

`EXPLAIN ANALYZE`로 성능 분석하기

EXPLAIN은 쿼리 실행 계획(즉, PostgreSQL이 쿼리를 어떻게 처리할지)을 보여주고, ANALYZE는 실제 실행 통계(예: 실행 시간 등)를 추가해줘. 예시:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

프로시저 안에서 어떻게 써?

프로시저의 복잡한 쿼리를 따로 떼서 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 $$;

분석과 개선 예시

원래 프로시저 (느림):

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