안 끝나던 배치 작업이 16초만에? 실화인가요?

이 이야기는 실제 경험을 바탕으로 각색한 내용입니다.

한 고객사에서 ORACLE로 운영하던 시스템을 PostgreSQL로 전환했습니다. 그리고, 오픈 다음날 아침 일찍 전화가 왔습니다. “배치가 안 끝나요.” 부랴부랴 기차를 타고 고객사에 내려가 배치 SQL을 확인했습니다. ORACLE에서는 20, 30분 내로 끝나던 SQL이 PostgreSQL에서는 한 시간이 지나도 끝나지 않아 강제로 종료한 상황이었습니다. 개발자에 의하면, ORACLE에 있던 프로시저를 그대로 문법만 수정해 PostgreSQL로 가져왔다고 합니다.

배치 프로시저를 뜯어보니, 비효율적인 LOOP 방식 배치였습니다. SELECT SQL로 대상을 CURSOR에 담은 후에, 루프를 돌면서, 한 레코드씩 다른 테이블에 UPSERT(UPDATE+INSERT) 하는 방식입니다. 이러한 처리 방식에 가장 좋은 해결책은 한방 쿼리입니다. 루프가 아닌, 한 문장의 SQL로 UPSERT 처리하는 것입니다. 다행히도 해당 SQL은 많이 복잡하지 않아 한방 쿼리로 변경하는 것이 어렵지 않았습니다. Stage 환경에서 테스트해보니, 기존 방식으로 한 시간이 넘어도 안 끝나던 배치가, 한방 쿼리로 변경하자 16초만에 끝나는 것을 확인했습니다.

모든 배치를 한방 쿼리로 개발할 수 있는 것은 아닙니다. 배치 처리 과정에 매우 복잡한 로직이 여러개 존재한다면 한방 쿼리가 불가능할 수 있습니다. 고급 인력을 투입해 한방 쿼리로 변경하고 테스트를 충분히 진행해도, 데이터 정확성에 대한 우려 탓에 기존 방식을 그대로 유지하는 경우가 많습니다.

한방 쿼리로 ‘급한 불’은 껐지만, 남은 배치도 모두 비슷한 구조였습니다. 다시 코드를 자세히 들여다보니, 루프 안에서 레코드 한 건을 처리할 때마다 COMMIT을 호출하고 있더군요. 처리할 데이터가 50만 건이면, COMMIT도 50만 번 실행되는 구조인 것이죠.

COMMIT은 간단한 작업이 아닙니다. PostgreSQL에서는 COMMIT이 실행되면, 해당 세션 본인이

WAL 버퍼(메모리에 임시로 쌓아 둔 변경 기록)를 WAL 파일에 write하고,

바로 이어서 fsync(디스크까지 안전하게 기록됐는지 확인)까지 처리합니다.

위와 같은 디스크 I/O가 발생하는 작업이 레코드마다 반복되니 전체 배치 시간이 눈덩이처럼 불어난 것입니다.

왜 ORACLE에서는 20~30분에 끝났을까?
ORACLE은 LGWR(Log Writer)라는 전용 프로세서가 여러 세션의 COMMIT 요청을 모아 한꺼번에 로그를 쓰고 동기화한다.

결과적으로 배치 프로시저를 한방 쿼리로 변경하지 않고, COMMIT 위치만 루프 바깥으로 옮겨도 제법 빨리 배치가 종료되었습니다. (시간을 정확히 기억하진 못하지만, 1분 초반대였던 것 같습니다.)

이 해법을 보고, ‘COMMIT은 무조건 한 번에’라고 확정 지어버리면 매우 곤란합니다. 매우 많은 데이터의 변경을 한 번에 COMMIT 처리하는 것은 또 다른 부하를 가져올 수 있기 때문입니다. 각자의 환경에서 테스트와 모니터링을 통해 적절한 해답을 찾아가야 합니다.

Local 환경에서 약 50만건 정도로 유사한 테스트 결과를 아래와 같이 공유합니다.

LOOP 안에서 COMMIT	10분 4 초
LOOP 바깥에서 한 번만 COMMIT	8.4 초
한방 SQL	4.07 초

PostgreSQL 테스트 Script

아래는 StartUP Tuning For PostgreSQL 과정에서 구축된 테이블을 활용한 테스트 스크립트입니다. 참고하기 바랍니다.

-- SELECT 과정의 성능을 위해 아래 인덱스가 없다면 추가해야 한다.
CREATE INDEX tr_ord_big_x01 ON startdbpg.tr_ord_big USING btree (ord_dtm)
SQL
복사

