[PostgreSQL] on conflict (upsert) 성능 측정해보기

SuYeong·2023년 10월 1일

안녕하세요.

오늘은 PostgreSQL에서 지원하는 on conflict (upsert 라고도 불립니다) 및 BULK 구문의 성능 측정 결과를 공유드려보겠습니다.

on conflict를 사용하게 된 계기

제가 개발하던 서비스에 새로운 요구사항이 들어왔습니다.

외부의 배치서버가 한번에 1,000건 이상의 데이터를 제 서비스에 전송합니다.

입력으로 들어온 데이터를 DB에 INSERT 혹은 UPDATE를 해줘야 합니다.
단, 데이터만 보고는 INSERT인지 UPDATE인지 알 수 없습니다.

그러면 사용할 수 있는 방법은 다음과 같습니다

사용 가능한 시나리오

개별 INSERT, Duplicated key error가 발생한 데이터들은 UPDATE로 전환.
SELECT후, DB에 없다면 INSERT(or BULK INSERT), DB에 있다면 UPDATE.
on conflict (UPSERT) 사용

시간 측정 대상 쿼리

BULK INSERT
BULK UPSERT
SELECT
개별 INSERT
개별 UPDATE
개별 UPSERT

위 쿼리들의 성능을 측정해보면, 위에서 말씀드린 시나리오 뿐 아니라 여러 시나리오의 성능을 가늠해볼 수 있을 것 같습니다.

BULK UPDATE는 왜 테스트하지 않는지 궁금하실 수 있을 것 같습니다.
UPDATE 문의 형태를 잘 생각해보시면, INSERT처럼 PK별로 다른 값을 지정하기가 어려운 형태입니다.

// UPDATE문
UPDATE table
SET column2 = 3
WHERE column1 = 1

아래처럼 CASE와 IN조합으로 BULK INSERT처럼 BULK UPDATE를 흉내낼 수는 있지만,,,,
성능이 엄청 안좋습니다.

// UPDATE문에 CASE-WHEN-THEN와 IN 적용
UPDATE table
SET column2 = (CASE column1 WHEN 1 THEN 'val1'
                 WHEN 2 THEN 'val2'
                 WHEN 3 THEN 'val3'
         END)
WHERE column1 IN(1, 2 ,3);

CASE WHEN THEN의 성능에 대해 찾아본 바는 다음과 같습니다.

