PostgreSQL upsert 정석: ON CONFLICT 사용법과 함정

인프라 PostgreSQL 데이터베이스
4 분 소요

“있으면 갱신, 없으면 삽입"은 애플리케이션에서 SELECT로 확인한 뒤 분기하는 것이 아니라, INSERT ... ON CONFLICT 한 문장으로 처리하는 것이 정석입니다. 확인과 삽입 사이의 틈에 다른 트랜잭션이 끼어드는 경쟁 조건을 DB가 원자적으로 막아 주기 때문입니다. 기초 6편에서 세운 “읽고-판단-쓰기는 원자적 한 문장으로"의 대표 사례가 바로 upsert입니다.

SELECT 후 INSERT가 깨지는 이유 #

안티패턴: SELECT 후 INSERT 
-- 안티패턴: 확인과 삽입 사이에 틈이 있습니다
SELECT id FROM users WHERE email = 'kim@example.com';
-- (없다는 응답을 받은 사이, 다른 요청이 같은 이메일을 INSERT)
INSERT INTO users (email, name) VALUES ('kim@example.com', '김개발');
-- ERROR: duplicate key value violates unique constraint

트래픽이 적을 때는 안 터지다가, 같은 사용자의 중복 요청(더블 클릭, 재시도)이 겹치는 순간 유니크 제약 위반으로 터집니다. 이 에러를 try-catch로 잡아 재시도하는 코드가 늘어나기 시작하면, 그 자리가 ON CONFLICT의 자리입니다.

DO NOTHING과 DO UPDATE #

DO NOTHING과 DO UPDATE 
-- 이미 있으면 조용히 넘어가기: 멱등한 삽입
INSERT INTO subscriptions (user_id, plan)
VALUES (42, 'basic')
ON CONFLICT (user_id) DO NOTHING;

-- 이미 있으면 갱신하기: 진짜 upsert
INSERT INTO users (email, name, last_seen_at)
VALUES ('kim@example.com', '김개발', now())
ON CONFLICT (email) DO UPDATE
SET name         = EXCLUDED.name,
    last_seen_at = EXCLUDED.last_seen_at;

문법의 부품은 세 개입니다. 충돌 판정 대상 (email)은 유니크 제약(또는 유니크 인덱스)이 설정된 컬럼이어야 하고, upsert의 전제 자체가 “유니크 제약이 있다"입니다(기초 2편의 제약 설계가 여기서 효과를 발휘합니다). EXCLUDED는 “삽입하려다 충돌한 그 새 행"을 가리키는 가상 테이블로, 새 값으로 갱신할 때 씁니다. 그리고 DO UPDATE에는 WHERE를 붙여 조건부 갱신도 됩니다.

조건부 갱신 
-- 새 값이 더 최신일 때만 갱신 (이벤트 순서 역전 방어)
ON CONFLICT (device_id) DO UPDATE
SET reading = EXCLUDED.reading, reported_at = EXCLUDED.reported_at
WHERE EXCLUDED.reported_at > sensor_readings.reported_at;

알아 둘 함정 세 가지 #

  • 충돌 대상은 명시하는 것이 정석입니다. ON CONFLICT DO NOTHING처럼 대상 생략도 문법상 가능하지만, 유니크 제약이 여러 개인 테이블에서는 “어느 충돌을 흡수할 의도였는지"가 코드에서 사라집니다. 의도한 제약을 (컬럼)으로 명시하면, 다른 제약의 위반은 정상적으로 에러가 나서 버그를 숨기지 않습니다.
  • DO UPDATE는 값이 같아도 행을 씁니다. 충돌한 행은 잠기고, 새 버전이 만들어집니다(MVCC 원리 그대로 dead tuple도 생깁니다). “사실상 변한 게 없는” 대량 upsert를 반복하는 배치라면 WHERE (기존 값) IS DISTINCT FROM (새 값) 조건으로 무의미한 갱신을 걸러 주는 것이 VACUUM 부담을 줄입니다.
  • 시퀀스는 충돌해도 소모됩니다. IDENTITY 채번은 충돌 판정 전에 일어나므로, DO NOTHING으로 흡수된 삽입도 번호를 하나 태웁니다. id가 띄엄띄엄해지는 것은 정상이고, 결번 없는 연속 번호가 필요한 요건(세금계산서 등)은 애초에 시퀀스가 아니라 별도 채번 설계가 맞습니다.

MERGE와의 관계 #

PostgreSQL 15부터 표준 SQL의 MERGE도 있습니다. 갈림길은 단순합니다. 동시성 안전한 upsert가 목적이면 ON CONFLICT(유니크 제약 기반의 원자적 처리), “삽입·갱신·삭제를 조건별로 나눠 적용하는 배치 동기화"가 목적이면 MERGE(WHEN MATCHED / NOT MATCHED 분기, PostgreSQL 17부터는 RETURNING도 지원)입니다. 웹 애플리케이션의 일상적인 upsert에서 MERGE를 쓸 이유는 거의 없습니다. MERGE는 ON CONFLICT와 달리 동시 삽입 경쟁에서 유니크 위반 에러가 날 수 있어, 재시도 로직 없이는 upsert 대용이 되지 못합니다.

적용 순서 #

  1. upsert 대상 컬럼에 유니크 제약(또는 유니크 인덱스)이 있는지 확인합니다. 없으면 만드는 것이 먼저입니다.
  2. 멱등 삽입(중복 무시)이면 DO NOTHING, 갱신까지 필요하면 DO UPDATE + EXCLUDED로 씁니다. 충돌 대상은 명시합니다.
  3. 대량 반복 배치라면 IS DISTINCT FROM 조건으로 무변경 갱신을 걸러 dead tuple을 줄입니다.
  4. 조건별 삽입·갱신·삭제가 섞인 동기화 작업만 MERGE를 검토합니다.

정리 #

  • upsert는 SELECT 후 분기가 아니라 INSERT … ON CONFLICT 한 문장입니다. 경쟁 조건을 DB가 원자적으로 막습니다.
  • 중복 무시는 DO NOTHING, 갱신은 DO UPDATE + EXCLUDED입니다. DO UPDATE의 WHERE로 조건부 갱신까지 됩니다.
  • 충돌 대상 명시가 정석입니다. 다른 유니크 제약의 위반까지 조용히 삼키지 않게 됩니다.
  • DO UPDATE는 같은 값이어도 새 버전을 쓰고, 시퀀스는 충돌해도 소모됩니다. 둘 다 정상 동작입니다.
  • 동시성 안전 upsert는 ON CONFLICT, 조건 분기형 배치 동기화는 MERGE입니다. 일상 upsert에 MERGE는 답이 아닙니다.
X