Postgres의 INSERT ... ON CONFLICT와 MERGE: 어떤 업서트를 쓸지, 그리고 각각이 내는 오류

Postgres에는 "이 행을 삽입하되, 이미 있으면 업데이트하라"를 표현하는 방법이 두 가지 있습니다. 9.5부터 있었던 INSERT ... ON CONFLICT, 그리고 15에서 추가되고 17에서 RETURNING, merge_action(), WHEN NOT MATCHED BY SOURCE가 더해지면서 훨씬 쓸모 있어진 MERGE입니다. 둘은 서로 바꿔 써도 될 것처럼 보이지만 그렇지 않습니다. 두 세션이 같은 새 키를 업서트하면 ON CONFLICT에서는 둘 다 성공하지만, MERGE에서는 그중 하나가 중복 키 오류를 받습니다. 이 글은 그 경쟁 상태와 사람들이 업서트에서 마주치는 다섯 가지 다른 오류와 의외의 동작을 재현하고, 각각의 해결 방법을 보여 준 뒤, 둘 중 하나를 고르는 규칙으로 마무리합니다. 아래의 모든 문장과 출력은 Relyt One이 실행하는 메이저 버전인 로컬 PostgreSQL 17.11에서 나온 것입니다.

동시에 실행되는 쓰기 작업이 같은 키를 삽입할 수 있다면 INSERT ... ON CONFLICT를 사용하세요. 요청 핸들러, 웹훅 컨슈머, 카운터가 그런 경우입니다. Postgres 문서는 "an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency"(원자적인 INSERT 또는 UPDATE 결과; 독립적인 오류가 없다면, 높은 동시성에서도 두 결과 중 하나가 보장된다)를 보장합니다. 야간 피드나 스테이징 테이블처럼 행 집합 전체를 테이블과 맞춰야 하고, 조건부 업데이트나 삭제, 또는 각 행에 무슨 일이 있었는지에 대한 보고가 필요하다면 MERGE를 사용하세요.

Postgres 17이면 무엇이든 됩니다. Docker를 쓰는 경우:

docker run -d --name upsert-lab -e POSTGRES_PASSWORD=secret postgres:17.11-alpine
docker exec -it upsert-lab psql -U postgres

identity 열을 키로 하고 sku가 고유한 테이블 하나:

CREATE TABLE products (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku   text NOT NULL UNIQUE,
  name  text NOT NULL,
  price numeric(10,2) NOT NULL
);

기본 업서트는 충돌하는 열을 지정하며, EXCLUDED는 삽입하려던 행입니다.

INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 49.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price
RETURNING id, sku, price, (xmax = 0) AS inserted;

 id |  sku  | price | inserted
----+-------+-------+----------
  1 | KB-01 | 49.00 | t

45.00으로 다시 실행하면 같은 행이 업데이트되어 돌아오고, inserted는 false입니다.

 id |  sku  | price | inserted
----+-------+-------+----------
  1 | KB-01 | 45.00 | f

(xmax = 0) 검사는 삽입과 업데이트를 구분하기 위해 널리 복사되는 요령입니다. 새로 삽입된 행 버전에는 xmax가 없기 때문에 동작하지만, 문서화된 동작이 아니라 Postgres가 행 버전을 저장하는 방식에 의존합니다. MERGE에는 수행한 작업을 보고하는 문서화된 방법이 있으며, 이 글의 끝에서 보여 드립니다.

psql 세션 두 개를 엽니다. 첫 번째 세션에서 트랜잭션을 시작하고 아직 존재하지 않는 SKU를 MERGE로 업서트하되, 커밋하지 마세요.

-- 세션 A
BEGIN;
MERGE INTO products AS p
USING (VALUES ('WC-01', 'Webcam', 59.00)) AS f(sku, name, price)
ON p.sku = f.sku
WHEN MATCHED THEN UPDATE SET name = f.name, price = f.price
WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (f.sku, f.name, f.price);

