PostgreSQL에서 Merge/Upsert 사용하기

데이터베이스 작업을 하다보면 들어오는 데이터가 없을 경우는 Insert, 있을 경우는 Update 해야할 때가 있습니다.

제 경우는 통계를 내서 웹으로 실시간으로 뿌려줘야하는 상황인데 GROUP BY 문으로 집계하기엔 ROW가 너무 많아 길게는 API 하나에 50초 이상 걸리는 상황이었습니다.

 

협력사의 테이블인데다가 인덱스는 다른 기능에 맞춰 걸려있어서 추가로 건다고 해도 효과가 드라마틱하지도 않은 상황이었어요.

 

저는 이 문제의 해결방법을 고민하다가 스케쥴러를 이용하여 통계에 필요한 데이터를 SELECT 해서 통계 테이블에 데이터를 넣어주고 이 테이블을 바라보게 하는 형태로 문제를 해결하였습니다.

 

이 때 사용한 쿼리가 PostgreSQL의 ON CONFLICT문입니다.

 

Oracle의 Merge문 같은 기능인데요. PostgreSQL에선 9.5버전부터 INSERT ... ON CONFLICT ... DO UPDATE ... 같은 형태로 지원하고 있습니다.

INSERT INTO SPEED_BY_ID(id, speed, date)
ON CONFLICT (id, date)
DO UPDATE
SET SPEED = EXCLUDED.SPEED
[,COLUMN_NAME = VALUE]

ON CONFLICT 이후 ON CONFLICT ON CONSTRAINT speed_id_date_unique_index와 같은 제약조건 혹은 제약조건이 걸린 칼럼명을 괄호 안에 넣어 INSERT 도중 오류가 나는 상황을 정의하고, 오류가 났을 때 DO UPDATE문으로 기존 ROW에 대해 수행할 UPDATE 작업을 기술합니다.

 

이 때 UPDATE문에서 수정할 값에 EXCLUDED.를 앞에 붙여주면 새로 들어오는 데이터를, 붙이지 않으면 기존 데이터를 사용하게 됩니다.

참고

• PostgreSQL Upsert Using INSERT ON CONFLICT statement
• 공식 문서 INSERT

INSERT

(2021.02.07 작성글을 티스토리로 옮김)