[PostgreSQL] DB 파티셔닝(Partitioning) 정의 및 예제

DB 파티셔닝(Partitioning) 정의 및 예제

배경

회사의 부동산 데이터를 적재하는 과정에서 연도별 데이터가 방대하게 쌓이고 있다. 빅데이터의 수준은 아니지만 추후 10년치 이상의 데이터를 쌓고나서는 용량의 한계와 성능저하를 불러올 수 있을 것이다. 하나의 DBMS에 너무 큰 테이블이 들어오면서 용량 및 성능 측면에서 많은 이슈가 발생할 수 있게 되고, 이런 이슈를 해결하기 위해 혜성처럼 등장한 table을 파티션(partition)이라는 작은 단위로 나누어 관리하는 파티셔닝 기법을 사용해볼 것이다. 

파티셔닝 기법을 통해 소프트웨어적으로 데이터베이스를 분산처리하여 성능 저하를 방지하고, 관리를 수월하게 할 수 있을 것이다. 

 

 

개념

논리적인 데이터들의 element들을 다수의 entity로 쪼개는 것을 뜻한다. 다시 말해, 방대한 테이블이나 인덱스를 관리하기 쉬운 파티션이라는 작은 단위로 분할하는 것을 의미한다. 물리적인 데이터 분할이므로 데이터베이스에 접근하는 어플리케이션을 이를 인식하지 못하는 것이다. 앞에서 언급한 것처럼 물리적인 데이터 분할이기 때문에 파티셔닝을 진행해도 기존에 작성해둔 CRUD 를 변경할 필요는 없다. 

 

 

사용목적

1. 특정 DML과 쿼리의 성능을 향상시킨다.

2. 대용량의 데이터 쓰기 환경에서 효율적이다. 

3. Full Scan에서 데이터의 접근 범위를 줄여 성능을 향상시킨다.

4. 물리적인 파티셔닝으로 인해 전체 데이터의 훼손 가능성을 줄이고 데이터 가용성이 향상된다.

5. 큰 테이블을 제거하여 관리를 쉽게 해준다. 

 

사용목적이자 장점은 명확하나 단점도 존재한다. 

 

단점

1. 테이블간의 join에 대한 비용이 증가한다.

2. 테이블과 인덱스를 별도로 파티셔닝할 수 없다. (테이블과 인덱스를 같이 파티셔닝 해야한다)

 

 

나의 경우에는 기존 테이블이 존재하는 상태에서 파티셔닝 테이블을 생성해야했기 때문에 아래와 같은 작업을 거쳐야했다. 

• 기존 테이블명을 변경한다.
• 기존 테이블의 Primary Key, Foreign Key 와 같은 제약조건 해제한다.
• 기존 테이블과 동일한 조건의 새로운 테이블을 생성 => 기존 테이블명과 동일하게 생성해야하며, Partition by range 키워드를 사용하여 연별로 파티셔닝을 진행한다. 
• 향후에 insert 된 연도의 파티셔닝 테이블을 생성해도 좋다.
• 조회 성능을 위해 기존 테이블에 인덱스를 생성한다.
• 기존 테이블의 데이터를 새로 생성한 테이블에 insert 한다.
• 기존 테이블을 삭제한다.

 

1. 기존 테이블 변경

-- 기존 테이블명 변경
alter table table_example rename to table_example_temp;

 

 

2. 기존 테이블의 제약조건 해제

-- 기존 테이블 내 제약조건 해제 
alter table table_example_temp drop constraint table_example_pk;

 

3. 기존 테이블과 동일한 조건의 신규 테이블 생성 (기존 테이블과 동일한 명칭으로 생성)

CREATE TABLE if not exists table_example (
	data_cd varchar(10) NOT NULL,
	dat_type_cd varchar(10) NOT NULL,
	seq int4 NOT NULL,
	ctrt_ymd varchar(8) NULL,
	price int NULL
);

 

4. 연도별 파티셔닝 테이블 생성

-- 연도별 파티션 테이블 생성
create table table_example_2023
partition of table_example
for values from ('20230101') to ('20240101');

create table table_example_2022
partition of table_example
for values from ('20220101') to ('20230101');

create table table_example_2021
partition of table_example
for values from ('20210101') to ('20220101');

 

5. 조회 성능을 위해 기존 테이블에 인덱스를 생성

기존 테이블에 인덱스 생성 시 파티셔닝 테이블에도 반영된다. 

-- 모 테이블에 인덱스 생성 
CREATE INDEX table_example_idx ON table_example USING btree (seq);

 

6. 기존 테이블의 데이터를 신규 테이블에 insert

-- 기존 테이블의 데이터를 새로 생성한 테이블에 insert 
insert into table_example
select * 
from table_example_temp

 

7. 기존 테이블 삭제 

-- 테이블 삭제
drop table table_example_temp;