[PostgreSQL] 파티션프루닝

파티셔닝

: 대용량 테이블이나 인덱스를 파티셔닝한다는 것은 Object를 여러 개의 세그먼트로 나눈다는 의미이다. 즉 하나의 테이블이나 인덱스가 동일한 논리적 속성을 가진 여러개의 단위로 나우어져 각각이 별도의 물리적 속성을 갖는 것이다.

특히 관리해야할 데이터가 늘어나면 성능과 스토리지 관점에서 문제가 생길 수 있는데, 이를 해결할 수 있는 효율적인 방법 가운데 하나가 곧 파티셔닝이다. 파티셔닝은 보통 다음과 같은 장점을 갖고 있다.

> 데이터 엑세스시(특히 풀스캔시)엑세스의 범위를 줄여 성능을 향상시킨다.
> 물리적으로 여러 영역으로 파티셔닝해 전체 데이터의 훼손 가능성이 줄어들고 데이터 가용성이 향상된다.
> 각 파티션별로 백업, 복구 작업을 할 수 있다.
> 테이블의 파티션 단위로 디스크 I/O를 분산해 부하를 줄일 수 있다.

* 레인지 파티셔닝
어떤 특정 컬럼의 정렬 값을 기준으로 분할
순차적인 데이터를 관리하는 테이블에서 많이 사용해보쟈

 

CREATE TABLE extn.keyword_ratio (

keyword_id int NULL,

keyword varchar(100) NULL,

period varchar(20) NULL,

ratio float8 NULL

) PARTITION BY RANGE (period);



create table extn.keyword_ratio_2017 partition of extn.keyword_ratio for values from ('2017-01-01') to ('2018-01-01');

create table extn.keyword_ratio_2018 partition of extn.keyword_ratio for values from ('2018-01-01') to ('2019-01-01');

create table extn.keyword_ratio_2019 partition of extn.keyword_ratio for values from ('2019-01-01') to ('2020-01-01');

create table extn.keyword_ratio_2020 partition of extn.keyword_ratio for values from ('2020-01-01') to ('2021-01-01');

create table extn.keyword_ratio_2021 partition of extn.keyword_ratio for values from ('2021-01-01') to ('2022-01-01');

create table extn.keyword_ratio_2022 partition of extn.keyword_ratio for values from ('2022-01-01') to ('2023-01-01');

create table extn.keyword_ratio_2023 partition of extn.keyword_ratio for values from ('2023-01-01') to ('2024-01-01');

 

 

---

https://jaimemin.tistory.com/1913

https://seoulforest.tistory.com/entry/Partition-Pruning-%ED%8C%8C%ED%8B%B0%EC%85%98-%ED%94%84%EB%A3%A8%EB%8B%9D