질문의 역사
PostgreSQL은 반복적으로 실행되는 SQL 쿼리에 대한 구문 분석 및 계획 오버헤드를 없애기 위해 준비된 문을 도입했습니다. 초기 버전은 항상 특정 파라미터 값에 맞춘 사용자 실행 계획을 생성했지만, 이는 복잡한 쿼리에 대해 상당한 CPU 비용을 초래했습니다. 이를 최적화하기 위해 PostgreSQL 9.2는 여러 실행 간에 단일 계획 구조를 재사용하는 일반적인 계획 메커니즘으로 계획 캐싱을 구현했습니다. 이 접근 방식은 계획 지연을 줄이지만, 초기 계획 단계에서 모든 파라미터를 불투명한 자리 표시자로 처리합니다.
문제
PostgreSQL의 분할 프루닝은 두 가지 뚜렷한 단계에서 작동합니다. 계획 시간 프루닝은 쿼리 계획 중에 계획자가 리터럴 값에 대해 분할 제약 조건을 검토할 때 발생하고, 런타임 프루닝은 실행 중에 분할별 조인 또는 추가 노드 필터링을 사용하여 분할을 필터링합니다. 준비된 문에 대해 생성된 일반 계획은 계획 시간에 구체적인 파라미터 값이 없기 때문에 계획 시간 프루닝이 불가능합니다. 결과적으로, 계획자는 실행 중 제공된 실제 파라미터 값과 관계없이 모든 파티션에 대한 스캔을 생성하여, 큰 분할 테이블에서 재앙적인 성능 저하를 초래합니다.
해결책
해결책은 PostgreSQL이 계획 단계에서 실제 파라미터 값을 포함하는 사용자 정의 계획을 생성하도록 강제하는 것입니다. 이는 세션이나 특정 쿼리에 대해 plan_cache_mode 구성을 force_custom_plan으로 설정함으로써 달성되며, 일반 계획 캐시를 완전히 우회합니다. 또는 프로토콜 수준의 해결 방법으로는 리터럴 값을 포함하는 Bind 메시지를 사용하여 확장된 쿼리 프로토콜을 사용하는 것이나, 다른 파라미터는 바인딩된 채로 두고 분할 키에 대한 리터럴 값을 인라인으로 사용하는 클라이언트 측 쿼리 빌더를 사용할 수 있습니다.
-- 이 세션에 대한 사용자 정의 계획 강제 SET plan_cache_mode = force_custom_plan; -- 또는 format()을 사용하여 안전하게 리터럴을 인라인으로 사용 EXECUTE format('SELECT * FROM logs WHERE log_date >= %L', '2024-01-01');
고빈도 거래 분석 플랫폼은 매일 아침 순간적인 가격 변동을 쿼리할 때 심각한 지연이 발생했습니다. 데이터베이스는 거래 세션 날짜별로 분할된 테이블에 틱 데이터를 저장하고 있었으며, 2,000개 이상의 파티션이 5년간 분포하고 있었습니다. 애플리케이션은 JDBC 준비된 문을 사용하여 SQL 인젝션을 방지하고 구문 분석 오버헤드를 줄이기 위해 날짜 파라미터에 ? 자리 표시자를 사용했습니다.
개발 팀은 초기적으로 "오늘"의 데이터를 필터링하는 쿼리가 역사적인 파티션을 스캔하고, 예상된 300밀리초 대신 45초를 소비하고 있음을 관찰했습니다. 이 성능 저하는 계획 단계에서 일반적인 계획이 무관한 파티션을 제거할 수 없었기 때문에 발생했습니다.
한 가지 접근 방식으로는 핫 데이터를 위한 별도의 비로그 테이블을 생성하고 기록을 매일 밤 이동하는 것이었습니다. 이 전략은 최근 쿼리에 대해 분할된 테이블을 완전히 우회하도록 할 수 있었지만, 복잡한 ETL 논리를 도입하고 시스템 장애 시 데이터 손실 위험을 초래했습니다.
또 다른 제안은 JDBC 연결 풀에서 준비된 문을 전역적으로 비활성화하는 것이었습니다. 이는 리터럴 값이 계획자에 노출됨으로써 계획 시간 프루닝을 복원할 수 있었지만, 벤치마크 결과, 반복적인 구문 분석 및 계획 오버헤드로 인해 데이터베이스 서버의 CPU 사용량이 40% 증가하는 것으로 나타났습니다.
팀은 또한 PostgreSQL 버전 11에서 도입된 런타임 분할 프루닝 기능을 사용하는 것을 평가했습니다. 그러나 런타임 프루닝은 실행자가 스캔을 시작한 후에야 파티션을 제거하므로, 계획자는 여전히 모든 파티션에 대한 리소스를 할당하고 분할 경계를 무시한 최적의 조인 순서를 생성했습니다.
궁극적으로 팀은 연결 수준 구성 전환을 구현하기로 결정했습니다. 그들은 연결 풀을 구성하여 분할된 테이블을 대상으로 하는 쿼리를 감지하고, 이러한 특정 문을 전송하기 전에 SET plan_cache_mode = force_custom_plan을 실행하도록 했습니다. 이는 사용자 입력 필터에 대한 파라미터화된 쿼리의 보안 이점을 보존하면서도 계획자가 분할 키 값을 볼 수 있도록 했습니다.
그 결과 쿼리 지연 시간이 280밀리초로 줄어들었고, 전체 데이터베이스 CPU 사용량이 15% 감소했습니다. 계획자는 이제 실행 전에 1,999개의 파티션을 제거하기 위해 제약 배제를 활용할 수 있었기 때문입니다. 이 최적화는 거래 플랫폼이 데이터 무결성이나 보안을 저해하지 않고도 엄격한 아침 지연 요구 사항을 충족할 수 있도록 했습니다.
회로를 통해 plan_cache_mode가 auto로 설정되어 있을 때 PostgreSQL은 일반 계획과 사용자 정의 계획을 어떻게 결정하나요?
auto 모드에서 PostgreSQL은 첫 다섯 번의 실행에 대해 사용자 정의 계획을 사용하여 쿼리를 계획하고 실행하며, 계획 비용을 누적합니다. 다섯 번째 실행 후, 첫 실행 중 추정된 일반 계획의 평균 실행 시간과 사용자 정의 계획의 평균 실행 시간 및 그 계획 오버헤드를 비교합니다. 일반 계획의 추정 비용이 사용자 정의 계획의 평균 비용보다 적으면 시스템은 해당 준비된 문에 대해 영구적으로 일반 계획으로 전환됩니다. 후보자들은 종종 이 비교에 일반 계획을 재사용하여 절약된 계획 오버헤드가 포함되어 있다는 점과 결정이 명시적으로 재계획되기 전까지는 해당 준비된 문의 수명자리에 영구적이라는 점을 놓칠 수 있습니다.
준비된 문맥에서 계획 시간 프루닝과 런타임 프루닝의 차이는 무엇인가요?
계획 시간 프루닝은 계획 단계에서 발생하며, 계획자가 특정 파티션이 쿼리의 파티션 제약 조건과 리터럴 값 기준으로 관련 행을 포함할 수 없음을 증명할 수 있을 때 발생합니다. 런타임 프루닝은 실행 중에 발생하며, 실행자가 실제 파라미터 값에 대해 파티션 제약 조건을 확인합니다. 일반 준비된 계획은 PostgreSQL 11부터 런타임 프루닝을 지원하지만, 파라미터 값이 알려지지 않기 때문에 계획 시간 프루닝을 지원할 수 없습니다. 후보자들은 종종 이러한 메커니즘을 혼동하여 런타임 프루닝이 모든 준비된 문 파티션 문제를 해결한다고 믿지만, 계획 시간 프루닝이 효율적인 조인 계획 및 인덱스 선택에 중요하다는 것을 인식하지 못합니다.
왜 force_custom_plan이 PostgreSQL 10 이전 버전에서 분할 프루닝 문제를 해결하지 못할 수 있나요?
10 이전 버전에서는 PostgreSQL이 런타임 분할 프루닝을 전혀 지원하지 않았고, extended query protocol을 사용하여 Bind 메시지를 통한 파라미터가 통과하더라도 준비된 문이 제약 배제를 활용할 수 없었습니다. 계획자는 모든 바인딩된 파라미터를 계획 프로세스 외부로 간주하여, 제약 배제를 유발하기 위해 쿼리 문자열 자체에 명시적인 리터럴 값이 필요했습니다. 이 역사적 제한으로 인해 레거시 시스템에서는 사용자 정의 계획조차 모든 파티션을 스캔해야 하며, 적절한 이스케이프가 적용된 EXECUTE ... USING를 통해 리터럴을 사용하거나 클라이언트 측 문자열 연결이 필요했습니다.