Postgresql Partitioning

Posted at 2012. 1. 22. 17:23 | Posted in Database
반응형
Postgresql 파티션 테이블 생성 및 사용 방법입니다.

http://www.postgresql.org/docs/8.4/static/ddl-partitioning.html



Create Partition Table

먼저 파티션 테이블을 적용할 마스터 테이블을 하나 만듭니다.

  1. -- drop table log cascade;
  2.  
  3. -- master table
  4. create table log (
  5.     -- uuid
  6.     log_uuid UUID not null default uuid_generate_v4(),
  7.     -- 인증 아이디
  8.     athn_id text not null,
  9.     -- 서비스명
  10.     svc_nm text not null,
  11.     -- 오퍼레이션명
  12.     oprt_nm text not null,
  13.     -- 요청일시
  14.     log_rqst_dt timestamp not null,
  15.     -- 응답일시
  16.     log_rply_dt timestamp not null,
  17.     -- 상태 코드
  18.     log_stat_cd text not null,
  19.     -- 등록일시
  20.     log_crtn_dt timestamp not null
  21. );

이제 데이터를 월 단위로 나눠서 담게될 파티션 테이블 12개를 만들어줍니다.

  1. -- partition table
  2. create table log_m01 (
  3.     check (date_part('month', log_rqst_dt) = 1)
  4. ) inherits (log);
  5.  
  6. create table log_m02 (
  7.     check (date_part('month', log_rqst_dt) = 2)
  8. ) inherits (log);
  9.  
  10. create table log_m03 (
  11.     check (date_part('month', log_rqst_dt) = 3)
  12. ) inherits (log);
  13.  
  14. create table log_m04 (
  15.     check (date_part('month', log_rqst_dt) = 4)
  16. ) inherits (log);
  17.  
  18. create table log_m05 (
  19.     check (date_part('month', log_rqst_dt) = 5)
  20. ) inherits (log);
  21.  
  22. create table log_m06 (
  23.     check (date_part('month', log_rqst_dt) = 6)
  24. ) inherits (log);
  25.  
  26. create table log_m07 (
  27.     check (date_part('month', log_rqst_dt) = 7)
  28. ) inherits (log);
  29.  
  30. create table log_m08 (
  31.     check (date_part('month', log_rqst_dt) = 8)
  32. ) inherits (log);
  33.  
  34. create table log_m09 (
  35.     check (date_part('month', log_rqst_dt) = 9)
  36. ) inherits (log);
  37.  
  38. create table log_m10 (
  39.     check (date_part('month', log_rqst_dt) = 10)
  40. ) inherits (log);
  41.  
  42. create table log_m11 (
  43.     check (date_part('month', log_rqst_dt) = 11)
  44. ) inherits (log);
  45.  
  46. create table log_m12 (
  47.     check (date_part('month', log_rqst_dt) = 12)
  48. ) inherits (log);

기본키를 생성합니다.

  1. -- create primary key
  2. alter table log add constraint log_pk primary key (log_uuid);
  3. alter table log_m01 add constraint log_m01_pk primary key (log_uuid);
  4. alter table log_m02 add constraint log_m02_pk primary key (log_uuid);
  5. alter table log_m03 add constraint log_m03_pk primary key (log_uuid);
  6. alter table log_m04 add constraint log_m04_pk primary key (log_uuid);
  7. alter table log_m05 add constraint log_m05_pk primary key (log_uuid);
  8. alter table log_m06 add constraint log_m06_pk primary key (log_uuid);
  9. alter table log_m07 add constraint log_m07_pk primary key (log_uuid);
  10. alter table log_m08 add constraint log_m08_pk primary key (log_uuid);
  11. alter table log_m09 add constraint log_m09_pk primary key (log_uuid);
  12. alter table log_m10 add constraint log_m10_pk primary key (log_uuid);
  13. alter table log_m11 add constraint log_m11_pk primary key (log_uuid);
  14. alter table log_m12 add constraint log_m12_pk primary key (log_uuid);

인덱스를 생성합니다. 마스터 테이블은 인덱스를 생성하지 않습니다.

  1. -- create index
  2. create index log_m01_athn_id_idx on log_m01 (athn_id);
  3. create index log_m01_svc_nm on log_m01 (svc_nm);
  4. create index log_m01_oprt_nm on log_m01 (oprt_nm);
  5.  
  6. create index log_m02_athn_id_idx on log_m02 (athn_id);
  7. create index log_m02_svc_nm on log_m02 (svc_nm);
  8. create index log_m02_oprt_nm on log_m02 (oprt_nm);
  9.  
  10. create index log_m03_athn_id_idx on log_m03 (athn_id);
  11. create index log_m03_svc_nm on log_m03 (svc_nm);
  12. create index log_m03_oprt_nm on log_m03 (oprt_nm);
  13.  
  14. create index log_m04_athn_id_idx on log_m04 (athn_id);
  15. create index log_m04_svc_nm on log_m04 (svc_nm);
  16. create index log_m04_oprt_nm on log_m04 (oprt_nm);
  17.  
  18. create index log_m05_athn_id_idx on log_m05 (athn_id);
  19. create index log_m05_svc_nm on log_m05 (svc_nm);
  20. create index log_m05_oprt_nm on log_m05 (oprt_nm);
  21.  
  22. create index log_m06_athn_id_idx on log_m06 (athn_id);
  23. create index log_m06_svc_nm on log_m06 (svc_nm);
  24. create index log_m06_oprt_nm on log_m06 (oprt_nm);
  25.  
  26. create index log_m07_athn_id_idx on log_m07 (athn_id);
  27. create index log_m07_svc_nm on log_m07 (svc_nm);
  28. create index log_m07_oprt_nm on log_m07 (oprt_nm);
  29.  
  30. create index log_m08_athn_id_idx on log_m08 (athn_id);
  31. create index log_m08_svc_nm on log_m08 (svc_nm);
  32. create index log_m08_oprt_nm on log_m08 (oprt_nm);
  33.  
  34. create index log_m09_athn_id_idx on log_m09 (athn_id);
  35. create index log_m09_svc_nm on log_m09 (svc_nm);
  36. create index log_m09_oprt_nm on log_m09 (oprt_nm);
  37.  
  38. create index log_m10_athn_id_idx on log_m10 (athn_id);
  39. create index log_m10_svc_nm on log_m10 (svc_nm);
  40. create index log_m10_oprt_nm on log_m10 (oprt_nm);
  41.  
  42. create index log_m11_athn_id_idx on log_m11 (athn_id);
  43. create index log_m11_svc_nm on log_m11 (svc_nm);
  44. create index log_m11_oprt_nm on log_m11 (oprt_nm);
  45.  
  46. create index log_m12_athn_id_idx on log_m12 (athn_id);
  47. create index log_m12_svc_nm on log_m12 (svc_nm);
  48. create index log_m12_oprt_nm on log_m12 (oprt_nm);

