PostgreSQL ON CONFLICT(UPSERT) 활용 패턴

개요

데이터를 삽입하면서 중복 키가 발생할 때 처리하는 방식은 빈번한 과제다. PostgreSQL의 ON CONFLICT(일명 upsert)는 이를 간결하게 해결한다. 이 글에서는 기본 문법부터 실무에서 자주 쓰이는 패턴과 성능 최적화 팁까지 순서대로 설명한다.

기본 문법 이해

DO NOTHING과 DO UPDATE

가장 단순한 형태는 충돌 시 아무 작업도 하지 않는 DO NOTHING이다. 반대로 기존 값을 갱신하려면 DO UPDATE를 사용한다.

INSERT INTO users(id, email, name)
VALUES (1, 'a@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

INSERT INTO users(id, email, name)
VALUES (1, 'a@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name;

EXCLUDED 키워드

EXCLUDED는 충돌로 인해 삽입되지 않은 행의 값을 가리킨다. 이를 이용해 삽입값을 기준으로 업데이트 로직을 작성한다.

실무에서 자주 쓰는 패턴

1) 초기 삽입 후 갱신(Insert-or-update)

기본 사용 형태다. 삽입 시 새 레코드를 넣고, 키 충돌이면 특정 컬럼만 갱신한다.

INSERT INTO products(sku, name, price, updated_at)
VALUES ('SKU123', 'Widget', 9.99, now())
ON CONFLICT (sku) DO UPDATE
SET price = EXCLUDED.price,
    updated_at = now();

2) 부분적 업데이트(조건부 업서트)

WHERE 절을 DO UPDATE에 붙여 조건부로 업데이트할 수 있다. 이를 통해 불필요한 쓰기를 줄인다.

ON CONFLICT (sku) DO UPDATE
SET price = EXCLUDED.price
WHERE products.price IS DISTINCT FROM EXCLUDED.price;

3) 복합 키 및 표현식 타깃

충돌 타깃은 단일 컬럼 또는 복합 인덱스일 수 있다. 적절한 UNIQUE 제약과 함께 설계한다.

성능 고려사항

ON CONFLICT는 편리하지만 무조건 빠르지는 않다. 성능을 위해 고려할 점을 정리한다.

인덱스 설계

충돌 대상은 항상 유니크 인덱스 또는 PRIMARY KEY여야 한다.
불필요하게 넓은 복합 인덱스는 쓰기 비용을 높인다.

불필요한 업데이트 방지

업데이트가 발생하면 WAL과 잠금 비용이 든다. WHERE 절로 실제 변경이 있을 때만 업데이트하도록 제한한다.

다중 행 삽입

여러 행을 한 번에 삽입하면 네트워크 왕복이 줄어든다. 단, 충돌 빈도가 높으면 내부적으로 개별 처리가 필요해 비용이 커진다.

INSERT INTO stats(key, cnt)
VALUES
  ('a', 1), ('b', 2), ('c', 3)
ON CONFLICT (key) DO UPDATE
SET cnt = stats.cnt + EXCLUDED.cnt;

동시성 및 잠금

ON CONFLICT는 내부적으로 필요한 행을 잠근다. 경쟁이 심한 키에 대해선 충돌과 대기 시간이 발생한다. 이를 완화하려면 다음을 고려한다.

충돌 가능성이 낮은 파티셔닝 또는 키 재설계
짧은 트랜잭션 유지
필요 시 애플리케이션 레벨의 재시도 로직

응용 패턴

UPSERT와 RETURNING 결합

삽입 혹은 업데이트 후 최종 상태를 바로 받아올 때 RETURNING을 사용하면 편리하다.

INSERT INTO users(id, email, name)
VALUES (1, 'a@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name
RETURNING id, email, name;

중복 데이터 집계

로그나 카운터 누적처럼 집계가 필요한 경우 EXCLUDED 값을 활용해 안전하게 합산한다.

자주 발생하는 실수

충돌 타깃이 되는 인덱스 미설정으로 ON CONFLICT가 동작하지 않음
항상 업데이트 하도록 SET만 작성해 불필요한 WAL 발생
대량 쓰기 시 인덱스 설계로 전체 성능 저하

요약과 권장사항

ON CONFLICT는 삽입-갱신 작업을 단순화한다. 그러나 성능과 동시성을 고려한 인덱스 설계, 조건부 업데이트, 다중 행 전략을 함께 적용해야 실제 운영에서 이점을 얻는다. 초중급 수준에서는 WHERE 조건으로 불필요한 업데이트를 막고, 경쟁이 심한 키는 분해하거나 파티셔닝을 검토하는 것을 권장한다.

참고 예제 모음

기본 DO NOTHING/DO UPDATE 예제
조건부 업데이트(WHERE 사용) 예제
다중 행 집계 예제

위 예제와 권장사항을 바탕으로 실제 스키마와 워크로드에 맞춰 테스트를 반복하면 안정적인 upsert 설계가 가능하다.

목차

개요