PostgreSQL 8.3.3문서
Prev	Fast Backward	Chapter 5. 데이터 정의	Fast Forward	Next

5.9. 파티셔닝

PostgreSQL는 기본적인 테이블의 파티셔닝(분할)을 서포트하고 있습니다. 이 마디에서는 데이터베이스 설계에 대해서, 왜 그리고 어떻게 파티셔닝을 실행하는지를 해설합니다.

5.9.1. 개요

파티셔닝은 논리적으로 1개의 큰 테이블을, 물리적으로 작은 파티션으로 나누는 것을 가리킵니다. 파티셔닝은 몇 가지 이점이 있습니다.

특정한 상황에 있어, 쿼리의 퍼포먼스가 극적으로 개선됩니다. 구체적으로 액세스가 많은 테이블 내의 행을 단일 파티션 또는 일부의 파티션 내에 넣는 것입니다. 파티셔닝은 인덱스의 선두에 열을 옮겨놓아 인덱스 크기가 감소합니다. 이 때문에, 인덱스 내의 자주 사용되는 부분이 메모리 내에 들어가기 쉬워집니다.
쿼리나 업데이트가 단일 파티션의 대부분에 액세스 하는 경우, 인덱스와 테이블 전체에 걸쳐 뿌려진 랜덤 액세스 read 대신에, 그 파티션의 시퀀셜 스캔으로 할 수가 있어 성능은 개선합니다.
그 사양이 파티션 설계에 맞도록 계획되고 있으면, 대량의 로드나 삭제가 파티션의 추가나 삭제에 의해 이루어질 가능성이 있습니다. ALTER TABLE는 대량 조작보다 빠릅니다. 또한, 대량의DELETE에 의해 발생하는 VACUUM의 오버헤드를 완전하게 막습니다.
좀처럼 사용되지 않는 데이터는 저렴한 가격과 느린 저장 미디어로 장소를 바꿀 수 있습니다.

이러한 장점은 테이블의 크기가 매우 커지는 경우에 발견됩니다. 테이블의 파티셔닝에 의한 장점은 어플리케이션에 의존하지만, 경험적으로 테이블의 크기가 데이터베이스 서버의 물리적 메모리를 초과하는 지가 포인트가 됩니다.

현재 상태로서는 PostgreSQL는 파티셔닝을 테이블의 상속에 의해 서포트하고 있습니다. 각각의 파티션은 1개의 부모 테이블의 자식 테이블로서 작성되지 않으면 안됩니다. 부모 테이블 자신은 보통, 비어 있는 테이블이 되며, 전체의 데이터를 대표하기 위해서 존재합니다. 파티셔닝을 설정하기 전에, 상속(Section 5.8를 참조해 주세요)에 대해 자세하게 알아 둘 필요가 있습니다.

파티셔닝에 대해 다음의 종류가PostgreSQL에 실행되고 있습니다.

범위 분할: 테이블은 키가 되는 열 혹은 열세트에 의해 정의되는 "범위"에 파티셔닝 됩니다. 다른 파티션에 지정할 수 있었던 값의 범위는 겹칠 것은 없습니다. 예를 들면, 날짜의 범위에 의해 파티셔닝 되거나 특정한 비지니스 오브젝트의 식별자의 범위에 의해 파티셔닝 되거나 합니다.
리스트 분할: 키 값이 각 파티션에 나타나는 명시적인 리스트에 의해서, 테이블이 파티셔닝 됩니다.

5.9.2. 파티셔닝의 실행

테이블의 파티셔닝을 실행하려면 , 이하를 실시해 주십시요.

모든 파티션이 상속하게 되는 "마스터"테이블을 작성해 주십시요.

이 테이블은 데이터를 저장하지 않습니다. 이 테이블은 모든 파티션에 대해 적용할 생각이 아닐 경우 체크 제한은 정의하지 말아 주십시요. 인덱스나 유일한 제한을 정의하는 일도 의미가 없습니다.
마스터 테이블에서 상속된, 몇 개의"자식"테이블을 작성합니다. 보통, 이러한 자식 테이블은 마스터로부터 상속된 열 이외에는 열을 추가하지 않게 합니다.

자식 테이블은 모든 점에서 PostgreSQL의 보통 테이블이지만, 자식 테이블은 파티션으로서 참조하게 됩니다.
각각의 파티션에서의 키 값를 정의하기 위해서, 파티셔닝된 테이블에 테이블 제한을 추가해 주십시요.

