PostgreSQL 파티션의 목적 및 프루닝 조건

글을 작성하기 전에

PostgreSQL은 버전에 따른 파티셔닝 구성의 발전이 이루어져 왔으며 11버전에 이르러서야 파티션 테이블의 장점이 도드라졌습니다. 파티셔닝에 대해서는 추후 포스팅에 상세하게 정리할 예정이지만, 파티션 구성의 목적 및 프루닝 조건에 특이점을 미리 포스팅에 정리하겠습니다.

PostgreSQL 파티션의 목적 및 프루닝 조건

작성자가 운영하는 서버들에 대해서는 50GB 이상의 단일 테이블의 경우 파티션테이블로 전환을 검토하고 있다.

파티셔닝 구성의 제약조건은 분명히 있지만 장점이 많기 때문에 전환을 검토하는 것인데 이부분을 관리적 차원, 성능적 차원으로 정리하려고 한다.

 

관리적 차원의 파티셔닝의 장점 

- 대용량 테이블을 파티션으로 전환함으로써 각각의 테이블에 대한 관리를 용이하게 함

- 파티션 키를 적절하게 설정함으로써 요구사항에 대한 조치가 원활함 ( 보관주기 만료 테이블 삭제 등)

성능적 차원의 파티셔닝의 장점 

- 파티션 키를 조건절에 포함한다면 파티션 프루닝이 되어 IO 부하를 감소 시킬수 있음

- 테이블의 사이즈가 줄어드므로 인덱스를 통한 랜덤 IO보다 풀스캔을 통한 순차 IO가 적어 옵티마이저가 풀스캔 판단 확률 증가(성능 개선)

 

 

파티션 프루닝 이란

파티션 테이블의 성능을 최적화 하기 위해 필요한 파티션만 탐색하는 것

 -> 파티션 프루닝이 수행되는 조건이 따로 있으며 쿼리 수행시 이를 검토하는 것이 필요

 

파티션 프루닝 조건

 

1. DB 설정 상 파티션 프루닝에 대한 조건이 설정되어 있어야함

- 11버전 부터 도입된 enable_partition_pruning 파라미터, 기존 inherit 파티션 조건 검토시 사용되는 constraint_exclusion

 

2. 쿼리의 조건절에 파티션 테이블 키가 포함되어 있어야함 

- 따라서 파티션 키 설정에 비즈니스로직을 포함시켜야함

 

3. 10버전까지는 stable 함수 적용시 프루닝 불가

 - Range 파티션 구성시 파티션키가 조건절에 포함되어 있더라도 우변이 to_timestamp 로 변환될 시 파티션 프루닝 되지 않음 

(select * from part_table where reg_date Between to_timestamp()::timestamp without time zone and to_timestamp()::timestamp without time zone 와 같은 쿼리 수행 시 프루닝 불가 , reg_date type은 timestamp without time zone)

 

위 3번 사유는 파티션 프루닝은 쿼리실행 단계가 아닌 계획단계 에서 정해지는데 to_timestamp 함수는 stable 함수 이므로 쿼리계획 수립시 동일한 타입으로 반환되지 않음