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 버전)은 입력 파티션 위치에 따른 성능 차이가 거의 없다.
파티션 테이블 생성
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE res_op_logs ( project_id character varying(128) NOT NULL, object_id character varying(128) NOT NULL, object_type character varying(10) NOT NULL, crt_date date NOT NULL default current_date, log_type character varying(10), log_data text, reg_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP ) PARTITION BY RANGE (crt_date); CREATE UNIQUE INDEX res_op_logs_idx_01 ON res_op_logs ( project_id,object_id,object_type,crt_date ); | cs |
새 파티션의 값이 하나 이상의 기존 파티션에 있는 값과 겹치도록 경계를 지정하면 오류가 발생한다. 이렇게 생성된 파티션은 모든 방법으로 일반적인 PostgreSQL 테이블이다. 각 파티션에 대한 테이블 스페이스 및 스토리지 매개 변수를 개별적으로 지정할 수 있다.
위의 예제의 경우 각 파티션은 한 번에 1년의 데이터를 삭제해야 하는 요구 사항과 일치하도록 테이블 생성 쿼리는 다음과 같다.
CREATE TABLE res_op_logs_y2021 PARTITION OF res_op_logs FOR VALUES FROM ('2021-01-01') TO ('2021-12-31'); CREATE TABLE res_op_logs_y2022 PARTITION OF res_op_logs FOR VALUES FROM ('2022-01-01') TO ('2022-12-31'); CREATE TABLE res_op_logs_y2023 PARTITION OF res_op_logs FOR VALUES FROM ('2023-01-01') TO ('2023-12-31'); | cs |
생성후 테이블의 구조. 해당 테이블의 하위에 파티션으로 생성한 테이블이 위치한다.
실습
insert into res_op_logs values (1,1,'type', date('2021-06-30'), 'info', CURRENT_TIMESTAMP); select * from res_op_logs; | cs |
select * from res_op_logs_y2021; | cs |
부모테이블과 파티셔닝된 테이블들에서 조회할 수 있다.
하지만 파티션이 없는 데이터를 입력하고자 하면 오류가 발생한다.
insert into res_op_logs values (1,1,'type', date('2025-06-30'), 'info', CURRENT_TIMESTAMP); | cs |