전형적인 예는
```
CHECK ( x = 1 )
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )
```
제한이, 다른 파티션에 대해 허가되고 있는 키 값의 사이에 겹치지 않도록 보증해 주십시요. 자주 있는 실수는 범위 제한을 다음과 같이 설정해 버립니다.
```
CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )
```
키 값 200이 어느 쪽의 파티션에 속하는지 명확하지 않기 때문에, 이것은 실수가 됩니다.

범위와 리스트 분할 동안에 구문의 차이가 없는 것을 주의하십시요. 이러한 조건은 기술상일 뿐입니다.
각각의 파티션에 대해, 다른 인덱스와 같이 키가 되는 열(열의 집합)에 인덱스를 작성해 주십시요. (키 인덱스는 반드시 필요하진 않지만, 대부분의 경우에 도움이 됩니다. 만약 키 값이 유일하게 하려면 항상 유일한 혹은 주키 제한을 각각의 파티션에 작성해 주십시요. )
선택적으로, 적절한 파티션에 마스터 테이블의 변경의 방향을 다시 설정하기 위해 룰이나 트리거를 정의해 주십시요.
constraint_exclusion설정 파라미터가postgresql.conf내에서 유효한 지 확인해 주십시요. 여기에 없다고 쿼리는 최적화되지 않습니다.

예를 들어, 대규모 아이스크림 회사의 데이터베이스를 구축한다고 가정해 주십시요. 회사는 각 지방의 아이스크림의 매상과 마찬가지로 매일 최고기온을 측정하고 있습니다. 개념적으로, 다음과 같은 테이블이 필요하게 됩니다.

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

대부분의 쿼리가 지난 주, 지난달 혹은 반년전의 데이터를 쿼리하는 것을 알고 있습니다. 그 이유는 이 테이블이 경영에 대해서 온라인의 리포트를 작성하는 것으로 주로 사용되기 때문입니다. 필요한 과거의 데이터양을 줄이기 위해서, 과거 3개월의 데이터만을 보존하기로 하겠습니다. 매달의 시작하고 과거의 데이터를 삭제합니다.

이러한 경우, measurements 테이블에 대한 다른 요구를 모두 채우도록 파티셔닝을 이용할 수 있습니다. 위와 같은 방법으로, 파티셔닝을 다음과 같이 설정합니다.

마스터 테이블은 위에서 선언된measurement테이블입니다.
다음에 각각의 달에 대해서 1개의 파티션을 작성합니다.
```
CREATE TABLE measurement_y2004m02 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 ( ) INHERITS (measurement);
...
CREATE TABLE measurement_y2005m11 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2005m12 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2006m01 ( ) INHERITS (measurement);
```
각각의 파티션은 완결된 테이블이지만 measurement테이블의 정의를 상속하고 있습니다.

이것은 데이터의 삭제라고 하는 문제를 해결합니다. 매월, 가장 오래된 자식 테이블을DROP TABLE하고, 신규 달에 대해서는 자식 테이블을 작성합니다.

겹치지 않는 것 같은 테이블 제한을 추가할 필요가 있습니다. 이하와 같은 테이블 작성 스크립트가 될 것입니다.

CREATE TABLE measurement_y2004m02 (
    CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 (
    CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2005m11 (
    CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m12 (
    CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m01 (
    CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
) INHERITS (measurement);

키가 되는 열에 인덱스가 필요할 것입니다.

CREATE INDEX measurement_y2004m02_logdate ON measurement_y2004m02 (logdate);
CREATE INDEX measurement_y2004m03_logdate ON measurement_y2004m03 (logdate);
...
CREATE INDEX measurement_y2005m11_logdate ON measurement_y2005m11 (logdate);
CREATE INDEX measurement_y2005m12_logdate ON measurement_y2005m12 (logdate);
CREATE INDEX measurement_y2006m01_logdate ON measurement_y2006m01 (logdate);

이번에는 더 이상의 인덱스를 추가하지 않도록 합니다.

만약 데이터가 최근의 파티션에만 추가 되어진다면, 데이터를 삽입하는 매우 간단한 룰을 설정할 수 있습니다. 이 매달을 재정의해서 이것이 항상 최근 파티션을 가리키도록 해야합니다.

CREATE OR REPLACE RULE measurement_current_partition AS
ON INSERT TO measurement
DO INSTEAD
    INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );

데이터를 삽입하길 원하고 자동적으로 파티션으로 할당되는 서버를 가집니다. 이 행은 추가되어져야만 합니다. 아래에 보여지는 더 복잡한 룰의 집합을 가지고 이를 실행할 수 있습니다.

CREATE RULE measurement_insert_y2004m02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
DO INSTEAD
    INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );
...
CREATE RULE measurement_insert_y2005m12 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
DO INSTEAD
    INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );
CREATE RULE measurement_insert_y2006m01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
DO INSTEAD
    INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );

