인덱스가 70TB, 데이터가 60TB인 DB환경에서 인덱스를 안정적으로 튜닝하는 절차

70 TB 인덱스 + 60 TB 데이터라는 초대형 환경에서는 “인덱스를 한 번 건드렸다가” 복구 시간만 수 일 이상 걸릴 수 있기 때문에 절차 한 단계 한 단계를 백업-검증-점진적 적용의 3단계 사이클로 반복해야 안정적으로 튜닝할 수 있습니다. 아래는 실무에서 검증된 10단계 절차입니다.

1. 현황 분석 및 위험도 산정
   • pg_stat_user_indexes, pg_stat_statements, 슬로우 쿼리 로그로 미사용·과다 인덱스를 추출
   • 인덱스 전체 크기(70 TB) 중 10 % 이상을 차지하거나 idx_scan=0 인 후보를 “제거 후보”로 리스트업
   • 동시에 INSERT/UPDATE 성능 저하가 예상되는 인덱스도 표시(쓰기 스큐 측정)
2. 대상 결정 및 롤백 계획 수립
   • 1회 작업 단위를 **단일 인덱스 또는 논리적 그룹(예: 1 테이블+보조 인덱스)**로 제한
   • REINDEX CONCURRENTLY, CREATE INDEX CONCURRENTLY 실행 계획 작성 → 실패 시 롤백 전략(스크립트 보관)
3. 전체 논리 백업(정지 시간 없음)
   • pg_dump --section=pre-data --section=post-data로 스키마만 별도 백업
   • 데이터는 지속적 증분(예: pgBackRest, WAL-G) 상태 확인 → 복구 지점 확보
4. 테스트 슬롯 복제 및 인덱스 실험
   • 동일 스토리지 사양의 스테이징 인스턴스에 최근 스냅샷 복원
   • 제거 후보 인덱스 DROP INDEX CONCURRENTLY → 쿼리 플랜(EXPLAIN ANALYZE) 및 실행 시간 재측정
   • 인덱스 재생성 시간 측정 → 생성 시간이 1 시간 이상이면 별도 유지보수 창 설정
5. 통계 정보 재생성
   • 인덱스 변경 후 반드시 ANALYZE 실행 → 플래너가 새로운 통계를 반영하도록
6. 운영 적용(점진적)
   • 1 테이블씩 CONCURRENTLY 모드로 작업 → 실시간 모니터링(CPU, I/O, 복제 지연)
   • 작업 도중 statement_timeout, lock_timeout 설정으로 장애 방지
   • 대용량 테이블은 청크 단위로 분할(예: 시간 파티션) 후 인덱스 재생성
7. 성능 회귀 테스트
   • 핵심 쿼리 20~30개를 슬로우 로그 기준으로 선정 → 이전 대비 5 % 이상 느려지면 즉시 롤백
   • pg_stat_statements_reset() 하지 않고 누적값 비교
8. 모니터링 및 잔여 인덱스 정리
   • 일주일간 idx_scan, idx_tup_read, idx_tup_fetch 추이 확인 → 미사용 확정 인덱스만 최종 제거
   • 불필요 인덱스가 60 TB 데이터에 비해 30 % 이상일 경우 용량·백업 시간 절감 효과 보고서 작성
9. 설정 파라미터 미세 조정
   • maintenance_work_mem : 인덱스 생성/재생성 시 메모리 ↑ (예: 2 GB~4 GB)
   • max_parallel_maintenance_workers : CPU 코어 수 기반 ↑ (예: 4~8)
   • random_page_cost : SSD 환경이라면 1.1 로 낮춰 인덱스 스캔 유도
10. 문서화 & 자동화
    • 변경 인덱스명, 생성/제거 시각, 롤백 스크립트를 Git에 버전 관리
    • Ansible 또는 Jenkins 파이프라인으로 “인덱스 CONCURRENTLY 생성 → ANALYZE → 회귀 테스트” 자동화

핵심 체크리스트
• “인덱스 하나당 1개 테스트 케이스” 원칙
• 백업-검증-적용-모니터링 반드시 4단계 모두 수행
• 70 TB 전체를 한 번에 건드리지 않고 파티션·테이블 단위로 잘게 쪼개서 진행

이 절차를 따르면 70 TB 인덱스라도 안정적으로 튜닝·축소할 수 있으며, 실수 시에도 최대 1 테이블 단위로만 영향을 제한할 수 있습니다.