postgresql 파티션 테이블 생성 및 데이터 이관

운영 중 이력테이블의 사이즈가 커져서 RDS용량 증가 문제 발생.

문제해결방안으로 파티션테이블로 만들고 오래된 하위테이블은 S3로 백업 후 삭제.

 

1. 파티션 테이블 생성

CREATE TABLE IF NOT EXISTS change_history_p
(
    history_sn bigint NOT NULL DEFAULT nextval('change_history_p_history_sn_seq'::regclass),
    ...
    change_dt timestamp with time zone NOT NULL
) PARTITION BY RANGE (change_dt);

2. 하위테이블 생성

CREATE TABLE change_history_p_y202305 PARTITION OF change_history_p FOR VALUES FROM ('2023-05-01') TO ('2023-06-01') ;
 ...
CREATE TABLE change_history_p_y202308 PARTITION OF change_history_p FOR VALUES FROM ('2024-04-01') TO ('2024-05-01') ;

 

3. 데이터 이관

INSERT INTO change_history_p
SELECT ...
FROM change_history

 

4. 시퀀스값(pk) 수정

SELECT setval('change_history_p_history_sn_seq', (SELECT MAX(history_sn)+1 FROM change_history_p));

 

5. 하위테이블 생성 스크립트. 실행 달의 다음 달 하위 테이블 생성

SELECT to_char(now()+ INTERVAL '1 month','yyyy-mm')||'-01',to_char(now()+ INTERVAL '2 month','yyyy-mm')||'-01',concat('change_history_p_y',to_char(now()+ INTERVAL '1 month','yyyymm'))

 

6. 프로시저로 생성 후 배치 등록.(예제)

DO
$$
begin
EXECUTE	format('CREATE TABLE IF NOT EXISTS %I PARTITION OF change_history_p FOR VALUES FROM(%L) TO(%L);'
	,concat('change_history_p_y',to_char(now()+ INTERVAL '1 month','yyyymm'))
	,concat(to_char(now()+ INTERVAL '1 month','yyyy-mm'),'-01')
	,concat(to_char(now()+ INTERVAL '2 month','yyyy-mm'),'-01'));
END;
$$ LANGUAGE plpgsql;