PostgreSQL의 EXCLUDED 가상 테이블은 ON CONFLICT 작업 중에 삽입이 제안된 행을 나타냅니다. 역사적으로, MySQL 또는 Oracle 환경에서 마이그레이션하는 개발자들은 직접적인 동등 비교 (=)가 업sert 패턴 내의 값 변경을 감지하는 데 충분하다고 가정하는 경우가 많습니다. 하지만 SQL 표준의 삼중 값 논리는 NULL이 알려지지 않은 상태를 나타낸다고 규정하고, 따라서 NULL = NULL은 NULL (알 수 없음)으로 평가되며, 이것은 TRUE가 아닙니다.
이로 인해 충돌 해결 조항이 WHERE EXCLUDED.phone != users.phone와 같은 WHERE 절을 추가하여 업데이트를 최적화하려고 할 때 중대한 문제가 발생합니다. 기존 행과 제안된 행 모두 전화 열에 대해 NULL을 포함하고 있다면, 비교 결과는 NULL로 반환되어 WHERE 절이 실패하게 됩니다. 결과적으로, 데이터베이스는 비즈니스 논리의 맥락에서 값이 실제로 다를 수 있음에도 불구하고 업데이트를 건너뜁니다. 또는 새로운 데이터의 NULL과 이전 데이터의 NULL을 구별할 수 없습니다.
해결책은 IS DISTINCT FROM 연산자를 사용하는 것이며, 이는 NULL을 비교 가능한 값으로 간주합니다. WHERE EXCLUDED.column IS DISTINCT FROM table.column으로 업데이트 절을 구성하면, 두 값이 모두 NULL일 때 비교 결과는 FALSE (변경 없음)를 반환하고 하나는 NULL이고 다른 하나는 그렇지 않을 경우 TRUE를 반환합니다. 이는 결정론적 동작을 보장하며 불필요한 쓰기를 방지합니다.
INSERT INTO patient_records (clinic_id, external_id, phone, updated_at) VALUES (101, 'P-2024-001', NULL, NOW()) ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = COALESCE(EXCLUDED.phone, patient_records.phone), updated_at = EXCLUDED.updated_at WHERE EXCLUDED.phone IS DISTINCT FROM patient_records.phone;
한 병원 네트워크는 50개의 외부 클리닉에서 매일 환자 수집 데이터를 중앙 PostgreSQL 데이터 웨어하우스로 동기화해야 했습니다. 각 클리닉은 누락된 환자 전화번호가 빈 문자열로 나타나는 CSV 파일을 내보냈으며, COPY 명령어는 이를 수집하는 동안 NULL로 변환했습니다. 기존의 Python ETL 스크립트는 SQLAlchemy를 사용하여 ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = EXCLUDED.phone로 대량 업sert를 실행했습니다.
문제는 클리닉 직원들이 중앙 시스템에 직접 입력한 유효한 전화번호가 밤마다 동기화 후 사라지는 현상이 발생했다는 것입니다. 조사가 진행됨에 따라 외부 피드가 NULL (알 수 없는 전화 번호를 나타냄)을 보냈을 때, SET 절이 조건 없이 실행되어 기존의 유효한 번호가 덮어쓰기 되는 것을 확인했습니다. 단순한 필터 WHERE EXCLUDED.phone != patient_records.phone를 추가했지만, 두 값이 모두 NULL일 때 비교가 NULL (알 수 없음)으로 반환되어 업데이트가 잘못 넘어갔으며, 새로운 값이 NULL이고 이전 값이 NULL이 아닐 때는 로직이 서로 다른 PostgreSQL 마이너 버전 간에 일관되게 작동하지 않았습니다.
세 가지 해결책이 평가되었습니다.
첫 번째 접근 방식은 SET 절에 COALESCE를 독점적으로 사용하는 것이었습니다: SET phone = COALESCE(EXCLUDED.phone, patient_records.phone). 이는 NULL로 덮어쓰는 것을 방지했지만, 충돌이 발생할 때마다 업데이트를 강제하여 phone 열의 비용이 많이 드는 B-Tree 인덱스 재구성을 촉발하고 "no-op" 변경 사항이 합법적인 수정으로 기록되는 감사 트리거를 발동시켰습니다. 이로 인해 WAL (Write-Ahead Log) 트래픽이 300% 증가하여 복제 지연이 발생하고 디스크 I/O가 포화 상태에 이르게 되었습니다.
두 번째 솔루션은 NULL을 처리하기 위해 명시적인 부울 논리를 시도했습니다: WHERE (EXCLUDED.phone != patient_records.phone) OR (EXCLUDED.phone IS NULL AND patient_records.phone IS NOT NULL). 논리적으로는 올바르지만, 이 장황한 패턴은 15개의 nullable 열에 걸쳐 신중한 유지보수를 요구하였고 쿼리 최적화기를 혼란스럽게 했습니다. 플래너는 인덱스 스캔을 포기하고 2000만 행의 테이블에서 순차 스캔을 선택하였고, 이로 인해 ETL 작업은 6시간의 유지보수 시간을 초과하였습니다.
세 번째 솔루션은 WHERE 절의 모든 nullable 열에 대해 IS DISTINCT FROM을 구현했습니다. 이는 NULL 전환을 포함하여 진정한 데이터 변경 사항을 올바르게 식별하는 간결하고 Sargable 한 술어를 제공했습니다. 이는 필요한 경우에만 업데이트를 허용하여 불필요한 트리거 실행 및 WAL 생성을 제거하고 일관된 쿼리 계획을 유지하게 되었습니다.
팀은 핵심 연락처 필드에 대해 세 번째 솔루션을 선택하고, 덮어쓰기 보호가 성능보다 더 중요하게 여겨지는 비핵심 메타 데이터에 대해 첫 번째 솔루션을 사용했습니다. 그 결과는 극적이었습니다: 동기화 작업 시간이 45분에서 12분으로 단축되었고, 복제 지연은 5초 이하로 안정화되었으며, "사라지는 전화번호" 사건은 배포 첫 주 내내 완전히 중단되었습니다.
왜 WHERE EXCLUDED.column != table.column이 두 값이 모두 NULL일 때 행을 건너 뛰고, 이것이 PostgreSQL의 업데이트 메커니즘과 어떻게 상호작용합니까?
많은 후보자들은 두 개의 NULL이 같지 않다면, 비교가 TRUE를 반환하고 업데이트를 허용해야 한다고 가정합니다. 그러나 SQL은 삼중 값 논리를 사용합니다: NULL은 알려지지 않은 값을 나타냅니다. NULL과의 모든 비교 ()NULL = NULL 또는 NULL != NULL)은 NULL (알 수 없음)을 반환할 뿐 조건부 불리언 TRUE 또는 FALSE가 아닙니다. PostgreSQL의 WHERE 절에서는 TRUE로 평가되는 행만 진행되는데, NULL은 FALSE로 처리됩니다. 따라서 두 개의 NULL 전화번호를 비교할 때 결과는 NULL이 되고, 업데이트는 누락되며 시스템은 변경이 필요 없다고 잘못 가정하게 됩니다. IS DISTINCT FROM은 NULL 대 NULL에서 FALSE를 반환하여 그들이 동일하다는 것을 올바르게 나타내고, 필요한 경우에만 업데이트를 건너뛰게 합니다. 반면 하나의 값이 NULL이고 다른 하나는 그렇지 않을 경우 TRUE를 반환합니다.
여러 열의 고유 제약 조건에서 열의 순서가 ON CONFLICT 해결 성능에 미치는 영향은 무엇이며, 충돌 대상이 인덱스 정의와 정확히 일치하지 않을 경우 어떻게 됩니까?
후보자들은 종종 PostgreSQL이 고유 인덱스 정의에 따라 충돌 대상 ( ON CONFLICT (...) 내의 열)을 정확하게 일치시켜야 한다는 사실을 간과합니다. 열 순서 및 함수 표현이 포함됩니다. (clinic_id, external_id)에 대한 고유 인덱스가 존재하지만 쿼리에서 ON CONFLICT (external_id, clinic_id)로 지정하면, 플래너는 인덱스를 유추하는 데 실패할 수 있으며 "충돌 사양에 일치하는 고유 또는 배제 제약 조건이 없습니다"라는 오류가 발생합니다. 성공하더라도, 순서가 맞지 않으면 최적화기가 인덱스 전용 스캔을 사용하지 못해 힙 가져오기를 강요하고 I/O 비용이 크게 증가할 수 있습니다.
SET 절에서 COALESCE(EXCLUDED.column, table.column) 사용과 WHERE EXCLUDED.column IS DISTINCT FROM table.column 사용의 차이는 무엇입니까, 특히 트리거 실행 및 행 버전 관리와 관련하여?
SET 절에 COALESCE를 사용하면 조건 없이 행에 값을 기록합니다 (새 데이터 또는 보존된 이전 데이터). 이 작업은 새 행 버전 (CTID)을 생성하고 WAL에 기록하며, 최종 값이 이전 상태와 동일하더라도 테이블과 관련된 모든 BEFORE 및 AFTER 트리거를 발생시킵니다. 이는 감사 테이블에 "노오프"를 생성하고 복제 부하를 증가시킵니다. 반면 WHERE 절에 IS DISTINCT FROM을 사용하면 실제로 변경 사항이 없는 경우 아예 행 수정을 방지합니다. 새로운 튜플 버전이 생성되지 않으며, 트리거가 발동되지 않고 WAL 생성이 피원서 됩니다. 이러한 구분은 감사 로깅이나 외래 키 전파가 있는 고량의 시스템에서 매우 중요하며, "노오프" 업데이트가 상당한 오버헤드를 생성합니다.