부울 플래그를 사용하여 소프트 삭제 패턴을 구현할 때, 복합 인덱스에 이 플래그를 추가하는 것이 때때로 부분 인덱스 전략에 비해 활성 기록 쿼리의 성능을 저하시킬 수 있는 이유는 무엇이며, 이러한 접근 방식 간에 쿼리 플래너의 선택성 추정이 어떻게 다른가요?

질문에 대한 답변

질문의 역사

소프트 삭제 패턴은 감사 추적 및 데이터 복구를 위해 하드 삭제의 대안으로 등장했습니다. 초기 구현은 간단한 부울 플래그 또는 타임스탬프 열을 사용했으나, 이러한 플래그를 표준 B-tree 인덱스에 통합할 때 성능 저하를 겪는 개발자들이 많았습니다. 이 문제는 2000년대 중반에 PostgreSQL의 부분 인덱스와 SQL Server의 필터링된 인덱스의 광범위한 채택과 함께 두드러지게 나타났습니다. 활성 레코드만 인덱싱할 수 있게 되었기 때문에, 전체 복합 인덱스와 부분 인덱싱 전략을 비교할 때 선택성 추정의 이해가 중요해졌습니다.

문제

소프트 삭제 플래그(예: is_deleted)가 (is_deleted, user_id, created_at)와 같은 복합 인덱스에 추가될 때, 데이터베이스 최적화 프로그램이 WHERE is_deleted = false로 필터링하는 쿼리에 대한 행 선택성을 잘못 계산할 수 있습니다. 90%의 행이 활성 상태라면, 최적화 프로그램은 인덱스 스캔 대신 순차 스캔을 선택할 수 있습니다. 또는 분포가 비틀어져 있다면 인덱스를 부적절하게 선호할 수 있습니다. 부분 인덱스(WHERE is_deleted = false)는 활성 행만 저장하여 높은 선택성을 보장하지만, 표준 복합 인덱스는 모든 행을 저장하여 인덱스 팽창과 통계가 소프트 삭제 분포를 정확히 반영하지 않을 때 모호한 카디널리티 추정을 초래합니다.

해결책

소프트 삭제된 행을 완전히 제외하는 부분 인덱스(예: PostgreSQL) 또는 필터링된 인덱스(예: SQL Server)를 구현하고 필요할 경우 삭제된 데이터에 대한 별도의 인덱스를 추가합니다. MySQL과 같은 부분 인덱싱이 없는 데이터베이스에서는 활성 데이터셋이 작을 경우에만 소프트 삭제 플래그를 선두 열로 사용하는 복합 인덱스를 사용하고, 그렇지 않으면 삭제 상태에 따라 테이블을 파티셔닝합니다. 대량 삭제 후 테이블 통계를 명시적으로 분석하여 오래된 히스토그램을 방지합니다. 활성 레코드를 쿼리할 때는 최적화 프로그램이 인덱스 적용 가능성을 인식하도록 하기 위해 부분 인덱스 정의에서 정확한 조건(WHERE is_deleted = false)을 사용합니다.

코드 예제

-- PostgreSQL: 활성 레코드만 위한 부분 인덱스 생성
CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE is_deleted = false;

-- SQL Server: 필터링된 인덱스와 동등
CREATE INDEX IX_Active_Users_Email 
ON Users(Email) 
WHERE IsDeleted = 0;

-- 부분 인덱스를 활용한 쿼리
SELECT * FROM users 
WHERE email = 'alice@example.com' 
AND is_deleted = false;

실생활의 상황

10백만 사용자 기록을 관리하는 SaaS 플랫폼은 생성일 기준으로 활성 사용자를 필터링할 때 관리자 대시보드에서 심각한 느려짐을 경험했습니다. 처음에는 WHERE is_deleted = false ORDER BY created_at 쿼리를 가속화할 것이라고 가정하고 PostgreSQL에서 (is_deleted, created_at) 복합 인덱스를 사용했습니다. 그러나 데이터셋이 80%의 소프트 삭제된 기록이 되었을 때, 쿼리의 실행 시간이 8-12초로 증가했습니다. 그 이유는 플래너가 부풀려진 인덱스를 스캔하는 비용을 과소평가했기 때문입니다.

해결책 A: 복합 인덱스를 유지하고 인덱스 사용을 강제하는 힌트를 적용합니다. 이 접근 방식은 SET enable_seqscan = off 또는 쿼리 계획 힌트를 사용하여 인덱스 활용을 강제했습니다. 특정 쿼리를 일시적으로 개선했으나, 유지 관리 부채를 발생시키고 데이터 분포가 변할 때 다른 접근 패턴에 대해 종종 비효율적인 계획을 강제했습니다. 이 해결책은 이전의 과도한 복합 인덱스 때문에 증가한 스토리지와 VACUUM 오버헤드를 유발하는 인덱스 부풀림의 근본적인 문제를 해결하지 못했습니다.

