핵심 문제는 일상적인 OLTP 작업에 최적화된 데이터베이스에서 복잡하고 자원 소모적인 분석 쿼리를 실행할 때 발생하는 성능 저하입니다. 이는 분석 효율성을 떨어뜨리고 기존 애플리케이션의 속도를 늦출 수 있습니다. 연사는 PostgreSQL과 pgbench를 활용하여 OLTP 활동과 분석 쿼리를 시뮬레이션하는 데모 환경을 설정합니다. 초기 분석 쿼리가 순차 스캔을 수행하고 디스크에 임시 파일을 생성하며 정렬하는 데 약 6초가 소요됨을 보여줍니다. 이후 다양한 최적화 기법을 소개합니다.
구성 매개변수 조정:
* max_connections: 전체 연결 수를 제한하며, 특히 분석 연결에 대해선 PG Bouncer와 같은 연결 풀링을 고려하여 연결 수를 제한합니다.
* work_mem: 쿼리 작업이 디스크로 스필(spill)되기 전 사용할 수 있는 메모리 양을 제어합니다. 이 값을 늘리면 디스크 정렬을 줄일 수 있으나, 과도한 할당은 특히 동시 쿼리가 많을 때 과도한 메모리 사용으로 이어질 수 있습니다. 연사는 work_mem을 1GB로 늘려 디스크 정렬을 없애고 쿼리 시간을 4.5초로 단축했지만, 이것이 항상 좋은 트레이드오프는 아니라고 언급합니다.
* statement_timeout: 장시간 실행되는 쿼리를 중단시킵니다. 이는 특정 분석 세션에 설정하여 OLTP에 미치는 영향을 방지할 수 있습니다.
* log_min_error_statement: 타임아웃된 문장을 로깅하여 성능 모니터링에 활용합니다.
인덱싱 전략: 분석 쿼리 전용 인덱스를 생성해야 합니다. 여기에는 함수나 계산된 스칼라 표현식에 대한 표현식 인덱스도 포함될 수 있습니다. 인덱스가 OLTP(삽입/업데이트) 작업에 미치는 오버헤드를 고려하여 적절한 균형을 찾는 것이 중요합니다.
사전 계산(Generated Columns & Materialized Views): * Generated Columns: 표현식을 기반으로 컬럼 값을 자동으로 계산하고 저장합니다. 이는 쿼리를 단순화하고 런타임 계산을 줄일 수 있지만, 삽입 작업에 오버헤드를 추가합니다. * Materialized Views: 쿼리 결과의 물리적 복사본을 생성합니다. 이는 데이터 사전 집계에 매우 효과적이며, 실시간 데이터가 필수적이지 않은 경우 특히 유용합니다. Materialized View에도 인덱스를 생성하여 성능을 더욱 향상시킬 수 있으며, 데이터 신선도 요구사항에 따라 새로고침 빈도를 조절할 수 있습니다.
분석 워크로드 오프로딩(복제): * 물리적 복제: 분석 쿼리를 읽기 전용 스탠바이 데이터베이스로 보냅니다. 이는 정확한 복사본을 제공하지만, 스탠바이에서 스키마 변경이나 인덱스 생성이 제한됩니다. * 논리적 복제: 특정 객체(스키마/테이블)를 구독자 데이터베이스로 복제합니다. 이는 더 많은 유연성을 제공하여 구독자에서 인덱스, 사용자, Materialized View를 생성할 수 있는 읽기-쓰기 접근을 허용하며, PostgreSQL 16부터는 스탠바이에서도 논리적 복제가 가능합니다. 설정은 더 복잡하지만, 상당한 워크로드 분리 효과를 제공합니다.
연사는 이러한 기법들이 상호 배타적이지 않으며, 최적의 결과를 위해 결합하여 사용할 수 있음을 강조합니다 (예: Materialized View에 인덱스 생성, 특정 워크로드에 대한 매개변수 조정 등).