Time to complete a query can be a problem. There is no defined maximum for the number of WHEN conditions you can have within a CASE WHEN statement. CASE WHEN queries can become very slow because the query has to check each condition for every row until it finds a case where it satisfies the condition. This can dramatically increase the query time.
요약 :
CASE WHEN THEN 구문은 모든 경우를 하나하나 비교한다.
따라서 쿼리 실행 속도가 많이 느려질 수 있다.
(참고: https://dataschool.com/how-to-teach-people-sql/how-case-when-works/)

자바의 switch와 달리 별도의 최적화가 없는 것 같습니다.

대충 계산해보더라도, O(N2)이므로 사용하지 않는 것이 좋을 것 같습니다.

테스트 테이블

테이블은 아래와 같습니다

create table users (
id char(20) primary key,
term1 char(1),
term2 char(1),
term3 char(1)
)

해당 id를 가진 user가 특정 동의서에 응답한 결과를 저장하는 테이블이라고 생각해주시면 됩니다.

환경 설명

OS : Ubuntu 22.04.2 LTS (가상머신)
CPU : Ryzen7 5800H 전체 할당
RAM : 16GB 할당

PostgreSQL은 가상머신의 컨테이너에 띄웠습니다.
(컨테이너에 자원 제한은 별도로 걸지 않았습니다)

구조는 위와 같습니다.
PostgreSQL을 VM 외부로 포트포워딩했고, 쿼리를 전송하는 자바 프로그램은 VM외부에서 동작시켰습니다.

측정 항목 및 측정 방법

1. 쿼리 실행시간

쿼리 실행시간은 JDBC의 Statement#execute() 혹은 Statement#executeUpdate 메서드 전 후,
서버시간의 차로 계산합니다.

한번의 테스트는 테스트 쿼리를 전송한 후 2분 휴동, DB 테이블 초기화 후 2분 휴동으로 이루어지며,
총 10번 반복합니다.

2. 자원 사용량

node-exporter, 프로메테우스, 그라파나를 사용하여 DB 서버 CPU사용량과 메모리 사용량을 측정하고 모니터링합니다.

BULK INSERT 테스트

BULK INSERT는 두가지 경우에 대해 실험해보겠습니다.

DB에 값이 없는 경우
DB에 이미 들어있는 값이 있는 경우

DB에 값이 있는경우와 없는 경우를 모두 테스트 하는 것은, 인덱스를 다시 구성하는데에 드는 시간 차이를 확인하기 위함입니다.

1. DB에 값이 없는 경우 BULK INSERT

21:07:49.888 [main] INFO reactive.reactivestreams.TestMain -- i : 0
21:07:52.105 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:08:12.363 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 21993
21:12:13.005 [main] INFO reactive.reactivestreams.TestMain -- i : 1
21:12:13.821 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:12:32.602 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 19584
21:16:33.096 [main] INFO reactive.reactivestreams.TestMain -- i : 2
21:16:33.867 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:16:52.839 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 19733
21:20:53.441 [main] INFO reactive.reactivestreams.TestMain -- i : 3
21:20:54.235 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:21:12.656 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 19203
21:25:13.247 [main] INFO reactive.reactivestreams.TestMain -- i : 4
21:25:14.053 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:25:32.339 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 19082
21:29:32.819 [main] INFO reactive.reactivestreams.TestMain -- i : 5
21:29:33.583 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:29:52.323 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 19494
21:33:52.957 [main] INFO reactive.reactivestreams.TestMain -- i : 6
21:33:53.771 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:34:12.275 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 19308
21:38:12.877 [main] INFO reactive.reactivestreams.TestMain -- i : 7
21:38:13.661 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:38:32.794 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 19908
21:42:33.336 [main] INFO reactive.reactivestreams.TestMain -- i : 8
21:42:34.136 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:42:52.654 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 19307
21:46:53.200 [main] INFO reactive.reactivestreams.TestMain -- i : 9
21:46:53.980 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:47:11.941 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 18731

전송된 쿼리가 실행 완료되는데에 걸리는 시간은 대략 19초 정도입니다.

CPU사용량은 5%정도 증가했습니다.

메모리 사용량은 7~8GB정도 증가했습니다.
캐시나 버퍼메모리의 사용량에 변화는 없었습니다.

CPU 점유 시간과 메모리 점유 시간은 쿼리의 실행 시간과 거의 동일했습니다.

2. DB에 값이 있는 경우 BULK INSERT

이번에는 DB에 데이터가 1,000,000개 있는 상태에서 1,000,000개를 입력해보겠습니다.

21:14:31.710 [main] INFO reactive.reactivestreams.TestMain -- i : 0
21:14:33.998 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:15:35.306 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 63209 // 초기 세팅을 위한 INSERT문 실행
21:19:35.334 [main] INFO reactive.reactivestreams.TestMain -- i : 0
21:19:36.065 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:19:55.134 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 19788
21:23:55.779 [main] INFO reactive.reactivestreams.TestMain -- i : 1
21:23:56.587 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:24:16.445 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 20655
21:28:17.031 [main] INFO reactive.reactivestreams.TestMain -- i : 2
21:28:17.740 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:28:37.478 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 20433
21:32:38.124 [main] INFO reactive.reactivestreams.TestMain -- i : 3
21:32:38.857 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:32:57.541 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 19407
21:36:58.189 [main] INFO reactive.reactivestreams.TestMain -- i : 4
21:36:58.954 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:37:17.183 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 18942
21:41:17.835 [main] INFO reactive.reactivestreams.TestMain -- i : 5
21:41:18.577 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:41:36.647 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 18802
21:45:37.314 [main] INFO reactive.reactivestreams.TestMain -- i : 6
21:45:38.075 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:45:56.295 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 18970
21:49:56.996 [main] INFO reactive.reactivestreams.TestMain -- i : 7
21:49:57.720 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:50:15.721 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 18715
21:54:16.474 [main] INFO reactive.reactivestreams.TestMain -- i : 8
21:54:17.191 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:54:35.677 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 19192
21:58:36.342 [main] INFO reactive.reactivestreams.TestMain -- i : 9
21:58:37.084 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
21:58:55.325 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 18962

전송된 쿼리가 실행 완료되는데에 걸리는 시간은 대략 19초 정도입니다.

CPU사용량은 대략 7%정도 증가했습니다.

아무것도 없는 상태에서 INSERT하는 것보다는 CPU자원을 약간 더 소모함을 확인할 수 있습니다.

메모리 사용량은 대략 7~8GB정도 증가했습니다.
캐시나 버퍼메모리의 사용량에 변화는 없었습니다.

CPU 점유 시간과 메모리 점유 시간은 쿼리의 실행 시간과 거의 동일했습니다.

Bulk UPSERT

이번에는 DB에 1,000,000개의 데이터가 있는 상태에서,
1,000,000개를 UPSERT해보겠습니다.

500,000개는 기존에 있는것과 충돌하여 UPDATE되도록 했고,
500,000개는 기존에 존재하지 않아, INSERT되도록 했습니다.

00:18:40.756 [main] INFO reactive.reactivestreams.TestMain -- i : 0
00:18:43.072 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
00:19:11.869 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 30625
00:23:21.365 [main] INFO reactive.reactivestreams.TestMain -- i : 1
00:23:22.135 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
00:23:50.510 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 29132
00:27:59.529 [main] INFO reactive.reactivestreams.TestMain -- i : 2
00:28:00.259 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
00:28:28.445 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 28898
00:32:37.657 [main] INFO reactive.reactivestreams.TestMain -- i : 3
00:32:38.424 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
00:33:06.510 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 28842
00:37:15.759 [main] INFO reactive.reactivestreams.TestMain -- i : 4
00:37:16.487 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
00:37:45.454 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 29682
00:41:54.657 [main] INFO reactive.reactivestreams.TestMain -- i : 5
00:41:55.412 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
00:42:23.616 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 28949
00:46:32.422 [main] INFO reactive.reactivestreams.TestMain -- i : 6
00:46:33.215 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
00:47:00.837 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 28406
00:51:09.849 [main] INFO reactive.reactivestreams.TestMain -- i : 7
00:51:10.580 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
00:51:38.511 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 28653
00:55:47.518 [main] INFO reactive.reactivestreams.TestMain -- i : 8
00:55:48.225 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
00:56:16.380 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 28850
01:00:25.467 [main] INFO reactive.reactivestreams.TestMain -- i : 9
01:00:26.220 [main] INFO reactive.reactivestreams.TestMain -- 쿼리 생성 완료
01:00:54.344 [main] INFO reactive.reactivestreams.TestMain -- 시간차이(ms) : 28867

쿼리 실행시간이 BULK INSERT문에 비해 1.5배 정도 증가했습니다.

테이블 초기화를 위해 UPDATE와 DELETE문을 실행하느라 CPU점유율 그래프가 이전 실험 그래프보다 울퉁불퉁한데, 높이가 높은 막대 위주로 봐주시면 됩니다.

CPU사용량은 이전과 같이 7% 정도 늘었습니다.

메모리 사용량은 7~8GB정도 증가했습니다.
캐시나 버퍼메모리의 사용량에 변화는 없었습니다.

SELECT

아래와 같은 쿼리로, 1,000,000개의 데이터 중 500,000개의 데이터를 가져오는 테스트를 해보겠습니다.

SELECT * FROM users WHERE id IN ('TEST0000000', ....)

23:30:28.316 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 0
23:30:30.032 [main] INFO reactive.reactivestreams.bulkquerytest.SqlMaker -- 쿼리 생성 완료
23:30:31.454 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ms) : 2747
23:30:51.499 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 1
23:30:51.915 [main] INFO reactive.reactivestreams.bulkquerytest.SqlMaker -- 쿼리 생성 완료
23:30:53.290 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ms) : 1779
23:31:13.312 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 2
23:31:13.699 [main] INFO reactive.reactivestreams.bulkquerytest.SqlMaker -- 쿼리 생성 완료
23:31:14.961 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ms) : 1640
23:31:34.985 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 3
23:31:35.404 [main] INFO reactive.reactivestreams.bulkquerytest.SqlMaker -- 쿼리 생성 완료
23:31:36.596 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ms) : 1601
23:31:56.626 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 4
23:31:57.085 [main] INFO reactive.reactivestreams.bulkquerytest.SqlMaker -- 쿼리 생성 완료
23:31:58.309 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ms) : 1674
23:32:18.330 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 5
23:32:18.774 [main] INFO reactive.reactivestreams.bulkquerytest.SqlMaker -- 쿼리 생성 완료
23:32:19.980 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ms) : 1641
23:32:40.002 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 6
23:32:40.386 [main] INFO reactive.reactivestreams.bulkquerytest.SqlMaker -- 쿼리 생성 완료
23:32:41.549 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ms) : 1537
23:33:01.567 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 7
23:33:02.187 [main] INFO reactive.reactivestreams.bulkquerytest.SqlMaker -- 쿼리 생성 완료
23:33:03.581 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ms) : 2006
23:33:23.596 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 8
23:33:23.959 [main] INFO reactive.reactivestreams.bulkquerytest.SqlMaker -- 쿼리 생성 완료
23:33:25.135 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ms) : 1530
23:33:45.160 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 9
23:33:45.535 [main] INFO reactive.reactivestreams.bulkquerytest.SqlMaker -- 쿼리 생성 완료
23:33:46.764 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ms) : 1594

