5.9.1. 개요

토토 베이션은 논리적으로 하나의 큰 것을 분할하는 것을 말합니다 작은 물리적 조각으로 테이블. 분할이 제공 할 수 있습니다 몇 가지 이점 :

• 쿼리 성능을 크게 향상시킬 수 있습니다 특정 상황, 특히 대부분의 경우 테이블의 액세스 행은 단일 토토 베이션 또는 소수의 토토 베이션. 분할 대체물 인덱스의 주요 열의 경우 인덱스 크기를 줄입니다 무겁게 사용되는 부분이 메모리에 적합한 색인.

• 쿼리 또는 업데이트가 많은 비율에 액세스 할 때 단일 토토 베이션, 성능을 개선 할 수 있습니다 대신 해당 토토 베이션의 순차적 스캔의 장점 인덱스와 임의의 액세스를 사용하여 전체 테이블.

• 벌크 하중 및 삭제는 추가 또는 해당 요구 사항이 계획된 경우 토토 베이션 제거 토토 베이션 디자인.ALTER 테이블 번호 상속and드롭 테이블대량 작동보다 훨씬 빠릅니다. 이 명령도 있습니다 완전히 피하십시오진공​​오버 헤드 벌크로 인해삭제.

• 거의 사용하지 않는 데이터를 저렴하고 느리게 마이그레이션 할 수 있습니다 스토리지 미디어.

혜택은 일반적으로 테이블 일 때만 가치가 있습니다. 그렇지 않으면 매우 클 것입니다. 테이블의 정확한 지점 분할의 혜택은 응용 프로그램에 따라 다릅니다. 경험의 규칙은 테이블의 크기가 데이터베이스 서버의 물리적 메모리를 초과합니다.

현재,PostgreSQL테이블 상속을 통한 파티셔닝을 지원합니다. 각 토토 베이션 단일 부모 테이블의 자식 테이블로 만들어야합니다. 그만큼 부모 테이블 자체는 일반적으로 비어 있습니다. 그것은 단지 존재합니다 전체 데이터 세트를 나타냅니다. 당신은 익숙해야합니다 상속 (참조토토 핫 : 문서 : 9.1 : 상속) 토토 베이을 설정하기 전에.

다음 형태의 파티셔닝은 구현 될 수 있습니다.PostgreSQL:

5.9.2. 구현 분할

분할 된 테이블을 설정하려면 다음을 수행하십시오.

1. 생성"마스터"테이블, 모든 토토 베이션이 상속 될 것입니다.

   이 테이블에는 데이터가 포함되지 않습니다. 수표를 정의하지 마십시오 이 테이블의 제약 조건, 당신이 그들을 의도하지 않는 한 모든 토토 베이션에 똑같이 적용됩니다. 요점이 없습니다 인덱스 또는 고유 한 제약 조건을 정의하고 어느 하나.

2. 여러 생성"Child"테이블 마스터 테이블에서 각각 상속합니다. 일반적으로, 이것들 테이블은 상속 된 세트에 열을 추가하지 않습니다. 마스터.

   우리는 하위 테이블을 토토 베이션이라고합니다. 그들은 모든면에서 정상입니다PostgreSQL테이블.

