대용량 PostgreSQL 조회 성능 최적화

왜 바꿔야 했는가

콘텐츠 제작 플랫폼의 사용자별 자료가 쌓이면서 대용량 테이블에서 슬로우 쿼리가 반복됐습니다. 문항·지문·정답·해설 HTML과 검색용 메타데이터가 하나의 큰 JSONB 컬럼에 함께 저장되어 있었고, 자료가 많은 사용자는 필터 조회에 16.2초, 이미 사용한 문항을 제외하는 조회에 4.9초가 걸렸습니다.

사용자가 자료를 고르는 첫 단계에서 기다려야 했을 뿐 아니라, 사용자별 데이터가 늘수록 비용도 선형으로 커지는 구조였습니다. 단순히 인덱스를 더하는 것보다 작은 검색 값 하나를 읽을 때 왜 큰 본문까지 처리하는지부터 확인해야 했습니다.

무엇을 확인했는가

production read replica에서 자료가 많은 사용자를 기준으로 EXPLAIN ANALYZE와 buffer 사용량을 비교했습니다. 병목의 중심은 JSONB와 PostgreSQL의 TOAST 접근 방식이었습니다.

• passageId 같은 작은 값 하나를 조회해도 HTML 전체가 든 JSONB를 디토스트해야 했습니다.
• 필터 대상 행이 많은 사용자일수록 큰 JSONB를 여는 비용이 함께 증가했습니다.
• JSONB 표현식 인덱스만으로는 필요한 값을 안정적으로 Index Only Scan의 커버 범위에 넣기 어려웠습니다.
• 후속 최악 조건에서는 PostgreSQL 14가 두 IN 배열 조건을 인덱스 진입 조건으로 함께 활용하지 못해 사용자 전체 행을 훑고 있었습니다.

따라서 문제는 쿼리 호출 횟수가 아니라, 검색용 메타데이터와 큰 본문이 같은 저장 구조에 묶인 점과 실제 조건에 맞지 않는 인덱스 key 설계였습니다.

어떤 선택을 했는가

자주 검색·조인하는 네 개 값을 JSONB에서 일반 컬럼으로 분리하고, 원본 HTML은 JSONB에 그대로 유지했습니다. 여러 서비스가 같은 테이블을 쓰고 있었기 때문에 애플리케이션 한 곳에만 동기화 책임을 두지 않고 DB trigger로 신규 쓰기와 수정 시 정규 컬럼을 자동 갱신했습니다.

각 조회가 필요한 값만 인덱스에서 반환할 수 있도록 컬럼 기반 covering index를 구성했습니다. 후속 최악 조건에서는 두 배열 필터 중 하나를 key에서 INCLUDE로 옮겨, 선택도가 높은 조건으로 진입하면서도 heap과 TOAST를 읽지 않는 Index Only Scan을 유지했습니다.

어떻게 전환하고 검증했는가

nullable 컬럼을 먼저 추가해 테이블 전체 rewrite를 피하고, trigger를 적용한 뒤 기존 전체 행을 keyset pagination과 배치별 commit으로 백필했습니다. 백필은 중단 지점부터 재개할 수 있게 구성했고 2시간 7분에 완료했습니다.

인덱스는 CREATE INDEX CONCURRENTLY로 생성하고 백필 뒤 VACUUM ANALYZE를 실행했습니다. 배포 전후에는 컬럼과 JSONB 값의 정합성, Index Only Scan 여부, heap fetch와 buffer 사용량을 확인했습니다. 활성 데이터에 더 이상 의미가 없어진 필터는 운영 데이터로 조건을 확인한 뒤 관련 조회에서 제거했습니다.

이후 무엇이 달라졌는가

서로 다른 세 조회 경로를 각각 검증했습니다.

• 보유 지문 필터 조회는 16.2초에서 79ms로 줄었습니다.
• 이미 사용한 문항 제외 조회는 초기 조건에서 4.9초에서 23ms로 줄었습니다.
• 외부 지문 사용 문항 조회는 11ms로 확인했습니다.

이후 더 큰 배열 조건에서 같은 문항 제외 조회가 다시 느려지는 사례를 발견했습니다. 인덱스 key를 재설계해 최악 조건을 6.8초에서 1.5ms로, 읽기량을 약 1GB에서 7MB로 줄였습니다. 이 수치들은 하나의 쿼리가 연속해서 개선된 단일 체인이 아니라, 서로 다른 조회 경로와 후속 최악 조건을 각각 측정한 결과입니다.

다시 한다면

큰 본문과 검색·조인에 자주 쓰는 메타데이터를 초기 모델링 단계에서 분리하겠습니다. 대표 사용자 하나만 볼 것이 아니라 데이터 보유량과 배열 조건이 큰 사용자까지 성능 회귀 조건에 포함하고, 응답 시간뿐 아니라 heap fetch와 buffer 읽기량도 함께 추적하겠습니다.

또한 인덱스가 있다는 사실보다 실제 PostgreSQL 버전에서 쿼리 조건이 어떤 scankey로 사용되는지를 먼저 확인하겠습니다. 데이터 모델, 인덱스와 실행계획을 한 세트로 검증하는 원칙은 유지할 것입니다.