PostgerSQL Upsert 쿼리 개념 및 대용량 속도 차이 예제

Upsert 포스팅

개요

안녕하세요 빈코입니다. 오늘은 DB 쿼리 중에 유용하게 사용되는 Upsert문에 대해서 포스팅 하려 합니다. Upsert문은 데이터를 삽입(Insert)하거나 수정(Update)하는 작업을 동시에 처리하는 SQL문입니다. 이 문은 주로 "존재하면 업데이트, 없으면 삽입"하는 형태로 동작하며, 기존 데이터가 있으면 수정하고 없으면 새로 추가하는 방법입니다. 좀 더 자세한 방법은 하단에서 살펴볼게요😁

 

Upsert문 개념📙

Upsert문은 개요에서 설명한 바와 같이 데이터를 삽입 및 수정을 동시에 처리하는 SQL문입니다. 여기서 존재하면 업데이트, 없으면 삽입하는 과정이 필요한데, 어떤 값을 기준으로 존재 여부를 판단하는지는 CONFLICT 절에 의해서 판단됩니다.

 

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1) DO NOTHING;

 

예시로 테이블에 데이터를 넣을 때 ON CONFLICT 절에서 column1이 이미 DB상에 존재한다면 아무것도 하지 않고, 존재하지 않다면 데이터를 넣는 과정입니다. 만약에 키가 겹쳤을 때 수정하는 과정을 넣으려면 아래와 같이 쿼리를 구성해야 합니다.

 

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1) DO UPDATE
SET column2 = EXCLUDED.column2;

 

해당 예제는 위와 다르게 column1의 키가 겹쳤을 때, 해당 키를 가진 로우의 column2의 값을 VALUES 안에 있는 value2로 수정하는 과정입니다. 여기서 EXCLUDED는 DB에 있는 값이 아닌, 새롭게 넣는 value1, value2의 값에 접근할 수 있는 키라고 생각하시면 됩니다. 

 

조금 더 이해하기 쉽게 하단에서는 간단한 테이블을 생성하고 Upsert 하는 일련의 과정을 소개해볼게요!

 

Upsert문 예제📘

첫 번째로 간단한 회원 테이블(user_test_t)을 생성합니다. user_serial은 회원들을 구분할 수 있는 PK 값이며, user_id는 중복된 데이터를 방지하기 위해 UNIQUE 값으로 설정했습니다. 여기서 UNIQUE 값은 NULL 값을 허용합니다.

CREATE TABLE user_test_t (
    user_serial SERIAL PRIMARY KEY,
    user_id VARCHAR(50) UNIQUE,
    user_name VARCHAR(100),
    user_email VARCHAR(100)
);

 

위에 예제처럼 테이블을 생성할 때 UNIQUE 설정을 해도 되지만, 이미 테이블이 생성된 상태라면 아래와 같이 제약조건을 추가할 수도 있습니다.

ALTER TABLE user_test_t
ADD CONSTRAINT user_test_t_ukey UNIQUE (user_id); -> 이렇게도 추가 가능

 

그럼 개념 챕터에서 본 Upsert문을 실행해볼까요?

INSERT INTO user_test_t (user_id, user_name, user_email)
VALUES 
('binco0', 'binco_0','binco_0@tistory.com')
ON CONFLICT (user_id) DO UPDATE 
SET user_name = excluded.user_name,
	user_email = excluded.user_email;

 

위의 쿼리를 실행했을 때, 만약 'binco'라는 user_id가 이미 DB에 존재한다면 user_name과 user_email을 각각 'binco_0', 'binco_0@tistory.com'으로 수정을 진행할 것이고, 존재하지 않다면 'binco'라는 user_id로 새롭게 로우가 생성될 것입니다😊

 

한 가지 의문이 드는 점은 DB 상에서 UNIQUE 값을 NULL을 허용한다고 했는데, 만약 DB에 user_id 값이 NULL인 로우들이 있고 새롭게 Upsert 하는 로우들 중에서 user_id가 NULL이면 충돌이 일어날까요? 아니면 충돌이 일어나지 않고 새롭게 삽입을 할까요?

 

정답은 새롭게 삽입합니다. NULL은 문자열 'NULL'이 아닌 '알 수 없는 값'을 의미합니다. 따라서 두 개의 NULL 값은 서로 다른 값으로 간주됩니다. SQL에서 NULL = NULL이 항상 FALSE인 것처럼요😃

 

Insert VS Upsert 시간 비교📒

만약 대용량의 데이터들을 DB에 넣어야 할 때를 고려하여 조금 더 깊게 접근해서 Insert와 Upsert의 시간차이를 비교해 볼까요? 첫 번째로는 아무런 제약 없이 3천 개의 데이터를 DB에 넣을 경우입니다.

3000개 데이터 Insert

3천개의 데이터는 약 0.127초 정도 소요되네요. 몰론, 데이터 크기에 따라 차이가 날 수 있습니다!

 

만약 같은 데이터를 Upsert 했을 경우는 얼마큼 걸릴까요?

3000개 데이터 Upsert

 

데이터를 절반의 개수인 1500개는 업데이트할 수 있게 구성하고, 나머지 1500개는 새롭게 Insert 할 수 있게 데이터를 구성하고 Upsert를 진행한 결과 0.113초 ~ 0.123초 정도의 시간이 걸렸습니다. 포스팅에는 기재하지 않지만, 1만 개의 데이터도 시간차이가 많이 나지 않는 것을 확인했는데 시간차이가 얼마 나지 않은 이유가 뭐가 있을까요?

 

첫 번째로는 Update를 진행하든 Insert를 진행하던 간에 데이터들을 각 로우의 user_id와 비교하여 충돌이 일어나는지에 대한 일련의 과정은 똑같이 진행하기 때문입니다. 즉, 삽입하려는 데이터들이 충돌이 일어나는지에 대한 여부는 어떤 데이터 간에 똑같이 과정이 이루어집니다.

 

두 번째로는 DB에서 작업 수행 시 Insert문이 Update문 보다 비교적 빠른 건 맞지만, 위 기능은 데이터 충돌이 일어나서 Update를 진행하더라도 두 개의 칼럼만 수정하기 때문에 속도적으로 큰 차이가 없었습니다.

 

결론적으로, 예시 쿼리는 수정 과정이 크지 않아 속도가 비슷했지만 CONFILCT이 일어났을 때 수정 과정이 많다면 시간적으로 더 큰 차이가 일어날 수 있습니다. 만약 그럴 경우에는 Batch를 따로 구성하는 방법도 좋을 것 같네요😁

 

마치며

지금까지 Postgresql에서 사용하는 Upsert문에 대한 일련의 과정과 일반적으로 데이터를 삽입하는 Insert문과의 시간차이에 대해 포스팅했습니다. 실무에서도 유용하게 사용하기 때문에 해당 기능에 대해 숙지해 두시면 좋을 것 같네요😄