해결책 B: 활성 및 삭제된 레코드에 대한 별도의 부분 인덱스를 생성합니다. CREATE INDEX idx_active_created ON users(created_at) WHERE is_deleted = false를 구현하여 인덱스 크기를 80% 감소시키고 플래너가 2백만 활성 행과 8백만 삭제 행을 정확하게 추정할 수 있도록 했습니다. 쿼리 실행 시간이 40ms로 줄어들었지만, is_deleted = false 조건이 명시적이며 함수나 추상화된 뷰 뒤에 감춰지지 않도록 모든 애플리케이션 쿼리를 리팩토링해야 했습니다.

팀은 이러한 유지보수 힌트 없이 지속 가능한 성능을 제공하는 해결책 B를 선택했습니다. 결과적으로 쿼리 지연 시간이 95% 감소하고 이전의 과도한 복합 인덱스로 인한 주기적인 VACUUM 팽창 문제가 해결되었습니다. 모니터링한 결과, 대시보드의 주요 사용 사례에 대한 일관된 서브초 응답 시간이 확인되었습니다.

후보자들이 자주 놓치는 점

NULL 값이 소프트 삭제 타임스탬프 열(활성은 NULL, 삭제된 경우는 타임스탬프 사용)에서 존재하면 부분 인덱스 사용에 대해 부울 플래그 접근 방식에 비해 어떤 영향을 미칠까요?

널이 허용되는 deleted_at 타임스탬프를 사용할 경우, WHERE deleted_at IS NULL과 같은 부분 인덱스는 PostgreSQL의 NULL 인덱스 처리와 관련된 문제를 겪게 됩니다. 벗어난 부울 플래그는 = false 조건이 명시적이며 sargable인 반면, IS NULL 조건은 플래너가 인덱스 적용 가능성을 인식해야 하며, 쿼리에서 매개변수화된 문을 사용할 경우 플래너가 매개변수가 NULL이 될 것이라는 것을 증명할 수 없어 실패할 수 있습니다. 또한, deleted_at = CURRENT_TIMESTAMP를 설정하는 업데이트는 활성 레코드의 부분 인덱스에서 행이 제거됨에 따라 인덱스 부풀림을 유발합니다. 반면 부울 플래그 업데이트는 비트를 뒤집지만 전체 복합 인덱스 내에서 유지됩니다. Nullable 접근은 삭제 상태 변경을 처리하기 위해 더 빈번한 ANALYZE 호출 및 인덱스 채우기 요인에 대한 세심한 고려를 요구합니다.

소프트 삭제 열을 포함하는 커버링 인덱스가 낮은 삭제 빈도로 인한 예상보다 느린 쓰기를 초래할 수 있는 이유는 무엇인가요?

커버링 인덱스(예: PostgreSQL 11+에서 INCLUDE 절 사용)는 테이블 조회를 피하기 위해 is_deleted를 덧붙여도 실제로 쓰기 성능이 저하됩니다. 왜냐하면 모든 소프트 삭제 작업(즉, UPDATE)이 여러 인덱스 구조를 수정해야 하기 때문입니다. 사용자가 소프트 삭제되면, 데이터베이스는 활성 부분 인덱스에서 오래된 인덱스 항목을 죽은 것으로 표시하고, 삭제된 레코드 인덱스에 새로운 항목을 삽입하며, 커버링 인덱스의 힙 포인터를 업데이트해야 합니다. 후보자들은 부분 인덱스가 이 변화 과정을 고립시키는 점을 놓칩니다. 즉, 활성 또는 삭제 상태에 대한 특정 부분 인덱스만 수정되지만, 기본 테이블에 대한 커버링 인덱스는 소프트 삭제 상태에 관계없이 기본 인덱스 구조를 업데이트해야 하므로 트랜잭션 처리량에 영향을 주는 쓰기 증폭이 발생합니다.

쿼리 최적화 프로그램이 삭제된 데이터를 위한 부분 인덱스를 무시하는 경우는 언제인가요?

부분 인덱스가 감사 쿼리를 위한 WHERE is_deleted = true로 정의되었으나, 애플리케이션이 활성 및 삭제 쿼리를 위해 $1 매개변수를 사용하는 준비된 문을 사용하는 경우, PostgreSQL는 특정 true 케이스에 대한 부분 인덱스를 인식하지 못하는 일반 계획을 캐시할 수 있습니다. 이는 준비된 문이 매개변수 값이 바인딩되기 전에 계획을 생성하기 때문이며, 최적화 프로그램이 $1 = true가 항상 인덱스 술책을 일치시킬 수 있다는 것을 증명할 수 없습니다. 후보자들은 다이나믹 SQL이나 재컴파일 힌트(예: SQL Server의 OPTION (RECOMPILE), PostgreSQL의 리터럴 값으로 실행)가 필요하다는 점을 놓칩니다. 이렇게 해야만 플래너가 구체적인 값을 보고 이를 부분 인덱스 술책과 일치시킬 수 있으며, 매개변수 값의 불확실성으로 인해 기본적으로 순차 스캔으로 이어지는 일반 계획에 의존하지 않도록 합니다.