PostgreSQL Partition 과 plan_cache_mode 파라미터.

테이블에 리스트 파티션을 약 8000여개 생성 (이렇게 사용하는곳이 없을줄 알았는데..우리가 이렇게 씀.)

 

현상

• Loop 등을 수행하는데 5-6 번째에서 한참동안 멈춰있다가 실행 또는 db 비정상 종료 됨.
• 메모리가 계속 증가

 

이슈1

• 쿼리를 작성할때 파티션 테이블의 파티션 기준에 대한 조건이 항상 있어야 함.
• 후행 테이블일때 조인 조건으로 받아먹는건 의미 없음(비결정적으로 판단하여 모든 파티션을 읽는 실행계획이 생성됨)
• 메모리 사용량이 계속 증가하는 현상과 함께 심한 경우 db 가 비정상종료되는 경우도 발생함.

 

이슈2

• 모든 파티션 조건을 추가해도, 같은 쿼리가 여러번 (5-6)수행되면, 내부적으로 실행계획을 재사용하기 위해 플랜을 생성하게 되는데, 이때 또 이슈 1과 같은 현상이 발생 함.
• 해결하기 위해 환경변수를 설정해야 함.  set plan_cache_mode = 'force_custom_plan
• 특히 파라미터를 설정하지 않으면 같은 원인으로 프로시져 내부에서 loop 를 돌리거나 했을때 파티션 테이블이 사용되면 5-6번째에  멈추는 현상이 발생한다.

plan_cache_mode( enum)

 

준비된 명령문(예: PL/pgSQL에 의해 명시적으로 준비되거나 암시적으로 생성됨)은 사용자 정의 또는 일반 계획을 사용하여 실행할 수 있습니다. 사용자 지정 계획은 특정 매개변수 값 집합을 사용하여 실행할 때마다 새로 만들어지는 반면 일반 계획은 매개변수 값에 의존하지 않으며 실행 전반에 걸쳐 재사용할 수 있습니다. 따라서 일반 계획을 사용하면 계획 시간이 절약되지만 이상적인 계획이 매개변수 값에 크게 의존하는 경우 일반 계획은 비효율적일 수 있습니다. 이러한 옵션 사이의 선택은 일반적으로 자동으로 이루어지지만 로 재정의할 수 있습니다 plan_cache_mode. 허용되는 값은 auto(기본값) force_custom_plan및 force_generic_plan입니다. 이 설정은 캐시된 계획이 준비될 때가 아니라 실행할 때 고려됩니다. 자세한 내용은 준비를 참조하십시오

 

https://www.postgresql.org/docs/12/runtime-config-query.html