3. 토토 베이션 테이블에 테이블 제약 조건을 추가하여 정의합니다 각 토토 베이션에서 허용 된 주요 값.

   일반적인 예는 다음과 같습니다.

   점검 (x = 1)
   Check (County in County ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire'))))
   Check (outletid = 100 및 outletid <200)

   제약 조건에 없음을 보장하십시오 다르게 허용되는 키 값간에 중첩됩니다 토토 베이션. 일반적인 실수는 범위 제약 조건을 설정하는 것입니다 좋다:

   점검 (100에서 200 사이의 outletid)
   확인 (200에서 300 사이의 outletid)

   이것은 어느 토토 베이션이 있는지 명확하지 않기 때문에 잘못되었습니다. 키 값은 200에 속합니다.

   범위 간의 구문에는 차이가 없습니다. 토토 베이을 나열합니다. 이러한 용어는 설명 적입니다 오직.

4. 각 토토 베이션마다 키에 인덱스를 만듭니다 열, 원하는 다른 인덱스뿐만 아니라 열이 있습니다. (주요 지수는 엄격하게 필요하지 않지만 대부분 시나리오 도움이됩니다. 핵심 값을 의도한다면 독특한 그러면 항상 독특한 또는 각 토토 베이션에 대한 1 차 키 제약 조건.)

5. 선택적으로 데이터를 리디렉션하기위한 트리거 또는 규칙을 정의하십시오 마스터 테이블에 적절한 곳에 삽입되었습니다 분할.

6. 제약 _exclusion구성 매개 변수가 비활성화되지 않았습니다postgresql.conf. 그렇다면 쿼리는 그렇지 않습니다 원하는대로 최적화됩니다.

예를 들어, 우리가 대형 아이스크림 회사. 회사는 최대 온도를 측정합니다 각 지역의 아이스크림 판매뿐만 아니라 매일. 개념적으로 우리는 다음과 같은 테이블을 원합니다.

테이블 측정 생성 (
    city_id int null not,
    로그 데이트 날짜는 null이 아닙니다.
    PeakTemp int,
    단위 int
);

우리는 대부분의 쿼리가 지난 주에만 액세스 할 것임을 알고 있습니다. 이 테이블의 주요 사용 이후 월 또는 분기 데이터 관리를 위해 온라인 보고서를 준비하는 것입니다. 줄이기 위해 저장 해야하는 오래된 데이터의 양, 우리는 가장 최근 3 년간의 데이터를 유지하십시오. 시작시 매달 우리는 가장 오래된 달의 데이터를 제거 할 것입니다.

이 상황에서 우리는 파티셔닝을 사용하여 우리가 만나는 데 도움을 줄 수 있습니다. 측정 테이블에 대한 모든 다른 요구 사항. 위에서 설명한 단계에 따라 토토 베이션을 설정할 수 있습니다. 다음과 같이 :

1. 마스터 테이블은입니다.측정테이블, 정확히 다음과 같이 선언했습니다 위에.

2. 다음으로 우리는 활성 달마다 하나의 토토 베이션을 만듭니다 :

   테이블 측정 생성 _y2006m02 () 상속 (측정);
   테이블 측정 _y2006m03 () 상속 (측정)을 작성합니다.
   ...
   테이블 측정 _y2007m11 () 상속 (측정)을 만듭니다.
   테이블 측정 _y2007m12 () 상속 (측정)을 만듭니다.
   테이블 측정 _y2008m01 () 상속 (측정); 생성

   각 토토 베이션은 자신의 완전한 테이블입니다. 맞아요.하지만 그들은에서 그들의 정의를 물려받습니다.측정테이블.

   이것은 우리의 문제 중 하나를 해결합니다 : 이전 데이터 삭제. 각 달, 우리가해야 할 일은 a를 수행하는 것입니다.드롭 테이블가장 오래된 어린이 테이블에서 새 달의 데이터에 대한 새 자식 테이블 만들기.

3. 우리는 겹치지 않는 테이블 제약 조건을 제공해야합니다. 위와 같이 토토 베이션 테이블을 만드는 대신 테이블 제작 스크립트는 실제로 :

   테이블 생성 측정 _y2006m02 (
       check (logdate = date '2006-02-01'및 logdate <날짜 '2006-03-01')
   ) 상속 (측정);
   테이블 측정 _y2006m03 생성 (
       check (logdate = date '2006-03-01'및 logdate <날짜 '2006-04-01'))
   ) 상속 (측정);
   ...
   테이블 측정 값 생성 _y2007m11 (
       check (logdate = date '2007-11-01'및 logdate <날짜 '2007-12-01'))
   ) 상속 (측정);
   테이블 측정 _y2007m12 생성 (
       check (logdate = date '2007-12-01'및 logdate <날짜 '2008-01-01')
   ) 상속 (측정);
   테이블 측정 _y2008m01 생성 (
       check (logdate = date '2008-01-01'및 logdate <날짜 '2008-02-01'))
   ) 상속 (측정);

