설치·접속: PostgreSQL 설치와 접속
부제: 매일 벤더가 주는 재고 파일을 본 테이블에 반영하는데, 신규는 INSERT·변경은 UPDATE·재고 0은 DELETE를 한 번에 처리하고 대량이면 트랜잭션이 몇 시간 잠길 때
매일 벤더 재고 스냅샷을 받아 재고 테이블과 동기화한다. 앱에서 파일을 파싱해 행마다 조회하고 분기 INSERT/UPDATE/DELETE를 돌리던 루프를, 세 가지 SQL 기능으로 갈아엎는다. 파일 적재는 COPY FROM PROGRAM, 다분기 반영은 MERGE, 대량일 때 롱 트랜잭션 회피는 PROCEDURE 청크 커밋이다.
1단계 — COPY FROM PROGRAM으로 스테이징 적재 (HEADER MATCH로 컬럼 검증)
COPY ... FROM PROGRAM은 서버가 셸 명령을 실행해 그 출력을 파이프로 직접 읽는다. 앱에서 압축 풀고 파싱해 INSERT 루프 도는 과정이 통째로 사라진다. HEADER MATCH는 파일 헤더가 테이블 컬럼명과 다르면 즉시 에러를 내 "컬럼 밀림" 사고를 막는다. (실무에선 'zcat /data/sales.csv.gz' 같은 명령을 쓰지만, 여기서는 재현을 위해 printf로 CSV를 만들어 넣는다.)
CREATE TABLE target (winename text PRIMARY KEY, stock int);
CREATE TABLE staging (winename text, stock int);
INSERT INTO target VALUES
('Chateau Latour', 12), ('Opus One', 8), ('Sassicaia', 0);
COPY staging FROM PROGRAM
'printf "winename,stock\nChateau Latour,15\nOpus One,0\nPenfolds Grange,20\n"'
WITH (FORMAT csv, HEADER MATCH);
SELECT * FROM staging ORDER BY winename;COPY 3
winename | stock
-----------------+-------
Chateau Latour | 15
Opus One | 0
Penfolds Grange | 20HEADER MATCH가 헤더 winename,stock을 테이블 컬럼과 대조해 통과시켰다. 헤더 순서가 뒤바뀐 파일이 오면 여기서 바로 실패해 조용한 데이터 오염을 차단한다.
2단계 — MERGE 다분기로 본 테이블에 반영
스테이징의 스냅샷을 본 테이블에 한 문장으로 반영한다. 매칭 안 되고 재고가 있으면 신규 INSERT, 재고 0이면 DELETE, 그 외는 UPDATE. ON CONFLICT로는 DELETE 분기를 표현할 수 없어 앱 루프를 돌던 걸 MERGE 하나로 대체한다.
MERGE INTO target t
USING staging s ON t.winename = s.winename
WHEN NOT MATCHED AND s.stock > 0 THEN
INSERT (winename, stock) VALUES (s.winename, s.stock)
WHEN MATCHED AND s.stock = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET stock = s.stock;
SELECT * FROM target ORDER BY winename;MERGE 3
winename | stock
-----------------+-------
Chateau Latour | 15
Penfolds Grange | 20
Sassicaia | 0한 문장에서 세 갈래가 다 돌았다. Chateau Latour는 12→15 UPDATE, Opus One은 재고 0이라 DELETE, Penfolds Grange는 신규 INSERT. 스테이징에 안 온 Sassicaia는 손대지 않았다.
3단계 — 대량이면 PROCEDURE 루프에서 청크 커밋
스냅샷이 수천만 건이면 한 트랜잭션 MERGE는 롱 트랜잭션·락·WAL 폭증을 부른다. FUNCTION은 호출자 트랜잭션 안에서 원자적으로 돌아 중간 커밋이 불가능하지만, PROCEDURE는 본문에서 COMMIT을 실행할 수 있다. 청크마다 커밋하며 진행률을 남긴다.
CREATE PROCEDURE sync_chunked(chunk int) LANGUAGE plpgsql AS $$
DECLARE n int; total int := 0;
BEGIN
LOOP
UPDATE dst d SET val = s.val, done = true
FROM src s
WHERE d.id = s.id AND d.done = false
AND d.id IN (SELECT id FROM dst WHERE done = false ORDER BY id LIMIT chunk);
GET DIAGNOSTICS n = ROW_COUNT;
EXIT WHEN n = 0;
total := total + n;
COMMIT; -- 청크 단위 커밋
RAISE NOTICE 'committed chunk of % rows (total %)', n, total;
END LOOP;
END $$;
CALL sync_chunked(5000); -- src/dst 각 25000행NOTICE: committed chunk of 5000 rows (total 5000)
NOTICE: committed chunk of 5000 rows (total 10000)
NOTICE: committed chunk of 5000 rows (total 15000)
NOTICE: committed chunk of 5000 rows (total 20000)
NOTICE: committed chunk of 5000 rows (total 25000)
CALL
synced | total
--------+-------
25000 | 250002.5만 건을 5천 건씩 다섯 청크로 나눠 각각 커밋했다. 중간에 죽어도 이미 커밋된 청크는 살아 있고, done = false 조건 덕에 재실행하면 남은 것부터 이어서 처리한다.
결론 — 언제 이 조합인가
외부 파일을 주기적으로 받아 본 테이블과 동기화하는 배치 ETL의 정석이다. 적재(COPY FROM PROGRAM + HEADER MATCH)로 파싱 코드와 컬럼 밀림을 없애고, 반영(MERGE)으로 다분기 upsert/delete를 한 문장에 담고, 규모가 커지면 PROCEDURE 청크 커밋으로 롱 트랜잭션을 피한다. 데이터가 작아 한 트랜잭션으로 충분하면 3단계는 생략하고 MERGE 한 방으로 끝내면 된다.
이렇게도 쓴다
실전에서는 gzip CSV를 서버가 직접 스트리밍한다.
COPY staging FROM PROGRAM 'zcat /data/sales_20260718.csv.gz'
WITH (FORMAT csv, HEADER MATCH, FORCE_NULL(discount));
MERGE에 DO NOTHING 분기를 둬 변경 없는 행은 건드리지 않는다. (조합: MERGE DO NOTHING)
MERGE INTO t t USING s s ON t.k = s.k
WHEN MATCHED THEN DO NOTHING
WHEN NOT MATCHED THEN INSERT VALUES (s.k, s.v);
MERGE를 청크 프로시저 안에 넣어 대량 upsert를 나눠 커밋한다. (조합: MERGE + PROCEDURE)
LOOP
MERGE INTO target t USING (SELECT * FROM staging LIMIT 10000) s
ON t.winename = s.winename
WHEN MATCHED THEN UPDATE SET stock = s.stock
WHEN NOT MATCHED THEN INSERT VALUES (s.winename, s.stock);
EXIT WHEN NOT FOUND;
COMMIT;
END LOOP;
FORCE_NULL로 따옴표 빈 문자열("")을 진짜 NULL로 적재한다.
COPY staging FROM PROGRAM 'cat /data/f.csv'
WITH (FORMAT csv, HEADER MATCH, FORCE_NULL(stock));
적재 후 스테이징을 TRUNCATE해 다음 배치를 깨끗이 받는다. (조합: 스테이징 재활용)
TRUNCATE staging;