PostgreSQL 파티션

PostgreSQL 파티션

 

▪ 상속 (Inherits)을 이용해서 파티션을 구현함

▪ 인덱스 정보는 상속되지 않음

▪ Trigger를 이용해서 파티션 Pruning을 처리함

▪ 파티션 생성 시에 CHECK 조건을 이용함

▪ Range, List 파티션을 제공함

▪ Global Index는 제공하지 않음

▪ 입력 조건에 맞는 파티션만 액세스하는 것을 ‘파티션 Pruning’이라고 한다.

▪ 이를 위해서는 constraint_exclusion 파라미터를 ‘on’ 또는 ‘partition’으로 설정해야 한다.

(기본 설정값: partition)

▪ CHECK 조건을 입력하면 정상적으로 파티션 Pruning이 수행된다.

 

서브 파티션을 이용한 튜닝 방안

▪ 1차 파티션을 통해 IO 범위를 줄인 후에도 여전히 IO 량이 많은 경우에는 2차 파티션을 고려한다.

▪ PostgreSQL은 상속에 의한 파티션 생성 방식이므로, 이론적으로는 n차 파티션도 가능하다.

▪ 따라서 업무 속성에 따른 서브 파티션 생성을 통한 IO 튜닝이 가능하다.

 

파티션 Partial 인덱스를 이용한 튜닝 방안

▪ PostgreSQL은 파티션 Partial 인덱스를 제공한다.

▪ 즉, 파티션 별로 인덱스 생성 여부를 결정할 수도 있고, 파티션 마다 다른 인덱스 (예, B*Tree, Covering Index, BRIN 등)를 생성할 수도 있다.

▪ 참고로, 오라클은 12c 버전부터 파티션 Partial 인덱스 기능을 제공한다.

 

파티션 입력 성능 향상을 위한 Tip

▪ RULE 보다는 Trigger를 이용한다. 어떤 경우에도 Trigger가 빠르다.

▪ 자주 입력되는 파티션을 Trigger의 윗 부분에 위치하는 것이 좋다. 그래야 IF THEN ELSE의 부하를 줄일 수 있다.

▪ 가능하면, 파티션을 지정해서 입력하는 것이 좋다.

 

PostgreSQL 파티션 Pruning 시의 주의 사항

▪ 예상과 달리, 파티션 Pruning이 동작하지 않는 경우가 존재한다.

▪ 이점은 쿼리 작성 시에 매우 주의해야할 점이다.

▪ IN 절 내에 Subquery를 사용할 경우에는 파티션 Pruning이 동작하지 않는다.

▪ IN 절 사용시 파티션 Pruning을 유도하기 위해서는 실제 CHECK 조건을 입력해야 한다.

▪ NL 조인으로 수행되면 추가 조건 없이도 파티션 Pruning이 수행된다.

▪ Hash 조인으로 수행되면 추가 조건을 반드시 입력해야 한다.

▪ 따라서, 쿼리 작성 후에 Explain 결과 확인 및 추가 조건을 입력할 필요가 있다.

▪ Hash 조인으로 수행될 때를 고려해서, 상수 조건을 추가하는 것을 습관화 하는 것이 좋다.

▪ PostgreSQL 10부터 오라클과 유사한 파티션 생성 문법을 제공한다.

▪ 이때, 트리거는 생성할 필요가 없다. (이점은 관리측면의 편의성을 제공한다)

▪ 단, 파티션 Pruning은 기존과 같은 한계가 존재한다. (즉, 해시 조인 시에는 상수 조건 추가 필요)

 

파티션 성능 테스트(INSERT)

▪ 9.6 까지는 파티션이 1개인 경우에도 일반 테이블보다 입력 속도가 3배 이상 느리다. 그 이유는 매 건마다 Trigger 함수를 호출하는 부하 때문이다.

▪ CPU 성능에 좋을수록 이 차이는 감소한다.

▪ 파티션 개수가 많아질 수록 입력 성능은 저하된다.

▪ 이 문제는 PG10 버전에서도 여전히 발생한다.

▪ 상속을 이용한 파티션은 Trigger 조정을 통해서 입력 성능을 향상 시킬 수 있다.

▪ 하지만, Native 파티션 (PG 10 버전)은 입력 파티션 위치에 따른 성능 차이가 거의 없다.