본문으로 건너뛰기
· backend · 2분 읽기

PostgreSQL bulk upsert의 중복 키와 청크 경계

INSERT ... ON CONFLICT DO UPDATE에서 같은 대상 행을 두 번 갱신하는 입력은 오류가 발생한다. 같은 키를 서로 다른 청크에 넣으면 오류 없이 오래된 값이 최신 값을 덮어쓸 수도 있다.

여러 페이지의 데이터를 모아 저장할 때는 청크를 나누기 전에 중복 키 처리 기준을 정한다.

같은 SQL 안의 중복 키

합성 재고 데이터의 키를 (tenant_id, sku)로 둔다.

sql
CREATE TEMP TABLE sample_stock (
    tenant_id text NOT NULL,
    sku text NOT NULL,
    quantity integer NOT NULL CHECK (quantity >= 0),
    revision integer NOT NULL,
    PRIMARY KEY (tenant_id, sku)
);

INSERT INTO sample_stock VALUES
    ('demo', 'A', 10, 1),
    ('demo', 'A', 20, 2)
ON CONFLICT (tenant_id, sku) DO UPDATE
SET quantity = EXCLUDED.quantity,
    revision = EXCLUDED.revision;

이 입력은 SQLSTATE 21000으로 실패한다. 한 명령이 같은 대상 행에 두 번 영향을 주게 되는 경우다. 이 제약은 PostgreSQL INSERT 문서에 명시돼 있다.

중복 판단은 실제 conflict target과 같아야 한다. sku만으로 묶으면 서로 다른 tenant의 데이터를 합치게 된다. expression index나 별도 collation을 사용한다면 Go 문자열 비교와 DB의 동등성 판단이 같은지도 확인해야 한다.

청크 분할 전 중복 키 처리

revision 2인 수량 20을 첫 청크에 저장한 뒤 revision 1인 수량 10을 다음 청크에 저장하면, 조건 없는 upsert는 최종 수량을 10으로 바꾼다. SQL 하나의 중복 오류는 없어졌지만 최신값 유지라는 요구는 깨진 것이다.

예제에서는 동일 키의 가장 큰 revision을 채택한다. 같은 revision에 값이 다르면 입력 오류로 처리한다. 실제 업무가 증분 합산이라면 이 정책을 그대로 쓰면 안 된다. 재고 스냅샷과 재고 증감 이벤트는 다른 입력이다.

작은 입력은 전체 키별로 정규화한 뒤 청크로 나눈다. 큰 입력은 staging table에 적재해 키별 winner를 고르거나, 정렬된 입력을 병합한다. 청크마다만 중복을 제거하면 페이지 경계의 동일 키는 여전히 남는다.

DB에도 version 조건을 둔다

입력을 정규화했더라도 다른 워커가 더 최신값을 먼저 저장할 수 있다. 최종 쓰기에도 revision 조건을 둔다.

sql
INSERT INTO sample_stock VALUES ('demo', 'A', 10, 1)
ON CONFLICT (tenant_id, sku) DO UPDATE
SET quantity = EXCLUDED.quantity,
    revision = EXCLUDED.revision
WHERE sample_stock.revision < EXCLUDED.revision;

revision 2가 이미 있으면 이 입력은 갱신하지 않는다. 단, 같은 revision에 다른 내용이 들어오는 오류까지 잡는 조건은 아니다. 그런 입력을 거절하려면 입력 서명 비교나 별도 충돌 검사가 필요하다.

버전 비교는 키별로 단조 증가한다는 원본 계약을 전제로 한다. 서버 시계를 그대로 revision 대신 쓰면 clock skew나 동률 때문에 같은 보장을 할 수 없다.

파라미터 수로 청크 상한 계산하기

PostgreSQL의 공식 제한표는 query parameter 상한을 65,535개로 제시한다. 실제 드라이버나 실행 모드가 더 낮은 제한을 둘 수도 있으므로 사용하는 경로에서 확인한다.

청크 행 수 상한
  = floor((파라미터 예산 - 고정 파라미터 수) / 행당 파라미터 수)

행당 8개를 바인딩하고 별도 조건에 3개를 쓴다면 이론상 floor((65535 - 3) / 8) = 8191행이다. 이것은 성능상 권장 청크 크기가 아니라 제한을 넘지 않는 계산값이다.

쿼리 문자열 길이, payload 크기, 잠금 시간, 취소 응답성을 고려해 더 작은 운영 상한을 둔다. 값은 바인딩하고 식별자는 고정된 스키마에서 만든다. 제한을 피하려고 사용자 값을 SQL에 직접 이어 붙이지 않는다.

청크별 실행과 트랜잭션 범위

전체 성공 계약이면 여러 청크를 같은 트랜잭션으로 실행하고 하나라도 실패하면 rollback한다. 청크별 커밋은 실패 시 앞쪽 데이터가 남는 다른 계약이다.

같은 트랜잭션에서 청크를 줄여도 잠금이 청크마다 풀리는 것은 아니다. 규모가 크다면 전체 원자성이 필요한지, staging 적재 후 한 번의 반영으로 바꿀 수 있는지 따로 판단한다.

합성 데이터 실행 결과

2026-10-12에 로컬 Docker의 postgres:18.4-alpine, PostgreSQL 18.4, Linux aarch64 환경에서 확인했다. 입력은 위의 demo/A 두 revision과 롤백 확인용 B·C다. 성능 측정은 하지 않았다.

실행 조건관찰 결과
한 INSERT에 동일 키 두 행SQLSTATE 21000, 저장된 행 0개
별도 INSERT로 revision 2 다음 1조건 없으면 수량 10으로 덮어씀
revision 2 저장 후 조건부 revision 1갱신 0행, 수량 20 유지
PL/pgSQL 예외 블록에서 B 저장 후 C의 CHECK 실패같은 하위 트랜잭션의 B도 롤백

실행 스크립트는 재현 SQL 다운로드로 받을 수 있다. 저장소에서는 examples/postgres-bulk-upsert/verify.sql에 있다. 빈 테스트 DB에서 다음처럼 실행할 수 있다.

bash
psql "$TEST_DATABASE_URL" -v ON_ERROR_STOP=1 
  -f examples/postgres-bulk-upsert/verify.sql

스크립트는 임시 테이블을 만들고 마지막에 rollback한다. 네 번째 결과는 PostgreSQL 하위 트랜잭션의 동작 검증이며 애플리케이션의 청크 트랜잭션 구현을 검증한 것은 아니다. 드라이버의 파라미터 상한, 동시 워커, 대량 입력 성능도 이 실험 범위에는 포함하지 않았다.

공유하기 X LinkedIn

관련 글