두 번째 세션에서 같은 SKU에 대해 같은 MERGE를 실행합니다. 멈춰서 기다립니다.

-- 세션 B
MERGE INTO products AS p
USING (VALUES ('WC-01', 'Webcam', 55.00)) AS f(sku, name, price)
ON p.sku = f.sku
WHEN MATCHED THEN UPDATE SET name = f.name, price = f.price
WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (f.sku, f.name, f.price);

세션 A에서 COMMIT;을 입력합니다. 세션 B는 다음과 같이 깨어납니다.

ERROR:  duplicate key value violates unique constraint "products_sku_key"
DETAIL:  Key (sku)=(WC-01) already exists.

무슨 일이 일어났는지 살펴보겠습니다. 세션 B가 시작될 때 A의 행은 커밋되지 않았으므로, B의 조인은 일치하는 행을 찾지 못했고 B는 WHEN NOT MATCHED 분기를 선택했습니다. 그러고 나서 B의 삽입은 고유 인덱스에서 A가 끝나기를 기다렸습니다. A가 커밋하자 키가 존재하게 되었지만, 그 시점에서 MERGE는 WHEN MATCHED 분기로 전환할 방법이 없습니다. MERGE 문서도 같은 내용을 말합니다. 문서는 INSERT ... ON CONFLICT를 "as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs"(동시에 INSERT가 발생하면 UPDATE를 실행할 수 있는 대안 문장)로 안내합니다.

이번에는 같은 경쟁 상태를 ON CONFLICT와 다른 새 SKU로 재현합니다.