4. 우리는 아마도 주요 열에 인덱스가 필요할 것입니다 :

   Measurement_Y2006M02 (logdate)에서 인덱스 측정 _y2006M02_Logdate 만들기;
   Measurement_Y2006M03 (logdate)에서 인덱스 측정 _y2006M03_LOGDATE를 만듭니다.
   ...
   Measurement_Y2007M11 (logdate)에서 색인 측정 _y2007m11_logdate를 만듭니다.
   Measurement_Y2007M12 (logdate)에서 색인 측정 _y2007m12_logdate를 만듭니다.
   Measurement_Y2008M01 (logdate);에서 색인 측정 _y2008m01_Logdate 작성

   현재 추가 인덱스를 추가하지 않기로 선택합니다.

5. 우리는 응용 프로그램을 말할 수 있기를 원합니다측정에 삽입 ...데이터는 적절한 토토 베이션 테이블로 리디렉션됩니다. 우리 적합한 트리거 함수를 첨부하여이를 배열 할 수 있습니다 마스터 테이블에. 데이터에만 데이터가 추가되는 경우 최신 토토 베이션, 우리는 매우 간단한 트리거를 사용할 수 있습니다 기능:

   함수 생성 또는 교체 측정 _insert_trigger ()
   트리거를 $$로 반환합니다
   시작하다
       측정 _y2008m01 값 (new.*)에 삽입;
       널 리턴;
   끝;
   $$
   언어 plpgsql;

   함수를 작성한 후 트리거를 만듭니다 트리거 기능을 호출합니다.

   Trigger insert_measurement_trigger 작성
       측정에 삽입하기 전에
       각 행마다 Execute Procedure Measurement_Insert_trigger ();

   우리는 매달 트리거 함수를 재정의하여 항상 현재 토토 베이션을 가리 킵니다. 방아쇠 그러나 정의를 업데이트 할 필요는 없습니다.

   우리는 데이터를 삽입하고 서버를 가질 수 있습니다. 행이있는 토토 베이션을 자동으로 찾습니다 추가해야합니다. 우리는 더 복잡하게 이것을 할 수 있습니다 트리거 기능 예를 들어 :

   함수 생성 또는 교체 측정 _insert_trigger ()
   트리거를 $$로 반환합니다
   시작하다
       if (new.logdate = date '2006-02-01'및
            new.logdate <날짜 '2006-03-01')
           측정 _y2006m02 값 (new.*)에 삽입;
       elsif (new.logdate = date '2006-03-01'및
               new.logdate <날짜 '2006-04-01')
           측정 _y2006m03 값 (new.*)에 삽입;
       ...
       elsif (new.logdate = date '2008-01-01'및
               new.logdate <날짜 '2008-02-01')
           측정 _y2008m01 값 (new.*)에 삽입;
       또 다른
           예외 '범위를 벗어난 날짜를 올리십시오.  측정 _insert_trigger () 함수! ';
       끝 IF;
       널 리턴;
   끝;
   $$
   언어 plpgsql;

   트리거 정의는 이전과 동일합니다. 주목하십시오 각if테스트는 정확히 일치해야합니다 그만큼check그것에 대한 제약 분할.

   이 함수는보다 복잡합니다 단일 월 경우 자주 업데이트 할 필요가 없습니다. 분기가 필요하기 전에 사전에 추가 될 수 있기 때문에

   참고 :실제로 확인하는 것이 가장 좋습니다 대부분의 인서트가 들어가면 최신 토토 베이션 먼저 그 토토 베이션. 단순화를 위해 우리는 다른 부분과 같은 순서로 트리거의 테스트 이 예.