-- 아래와 같이 일별 집계 테이블을 만들어, 해당 테이블에 배치 UPSERT를 수행한다.
-- PostgreSQL
CREATE TABLE startdbpg.tr_ord_big_dt
(     ord_dt         TIMESTAMP WITHOUT TIME ZONE     NOT NULL
      ,mbr_id        VARCHAR   (40)                  NOT NULL 
      ,shop_id       VARCHAR   (40)                  NOT NULL 
      ,ord_st        VARCHAR   (40)                  NOT NULL  
      ,pay_tp        VARCHAR   (40)                  NOT NULL
      ,ord_amt       DECIMAL   (18,3)                NULL 
      );
      
ALTER TABLE startdbpg.tr_ord_big_dt ADD CONSTRAINT tr_ord_big_dt_pk PRIMARY KEY(ord_dt,mbr_id,shop_id,ord_st,pay_tp);
SQL
복사

-- LOOP 안에서 COMMIT 처리하는 배치 프로지저(BAD CASE)
CREATE OR REPLACE PROCEDURE startdbpg.usp_bat_tr_ord_big_dt
    (
        IN p_ym VARCHAR(6)
    )
    LANGUAGE plpgsql
    AS $proc$
    DECLARE
        v_fr_dt DATE;
        v_to_dt DATE;
        cur_ord RECORD;
    BEGIN
        SELECT  TO_DATE(p_ym || '01','YYYYMMDD') fr_dt
                ,(TO_DATE(p_ym || '01', 'YYYYMMDD') + interval '1 month') to_dt
        INTO    v_fr_Dt
                ,v_to_dt;

        FOR cur_ord IN
            SELECT  DATE_TRUNC('day',t1.ord_dtm)::date ord_dt
                    ,t1.shop_id
                    ,t1.mbr_id
                    ,t1.ord_st
                    ,t1.pay_tp
                    ,SUM(t1.ord_amt) ord_amt
            FROM    startdbpg.tr_ord_big t1
            WHERE   t1.ord_dtm >= '20250101' -- v_fr_dt
            AND     t1.ord_dtm <  '20250201' -- v_to_dt
            GROUP BY DATE_TRUNC('day',t1.ord_dtm)::date
                    ,t1.shop_id
                    ,t1.mbr_id
                    ,t1.ord_st
                    ,t1.pay_tp
        LOOP
            BEGIN
                MERGE INTO startdbpg.tr_ord_big_dt t1
                USING (
                        SELECT  cur_ord.ord_dt
                                ,cur_ord.shop_id
                                ,cur_ord.mbr_id
                                ,cur_ord.ord_st
                                ,cur_ord.pay_tp
                                ,cur_ord.ord_amt
                        ) t2
                    ON (t1.ord_dt = t2.ord_dt
                        AND t1.shop_id = t2.shop_id
                        AND t1.mbr_id = t2.mbr_id
                        AND t1.ord_st = t2.ord_st
                        AND t1.pay_tp = t2.pay_tp)
                WHEN MATCHED THEN UPDATE
                        SET ord_amt = t2.ord_amt
                WHEN NOT MATCHED THEN INSERT
                        (ord_dt    ,shop_id    ,mbr_id    ,ord_st    ,pay_tp    ,ord_amt)
                 VALUES (t2.ord_dt ,t2.shop_id ,t2.mbr_id ,t2.ord_st ,t2.pay_tp ,t2.ord_amt);
            EXCEPTION
                WHEN OTHERS THEN
                    RAISE EXCEPTION '-- > %, %', sqlstate,sqlerrm;
            END;
            COMMIT;
        END LOOP;
    END;
    $proc$;
SQL
복사

