random_page_cost 튜닝을 통한 인덱스 활용도 증대
PostgreSQL의 random_page_cost는 디스크에 저장된 인덱스를 통해 데이터를 가져오는 비용을 추정하는 값입니다. 기본값인 4는 과거 HDD 기반 시스템에서 유래한 것으로, 현대의 SSD 기반 데이터베이스에서는 종종 비효율적입니다. 이 값이 높게 설정되어 있으면 쿼리 플래너는 인덱스 스캔 대신 CPU와 메모리 집약적인 순차 스캔을 선호하게 됩니다. 이는 결국 애플리케이션의 성능 저하로 이어질 수 있습니다. random_page_cost를 1.1과 같이 낮게 설정하면 쿼리 플래너가 인덱스 스캔을 더 자주 선택하도록 유도하여 성능을 향상시킬 수 있습니다. EXPLAIN 명령을 통해 쿼리 계획의 변화를 확인할 수 있으며, RailsPgExtras.unused_indexes 메서드를 사용하여 변경 전후의 미사용 인덱스 수를 비교함으로써 실제 효과를 측정할 수 있습니다. 이 설정은 현재 세션에만 영향을 미치므로 프로덕션 환경에서도 안전하게 테스트하고 적용할 수 있는 장점이 있습니다.
인덱스 및 순차 스캔 제어 이해
enable_indexscan, enable_bitmapscan, enable_seqscan과 같은 설정을 조정함으로써 쿼리 플래너의 동작 방식을 심층적으로 이해할 수 있습니다. 예를 들어, 인덱스 스캔을 비활성화하면 쿼리 플래너는 다른 대안이 없는 경우 순차 스캔으로 대체됩니다. 이는 random_page_cost를 극단적으로 높이는 것과 유사한 효과를 냅니다. 특정 스캔 유형을 강제로 비활성화하더라도, 쿼리 플래너는 가능한 최선의 방법을 찾으려 하며, 다른 대안이 없다면 비활성화된 스캔을 여전히 사용할 수 있음을 EXPLAIN 결과를 통해 확인할 수 있습니다. 이러한 테스트는 쿼리 플래너의 복잡한 로직을 파악하는 데 유용합니다.
NULL 인덱스 최적화
데이터베이스 인덱스는 쓰기 작업에 오버헤드를 추가하고, autovacuum 시간 및 잠금 경합을 증가시킬 수 있습니다. 특히 대부분 NULL 값을 포함하는 인덱스, 이른바 ‘NULL 인덱스’는 비효율성을 야기합니다. RailsPgExtras.null_indexes 메서드를 사용하여 이러한 인덱스를 쉽게 식별할 수 있습니다. 예를 들어, 선택적 관계의 외래 키에 대한 인덱스가 NULL 인덱스의 전형적인 예시입니다. 99%가 NULL 값으로 채워진 50GB 이상의 인덱스도 발견될 수 있습니다. 이러한 인덱스를 WHERE IS NOT NULL 조건을 사용하여 재인덱싱함으로써 인덱스 크기를 대폭 줄이고 쓰기 작업 성능을 현저히 개선할 수 있습니다. 이는 사실상 사용되지 않는 인덱스를 완전히 제거하는 것과 유사한 긍정적인 효과를 가져옵니다.
work_mem PostgreSQL 설정 조정
work_mem 설정은 ORDER BY, DISTINCT, IN과 같은 쿼리 작업이 사용할 수 있는 최대 메모리 양을 정의합니다. PostgreSQL의 기본 work_mem 값인 4MB는 특정 쿼리에서 메모리가 부족하여 디스크에 임시 파일을 생성하고 정렬 작업을 수행하게 만들 수 있습니다. 디스크 기반 정렬은 인메모리 정렬보다 훨씬 느려 전반적인 쿼리 응답 시간을 지연시킵니다. AWS RDS 사용자의 경우, Performance Insights에서 ‘Temp bytes’ 및 ‘Temp files’ 지표를 모니터링하여 애플리케이션이 디스크 기반 임시 파일을 사용하고 있는지 확인할 수 있습니다. PGTune과 같은 도구는 데이터베이스에 적합한 work_mem 값을 결정하는 데 도움을 줄 수 있습니다. 이 값을 점진적으로 2배씩 증가시키면서 데이터베이스의 메모리 사용량을 주의 깊게 모니터링하는 것이 중요합니다. work_mem은 동적으로 변경 가능한 설정이므로 데이터베이스를 재시작할 필요 없이 조정할 수 있습니다. RailsPgExtras.outliers와 pg_stat_statements_reset을 활용하여 work_mem 변경 전후의 쿼리 성능 변화를 측정하고, 영향을 받은 쿼리의 실행 시간이 유의미하게 감소하는지 확인할 수 있습니다.