질문의 역사
이러한 모호성은 PostgreSQL 9.5에서 ON CONFLICT 절을 통해 기본 UPSERT 기능이 도입되면서 발생했습니다. 이 릴리스 이전에는 개발자들이 복잡한 PL/pgSQL 루프나 오류가 발생하기 쉬운 애플리케이션 측 논리를 사용하여 무효화된 삽입을 구현했습니다. RETURNING 절은 오랫동안 UUID 또는 순차 ID를 검색하는 데 필수적이었지만, UPSERT의 이중 경로 실행 모델과의 상호작용—명령문이 INSERT 또는 UPDATE 결과를 초래할 수 있는 경우—은 실제로 어떤 행 버전이 반환되는지를 혼란스럽게 만드는 미세한 의미의 격차를 만들어냈습니다.
문제
INSERT ... ON CONFLICT ... DO UPDATE 문이 고유 위반을 만나면 기존 행을 업데이트하는 방향으로 전환됩니다. 그 후 RETURNING 절이 해당 행의 최종 영구 상태를 참조합니다. 그러나 애플리케이션 논리가 created_at 타임스탬프, 기본 표현식 또는 애플리케이션에서 계산된 값과 같이 시도한 삽입을 위해 생성된 값에 의존하는 경우, 명령문은 대신에 기존 행의 오래된 데이터를 반환합니다. 이 조용한 대체는 캐시 비동기화, 감사 추적 손상 및 다운스트림 시스템이 시간적으로 불일치하는 메타데이터를 수신하는 미세한 경쟁 조건을 초래합니다.
해결책
EXCLUDED 가상 테이블은 충돌을 유발하는 제안된 삽입 값에 대한 창을 제공합니다. RETURNING 절이나 UPDATE 집합 목록 내에서 EXCLUDED.column_name을 명시적으로 참조하면, 어떤 실행 경로가 선택되었는지에 관계없이 의도한 새 데이터에 대한 접근을 보장합니다.
INSERT INTO user_sessions (user_id, login_count, last_seen, session_token) VALUES (1001, 1, NOW(), gen_random_uuid()) ON CONFLICT (user_id) DO UPDATE SET login_count = user_sessions.login_count + 1, last_seen = EXCLUDED.last_seen, session_token = EXCLUDED.session_token RETURNING session_id, user_id, CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation_type, session_token, EXCLUDED.last_seen AS intended_timestamp;
이 패턴에서 EXCLUDED.last_seen 및 EXCLUDED.session_token은 데이터베이스가 대신 업데이트를 수행할 때에도 애플리케이션이 시도된 삽입에서 새 값을 받도록 보장합니다.
동시 로열티 포인트 적립
고빈도 마이크로 트랜잭션을 처리하는 핀테크 플랫폼은 유령 보상 계산 문제에 직면했습니다. 두 개의 병렬 요청이 동시에 같은 사용자 계정에 포인트를 적립하려고 할 때, PostgreSQL 데이터베이스는 원자성을 정확하게 유지했지만, Redis 캐시 레이어는 RETURNING 절에서 오래된 updated_at 타임스탬프를 받았습니다. 이로 인해 캐시는 유효한 포인트 증가를 구식으로 거부하여 수익 유출 및 보상 누락에 대한 고객 불만을 초래했습니다.
해결책 A: Redis를 이용한 분산 락
엔지니어링 팀은 처음에 데이터베이스 트랜잭션을 실행하기 전에 Redis에서 분산 잠금을 획득할 것을 제안했습니다. 이 접근 방식은 충돌하는 작업을 직렬화하고 순차적 일관성을 보장할 수 있었습니다. 그러나 이는 단일 실패 지점을 도입하고 요청당 12-18ms의 네트워크 지연을 추가했으며, 잠금을 획득한 후 트랜잭션이 중단될 때 복잡한 교착 상태를 발생시켰습니다. 잠금 관리의 운영 오버헤드와 연쇄 실패의 가능성은 이 아키텍처를 대규모에서 지속 가능하지 않게 만들었습니다.
해결책 B: 애플리케이션 측의 읽기-수정-쓰기
또 다른 제안은 먼저 SELECT로 기록 존재를 쿼리한 후 애플리케이션 코드에서 INSERT 또는 UPDATE를 결정하는 것이었습니다. 개념적으로 간단해 보이지만, 이 패턴은 READ COMMITTED 격리로 인해 검사와 작성 사이에서 다시 읽을 수 없는 읽기를 허용하는 동시 부하 하에서 끔찍하게 실패합니다. 경쟁 조건을 방지하기 위해 SERIALIZABLE 격리를 구현하는 것은 지나치게 많은 직렬화 실패와 재시도 문제를 야기했으며, 명시적인 테이블 잠금은 수용할 수 없는 수준으로 처리를 제한했습니다.
해결책 C: 적절한 EXCLUDED 활용
선택한 접근 방식은 RETURNING 절에서 모든 가변 값에 대해 EXCLUDED를 활용하도록 쿼리를 재구성했습니다. EXCLUDED.points와 EXCLUDED.calculated_at을 참조함으로써, 애플리케이션은 작업이 새 행을 생성했든 업데이트됐든 관계없이 삽입 시도의 의도된 메타데이터를 일관되게 받았습니다.
선택한 솔루션과 결과
팀은 보상 마이크로서비스 전반에 걸쳐 솔루션 C를 구현했습니다. 이를 통해 네트워크 홉을 추가하거나 격리 수준을 저하시키지 않고 캐시 불일치 문제를 제거했습니다. 포인트 적립 정확도는 99.99%로 향상되었고, 쿼리 왕복 감소로 인해 데이터베이스 CPU 사용량이 35% 감소했으며, 시스템은 수동 개입 없이 블랙 프라이데이 트래픽 급증을 성공적으로 처리했습니다.
여러 인덱스가 테이블에 존재할 때, PostgreSQL은 충돌 감지를 위해 어떤 고유 인덱스를 선택합니까?
PostgreSQL은 ON CONFLICT 절에서 명시적인 중재자 지정을 요구합니다. ON CONFLICT (column_list)를 작성할 때, 플래너는 제공된 목록과 정확히 일치하는 인덱스 열이 있는 고유 인덱스를 선택합니다. 동일한 열에 대해 여러 인덱스가 존재하는 경우, 먼저 생성된 것을 선택합니다. 부분 고유 인덱스( WHERE 절이 있는 인덱스) 또는 표현식 인덱스를 위해서는 ON CONFLICT ON CONSTRAINT constraint_name 구문을 사용해야 합니다. 그렇지 않으면 엔진은 중재 인덱스를 추론할 수 없다는 오류를 발생시킵니다. 후보자들은 종종 데이터베이스가 "가장 선택적인" 인덱스를 자동으로 선택한다고 가정하거나 함수 인덱스가 명시적인 제약 조건 이름을 요구한다는 것을 간과합니다.
여러 트랜잭션이 같은 키에 충돌할 때, READ COMMITTED 격리 하에서 UPSERT 문이 업데이트를 조용히 잃는 이유는 무엇입니까?
이는 UPDATE 절의 재평가 동작 때문에 발생합니다. 트랜잭션 A가 행을 삽입하고 커밋하면, 행 잠금을 기다리는 트랜잭션 B는 새로 가시화된 행에 대해 자신의 UPDATE 논리를 다시 실행합니다. UPDATE 논리가 절대 절 assignment(예: SET balance = 100)를 사용하는 경우 Transaction B는 Transaction A의 변경 사항을 완전히 덮어쓰게 됩니다. 많은 후보자들은 UPSERT가 자동으로 병합되거나 누적된다고 잘못 가정하고 DO UPDATE 절이 EXCLUDED 값을 명시적으로 처리해야 한다는 것을 인식하지 못합니다.
xmax = 0과 xmax IS NULL을 체크하여 UPSERT가 삽입을 수행했는지 여부를 결정하는 데 있어 정확한 차이점은 무엇이며, 왜 이 구별이 HOT 업데이트에 중요합니까?
PostgreSQL에서 xmax는 삭제 또는 업데이트 트랜잭션의 트랜잭션 ID를 저장합니다. 새로 삽입된 행의 경우, xmax는 0으로 초기화되며, 결코 NULL이 아닙니다. 후보자들은 종종 삽입을 감지하기 위해 xmax IS NULL을 잘못 검사하여 항상 false를 반환합니다. xmax = 0 검사는 삽입과 업데이트를 신뢰성 있게 식별합니다. 이 차이는 HOT (Heap Only Tuple) 업데이트와 관련하여 중요해지며, 여기서 PostgreSQL은 성능을 최적화하기 위해 인덱스를 수정하지 않고 같은 페이지에서 행을 제자리에서 업데이트합니다. xmax가 행이 촉발되었음을 정확하게 나타내지만, 0이 "이전 업데이트 없음"을 나타내고 비0이 버전 관리를 나타낸다는 것을 이해하는 것은 행 생성 번호를 계산하거나 출생과 변이를 구분해야 하는 커스텀 변경 데이터 캡처 로직을 구현할 때 논리적 오류를 방지합니다.