우리가 볼 수 있듯이 복잡한 분할 계획에는 상당한 양의 DDL. 위의 예에서는 우리가 될 것입니다 매달 새로운 토토 베이션을 만들므로 현명 할 수 있습니다. 필요한 DDL을 생성하는 스크립트를 작성하십시오 자동으로.

5.9.3. 관리 토토 베이션

일반적으로 처음에 설정된 토토 베이션 세트 테이블을 정의하는 것은 정적을 유지하기위한 것이 아닙니다. 그것은 구식 토토 베이션을 제거하고 싶고 새로운 데이터에 대한 새로운 토토 베이션을 주기적으로 추가하십시오. 가장 중 하나입니다 분할의 중요한 장점은 정확히 그것입니다 이 고통스러운 작업을 거의 실행할 수 있습니다 오히려 토토 베이션 구조를 조작하여 즉시 많은 양의 데이터를 물리적으로 움직이는 것보다.

오래된 데이터를 제거하기위한 가장 간단한 옵션은 단순히 삭제하는 것입니다. 더 이상 필요하지 않은 토토 베이션 :

드롭 테이블 측정 _y2006m02;

이것은 수백만 레코드를 매우 빠르게 삭제할 수 있습니다. 모든 레코드를 개별적으로 삭제할 필요는 없습니다.

종종 바람직한 또 다른 옵션은 분할 된 테이블에서 분할하지만 그 자체로 테이블 :

ALTER 테이블 측정 _Y2006M02 상속 측정 없음;

이것은 데이터에서 추가 작업을 수행 할 수 있습니다. 떨어지기 전에. 예를 들어, 이것은 종종 유용한 시간입니다 사용하여 데이터를 백업하려면COPY, pg_dump또는 유사한 도구. 그것 데이터를 더 작은 것으로 집계하는 데 유용한 시간 일 수도 있습니다. 형식, 기타 데이터 조작을 수행하거나 보고서를 실행합니다.

마찬가지로 새로운 데이터를 처리하기 위해 새 토토 베이션을 추가 할 수 있습니다. 우리 분할 된 테이블에서 빈 토토 베이션을 만들 수 있습니다. 원래 토토 베이션은 위에서 만들어졌습니다.

테이블 생성 _y2008m02 (
    check (logdate = date '2008-02-01'및 logdate <날짜 '2008-03-01'))
) 상속 (측정);

대안으로, 때로는 생성하는 것이 더 편리합니다. 토토 베이션 구조 외부의 새로운 테이블을 나중에 적절한 토토 베이션. 이를 통해 데이터를로드 할 수 있습니다. 점검하고, 그 전에 나타나기 전에 변형되었습니다 분할 된 테이블 :

생성 테이블 측정 _y2008m02
  (제약 조건을 포함한 기본값을 포함한 측정과 같은);
Alter Table Measurement_Y2008M02 추가 제약 조건 Y2008M02
   check (logdate = date '2008-02-01'및 logdate <날짜 '2008-03-01');
\ '측정 _y2008m02'에서 측정 _y2008m02 복사
- 아마도 다른 데이터 준비 작업
Alter Table Measurement_Y2008M02 상속 측정;

5.9.4. 분할 및 제약 제외

제약 제외는 쿼리입니다 성능을 향상시키는 최적화 기술 위에서 설명한 방식으로 정의 된 토토 베이션 테이블. 처럼 예 :

SET SORMSARTAINT_EXCLUSION = ON;
Logdate = Date '2008-01-01'; 측정에서 count (*)를 선택하십시오.

제약 제외가 없으면 위의 쿼리가 스캔됩니다 의 각 토토 베이션측정테이블. 제약 제외 활성화 된 플래너는 각각의 제약 조건을 조사합니다 토토 베이션이 토토 베이션이 될 필요가 없음을 증명하려고 노력합니다. 줄을 섭취 할 수 없기 때문에 스캔했습니다. 쿼리여기서절. 플래너시기 이것을 증명할 수 있고, 쿼리에서 토토 베이션을 제외합니다. 계획.