대략 1.8초 정도 소요됩니다.

cpu사용량과 메모리 사용량에 큰 변화가 없습니다.

개별 INSERT (1건)

개별 INSERT와 UPDATE는 쿼리 실행시간이 너무 짧아서, ms단위가 아닌 ns단위로 측정해보겠습니다.

23:03:22.883 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 0
23:03:23.242 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 4884900
23:03:25.288 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 1
23:03:25.305 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 2496000
23:03:27.349 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 2
23:03:27.361 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1923000
23:03:29.383 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 3
23:03:29.394 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1610500
23:03:31.419 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 4
23:03:31.440 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 12097500
23:03:33.465 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 5
23:03:33.487 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 11246600
23:03:35.510 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 6
23:03:35.521 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1531400
23:03:37.543 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 7
23:03:37.564 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 11725500
23:03:39.602 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 8
23:03:39.622 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 11217600
23:03:41.651 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 9
23:03:41.665 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1869500

Process finished with exit code 0

cpu와 메모리 사용량에 큰 변화는 없습니다.

개별 UPDATE (1건)

23:00:33.352 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 0
23:00:33.728 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 16953800
23:00:35.779 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 1
23:00:35.792 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1481400
23:00:37.832 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 2
23:00:37.843 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1792700
23:00:39.877 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 3
23:00:39.890 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1684000
23:00:41.906 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 4
23:00:41.935 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 2715300
23:00:43.954 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 5
23:00:43.965 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1692700
23:00:46.005 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 6
23:00:46.017 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1702400
23:00:48.055 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 7
23:00:48.068 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 2210400
23:00:50.084 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 8
23:00:50.095 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1537300
23:00:52.124 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 9
23:00:52.134 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1395300

