Batch Insert/Delete

사용자에게 적게는 수 개에서 많게는 수만 개의 데이터를 받아 일괄적으로 처리하는 CRUD API를 개발한 경험이 있다.

 

처음으로 다량의 데이터를 받아 처리해야 하는 기능을 맡게 되었었다. 그래서 경험이 없어 고민을 했던 게 있다. 그건 바로 "어떻게 하면 보다 빠르게 CRUD가 완료될까"에 대한 고민이었다.

 

우선 로직은 아래와 같았다.

• 사용자에게 데이터 리스트를 입력받는다.
• 데이터를 하나하나 검증해보며 양식에 맞지 않거나 잘못 들어온 데이터를 식별한다.
• 검증된 정상적인 데이터만 골라내 DB에 Upsert한다.
• 검증 실패 데이터와, 검증 성공 및 db 정상 입력 데이터를 각각의 리스트에 담아 return한다.

JAVA 내부에서는 어쨌든 전달받은 데이터를 개별적으로 검증하는 작업을 해야 하므로 속도를 극대화시킬 수 있는 방법이 보이지 않았다. 그래서 대신 실제로 DB에 입출력 되는 과정에 집중했다.

 

그렇게 고민을 하던 중 한 번에 여러 데이터를 넣는 BATCH INSERT를 하면 얼마나 빨라질까? 하는 고민을 했다. 그 때 했던 고민과 테스트 결과를 1년이 지난 이제서야 다시 생각나서 한 번 글을 적어본다.

*사실 면접을 위해 진행했던 프로젝트 복기를 하다가 생각이 났다.

 

데이터에 대한 CRUD에서 CD, 즉 INSERT와 DELETE의 경우 한 번에 다음과 같이 여러 개의 데이터를 삽입 혹은 삭제할 수 있다.

*update도 case를 사용하면 가능은 하다. 하지만 쿼리가 과하게 복잡해지므로 패스.

 

INSERT

INSERT INTO user VALUES
-- 이름, 나이, 생성일 순
('a', 29, now()), 
('b', 30, now()),
('c', 31, now());

DELETE

DELETE FROM user 
WHERE ID IN ('a', 'b', 'c');

 

INSERT의 경우에는 하나씩 INSERT하는 것보다 위와 같이 Batch로 insert하는 것이 빠르다는 것은 이미 알고있는 사실이다. 그런데 빠르다는 것은 알고는 있는데, "왜" 빠른 것인지는 모른다.

 

그리고 DELETE의 경우는 하나씩 삭제하는 것이 빠른지, IN에 조건을 넣어 삭제하는 것이 빠른지 알지 못한다.

 

그래서 위의 두 가지 사항을 알아볼까 한다.

 

INSERT

1,000개의 데이터에 대한 INSERT를 배치, 개별로 하기 위한 쿼리문 두 개를 생성했다. 위의 쿼리를 사용해 복사-붙여넣기했으며, PK는 해제해놓았다.

batch

개별

눈에 확 띄는 속도 차이를 보였다. 툴을 사용한거라 쿼리 하나하나가 출력되는 것을 감안하더라도 꽤나 속도에 차이가 있다는 것을 예측할 수 있었다.

 

어쨌든 배치가 훨씬 빠르다는 사실은 확인했으니, 이유를 알아보자. 요즘 나의 최애 장난감 챗지피티에게 물어보았다.

챗지피티가 내놓은 답변은 아래와 같았다.

• 개별적으로 INSERT하면 그만큼 네트워크를 통해 데이터가 전송되므로 네트워크 지연이 클 수 있다.
• BATCH INSERT 시 하나의 트랜잭션 내에서 여러 행을 INSERT 할 수 있어 트랜잭션 오버헤드가 줄어든다.
• SQL INSERT 문장은 데이터베이스에서 파싱 및 컴파일이 필요한데, BATCH 방식이 한 번만 파싱/컴파일 하면 되므로 오버헤드가 줄어든다.
• 개별 삽입은 데이터의 수만큼 디스크 I/O가 동작하는데 반해 BATCH 삽입은 한 번의 디스크 I/O만 발생되므로 효율성이 크게 증가한다.

결국에는 여러 데이터를 넣어야 한다면, BATCH 방식으로 한 번에 여러 행을 넣어버리는 것이 효율적이다.

 

그렇다면 BATCH를 사용하는 방법이 무조건 좋은가? 그건 아니다. 주의해야할 사항들이 분명 존재한다.

• DB I/O 를 위해 데이터가 메모리에 적재되므로, 데이터양이 과도하게 많을 경우 메모리 부족 문제가 발생할 수 있다.
• 데이터 중 하나의 데이터에만 오류가 존재해도 전체 배치가 실패할 수 있다.

 

DELETE

다음은 DELETE를 알아보자.

아래 두 개의 쿼리를 각각 실행시켜보았다. 참고로 1,000개로는 눈에 띄는 속도차이가 나지 않아 10,000개의 데이터로 실험을 진행했다.

DELETE FROM user WHERE id IN ('a', 'b');

DELETE FROM user WHERE id = 'a';
DELETE FROM user WHERE id = 'b';

 

그렇게 심한 차이는 아니지만, 어쨌든 아주 약간의 시간 차이가 발생했다.

 

이 또한 왜 그런지 챗지피티에게 물어보았다.

• SQL 파싱 및 컴파일 과정이 IN 사용 시 한 번만 발생한다.
• IN 사용 시 네트워크를 통해 한 번의 전송만이 이루어진다.
• IN 사용 시 하나의 트랜잭션으로 DELETE를 처리할 수 있다.
• IN 사용 시 한 번의 디스크 I/O가 발생한다.

결국에는 BATCH INSERT와 동일한 이유였다.

 

 

옛날에 과장님께서 지나가는 말로 "DB는 한 번이라도 덜 접근하는게 좋아요" 라고 말씀하신 적이 있다. 이제서야 그 의미를 확실하게 깨달으며, 오늘의 포스팅을 완료한다.