당신은를 사용할 수 있습니다설명명령으로 계획의 차이점제약 _exclusionon 및 계획. 이 유형의 테이블 설정에 대한 일반적인 최적화 된 계획은 다음과 같습니다.

SET SORMSARTAINT_EXCLUSION = OFF;
로그 데이트 = 날짜 '2008-01-01'에서 측정에서 선택한 수 (*)를 설명하십시오.

                                          쿼리 계획
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
 집계 (비용 = 158.66..158.68 행 = 1 너비 = 0)
   - Append (비용 = 0.00..151.88 행 = 2715 너비 = 0)
         - 측정시 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
               필터 : (logdate = '2008-01-01':: 날짜)
         - 측정 _y2006m02 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
               필터 : (logdate = '2008-01-01':: 날짜)
         - 측정 _y2006m03 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
               필터 : (logdate = '2008-01-01':: 날짜)
...
         - 측정 _y2007m12 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
               필터 : (logdate = '2008-01-01':: 날짜)
         - 측정 _y2008m01 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
               필터 : (logdate = '2008-01-01':: 날짜)

토토 베이션 중 일부 또는 전부는 대신 인덱스 스캔을 사용할 수 있습니다. 전체 테이블 순차적 스캔이지만 여기서 요점은 대답하기 위해 이전 토토 베이션을 전혀 스캔 할 필요가 없습니다. 이 쿼리. 제약 제외를 활성화하면 a를 얻습니다 동일하게 제공 할 상당히 저렴한 계획 답변:

SET SORMSARTAINT_EXCLUSION = ON;
로그 데이트 = 날짜 '2008-01-01'에서 측정에서 선택한 수 (*)를 설명하십시오.
                                          쿼리 계획
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
 집계 (비용 = 63.47..63.48 행 = 1 너비 = 0)
   - Append (비용 = 0.00..60.75 행 = 1086 너비 = 0)
         - 측정시 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
               필터 : (logdate = '2008-01-01':: 날짜)
         - 측정 _y2008m01 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
               필터 : (logdate = '2008-01-01':: 날짜)

제약 제외는에 의해서만 구동됩니다.check존재가 아니라 제약 인덱스. 따라서 인덱스를 정의 할 필요는 없습니다 주요 열. 주어진에 대해 인덱스를 만들어야하는지 여부 토토 베이션은 스캔 쿼리를 기대하는지 여부에 따라 토토 베이션은 일반적으로 토토 베이션의 많은 부분을 스캔합니다. 아니면 작은 부분. 색인은 후자에 도움이 될 것입니다 사례는 아니지만 전자는 아닙니다.

기본 (및 권장) 설정제약 _exclusion실제로는 아닙니다onNOROFF, 그러나 중간 설정은토토 베이션,이 기술은 기술을 일으킨다 작업 할 가능성이있는 쿼리에만 적용됩니다. 분할 된 테이블. 그만큼on설정 플래너가 검사하게합니다check모든 쿼리의 제약 조건, 심지어 간단한 간단한 제약 조건 혜택을 위해.

5.9.5. 대체 파티셔닝 행동 양식

인서트를 리디렉션하는 다른 접근법 적절한 토토 베이션 테이블은 마스터 테이블에서 트리거. 예를 들어:

규칙 생성 _insert_y2006m02 AS
측정 값으로 삽입하십시오
    (logdate = date '2006-02-01'및 logdate <날짜 '2006-03-01'))
대신하십시오
    측정 _y2006m02 값 (new.*)에 삽입;
...
규칙 측정 _insert_y2008m01을 만듭니다
측정 값으로 삽입하십시오
    (logdate = date '2008-01-01'및 logdate <날짜 '2008-02-01'))
대신하십시오
    측정 _y2008m01 값 (new.*);에 삽입하십시오.