cpu와 메모리 사용량에 큰 변화는 없습니다.

개별 UPSERT(1건, INSERT)

단건 UPSERT입니다.
이번 UPSERT의 결과는 INSERT 입니다.

00:00:18.524 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 0
00:00:18.888 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 4274500
00:00:20.923 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 1
00:00:20.937 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1823700
00:00:22.963 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 2
00:00:22.974 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1079100
00:00:24.995 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 3
00:00:25.006 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1415900
00:00:27.023 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 4
00:00:27.033 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1061400
00:00:29.066 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 5
00:00:29.077 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1099700
00:00:31.097 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 6
00:00:31.119 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1235500
00:00:33.145 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 7
00:00:33.155 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1289600
00:00:35.178 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 8
00:00:35.192 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1383400
00:00:37.207 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 9
00:00:37.219 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1697600

개별 UPSERT(1건, UPDATE)

단건 UPSERT입니다.
이번 UPSERT의 결과는 UPDATE 입니다.

00:12:41.705 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 0
00:12:42.093 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 17452400
00:12:44.153 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 1
00:12:44.167 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1659400
00:12:46.199 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 2
00:12:46.210 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1455100
00:12:48.238 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 3
00:12:48.247 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1326000
00:12:50.268 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 4
00:12:50.278 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1187500
00:12:52.305 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 5
00:12:52.315 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1180100
00:12:54.342 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 6
00:12:54.353 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1224200
00:12:56.380 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 7
00:12:56.391 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1438800
00:12:58.413 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 8
00:12:58.423 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1283600
00:13:00.451 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- i : 9
00:13:00.465 [main] INFO reactive.reactivestreams.bulkquerytest.BulkTestMain -- 시간차이(ns) : 1414900

