PostgreSQL ON CONFLICT로 중복 INSERT 처리하기: 유니크 제약과 RETURNING

같은 데이터를 두 요청이 동시에 저장할 수 있다면, 먼저 조회해서 없을 때만 INSERT하는 코드만으로는 중복을 막을 수 없습니다. PostgreSQL에서는 중복의 기준을 유니크 제약이나 기본 키로 정하고, 충돌했을 때 건너뛸지 갱신할지를 ON CONFLICT로 선택할 수 있습니다. 기존 값을 유지하려면 DO NOTHING, 새 입력으로 지정한 열을 바꾸려면 DO UPDATE를 사용합니다. RETURNING은 실제로 삽입하거나 갱신한 행을 돌려주므로, 건너뛴 기존 행까지 자동으로 반환한다고 기대해서는 안 됩니다.

아래 설명은 PostgreSQL 17의 일반 테이블과 INSERT를 기준으로 합니다. 예제는 별도 연습용 연결에서 임시 테이블 하나로 순서대로 실행하도록 구성했습니다. 사용자 데이터베이스에서 실행하거나 측정한 결과가 아니라, 예제의 조건으로 예상되는 동작을 설명합니다.

1. 중복 기준은 조회 코드가 아니라 제약으로 정합니다

외부 자료를 가져오는 작업을 생각해 보겠습니다. 같은 제공처에서 같은 외부 식별자를 가진 자료가 다시 들어오면 한 건으로 취급하려고 합니다. 이때 제목이 같은지는 중복 기준이 아니며, 제공처와 외부 식별자의 조합이 기준입니다.

두 요청이 거의 동시에 SELECT를 실행하면 둘 다 “아직 없음”을 볼 수 있습니다. 이후 두 요청이 각각 INSERT를 실행하는 구조에서는 조회와 저장 사이의 간격을 해결해야 합니다. 단순히 조회를 한 번 더 넣는 것으로 이 경쟁이 사라지지는 않습니다.

예제에서는 복합 기본 키로 중복 기준과 NULL 금지를 함께 표현합니다.

CREATE TEMP TABLE demo_import (
    source text NOT NULL,
    external_id text NOT NULL,
    title text NOT NULL,
    PRIMARY KEY (source, external_id)
);

임시 테이블은 현재 연결에서 사용하는 연습용입니다. 아래 문장들은 같은 연결에서 실행해야 하며, 실제 서비스 테이블의 이름이나 제약을 변경할 필요는 없습니다. 같은 연결에서 예제를 처음부터 다시 실행하면 테이블이 이미 존재할 수 있으므로 별도 새 연습 연결을 사용하는 편이 단순합니다.

기본 키 대신 UNIQUE 제약을 설계할 수도 있지만 NULL 처리까지 같은 것은 아닙니다. PostgreSQL의 기본적인 UNIQUE 동작에서는 NULL을 서로 다른 값으로 취급합니다. 중복 키에서 NULL을 허용할 것인지 먼저 결정해야 하며, 이번 예제는 기본 키이므로 두 키 값에 NULL을 허용하지 않습니다.

2. 기존 행을 유지하려면 DO NOTHING을 사용합니다

첫 입력은 다음처럼 저장합니다.

INSERT INTO demo_import (source, external_id, title)
VALUES ('feed_a', 'A-17', '첫 제목')
ON CONFLICT (source, external_id) DO NOTHING
RETURNING source, external_id, title;

처음 실행하면 한 행이 삽입되고 그 행이 반환됩니다. 다음에는 같은 키에 다른 제목을 전달합니다.

INSERT INTO demo_import (source, external_id, title)
VALUES ('feed_a', 'A-17', '바꾼 제목')
ON CONFLICT (source, external_id) DO NOTHING
RETURNING source, external_id, title;

이번 입력은 지정한 기본 키와 충돌하므로 건너뜁니다. RETURNING의 결과는 0행이며, 이미 저장된 제목은 여전히 ‘첫 제목’입니다. DO NOTHING은 “입력으로 기존 행을 덮어쓴다”가 아니라 “이 충돌에 대해서는 삽입하지 않는다”는 선택입니다.

충돌 대상을 괄호에 적었다고 그 조합의 유니크 제약이 새로 만들어지는 것은 아닙니다. 대응하는 유니크 인덱스 등을 찾을 수 없다면 문장은 오류가 됩니다. 예제에서는 앞서 만든 복합 기본 키가 해당 기준을 제공합니다.

DO NOTHING에서 충돌 대상을 생략하는 문법도 있지만, 특정 중복만 허용하려는 코드라면 어떤 키를 기준으로 삼았는지 드러내는 편이 검토하기 쉽습니다. 다른 유니크 충돌까지 무조건 같은 의미로 취급하지 않도록 주의합니다.

3. 입력값으로 갱신하려면 바꿀 열을 명시합니다

같은 자료가 다시 들어왔을 때 제목을 새 입력으로 바꾸려면 DO UPDATE를 선택합니다.

INSERT INTO demo_import (source, external_id, title)
VALUES ('feed_a', 'A-17', '바꾼 제목')
ON CONFLICT (source, external_id)
DO UPDATE SET title = EXCLUDED.title
RETURNING source, external_id, title;