마스터 테이블에 인서트가 될 때 각 파티션 테이블로 데이터를 인서트할 트리거를 만듭니다.

룰(RULE), 트리거(TRIGGER) 두가지 방법이 있는데 전 트리거를 사용하겠습니다.

  1. create language plpgsql;
  2.  
  3. create or replace function log_insert_trigger()
  4. returns trigger as $$
  5. begin
  6.     if ( date_part('month', new.log_rqst_dt) = 1 ) then
  7.         insert into log_m01 values (new.*);
  8.     elsif ( date_part('month', new.log_rqst_dt) = 2 ) then
  9.         insert into log_m02 values (new.*);
  10.     elsif ( date_part('month', new.log_rqst_dt) = 3 ) then
  11.         insert into log_m03 values (new.*);
  12.     elsif ( date_part('month', new.log_rqst_dt) = 4 ) then
  13.         insert into log_m04 values (new.*);
  14.     elsif ( date_part('month', new.log_rqst_dt) = 5 ) then
  15.         insert into log_m05 values (new.*);
  16.     elsif ( date_part('month', new.log_rqst_dt) = 6 ) then
  17.         insert into log_m06 values (new.*);
  18.     elsif ( date_part('month', new.log_rqst_dt) = 7 ) then
  19.         insert into log_m07 values (new.*);
  20.     elsif ( date_part('month', new.log_rqst_dt) = 8 ) then
  21.         insert into log_m08 values (new.*);
  22.     elsif ( date_part('month', new.log_rqst_dt) = 9 ) then
  23.         insert into log_m09 values (new.*);
  24.     elsif ( date_part('month', new.log_rqst_dt) = 10 ) then
  25.         insert into log_m10 values (new.*);
  26.     elsif ( date_part('month', new.log_rqst_dt) = 11 ) then
  27.         insert into log_m11 values (new.*);
  28.     elsif ( date_part('month', new.log_rqst_dt) = 12 ) then
  29.         insert into log_m12 values (new.*);
  30.     else
  31.         raise exception 'month out of range. fix the log_insert_trigger() function!';
  32.     end if;
  33.  
  34.     -- return null
  35.     return null;
  36. end;
  37. $$
  38. language plpgsql;
  39.  
  40. -- log 마스터 테이블에 트리거 적용
  41. create trigger log_insert_trigger
  42. before insert on log
  43. for each row execute procedure log_insert_trigger();



Select Partition Table

이제 파티션 테이블은 다 만들었습니다.

이렇게 만들어진 파티션 테이블을 관리용(월 단위 삭제라든가..)으로 사용할땐 상관이 없지만, 조회(select)시에 그냥 하면 파티션을 제대로 이용하지 못합니다.

파티션을 만들 때 CHECK 구문에 들어갔던 조건절이 반드시 들어가야 합니다.

이게 뭔 소리냐 하면...

  1. select
  2.     count(*)
  3. from
  4.     log
  5. where
  6.     log_rqst_dt between date '2011-04-11' and date '2011-05-02'

파티션의 기준이 됐던 "log_rqst_dt" 컬럼의 범위로 검색을 했씁니다.

어떻게 검색이 되는지 보면 아래와 같습니다..


월단위로 나눠서 저장했던 12개의 테이블을 다 스캔하게 됩니다. ㅠ_ㅠ

log_m04, log_m05 테이블만 조회 해야 하지요....

아래와 같이 파티션 테이블 만들때의 CHECK 조건을 명시해줘야 합니다.

  1. select
  2.     count(*)
  3. from
  4.     log
  5. where
  6.     log_rqst_dt between date '2011-04-11' and date '2011-05-02'
  7.     and date_part('month', log_rqst_dt) in (4,5)

7번 라인처럼 하면 두개의 테이블만 스캔합니다.


이번에는 인덱스로 지정한 컬럼으로 검색 해봅시다.

  1. select
  2.     count(*)
  3. from
  4.     log
  5. where
  6.     date_part('month', log_rqst_dt) in (1,2,3)
  7.     and athn_id = 'athn_02'
  8.     and log_rqst_dt between date '2011-01-01' and date '2011-03-02'

마찬가지로 파티션을 검색에 적용하려면 조건을 넣어줘야 합니다.



물론 날짜 범위 검색이 필수가 아니게 되면 12개 테이블을 전부 스캔하게 되니 주의!!!

반응형
//