-- LOOP 바깥에서 한번만 COMMIT 처리하는 배치 프로지저(NORMAL CASE)
     CREATE OR REPLACE PROCEDURE startdbpg.usp_bat_tr_ord_big_dt_one_commit
    (
        IN p_ym VARCHAR(6)
    )
    LANGUAGE plpgsql
    AS $proc$
    DECLARE
        v_fr_dt DATE;
        v_to_dt DATE;
        cur_ord RECORD;
    BEGIN
        SELECT  TO_DATE(p_ym || '01','YYYYMMDD') fr_dt
                ,(TO_DATE(p_ym || '01', 'YYYYMMDD') + interval '1 month') to_dt
        INTO    v_fr_Dt
                ,v_to_dt;

        FOR cur_ord IN
            SELECT  DATE_TRUNC('day',t1.ord_dtm)::date ord_dt
                    ,t1.shop_id
                    ,t1.mbr_id
                    ,t1.ord_st
                    ,t1.pay_tp
                    ,SUM(t1.ord_amt) ord_amt
            FROM    startdbpg.tr_ord_big t1
            WHERE   t1.ord_dtm >= '20250101' -- v_fr_dt
            AND     t1.ord_dtm <  '20250201' -- v_to_dt
            GROUP BY DATE_TRUNC('day',t1.ord_dtm)::date
                    ,t1.shop_id
                    ,t1.mbr_id
                    ,t1.ord_st
                    ,t1.pay_tp
        LOOP
            BEGIN
                MERGE INTO startdbpg.tr_ord_big_dt t1
                USING (
                        SELECT  cur_ord.ord_dt
                                ,cur_ord.shop_id
                                ,cur_ord.mbr_id
                                ,cur_ord.ord_st
                                ,cur_ord.pay_tp
                                ,cur_ord.ord_amt
                        ) t2
                    ON (t1.ord_dt = t2.ord_dt
                        AND t1.shop_id = t2.shop_id
                        AND t1.mbr_id = t2.mbr_id
                        AND t1.ord_st = t2.ord_st
                        AND t1.pay_tp = t2.pay_tp)
                WHEN MATCHED THEN UPDATE
                        SET ord_amt = t2.ord_amt
                WHEN NOT MATCHED THEN INSERT
                        (ord_dt    ,shop_id    ,mbr_id    ,ord_st    ,pay_tp    ,ord_amt)
                 VALUES (t2.ord_dt ,t2.shop_id ,t2.mbr_id ,t2.ord_st ,t2.pay_tp ,t2.ord_amt);
            EXCEPTION
                WHEN OTHERS THEN
                    RAISE EXCEPTION '-- > %, %', sqlstate,sqlerrm;
            END;
        END LOOP;
        COMMIT;
    END;
    $proc$;
SQL
복사

-- 한방 쿼리 배치 프로시저(GOOD CASE)

    CREATE OR REPLACE PROCEDURE startdbpg.usp_bat_tr_ord_big_dt_one_sql
    (
        IN p_ym VARCHAR(6)
    )
    LANGUAGE plpgsql
    AS $proc$
    DECLARE
        v_fr_dt DATE;
        v_to_dt DATE;
        cur_ord RECORD;
    BEGIN

        BEGIN
            MERGE INTO startdbpg.tr_ord_big_dt t1
            USING (
                    SELECT  DATE_TRUNC('day',t1.ord_dtm)::date ord_dt
                            ,t1.shop_id
                            ,t1.mbr_id
                            ,t1.ord_st
                            ,t1.pay_tp
                            ,SUM(t1.ord_amt) ord_amt
                    FROM    startdbpg.tr_ord_big t1
                    WHERE   t1.ord_dtm >= '20250101' -- v_fr_dt
                    AND     t1.ord_dtm <  '20250201' -- v_to_dt
                    GROUP BY DATE_TRUNC('day',t1.ord_dtm)::date
                            ,t1.shop_id
                            ,t1.mbr_id
                            ,t1.ord_st
                            ,t1.pay_tp
                    ) t2
                ON (t1.ord_dt = t2.ord_dt
                    AND t1.shop_id = t2.shop_id
                    AND t1.mbr_id = t2.mbr_id
                    AND t1.ord_st = t2.ord_st
                    AND t1.pay_tp = t2.pay_tp)
            WHEN MATCHED THEN UPDATE
                    SET ord_amt = t2.ord_amt
            WHEN NOT MATCHED THEN INSERT
                    (ord_dt    ,shop_id    ,mbr_id    ,ord_st    ,pay_tp    ,ord_amt)
             VALUES (t2.ord_dt ,t2.shop_id ,t2.mbr_id ,t2.ord_st ,t2.pay_tp ,t2.ord_amt);
        EXCEPTION
            WHEN OTHERS THEN
                RAISE EXCEPTION '-- > %, %', sqlstate,sqlerrm;
        END;

        COMMIT; 
    END;
    $proc$;
SQL
복사

각 프로시저별 테스트 결과는 다음과 같다.

-- PostgreSQL 509,567 건 MERGE
CALL startdbpg.usp_bat_tr_ord_big_dt('202501'); -- 10m 4s (루프안에서 COMMIT)
CALL startdbpg.usp_bat_tr_ord_big_dt_one_commit('202501'); -- 8.499 s
CALL startdbpg.usp_bat_tr_ord_big_dt_one_sql('202501'); -- 4.075 s
SQL
복사

준비한 내용은 여기까지입니다.

데이터 활용이 필요한 모든 분을 위한 SQL 실무서를 소개합니다.

•

SQL 입문자를 위한 실습환경 구성과 SELECT 기초부터

•

SQL 중급자를 위한 GROUP BY와 JOIN, 그리고 분석함수까지!

•

그림과 친절한 설명, 손끝으로 익히는 실습 SQL과 실무형 퀴즈를 더한 올인원 SQL 실무서

책 정보 링크>>>

SQL STARTER For MySQLSQL STARTER For MySQL