http://www.postgresql.org/docs/8.4/static/ddl-partitioning.html
Create Partition Table
먼저 파티션 테이블을 적용할 마스터 테이블을 하나 만듭니다.
-
-- drop table log cascade;
-
-
-- master table
-
create table log (
-
-- uuid
-
log_uuid UUID not null default uuid_generate_v4(),
-
-- 인증 아이디
-
athn_id text not null,
-
-- 서비스명
-
svc_nm text not null,
-
-- 오퍼레이션명
-
oprt_nm text not null,
-
-- 요청일시
-
log_rqst_dt timestamp not null,
-
-- 응답일시
-
log_rply_dt timestamp not null,
-
-- 상태 코드
-
log_stat_cd text not null,
-
-- 등록일시
-
log_crtn_dt timestamp not null
-
);
이제 데이터를 월 단위로 나눠서 담게될 파티션 테이블 12개를 만들어줍니다.
-
-- partition table
-
create table log_m01 (
-
check (date_part('month', log_rqst_dt) = 1)
-
) inherits (log);
-
-
create table log_m02 (
-
check (date_part('month', log_rqst_dt) = 2)
-
) inherits (log);
-
-
create table log_m03 (
-
check (date_part('month', log_rqst_dt) = 3)
-
) inherits (log);
-
-
create table log_m04 (
-
check (date_part('month', log_rqst_dt) = 4)
-
) inherits (log);
-
-
create table log_m05 (
-
check (date_part('month', log_rqst_dt) = 5)
-
) inherits (log);
-
-
create table log_m06 (
-
check (date_part('month', log_rqst_dt) = 6)
-
) inherits (log);
-
-
create table log_m07 (
-
check (date_part('month', log_rqst_dt) = 7)
-
) inherits (log);
-
-
create table log_m08 (
-
check (date_part('month', log_rqst_dt) = 8)
-
) inherits (log);
-
-
create table log_m09 (
-
check (date_part('month', log_rqst_dt) = 9)
-
) inherits (log);
-
-
create table log_m10 (
-
check (date_part('month', log_rqst_dt) = 10)
-
) inherits (log);
-
-
create table log_m11 (
-
check (date_part('month', log_rqst_dt) = 11)
-
) inherits (log);
-
-
create table log_m12 (
-
check (date_part('month', log_rqst_dt) = 12)
-
) inherits (log);
기본키를 생성합니다.
-
-- create primary key
-
alter table log add constraint log_pk primary key (log_uuid);
-
alter table log_m01 add constraint log_m01_pk primary key (log_uuid);
-
alter table log_m02 add constraint log_m02_pk primary key (log_uuid);
-
alter table log_m03 add constraint log_m03_pk primary key (log_uuid);
-
alter table log_m04 add constraint log_m04_pk primary key (log_uuid);
-
alter table log_m05 add constraint log_m05_pk primary key (log_uuid);
-
alter table log_m06 add constraint log_m06_pk primary key (log_uuid);
-
alter table log_m07 add constraint log_m07_pk primary key (log_uuid);
-
alter table log_m08 add constraint log_m08_pk primary key (log_uuid);
-
alter table log_m09 add constraint log_m09_pk primary key (log_uuid);
-
alter table log_m10 add constraint log_m10_pk primary key (log_uuid);
-
alter table log_m11 add constraint log_m11_pk primary key (log_uuid);
-
alter table log_m12 add constraint log_m12_pk primary key (log_uuid);
인덱스를 생성합니다. 마스터 테이블은 인덱스를 생성하지 않습니다.
-
-- create index
-
create index log_m01_athn_id_idx on log_m01 (athn_id);
-
create index log_m01_svc_nm on log_m01 (svc_nm);
-
create index log_m01_oprt_nm on log_m01 (oprt_nm);
-
-
create index log_m02_athn_id_idx on log_m02 (athn_id);
-
create index log_m02_svc_nm on log_m02 (svc_nm);
-
create index log_m02_oprt_nm on log_m02 (oprt_nm);
-
-
create index log_m03_athn_id_idx on log_m03 (athn_id);
-
create index log_m03_svc_nm on log_m03 (svc_nm);
-
create index log_m03_oprt_nm on log_m03 (oprt_nm);
-
-
create index log_m04_athn_id_idx on log_m04 (athn_id);
-
create index log_m04_svc_nm on log_m04 (svc_nm);
-
create index log_m04_oprt_nm on log_m04 (oprt_nm);
-
-
create index log_m05_athn_id_idx on log_m05 (athn_id);
-
create index log_m05_svc_nm on log_m05 (svc_nm);
-
create index log_m05_oprt_nm on log_m05 (oprt_nm);
-
-
create index log_m06_athn_id_idx on log_m06 (athn_id);
-
create index log_m06_svc_nm on log_m06 (svc_nm);
-
create index log_m06_oprt_nm on log_m06 (oprt_nm);
-
-
create index log_m07_athn_id_idx on log_m07 (athn_id);
-
create index log_m07_svc_nm on log_m07 (svc_nm);
-
create index log_m07_oprt_nm on log_m07 (oprt_nm);
-
-
create index log_m08_athn_id_idx on log_m08 (athn_id);
-
create index log_m08_svc_nm on log_m08 (svc_nm);
-
create index log_m08_oprt_nm on log_m08 (oprt_nm);
-
-
create index log_m09_athn_id_idx on log_m09 (athn_id);
-
create index log_m09_svc_nm on log_m09 (svc_nm);
-
create index log_m09_oprt_nm on log_m09 (oprt_nm);
-
-
create index log_m10_athn_id_idx on log_m10 (athn_id);
-
create index log_m10_svc_nm on log_m10 (svc_nm);
-
create index log_m10_oprt_nm on log_m10 (oprt_nm);
-
-
create index log_m11_athn_id_idx on log_m11 (athn_id);
-
create index log_m11_svc_nm on log_m11 (svc_nm);
-
create index log_m11_oprt_nm on log_m11 (oprt_nm);
-
-
create index log_m12_athn_id_idx on log_m12 (athn_id);
-
create index log_m12_svc_nm on log_m12 (svc_nm);
-
create index log_m12_oprt_nm on log_m12 (oprt_nm);
마스터 테이블에 인서트가 될 때 각 파티션 테이블로 데이터를 인서트할 트리거를 만듭니다.
룰(RULE), 트리거(TRIGGER) 두가지 방법이 있는데 전 트리거를 사용하겠습니다.
-
create language plpgsql;
-
-
create or replace function log_insert_trigger()
-
returns trigger as $$
-
begin
-
if ( date_part('month', new.log_rqst_dt) = 1 ) then
-
insert into log_m01 values (new.*);
-
elsif ( date_part('month', new.log_rqst_dt) = 2 ) then
-
insert into log_m02 values (new.*);
-
elsif ( date_part('month', new.log_rqst_dt) = 3 ) then
-
insert into log_m03 values (new.*);
-
elsif ( date_part('month', new.log_rqst_dt) = 4 ) then
-
insert into log_m04 values (new.*);
-
elsif ( date_part('month', new.log_rqst_dt) = 5 ) then
-
insert into log_m05 values (new.*);
-
elsif ( date_part('month', new.log_rqst_dt) = 6 ) then
-
insert into log_m06 values (new.*);
-
elsif ( date_part('month', new.log_rqst_dt) = 7 ) then
-
insert into log_m07 values (new.*);
-
elsif ( date_part('month', new.log_rqst_dt) = 8 ) then
-
insert into log_m08 values (new.*);
-
elsif ( date_part('month', new.log_rqst_dt) = 9 ) then
-
insert into log_m09 values (new.*);
-
elsif ( date_part('month', new.log_rqst_dt) = 10 ) then
-
insert into log_m10 values (new.*);
-
elsif ( date_part('month', new.log_rqst_dt) = 11 ) then
-
insert into log_m11 values (new.*);
-
elsif ( date_part('month', new.log_rqst_dt) = 12 ) then
-
insert into log_m12 values (new.*);
-
else
-
raise exception 'month out of range. fix the log_insert_trigger() function!';
-
end if;
-
-
-- return null
-
return null;
-
end;
-
$$
-
language plpgsql;
-
-
-- log 마스터 테이블에 트리거 적용
-
create trigger log_insert_trigger
-
before insert on log
-
for each row execute procedure log_insert_trigger();
Select Partition Table
이제 파티션 테이블은 다 만들었습니다.
이렇게 만들어진 파티션 테이블을 관리용(월 단위 삭제라든가..)으로 사용할땐 상관이 없지만, 조회(select)시에 그냥 하면 파티션을 제대로 이용하지 못합니다.
파티션을 만들 때 CHECK 구문에 들어갔던 조건절이 반드시 들어가야 합니다.
이게 뭔 소리냐 하면...
-
select
-
count(*)
-
from
-
log
-
where
-
log_rqst_dt between date '2011-04-11' and date '2011-05-02'
파티션의 기준이 됐던 "log_rqst_dt" 컬럼의 범위로 검색을 했씁니다.
어떻게 검색이 되는지 보면 아래와 같습니다..
월단위로 나눠서 저장했던 12개의 테이블을 다 스캔하게 됩니다. ㅠ_ㅠ
log_m04, log_m05 테이블만 조회 해야 하지요....
아래와 같이 파티션 테이블 만들때의 CHECK 조건을 명시해줘야 합니다.
-
select
-
count(*)
-
from
-
log
-
where
-
log_rqst_dt between date '2011-04-11' and date '2011-05-02'
-
and date_part('month', log_rqst_dt) in (4,5)
7번 라인처럼 하면 두개의 테이블만 스캔합니다.
이번에는 인덱스로 지정한 컬럼으로 검색 해봅시다.
-
select
-
count(*)
-
from
-
log
-
where
-
date_part('month', log_rqst_dt) in (1,2,3)
-
and athn_id = 'athn_02'
-
and log_rqst_dt between date '2011-01-01' and date '2011-03-02'
마찬가지로 파티션을 검색에 적용하려면 조건을 넣어줘야 합니다.
물론 날짜 범위 검색이 필수가 아니게 되면 12개 테이블을 전부 스캔하게 되니 주의!!!