postgreSQL 파티셔닝 알아보기

동기

이번에 대용량으로 유저정보를 저장할 일이 생겼는데 그때 파티셔닝을 이용해서 테이블을 분리하는 작업을 했다.

파티셔닝이란?

파티셔닝은 논리적으로 하나의 큰 테이블을 더 작은 물리적인 조각으로 분할하는 것을 말한다.

• 이점

1. 쿼리 성능이 특정 상황에서 크게 향상 될수 있다. 특히 테이블에서 자주 액세스 되는 행의 대부분이 단일 파티션 또는 적은 수의 파티션에 있을때 성능이 향상된다.
2. 쿼리 또는 업데이트가 단일 파티션의 많은 부분에 액세스 하는 경우 전체 테이블의 인덱스를 사용하는 대신 해당 파티션의 순차적 스캔을 사용해 성능을 향상 시킬수 있다.
3. 대량 로드나 삭제 같은경우 파티션을 제거하거나 더하는 것으로 대체 할수 있는데 이경우 bulk operation 보다 훨씬 속도가 빠르다.
4. 잘 사용하지 않는 데이터를 더 느리고 저렴한 저장매체로 마이그레이션 할수 있다.

단, 이러한 이점은 테이블이 매우 큰 경우에만 가치가 있다는걸 명심!

• 종류

1. 범위 파티셔닝 (range partitioning)
   테이블이 키 혹은 열의 집합에 의해 정의된 "범위" 로 분할 된다.

예를 들어 아이스크림회사에서 매일최고 기온과 아이스크림 판매량을 측정 한다고 했을때 다음과 같은 테이블이 필요하다.

CREATE TABLE 측정( 
    city_id int는 null이 아님, 
    logdate 날짜는 null이 아님, 
    peaktemp int, 
    unitsales int 
);

이럴때 날짜로 범위 파티셔닝을 할수 있다.

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
    FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

CREATE TABLE measurement_y2007m12 PARTITION OF measurement
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
    TABLESPACE fasttablespace;

CREATE TABLE measurement_y2008m01 PARTITION OF measurement
    FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
    WITH (parallel_workers = 4)
    TABLESPACE fasttablespace;

여기서 분할된 테이블은 다른 인덱스뿐만 아니라 키에 대한 인덱스를 만든다. (키 인덱스가 반드시 필요한 것은 아니지만 대부분의 시나리오에서 유용하다.)

CREATE INDEX ON 측정(logdate);

2. 리스트 파티셔닝(list partitioning)
   명시적으로 지정된 키 값으로 테이블이 분할 된다.
   (이번에 우리팀에서는 이 파티셔닝 방식을 사용했다.)

3. 해시 파티셔닝(hash partitioning)
   지정된 키값을 해시함수를 이용한 결과값으로 테이블이 분할 되는것을 의미한다.
   어떤 값으로 파티셔닝을 할지 애매할때 사용하면 유용하다.

• DETACH 와 ATTACH

오래된 데이터를 제거하는 가장 간단한 옵션은 더 이상 필요하지 않은 파티션을 삭제하는 것이다.

DROP TABLE measurement_y2006m02;

이렇게 하면 코든 레코드를 개별적으로 삭제 할 필요가 없기 때문에 수백만 개의 레코드를 매우 빠르게 삭제 할수 있다.

그런데 데이터가 삭제되기전에 데이터를 백업하거나 다른 작업을 수행하고 싶을 수가 있는데, 이때는 DETACH 를 활용 하면 좋다.

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;

이렇게 하면 부모테이블에 영향을 받지 않으면서 데이터를 삭제하기전에 백업을 수행할수 있다. 위에서 CONCURRENTLY 키워드를 추가하면 부모테이블에서 파티션을 분리하는 작업을 하는동안 부모테이블에 대한 락을 걸수가 있다.

ATTACH의 경우에도 새테이블을 만들고 부모테이블에 파티션을 붙이기 전에 부모테이블에 영향이 갈수 있는 작업들을 미리 해두고 ATTACH 할수 있다.

적용

이번에 내가 있는 마케팅 플랫폼팀에서 플랫폼에 새로 추가된 기능은 푸시알림을 수동발송 하는 기능이다.
이 수동 발송은 엑셀 파일로 유저정보(전화번호 등..)를 받는데 이 유저정보가 적게는 몇천건에서 몇만건이 될수 있으므로 이 데이터를 postgreSQL DB 에 저장하기 위해서는 파티셔닝이 필요하다고 개발팀 논의결과 결정되었다.

어떤 키값으로 파티셔닝을 해야할지가 고민이었는데 각각 고객사가 발송할려고 하는 엑셀파일의 유저정보를 하나의 key 값으로 묶어 파티셔닝 하기로 했다.

이 키값을 sendKey라고 부르고 유저 정보가 들어오면 유저정보 테이블에 이 sendKey값으로된 테이블을 만들고 유저정보 테이블에 attach하는 방식으로 저장을 하게 되었다.

이때 각 고객사 마다의 유저정보를 조회할때는 파티션 푸르닝이 적용된다.

• 파티션 푸르닝(Partition Pruning)
  파티션으로 나눠진 테이블에서 파티션으로 나눈 키값으로 조회 쿼리를 날리면 해당하는 파티션테이블에서만 스캐닝이 이루어 지는데 이것을 파티션 푸르닝, 파티션 가지치기 라고 부른다.

파티션 푸르닝으로 좀 더 빠른 조회성능을 가질 수 있고 이 조회성능으로 보다 빠른 푸시 발송이 가능해졌다.

기본적으로 파티션 푸르닝 옵션은 기본이 on 으로 되어있고 off 로 설정하게 되면 모든 테이블이 스캔되어버리므로 설정이 off 가 되지않게 신경써야한다.

• 파티션 푸르닝 옵션이 off 일 때

SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-------------------------------------------------------------------​----------------
 Aggregate  (cost=188.76..188.77 rows=1 width=8)
   ->  Append  (cost=0.00..181.05 rows=3085 width=0)
         ->  Seq Scan on measurement_y2006m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m11  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2007m12  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)

• 파티션 푸르닝 옵션이 on 일 때

SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-------------------------------------------------------------------​----------------
 Aggregate  (cost=37.75..37.76 rows=1 width=8)
   ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
         Filter: (logdate >= '2008-01-01'::date)

결론

• 대량의 데이터를 삽입하고 그것을 조회하고 싶다면 파티셔닝을 고려하면 좋다.

• 파티셔닝을 할때는 기준이 되는 키 값으로 한다. 이때 어떤 방식으로 조회를 할지 고려해야한다.

• 이를 고려한 뒤 적절한 파티셔닝 기법을 찾는다.

• 파티션 푸르닝 옵션이 on이 되어있는지 확인한다. (기본이 on)