Postgresql Partitioning

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개 테이블을 전부 스캔하게 되니 주의!!!