-- 세션 A
BEGIN;
INSERT INTO products (sku, name, price) VALUES ('HS-01', 'Headset', 79.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

-- 세션 B: A를 기다림
INSERT INTO products (sku, name, price) VALUES ('HS-01', 'Headset', 75.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

세션 A에서 COMMIT;을 하면 세션 B는 INSERT 0 1을 출력하고, 행에는 B의 가격이 들어 있습니다.

SELECT sku, price FROM products WHERE sku IN ('WC-01', 'HS-01') ORDER BY sku;

  sku  | price
-------+-------
 HS-01 | 75.00
 WC-01 | 59.00

WC-01은 세션 B의 MERGE가 실패했기 때문에 세션 A의 가격을 유지했습니다. 두 쓰기 작업이 같은 키를 만들 수 있는 경로에서 MERGE를 쓴다면 SQLSTATE 23505를 잡아 트랜잭션 전체를 재시도하세요. 기본 격리 수준인 READ COMMITTED에서는 재시도가 새 스냅샷을 가져와 A의 행을 보고 WHEN MATCHED 분기를 실행합니다. 명시적 트랜잭션 안에서는 문장만 재시도하는 방법이 통하지 않습니다. MERGE를 세이브포인트로 감싸고 그 세이브포인트로 롤백하지 않는 한, 오류가 이미 트랜잭션을 중단시켰기 때문입니다. 아니면 그 경로는 ON CONFLICT로 유지하세요.

같은 키를 두 번 포함한 배치 업서트는 전체가 실패합니다.

INSERT INTO products (sku, name, price)
VALUES ('MN-27', 'Monitor', 199.00),
       ('MN-27', 'Monitor 27"', 189.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

이것은 의도된 동작입니다. Postgres는 두 행 중 어느 쪽이 이겨야 하는지 추측하지 않으므로 둘 다 적용하기를 거부합니다. 같은 키가 두 번 이상 나타나는 이벤트 스트림이나 CSV 파일로 만든 배치에서 이런 일이 생깁니다. 삽입 전에 중복을 제거하고 어느 행이 이길지 정하세요. 여기서는 배치에서 나중에 오는 행이 이깁니다.

INSERT INTO products (sku, name, price)
SELECT DISTINCT ON (sku) sku, name, price
FROM (VALUES ('MN-27', 'Monitor', 199.00, 1),
             ('MN-27', 'Monitor 27"', 189.00, 2)) AS batch(sku, name, price, seq)
ORDER BY sku, seq DESC
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price
RETURNING sku, name, price;

  sku  |    name     | price
-------+-------------+--------
 MN-27 | Monitor 27" | 189.00

MERGE에도 같은 규칙이 있고 메시지만 다릅니다. 소스 행 두 개가 기존 대상 행 하나와 일치하는 경우:

MERGE INTO products AS p
USING (VALUES ('KB-01', 'Keyboard', 44.00),
              ('KB-01', 'Keyboard', 43.00)) AS f(sku, name, price)
ON p.sku = f.sku
WHEN MATCHED THEN UPDATE SET price = f.price
WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (f.sku, f.name, f.price);

ERROR:  MERGE command cannot affect row a second time
HINT:  Ensure that not more than one source row matches any one target row.

중복된 키가 아직 테이블에 없다면 MERGE는 WHEN NOT MATCHED 분기를 두 번 타고, 두 번째 삽입이 대신 중복 키 오류로 실패합니다. 해결 방법은 두 경우 모두 같습니다. 키마다 소스 행 하나입니다.

"없으면 만든다"를 쓰는 자연스러운 방법은 DO NOTHING이고, id를 얻는 자연스러운 방법은 RETURNING id입니다. 그런데 이미 존재하는 행에 대해서는 아무것도 돌아오지 않습니다.

INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00)
ON CONFLICT (sku) DO NOTHING
RETURNING id;

 id
----
(0 rows)

RETURNING은 실제로 삽입되거나 업데이트된 행만 반환합니다. 항상 id를 돌려받으려면 같은 문장에서 기존 행을 읽으세요.

WITH ins AS (
  INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00)
  ON CONFLICT (sku) DO NOTHING
  RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM products WHERE sku = 'KB-01'
LIMIT 1;

 id
----
  1

빈틈이 하나 남아 있습니다. 다른 세션이 같은 SKU를 삽입하고 아직 커밋하지 않았다면 INSERT는 그 세션을 기다린 뒤 아무것도 하지 않지만, SELECT 부분은 문장이 시작될 때 찍은 스냅샷을 사용하므로 새 행을 보지 못하고, 결과는 0행이 됩니다. 빈 결과는 "다시 시도"로 처리하세요. 대안인 DO UPDATE SET sku = EXCLUDED.sku는 항상 행을 반환하지만 매번 새 행 버전을 쓰며, 이것이 5절의 문제입니다.

ON CONFLICT (column)은 "이 열에서 중복을 확인하라"는 뜻이 아닙니다. "정확히 이 열들에 대한 고유 인덱스를 찾아 사용하라"는 뜻입니다. 고유 인덱스가 없는 열을 지정하면 문장은 아무것도 하기 전에 실패합니다.

INSERT INTO products (sku, name, price) VALUES ('MS-01', 'Mouse', 19.00)
ON CONFLICT (name) DO NOTHING;

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

덜 눈에 띄는 경우는 부분 고유 인덱스입니다. 소프트 삭제되지 않은 계정 사이에서 이메일을 고유하게 유지하는 흔한 방법입니다.

CREATE TABLE accounts (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL,
  deleted_at timestamptz
);
CREATE UNIQUE INDEX accounts_live_email ON accounts (email) WHERE deleted_at IS NULL;

INSERT INTO accounts (email) VALUES ('ana@example.com')
ON CONFLICT (email) DO NOTHING;

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

인덱스는 존재하지만 deleted_at IS NULL인 행만 다루므로, 충돌 대상에서 같은 조건을 명시하지 않으면 Postgres는 그 인덱스를 사용하지 않습니다.

INSERT INTO accounts (email) VALUES ('ana@example.com')
ON CONFLICT (email) WHERE deleted_at IS NULL DO NOTHING;

INSERT 0 1

ORM이 ON CONFLICT 절을 생성한다면 그 WHERE를 출력할 수 있는지 확인하세요. ORM에 맞추려고 부분 인덱스를 일반 고유 인덱스로 바꾸지 마세요. 규칙이 바뀌어, 소프트 삭제된 계정이 이메일을 계속 차지하게 됩니다.

DO UPDATE는 모든 값이 같더라도 충돌이 있으면 언제나 행을 업데이트합니다. Postgres에서 업데이트는 새 행 버전을 쓰고, 이전 버전은 vacuum이 정리해야 하는 dead tuple이 됩니다. 현재 버전을 쓴 트랜잭션의 id인 xmin에서 이를 확인할 수 있습니다(숫자는 환경마다 다릅니다).

SELECT xmin, price FROM products WHERE sku = 'KB-01';
INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;
SELECT xmin, price FROM products WHERE sku = 'KB-01';

 xmin | price
------+-------
  742 | 45.00

 xmin | price
------+-------
  753 | 45.00

가격은 같은데 행 버전은 새것입니다. 매시간 바뀌지 않은 행 백만 개를 다시 보내는 동기화 작업이라면 시간당 dead tuple 백만 개에, WAL과 업데이트 시 실행되는 트리거까지 더해집니다. 아무것도 바꾸지 않는 업데이트는 건너뛰세요.

INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price
WHERE (products.name, products.price) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.price)
RETURNING id;

 id
----
(0 rows)

xmin은 753에 머뭅니다. NULL에서 바뀌거나 NULL로 바뀌는 경우도 변경으로 치도록 <>가 아니라 IS DISTINCT FROM을 사용하세요. 비어 있는 RETURNING에 주목하세요. WHERE가 걸러 낸 행은 잠기지만 업데이트되지 않으므로 반환되지 않습니다. 3절과 같은 상황입니다.

위의 모든 작업을 마친 뒤 테이블에는 제품 네 개가 있습니다.

SELECT id, sku FROM products ORDER BY id;

 id |  sku
----+-------
  1 | KB-01
  3 | WC-01
  5 | HS-01
  9 | MN-27

INSERT ... ON CONFLICT는 충돌을 확인하기 전에, 다음 identity 값을 포함해 삽입할 행을 만듭니다. 결국 업데이트로 끝난 업서트, 아무것도 하지 않은 DO NOTHING, 실패한 삽입 시도는 모두 id를 하나씩 소비했습니다. 4절의 ON CONFLICT (name) 오류처럼 무언가를 삽입하려 하기 전에 실패한 문장은 소비하지 않았습니다. 시퀀스 문서는 바로 이 경우를 명시합니다. WHEN MATCHED에 해당하는 MERGE는 id를 할당받지 않지만, 실패한 MERGE 삽입은 할당받습니다. 1절에서 id 4는 세션 B에게 갔습니다.

id를 개수로 취급하는 것이 없는 한 이는 무해합니다. 트래픽이 많은 업서트 경로가 32비트 integer id를 계획보다 빨리 소진한다면, 위의 테이블처럼 bigint를 사용하세요.

지금까지의 내용만 보면 MERGE가 더 위험한 선택처럼 보입니다. 하지만 다른 작업, 즉 완전한 소스를 기준으로 테이블을 맞추는 작업에는 MERGE가 더 나은 선택입니다. 공급업체가 매일 밤 전체 가격표를 보내고, 이를 스테이징 테이블에 적재한다고 해 봅시다.

CREATE TABLE price_feed (
  sku   text PRIMARY KEY,
  name  text NOT NULL,
  price numeric(10,2) NOT NULL
);
INSERT INTO price_feed VALUES
  ('KB-01', 'Keyboard',    42.00),
  ('MN-27', 'Monitor 27"', 189.00),
  ('HS-01', 'Headset',     79.00),
  ('MS-01', 'Mouse',       19.00);

MERGE 하나로 바뀐 것은 업데이트하고, 새것은 삽입하고, 공급업체가 뺀 것은 삭제하며, 각 행에 셋 중 어떤 일이 있었는지 알려 줍니다.

MERGE INTO products AS p
USING price_feed AS f
ON p.sku = f.sku
WHEN MATCHED AND (p.name, p.price) IS DISTINCT FROM (f.name, f.price) THEN
  UPDATE SET name = f.name, price = f.price
WHEN NOT MATCHED THEN
  INSERT (sku, name, price) VALUES (f.sku, f.name, f.price)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
RETURNING merge_action(), p.sku, p.price;

 merge_action |  sku  | price
--------------+-------+-------
 UPDATE       | KB-01 | 42.00
 UPDATE       | HS-01 | 79.00
 INSERT       | MS-01 | 19.00
 DELETE       | WC-01 | 59.00

MN-27은 바뀌지 않았기 때문에 출력에 없습니다. 이 문장의 세 부분은 Postgres 17에서 새로 추가되었습니다. MERGE의 RETURNING, merge_action() 함수, 그리고 WHEN NOT MATCHED BY SOURCE입니다. Postgres 16에서 MERGE ... RETURNING은 구문 오류입니다. 17 이전에는 같은 동기화에 업서트, 별도의 DELETE ... WHERE NOT EXISTS, 그리고 무엇이 바뀌었는지 보고하기 위한 별도의 기록 작업이 필요했습니다.

WHEN NOT MATCHED BY SOURCE THEN DELETE는 소스에 없는 테이블의 모든 행을 삭제합니다. 일부만 담긴 피드나, 적재에 실패한 뒤 비어 있는 스테이징 테이블을 대상으로 실행하면 유지하려던 행까지 삭제됩니다. 실행하기 전에 피드의 행 수를 확인하거나, 대상 열에 조건을 추가하세요. 예를 들면 WHEN NOT MATCHED BY SOURCE AND p.supplier = 'acme' THEN DELETE입니다.

1절의 동시성 주의 사항은 여전히 적용됩니다. 야간 동기화는 보통 그 키들에 쓰는 유일한 작업이며, 그래서 MERGE가 잘 맞습니다. 동기화가 실행되는 동안 요청 트래픽이 같은 SKU를 삽입할 수 있다면 23505에서 동기화를 재시도하세요.

1. 같은 새 키를 동시에 삽입할 수 있는 쓰기 작업: INSERT ... ON CONFLICT DO UPDATE.
2. 업데이트, 삽입, 삭제를 한 번에 처리하며 테이블을 완전한 소스와 맞추기: MERGE. 다른 무언가가 그 키들에 쓴다면 SQLSTATE 23505에서 재시도.
3. 행마다 삽입, 업데이트, 삭제 중 무엇이 일어났는지 알아야 할 때: Postgres 17의 MERGE ... RETURNING merge_action().
4. 배치: 어느 문장을 쓰든 먼저 키 기준으로 중복 제거.
5. 조회 또는 생성(get-or-create): DO NOTHING과 기존 행 읽기를 함께 쓰고, 0행은 "재시도"로 처리.
6. 바뀌지 않은 데이터를 다시 보낼 때: 업데이트에 WHERE ... IS DISTINCT FROM 추가.
7. 부분 고유 인덱스: 충돌 대상에 인덱스의 WHERE를 반복.

이 글에서 위험한 문장은 DELETE가 들어간 문장입니다. 새 MERGE를 프로덕션에 실행하기 전에 프로덕션의 복사본에서 실행해 보세요. Relyt One에서는 모든 브랜치가 공유 copy-on-write 스토리지 위에 자체 컴퓨트를 가지므로, 데이터베이스의 브랜치를 만들고, 오늘 밤 피드를 거기의 스테이징 테이블에 적재하고, MERGE를 실행하고, merge_action() 출력을 읽어 볼 수 있습니다. 카탈로그의 절반을 삭제하려 한다면 브랜치에서 알게 되고, 그 브랜치는 버리면 됩니다. Relyt One은 PostgreSQL 17을 실행하므로 RETURNING, merge_action(), WHEN NOT MATCHED BY SOURCE가 위에서 보여 준 대로 동작합니다.

무료로 시작하기