파티션을 위해 각 룰과 정확하게 일치하는CHECK제한은 WHERE구문이라는 것을 주의하십시요.

이상과 같이, 복잡한 파티션화의 계획은 많은 DDL가 필요합니다. 위의 예에서는 매월 새로운 파티션을 작성하게 됩니다만, 필요한 DDL를 자동으로 생성하는 스크립트를 쓰는 것이 현명합니다.

분할은 테이블 상속이 아닌,UNION ALL뷰를 사용해 조정할 수도 있습니다.

CREATE VIEW measurement AS
          SELECT * FROM measurement_y2004m02
UNION ALL SELECT * FROM measurement_y2004m03
...
UNION ALL SELECT * FROM measurement_y2005m11
UNION ALL SELECT * FROM measurement_y2005m12
UNION ALL SELECT * FROM measurement_y2006m01;

그러나, 뷰의 재작성이 필요하므로, 데이터 집합의 개개의 파티션의 추가 및 삭제에 불필요한 처리가 더해집니다.

5.9.3. 파티션의 관리

보통, 초기의 테이블 정의로 정적 상태를 의도하지 않는 경우에, 파티션 집합은 확립됩니다. 낡은 데이터 파티션의 삭제나 신규 데이터전용의 정기적인 신규 파티션의 추가 요구는 자주 있습니다. 파티셔닝의 가장 중요한 이점은 정확성입니다. 이 때문에, 다른 한편으로 거의 동시에 위험한 작업을 실행을, 대량의 데이터를 이동시키지 않고 , 파티션 구조 조작으로 거의 순간적으로 실시할 수 있습니다.

낡은 데이터를 삭제하는 가장 간단한 방법은 단지 불필요해진 파티션을 삭제하는 것입니다.

DROP TABLE measurement_y2003m02;

레코드 마다 삭제할 필요가 없기 때문에, 이것은 고속으로 100만 레코드를 삭제할 수 있습니다.

이 외, 자주 사용되는 방법은 테이블 자체에의 액세스권한을 그대로 남긴 채, 파티션된 테이블에서 파티션을 삭제하는 것입니다.

ALTER TABLE measurement_y2003m02 NO INHERIT measurement;

이것에 의해, 삭제 전에 데이터 조작을 한층 더 실시할 수 있습니다. 예를 들어,COPY,pg_dump등의 툴을 사용해 데이터를 백업 하는 것은 자주 있습니다. 또, 데이터를 보다 작은 서식에 집약하거나 다른 데이터 조작을 실시하거나 보고를 작성하거나 하는 일도 자주 있습니다.

같이 새로운 데이터를 취급하기 위해서 새로운 파티션을 추가할 수도 있습니다. 위에서 원래의 파티션을 작성했을 때와 같이, 파티션된 테이블에 비어있는 파티션을 작성할 수 있습니다.

CREATE TABLE measurement_y2006m02 (
    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);

다른 방법으로 파티션 구조의 외부에서 새로운 테이블을 작성하고, 다음에 적절히 파티션하는 것이 편리한 경우도 있습니다. 이것에 의해, 파티션된 테이블에 추가하기 전에, 데이터를 로드하고, 검사, 변환할 수 있습니다.

CREATE TABLE measurement_y2006m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2006m02 ADD CONSTRAINT y2006m02
   CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' );
\copy measurement_y2006m02 from 'measurement_y2006m02'
-- possibly some other data preparation work
ALTER TABLE measurement_y2006m02 INHERIT measurement;

5.9.4. 파티셔닝과 제한에 의한 제외

제한에 의한 제외는 위의 방법으로 정의된 파티셔닝된 테이블에 대한 퍼포먼스를 향상시키는 쿼리의 최적화 기술입니다. 예를 들면,

SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

제한에 의한 제외가 없는 경우, 위의 쿼리는 measurement테이블의 파티션을 스캔 할 것입니다. 제한에 의한 제외가 유효할 경우, planner는 각각의 파티션 제한을 조사하고, 파티션이 쿼리WHERE에 일치하는 행을 포함하지 않도록 하기 위해, 스캔 될 필요가 없으면 분석하려고 합니다.

