PostgreSQL 인덱스 블로트로 느려진 조회를 되돌리는 복구 플레이북

월말 정산 주간에 검색 API p95가 180ms에서 1.4초까지 튄 적이 있었습니다. 처음엔 애플리케이션 배포를 의심했지만 배포 이력은 없었고, 커넥션 풀도 안정적이었습니다. 로그를 뒤져 보니 같은 쿼리인데 실행 시간이 시간대별로 크게 흔들렸고, 특히 업데이트가 많은 상품 테이블에서 현상이 집중됐습니다. 당시 팀 채널에서는 "일단 인스턴스 스펙을 올리자"는 의견이 빠르게 나왔지만, 온콜 엔지니어가 스케일업 전에 pg_stat_user_indexes와 pgstattuple 확인을 제안하면서 방향이 바뀌었습니다. 실제로 핵심 보조 인덱스 하나의 블로트 비율이 38%를 넘었고, 인덱스 스캔 블록 읽기가 평소의 2배 가까이 늘어 있었습니다. 이 장면에서 배운 점은 단순한 성능 저하처럼 보이는 사건도, 쓰기 패턴과 인덱스 유지비가 겹치면 인프라 증설보다 데이터 구조 복구가 먼저라는 사실입니다.

판단 기준은 네 가지로 고정하면 회의가 짧아집니다. 첫째, 슬로우쿼리 상위 10개가 특정 테이블에 편중되는지 확인합니다. 둘째, n_dead_tup 증가 속도와 autovacuum 실행 주기가 최근 24시간에 비해 벌어졌는지 봅니다. 셋째, 동일 쿼리의 실행계획에서 Bitmap Heap Scan과 Index Scan 비용이 비정상적으로 커졌는지 비교합니다. 넷째, 인덱스 크기 대비 실제 유효 데이터 비율을 점검해 재생성이 이득인지 계산합니다. 우리 팀은 이 네 가지가 3개 이상 맞으면 "쿼리 튜닝 먼저"가 아니라 "인덱스 복구 우선"으로 분류합니다. 이전에는 쿼리 힌트나 조건절 변경으로 우회하다가 일주일 뒤 다시 느려지는 일이 반복됐는데, 원인 분류 규칙을 만든 뒤부터는 같은 장애가 길게 끌리지 않았습니다. 핵심은 증상 완화와 원인 제거를 분리해서 보는 것입니다.

실행 절차는 준비, 제한 복구, 확장 적용, 검증 종료 순서로 운영합니다. 준비 단계에서 영향 테이블의 쓰기 TPS와 배치 시간창을 먼저 고정하고, REINDEX CONCURRENTLY 대상 인덱스를 크기와 조회 기여도로 정렬합니다. 제한 복구 단계에서는 상위 1~2개 인덱스만 먼저 재생성하고 10분 단위로 쿼리 p95, lock wait, replication lag를 확인합니다. 여기서 숫자가 안정되면 확장 적용 단계로 넘어가 같은 패턴의 인덱스를 순차 실행합니다. 한 번은 새벽에 전체 인덱스를 동시에 돌렸다가 체크포인트 I/O가 치솟아 쓰기 지연이 더 커진 적이 있어, 지금은 "동시 1개" 원칙을 강제합니다. 또한 복구 중에는 autovacuum 관련 파라미터를 즉시 크게 바꾸지 않고, 먼저 인덱스 재생성 효과를 확인한 뒤 필요한 항목만 조정합니다. 파라미터와 구조 변경을 한꺼번에 하면 사후 분석에서 무엇이 효과였는지 남지 않기 때문입니다.

운영 체크리스트는 복구 이후가 더 중요합니다. 배포 파이프라인에 대량 UPDATE/DELETE 작업이 포함될 때는 사전 점검으로 예상 dead tuple 증가량을 기록하고, 특정 임계치를 넘기면 배치를 분할 실행하도록 룰을 둡니다. 관측 지표는 평균 응답시간 하나로 끝내지 말고, 최소한 API p95, 인덱스 스캔 블록 읽기, n_dead_tup 추세, autovacuum 지연 시간을 같은 대시보드에서 묶어 보세요. 그리고 장애 회고 문서에는 "왜 REINDEX를 선택했는지"와 "왜 다른 선택지를 미뤘는지"를 함께 남겨야 다음 온콜이 빠르게 재현할 수 있습니다. 우리 팀은 이 기록을 남긴 뒤, 비슷한 징후가 다시 왔을 때 20분 안에 대상 인덱스를 특정하고 서비스 지연을 점심 전으로 복구했습니다. 결국 PostgreSQL 성능 운영은 쿼리 문장 자체보다, 테이블 변경 리듬과 인덱스 수명주기를 팀 규칙으로 관리하느냐가 승부를 가릅니다.