5.9.1. 개요

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

• 특히 테이블의 많은 액세스 행이 단일 스포츠 토토 결과 또는 소수의 스포츠 토토 결과에있을 때 특정 상황에서 쿼리 성능을 크게 향상시킬 수 있습니다. 분할은 인덱스의 선행 열을 대체하여 인덱스 크기를 줄이고 인덱스의 많은 부분이 메모리에 적합 할 가능성이 높아집니다.

• 쿼리 또는 업데이트가 단일 스포츠 토토 결과의 많은 비율에 액세스 할 때 전체 테이블에 흩어져있는 인덱스 및 랜덤 액세스 판독 값을 사용하는 대신 해당 스포츠 토토 결과의 순차적 스캔을 활용하여 성능을 향상시킬 수 있습니다.

• 스포츠 토토 결과 설계에 해당 요구 사항이 계획된 경우 스포츠 토토 결과을 추가하거나 제거하여 벌크 하중 및 삭제를 수행 할 수 있습니다..ALTER 테이블 없음 상속and드롭 테이블는 모두 벌크 작업보다 훨씬 빠릅니다. 이 명령은 또한 전적으로를 피합니다.진공벌크로 인한 오버 헤드삭제.

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

혜택은 일반적으로 테이블이 매우 큰 경우에만 가치가 있습니다. 테이블의 크기가 데이터베이스 서버의 물리적 메모리를 초과해야한다는 것입니다.

현재,PostgreSQL테이블 상속을 통한 파티셔닝을 지원합니다. 각 스포츠 토토 결과은 단일 상위 테이블의 자식 테이블로 만들어야합니다. 부모 테이블 자체는 일반적으로 비어 있습니다. 전체 데이터 세트를 나타 내기 만하면 존재합니다. 상속에 익숙해야합니다 (참조스포츠 토토 사이트 PostgreSQL : 문서 : 9.4 : 상속) 파티셔닝을 설정하기 전에.

다음 형식의 파티셔닝은에서 구현할 수 있습니다.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)

   이것은 어떤 스포츠 토토 결과이 어떤 스포츠 토토 결과에 속하는 지 명확하지 않기 때문에 잘못되었습니다.

   범위와 목록 분할 사이의 구문에는 차이가 없습니다. 이 용어는 설명만이 있습니다.

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 SERPARTAINT_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':: 날짜)

스포츠 토토 결과의 일부 또는 전부는 전체 테이블 순차 스캔 대신 인덱스 스캔을 사용할 수 있지만 여기서는이 쿼리에 응답하기 위해 이전 스포츠 토토 결과을 전혀 스캔 할 필요가 없다는 것입니다. 제약 제외를 활성화하면 동일한 답변을 전달할 상당히 저렴한 계획을 얻습니다.

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화재 트리거를 사용하므로 트리거 접근법을 사용하면 정상적으로 사용할 수 있습니다.

규칙 접근법의 또 다른 단점은 규칙 세트가 삽입 날짜를 다루지 않으면 오류를 강제하는 간단한 방법이 없다는 것입니다. 데이터는 대신 마스터 테이블로 조용히 들어갑니다.

파티셔닝을 사용하여 배열 할 수 있습니다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제약. 이러한 사례를 처리 해야하는 경우 스포츠 토토 결과 테이블에 적절한 업데이트 트리거를 넣을 수 있지만 구조 관리가 훨씬 더 복잡해집니다..

• 매뉴얼을 사용하는 경우진공또는분석명령, 각 스포츠 토토 결과에서 개별적으로 실행해야한다는 것을 잊지 마십시오. 명령 :

  측정 분석;

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

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

• 제약 제외는 쿼리의 경우에만 작동합니다여기서절은 상수 (또는 외부로 제공된 매개 변수)를 포함합니다. 예를 들어,와 같은 상상할 수없는 함수에 대한 비교current_timestamp플래너가 런 타임에 기능 값이 어떤 스포츠 토토 결과에 빠질 수 있는지 알 수 없으므로 최적화 할 수 없습니다.

• 분할 제약 조건을 단순하게 유지하십시오. 그렇지 않으면 플래너가 스포츠 토토 결과을 방문 할 필요가 없음을 증명하지 못할 수 있습니다. 목록 분할에 간단한 평등 조건을 사용하거나 앞의 예제와 같이 범위 분할을위한 간단한 범위 테스트를 사용하십시오. 경험의 좋은 규칙은 분할 제약 조건에 B- 트리-무인 연산자를 사용하여 상수와 분할 열의 비교 만 포함해야한다는 것입니다..

• 마스터 테이블의 모든 스포츠 토토 결과에 대한 모든 제약은 제약 제외 중에 검사되므로 많은 스포츠 토토 결과이 쿼리 계획 시간을 상당히 증가시킬 수 있습니다. 이러한 기술을 사용하는 분할은 아마도 백 스포츠 토토 결과까지 잘 작동합니다. 수천 개의 스포츠 토토 결과을 사용하려고하지 마십시오.