constraint_exclusion를 유효한 계획과 그렇지 않은 계획의 차이를 보기 위해서,EXPLAIN커맨드를 사용할 수 있습니다. 이 형태의 테이블 설정에 대한 전형적인 디폴트의 계획은 이하와 같이 됩니다.

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=158.66..158.68 rows=1 width=0)
   ->  Append  (cost=0.00..151.88 rows=2715 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2004m02 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2004m03 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
...
         ->  Seq Scan on measurement_y2005m12 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)

일부의 파티션, 혹은 모든 파티션으로, 테이블 전체에 대한 시퀀셜 스캔은 아닌, 인덱스 스캔이 사용될 가능성이 있습니다. 그러나 여기서의 포인트는 이 쿼리에 대한 대답을 위해서 오래된 파티션을 스캔 할 필요가 전혀 없다는 것입니다. 제한에 의한 제외를 가능하도록 했을 때, 큰폭으로 작아진 같은 대답을 돌려줄 계획을 얻을 수 있습니다.

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=63.47..63.48 rows=1 width=0)
   ->  Append  (cost=0.00..60.75 rows=1086 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)

제한에 의한 제외는 CHECK제한만으로 동작하고, 인덱스의 유무에서는 동작하지 않는 것을 주의하십시요. 따라서 키열의 인덱스를 정의하는 것은 필요하지 않습니다. 어느 파티션으로 인덱스가 필요한가는 파티션을 스캔 하는 쿼리가 파티션의 대부분이나 작은 부분을 스캔하는지에 따라 결정됩니다. 전자는 아니고 후자에 있어, 인덱스는 도움이 됩니다.

5.9.5. 경고

이하의 경고가 파티션된 테이블에 대해서 적용됩니다.

최근에 CHECK의 모든 제한이 서로 배타적이어서 검증할 수 있는 방법이 없습니다. 데이터베이스 디자이너에 의해 주의가 요구되었습니다.
행은 마스터 테이블에 삽입되서는 안된다는 것을 지정하는 간단한 방법은 없습니다. 마스터 테이블의 CHECK (false) 제한은 모든 자식 테이블에 의해 상속되어집니다. 이는 이러한 목적을 위해 사용되지 못하도록 하기 위해서 입니다. 하나의 가능성은 ON INSERT 트리거를 항상 에러가 나는 마스터 테이블에 설정하는 것입니다. (다른 방법으로, 트리거는 적절한 자식 테이블로 데이터가 새로운 방향을 잡기 위해 사용됩니다. 위에 제안한 룰의 집합을 사용하는 것을 대신해서 말입니다.

이하의 경고가 제한에 의한 제외에 적용됩니다.

제한의 제외는 쿼리의WHERE구문이 정수를 포함하고 있었을 때에만 동작합니다. 파라미터화된 쿼리는 최적화되지 않습니다. 그 이유로 planner는 실행시에, 파라미터치가 어느 파티션을 선택할까를 알 수 없습니다. 같은 이유로써,CURRENT_DATE와 같은"안정된"함수는 피해야 합니다.
CHECK제한에서 cross-데이터형 비교를 피하기 위해서, planner는 상태 false를 증명해서 실패할 수 있습니다. 예를 들어, x가 integer열이면 다음의 제한은 작동합니다. 그러나 x 가 bigint일 경우는 아닙니다.
```
CHECK ( x = 1 )
```
For a bigint column we must use a constraint like:
```
CHECK ( x = 1::bigint )
```
문제는 bigint 데이터형이 제한되지 않은 것입니다. 정수의 디폴트 데이터형이 열의 데이터형과 일치하지 않으면, 이 둘이 비교되어 질 수 있습니다. cross-데이터형은 제공되는 쿼리와 비교할 때는 보통 괜찮지만, CHECK 조건에서는 그렇지 않습니다.
마스터 테이블의 모든 파티션의 모든 제한은 제한에 의한 제외로 검증됩니다. 따라서 파티션의 수가 많아지면 실행 계획의 시간이 증가합니다.
개별적인 파티션에 대해서 아직도 ANALYZE를 실행할 필요가 있다는 것을 잊지 마십시요. 커맨드는 아래와 같습니다.
```
ANALYZE measurement;
```
위와 같은 커맨드에서는 마스터 테이블 밖에 처리되지 않습니다.

Prev	Home	Next
상속	Up	그 외의 데이터베이스 오브젝트