업서트가 업데이트하지 않아도 쓰기가 발생하는 경우: 대규모 Postgres 성능 디버깅 (새 탭에서 열림)
데이터독(Datadog)은 수백만 개의 일시적인 호스트 메타데이터를 효율적으로 관리하기 위해 새로운 업서트(Upsert) 쿼리를 도입했으나, 예상과 달리 디스크 쓰기와 WAL(Write-Ahead Logging) 동기화가 급증하는 문제에 직면했습니다. 조사 결과, PostgreSQL의 `ON CONFLICT DO UPDATE` 구문은 `WHERE` 조건에 의해 실제 업데이트가 수행되지 않더라도 행 잠금을 위해 WAL 레코드를 생성한다는 점이 원인이었습니다. 이 글은 고성능 시스템에서 단순한 쿼리 최적화 가정이 어떻게 물리적 성능 병목으로 이어질 수 있는지, 그리고 이를 어떻게 진단했는지 설명합니다. ### 효율적인 업서트 테이블 설계 * **업데이트 비용 절감:** PostgreSQL은 MVCC(MultiVersion Concurrency Control)를 사용하므로 업데이트 시마다 새로운 행 버전이 생성됩니다. 메타데이터 테이블의 비대화를 막기 위해 `last_ingested` 필드를 별도의 전용 테이블로 분리하여 쓰기 데이터양을 최소화했습니다. * **HOT(Heap-Only Tuples) 업데이트 활용:** 인덱스가 있는 컬럼을 수정하면 인덱스 페이지도 함께 수정되어야 합니다. 이를 피하기 위해 `last_ingested` 컬럼에는 인덱스를 생성하지 않았으며, `fillfactor`를 80%로 설정하여 페이지 내 여유 공간을 확보함으로써 HOT 업데이트가 가능하도록 설계했습니다. * **업데이트 빈도 제한:** 7일간 데이터가 없는 호스트를 식별하는 것이 목적이므로 1일 단위의 정밀도로 충분했습니다. 따라서 `WHERE` 절을 사용하여 마지막 업데이트로부터 24시간이 지난 경우에만 실제 쓰기가 발생하도록 쿼리를 구성했습니다. ### 예상치 못한 성능 지표의 변화 * **I/O 및 WAL 동기화 급증:** 쿼리 배포 후 업데이트 속도는 예상대로 낮게 유지되었으나, 디스크 쓰기 IOPS는 2배, WAL sync 횟수는 4배나 증가했습니다. * **쓰기 예산 소모:** PostgreSQL 클러스터는 단일 라이터(writer) 구조이므로 처리 가능한 쓰기 작업량에 한계가 있습니다. 실제 데이터 변경이 없는 'No-op' 쿼리들이 이 한정된 자원을 과도하게 소모하는 문제가 발생했습니다. * **내부 동작의 모순:** `INSERT ... ON CONFLICT DO UPDATE` 문에서 `WHERE` 조건이 거짓(false)이 되어 행이 업데이트되지 않더라도, 데이터베이스는 동시성 제어를 위해 해당 행에 락(lock)을 겁니다. 이 잠금 행위 자체가 WAL에 기록되면서 물리적인 쓰기 부하를 유발한 것입니다. ### pg_walinspect를 이용한 심층 진단 * **WAL 레코드 조사:** Postgres 15에서 도입된 `pg_walinspect` 확장 프로그램을 사용하여 실제 WAL에 어떤 데이터가 기록되고 있는지 분석했습니다. * **진단 도구 설정:** `pg_get_wal_records_info` 함수를 호출하여 특정 LSN(Log Sequence Number) 범위 내의 레코드를 확인했습니다. 이를 통해 쿼리 실행 시 업데이트가 발생하지 않음에도 불구하고 WAL 레코드가 생성되는 과정을 구체적으로 확인했습니다. * **원인 규명:** 분석 결과, `ON CONFLICT` 상황에서 잠금 처리가 WAL에 기록되는 것을 확인했으며, 이것이 전체적인 디스크 I/O 상승의 주범임을 입증했습니다. ### 실용적인 제언 PostgreSQL에서 고빈도 업서트를 설계할 때는 `WHERE` 조건문이 애플리케이션 레벨의 논리적 업데이트는 막아줄 수 있지만, 데이터베이스 엔진 레벨의 물리적 쓰기(WAL)까지 완전히 차단하지 못할 수 있음을 유의해야 합니다. 극도로 높은 처리량이 요구되는 환경에서는 `pg_walinspect`와 같은 도구를 사용하여 쿼리의 물리적 오버헤드를 사전에 검증하고, 불필요한 잠금 발생을 줄이는 방향으로 쿼리를 재작성하는 과정이 필수적입니다.