EXCLUDED.title은 이 INSERT가 넣으려던 행의 제목을 가리킵니다. 예제에서는 기존 행의 title만 ‘바꾼 제목’으로 갱신하고, 갱신한 행을 반환합니다. 충돌하지 않는 키였다면 새 행을 삽입합니다.

이 형태를 흔히 UPSERT라고 부르지만 모든 열이 자동으로 동기화되는 것은 아닙니다. SET에 적은 열과 표현식이 갱신 규칙입니다. 기존 값을 보존해야 하는 열, 입력이 비었을 때의 처리, 더 오래된 자료로 최신 내용을 덮어쓰지 않을 조건은 업무 규칙에 맞춰 별도로 정해야 합니다.

DO UPDATE는 충돌 대상을 지정해야 합니다. 또한 삽입 또는 갱신을 원자적으로 처리하는 성질과 어떤 요청의 값을 최종적으로 채택할지는 다른 문제입니다. “마지막에 도착한 입력이 항상 업무상 최신 자료”라고 가정해서는 안 됩니다.

4. RETURNING이 비었다고 기존 행이 없다는 뜻은 아닙니다

RETURNING은 이번 문장이 실제로 삽입하거나 갱신한 행에 대한 결과입니다. 앞의 DO NOTHING 예제처럼 충돌 때문에 건너뛰면 기존 행이 있어도 결과는 비어 있습니다. DO UPDATE에 조건을 추가해 갱신하지 않은 행 역시 반환 대상이 아닙니다.

API에서 기존 식별자를 반드시 돌려줘야 한다면 “새로 삽입된 경우”와 “이미 존재하는 경우”의 응답 정책을 나누어야 합니다. 건너뛴 뒤 조회하는 방법을 쓴다면 트랜잭션 격리 수준과 다른 요청의 동시 변경을 함께 검토해야 합니다. 빈 RETURNING을 곧바로 ‘대상 없음’으로 바꾸는 처리는 피합니다.

특히 Read Committed 환경에서는 다른 트랜잭션의 진행 때문에 충돌 처리가 영향을 받을 수 있습니다. 복잡한 동시 실행에서 단일 문장 안의 조회와 충돌 판정이 같은 시점의 가시성을 가진다고 단정하지 않는 편이 좋습니다.

기존 행을 반환받겠다는 이유만으로 의미 없는 갱신을 넣는 것도 무조건 안전한 대안은 아닙니다. 갱신 권한, 잠금, 트리거, 변경 이력에 영향을 줄 수 있습니다. 필요한 결과와 실제 쓰기 동작을 구분해서 설계해야 합니다.

5. ON CONFLICT가 처리하지 않는 오류도 남습니다

ON CONFLICT는 모든 INSERT 오류를 무시하는 구문이 아닙니다. 예제에서 title에 NULL을 넣으면 NOT NULL 조건을 위반합니다. 외래 키나 CHECK 조건 위반, 권한 문제 등을 중복 입력과 같은 상황으로 처리해서는 안 됩니다.

한 INSERT에 여러 행을 넣으면서 DO UPDATE를 사용할 때는 입력 묶음 안에 같은 키가 반복되지 않는지도 확인합니다. 하나의 기존 행을 같은 문장에서 여러 번 갱신하려는 형태는 오류가 될 수 있습니다. 입력을 미리 합칠 때도 무작정 한 행을 남기기보다 어떤 값을 우선할지 규칙을 정해야 합니다.

동시 실행에 따른 대기와 트랜잭션 재시도 필요성도 사라지지 않습니다. ON CONFLICT가 중복 처리에 유용하다는 이유로 잠금이 없거나 모든 요청이 즉시 끝난다고 설명할 수는 없습니다.

마지막으로 이 문장이 파일 업로드나 외부 알림까지 한 번만 실행되게 만드는 것은 아닙니다. 데이터 행의 중복 제어와 서비스 전체 작업의 중복 실행 방지는 범위가 다릅니다. 외부 작업이 연결된다면 별도의 처리 상태와 재시도 정책이 필요합니다.

6. 세 경우를 나누어 결과를 검증합니다

연습에서는 새 키 입력, 기존 키에 DO NOTHING, 기존 키에 DO UPDATE를 순서대로 비교합니다. 각 단계에서 반환 행 수만 보지 말고 저장된 제목도 확인합니다.

SELECT source, external_id, title
FROM demo_import
WHERE source = 'feed_a' AND external_id = 'A-17';

위 순서대로 실행했다면 최종 제목은 ‘바꾼 제목’입니다. DO NOTHING까지만 실행한 시점에는 ‘첫 제목’이어야 합니다. 이 차이를 기록하면 “중복을 허용하지 않는다”와 “중복 입력으로 값을 갱신한다”가 코드에서 어떻게 구분되는지 확인할 수 있습니다.

정리
중복의 기준은 기본 키나 유니크 제약으로 표현하고, ON CONFLICT에는 그 기준에 맞는 처리 정책을 연결합니다.
DO NOTHING은 기존 행을 유지하고, DO UPDATE는 SET에 명시한 방식으로 갱신합니다. RETURNING은 실제로 처리된 행을 기준으로 해석합니다.
NULL·다른 제약 위반·동시 실행·외부 작업의 재시도는 별도 점검 대상으로 남겨 두어야 합니다.