규칙은 트리거보다 훨씬 더 많은 오버 헤드를 가지고 있지만 오버 헤드는 행당 한 번이 아닌 쿼리 당 한 번 지불되며 따라서이 방법은 벌크 삽입에 유리할 수 있습니다 상황. 그러나 대부분의 경우 트리거 방법이 있습니다 더 나은 성능을 제공합니다.

COPY규칙을 무시합니다. 사용하고 싶다면COPY삽입하려면 데이터, 올바른 토토 베이션 테이블에 복사해야합니다. 마스터보다는 대신.COPY방화 트리거이므로 사용하면 정상적으로 사용할 수 있습니다. 트리거 접근.

규칙 접근법의 또 다른 단점은 규칙 세트가 그렇지 않은 경우 오류를 강요하는 간단한 방법이 없습니다. 삽입 날짜를 덮으십시오. 데이터가 조용히 들어갑니다 대신 마스터 테이블.

토토 베이도 A를 정리할 수 있습니다.Union All테이블 상속 대신보기. 예를 들어,

보기 측정을 만듭니다
          Measurement_y2006m02에서 *를 선택하십시오
Union All Select * From Measurement_Y2006M03
...
Union All Select * From Measurement_Y2007M11
Union All Select * From Measurement_Y2007M12
Union All Select * From Measurement_Y2008M01;

그러나보기를 재현해야 할 필요성은 추가 단계를 추가합니다. 데이터 세트의 개별 토토 베이션 추가 및 삭제. ~ 안에 이 방법을 연습하는 것은 상속 사용.

5.9.6. 경고

다음 경고는 분할 된 테이블에 적용됩니다 :

• 모든 것을 확인하는 자동 방법이 없습니다check제약 조건은 상호입니다 독점적인. 생성하는 코드를 만드는 것이 더 안전합니다 관련 객체를 분할 및 생성 및/또는 수정합니다 각각을 손으로 쓰는 것보다.

• 여기에 표시된 체계는 토토 베이션 키를 가정합니다 행의 열은 절대 변하지 않거나 최소한 변경되지 않습니다. 다른 토토 베이션으로 이동하도록 요구하기에 충분합니다.업데이트그렇게하려고 시도합니다 때문에 실패합니다.check제약. 그러한 경우를 처리 해야하는 경우 토토 베이션 테이블에서 적절한 업데이트 트리거이지만 구조의 관리를 훨씬 더 많이 만듭니다 복잡한.

• 매뉴얼을 사용하는 경우진공또는분석명령, 잊지 마십시오 각 토토 베이션에서 개별적으로 실행해야합니다. 에이 명령 :

  측정 분석;

  마스터 테이블 만 처리합니다.

다음 경고는 제약 제외에 적용됩니다 :

• 제약 제외는 쿼리의 경우에만 작동합니다여기서절은 상수를 포함합니다. 에이 매개 변수화 된 쿼리는 이후로 최적화되지 않습니다 Planner는 매개 변수 값을 분할 할 수 없습니다 런타임에 선택할 수 있습니다. 같은 이유로"안정적인"함수current_date피해야합니다.

• 분할 제약 조건을 간단하게 유지하십시오 플래너는 토토 베이션이 필요하지 않다는 것을 증명하지 못할 수 있습니다. 방문합니다. 목록에 간단한 평등 조건을 사용하십시오 파티셔닝 또는 범위 분할을위한 간단한 범위 테스트, 앞의 예에 설명 된 바와 같이. 좋은 규칙 엄지 손가락은 분할 제약 조건에만 포함되어야한다는 것입니다 분할 열의 상수와 비교 B- 트리-지연 연산자 사용.

• 마스터 테이블의 모든 토토 베이션에 대한 모든 제약 제약 제외 중에 검사를 받으므로 많은 숫자입니다 토토 베이션의 쿼리 계획 시간이 증가 할 가능성이 높습니다 상당히. 이러한 기술을 사용하는 파티셔닝이 작동합니다 아마도 100 개의 토토 베이션으로; 시도하지 마십시오 수천 개의 토토 베이션을 사용하십시오.