cpu와 메모리 사용량에 큰 변화는 없습니다.

실험 결과 정리

1. BULK와 단건 쿼리의 실행 완료 시간 차이

단건 쿼리는 건당 1ms ~ 1.5ms가 소요되었습니다.
BULK 쿼리 실험처럼 1,000,000개의 데이터에 대해 실행한다면, 1,000,000ms가 소요되는 것입니다.
BULK로 1,000,000건에 대한 쿼리를 실행시킨 결과인 20,000ms ~ 30,000ms과는 큰 차이가 존재합니다.

시간 측면에서, BULK 승!

2. BULK와 단건 쿼리의 자원(CPU, GPU) 사용량 차이

BULK 쿼리가 실행 시간이 빠르지만 CPU사용량와 메모리 사용량이 제 기대보다 훨씬 높게 측정되었습니다.
(100,000건에서는 자원 사용량이 20%정도로 감소했습니다. 해당 실험 결과는 첨부되어있지 않습니다.)

단건 쿼리는 메모리 사용량이 특히 적었습니다.
같은 1,000,000건을 입력할 때, CPU사용량은 BULK와 비슷했지만 메모리 사용이 거의 증가하지 않았습니다.

다만, BULK보다 1,000,000건에 대해 쿼리 실행 완료 시간이 오래 걸리기때문에, CPU 사용량이 높아진채로 오래 유지되었습니다.
(해당 실험 결과는 첨부되어있지 않습니다.)

메모리 측면에서, 단건 쿼리 승!

3. BULK UPSERT의 실행 시간 변화

BULK UPSERT에서 충돌하는 데이터가 많아질수록, 실행시간은 길어졌습니다. 최대로는 BULK INSERT의 두배 가까이 늘어났습니다.

어찌보면 당연한 결과입니다.

on conflict는 우선 INSERT를 실행해보고, 이미 존재하는 데이터라면 UPDATE문을 실행하는 것이므로 사실상 쿼리가 두 번 실행되는 것입니다.

따라서 UPSERT를 사용할 때, UPDATE 대상 데이터의 갯수에 따라 쿼리 실행 시간이 달라질 수 있음을 염두에 두고 사용하는 것이 좋겠습니다.

그래서 뭘 사용하면 좋을까?🤔

여러 데이터를 한번에 처리해야 한다는 가정 하에 BULK 쿼리가 단건 쿼리보다 장점이 더 큰것 같습니다.
실험에서는 극한의 상황을 보기 위해서 1,000,000건의 데이터를 BULK로 처리하느라 CPU와 메모리 사용량이 높아졌지만, 서버의 자원에 맞는 적절한 BULK SIZE를 찾는다면 적절한 자원 사용량과, 짧은 쿼리 실행시간, 적은 DB커넥션 풀 사용 등 여러 장점을 가져갈 수 있을 것 같습니다.

특히 배치 서버에서 BULK 쿼리를 사용한다면, 빠르게 작업을 완료하고 배치 서버를 종료할 수 있으므로 클라우드 서비스를 사용중이라면, 비용적인 측면에서도 장점이 될 것 같습니다.

다만, BULK 쿼리들을 사용하려면 특정 ORM에 종속성을 갖는 별도의 코드나 설정을 추가해줘야 합니다.
또한 UPSERT문 같은 경우는 DB 벤더에 따라 다른 형태를 가지고 있어서 ORM에서 BULK 구문 자동생성이 없다면, 특정 DB에 종속적인 코드를 작성해야 합니다.
또, BULK쿼리가 실행되다가 쿼리 실행에 실패한 경우, DB 벤더와 DB 설정에 따라 해당 BULK쿼리의 모든 내용이 ROLLBACK될 수 있습니다.

성능상의 장점이 있지만, 운영상의 단점이 존재하기 때문에 면밀히 검토하고 사용하시는 것을 추천드립니다.

추가적으로 또다른 BULK 쿼리 관련 실험 결과를 링크로 달아드릴테니, 읽어보시면 더욱 슬기로운 BULK 쿼리 사용이 가능할 것 같습니다.

실험에 사용한 자바 코드 또한 첨부해두었으니, 직접 테스트해보고 싶으신 분들은 가져다 사용하시면 됩니다.

감사합니다.