PostgreSQL기본을 지원합니다 테이블 토토 핫셔닝. 이 섹션에서는 이유와 방법에 대해 설명합니다 데이터베이스 디자인의 일부로 토토 핫셔닝 구현.
토토 핫션은 논리적으로 하나의 큰 것을 분할하는 것을 말합니다 작은 물리적 조각으로 테이블. 분할이 제공 할 수 있습니다 몇 가지 이점 :
쿼리 성능을 크게 향상시킬 수 있습니다 특정 상황, 특히 대부분의 경우 테이블의 액세스 행은 단일 토토 핫션 또는 소수의 토토 핫션. 분할 대체물 인덱스의 주요 열의 경우 인덱스 크기를 줄입니다 무겁게 사용되는 부분이 메모리에 적합한 색인.
쿼리 또는 업데이트가 많은 비율에 액세스 할 때 단일 토토 핫션, 성능을 개선 할 수 있습니다 대신 해당 토토 핫션의 순차적 스캔의 장점 인덱스와 임의의 액세스를 사용하여 전체 테이블.
벌크 하중 및 삭제는 추가 또는 해당 요구 사항이 계획된 경우 토토 핫션 제거 토토 핫션 디자인.변경 테이블는 대량 작동보다 훨씬 빠릅니다. 또한 완전히 피해진공벌크로 인한 오버 헤드삭제.
거의 사용하지 않는 데이터를 저렴하고 느리게 마이그레이션 할 수 있습니다 스토리지 미디어.
혜택은 일반적으로 테이블 일 때만 가치가 있습니다. 그렇지 않으면 매우 클 것입니다. 테이블의 정확한 지점 분할의 혜택은 응용 프로그램에 따라 다릅니다. 경험의 규칙은 테이블의 크기가 데이터베이스 서버의 물리적 메모리를 초과합니다.
현재,PostgreSQL테이블 상속을 통한 토토 핫셔닝을 지원합니다. 각 토토 핫션 단일 부모 테이블의 자식 테이블로 만들어야합니다. 그만큼 부모 테이블 자체는 일반적으로 비어 있습니다. 그것은 단지 존재합니다 전체 데이터 세트를 나타냅니다. 당신은 익숙해야합니다 상속 (참조범퍼카 토토 PostgreSQL : 문서 : 8.2 : 상속) 토토 핫셔닝을 설정하기 전에.
다음 형식의 파티셔닝은 구현 될 수 있습니다.PostgreSQL:
테이블이 분할되어"Ranges"키 열 또는 세트로 정의됩니다 값의 범위에 겹치지 않는 열 다른 토토 핫션에 할당됩니다. 예를 들어, 하나는 할 수 있습니다 날짜 범위 또는 식별자 범위별로 분할 특정 비즈니스 개체.
테이블은 명시 적으로 나열하여 토토 핫션됩니다 각 토토 핫션에 주요 값이 나타납니다.
분할 된 테이블을 설정하려면 다음을 수행하십시오.
생성"마스터"테이블, 모든 토토 핫션이 상속 될 것입니다.
이 테이블에는 데이터가 포함되지 않습니다. 수표를 정의하지 마십시오 이 테이블의 제약 조건, 당신이 그들을 의도하지 않는 한 모든 토토 핫션에 똑같이 적용됩니다. 요점이 없습니다 인덱스 또는 고유 한 제약 조건을 정의하고 어느 하나.
몇 가지 생성"Child"테이블 마스터 테이블에서 각각 상속합니다. 일반적으로, 이것들 테이블은 상속 된 세트에 열을 추가하지 않습니다. 마스터.
우리는 하위 테이블을 토토 핫션이라고합니다. 그들은 모든면에서 정상입니다PostgreSQL테이블.
토토 핫션 테이블에 테이블 제약 조건을 추가하여 정의합니다 각 토토 핫션에서 허용 된 주요 값.
일반적인 예는 다음과 같습니다.
점검 (x = 1) Check (County in County ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire')))) Check (outletid = 100 및 outletid <200)
제약 조건에 없음을 보장하십시오 다르게 허용되는 키 값간에 중첩됩니다 토토 핫션. 일반적인 실수는 범위 제약 조건을 설정하는 것입니다 이와 같이:
점검 (100에서 200 사이의 outletid) 확인 (200에서 300 사이의 outletid)
어느 토토 핫션이 있는지 명확하지 않기 때문에 이것은 잘못되었습니다. 키 값은 200에 속합니다.
범위 간의 구문에는 차이가 없습니다. 토토 핫셔닝을 나열합니다. 이러한 용어는 설명 적입니다 오직.
각 토토 핫션마다 키에 인덱스를 만듭니다 열, 원하는 다른 인덱스뿐만 아니라 열이 있습니다. (주요 지수는 엄격하게 필요하지 않지만 대부분 시나리오 도움이됩니다. 핵심 값을 의도한다면 독특한 그러면 항상 독특한 또는 각 토토 핫션에 대한 1 차 키 제약 조건.)
선택적으로, 규칙을 정의하거나 리디렉션하기위한 트리거를 정의하십시오 마스터 테이블의 적절한 수정 분할.
제약 _exclusion구성 매개 변수가 활성화되어postgresql.conf. 이것 없이는 쿼리가됩니다 원하는대로 최적화되지 않습니다.
예를 들어, 우리가 대형 아이스크림 회사. 회사는 최대 온도를 측정합니다 각 지역의 아이스크림 판매뿐만 아니라 매일. 개념적으로 우리는 다음과 같은 테이블을 원합니다 :
테이블 측정 생성 (
city_id int null not,
로그 데이트 날짜는 null이 아닙니다.
PeakTemp int,
단위 int
);
우리는 대부분의 쿼리가 지난 주에만 액세스한다는 것을 알고 있습니다. 이 테이블의 주요 사용 이후 월 또는 분기 데이터 관리를 위해 온라인 보고서를 준비하는 것입니다. 줄이기 위해 저장 해야하는 오래된 데이터의 양, 우리는 가장 최근 3 년간의 데이터를 유지하십시오. 시작시 매달 우리는 가장 오래된 달의 데이터를 제거 할 것입니다.
이 상황에서 우리는 파티셔닝을 사용하여 우리가 만나는 데 도움을 줄 수 있습니다. 측정 테이블에 대한 모든 다른 요구 사항. 위에서 설명한 단계에 따라 파티션을 설정할 수 있습니다. 다음과 같이 :
마스터 테이블은입니다.측정테이블, 정확히 다음과 같이 선언했습니다 위에.
다음으로 우리는 활성 달마다 하나의 파티션을 만듭니다 :
테이블 측정 생성 _y2004m02 () 상속 (측정); 테이블 측정 _y2004m03 () 상속 (측정)을 만듭니다. ... 테이블 측정 _y2005m11 () 상속 (측정)을 작성합니다. 테이블 측정 _y2005m12 () 상속 (측정)을 작성합니다. 테이블 측정 _y2006m01 () 상속 (측정); 생성
각 토토 핫션은 자신의 완전한 테이블입니다. 맞아요.하지만 그들은에서 그들의 정의를 물려받습니다.측정테이블.
이것은 우리의 문제 중 하나를 해결합니다 : 이전 데이터 삭제. 각 달, 우리가해야 할 일은 a를 수행하는 것입니다.드롭 테이블가장 오래된 어린이 테이블에서 새 달의 데이터에 대한 새 자식 테이블 만들기.
우리는 겹치지 않는 테이블 제약 조건을 추가해야합니다 테이블 작성 스크립트가됩니다.
테이블 생성 측정 _y2004m02 (
check (logdate = date '2004-02-01'및 logdate <날짜 '2004-03-01'))
) 상속 (측정);
테이블 측정 _y2004m03 생성 (
check (logdate = date '2004-03-01'및 logdate <날짜 '2004-04-01')
) 상속 (측정);
...
테이블 측정 값 생성 _y2005M11 (
check (logdate = date '2005-11-01'및 logdate <날짜 '2005-12-01'))
) 상속 (측정);
테이블 측정 _y2005m12 생성 (
check (logdate = date '2005-12-01'및 logdate <날짜 '2006-01-01')
) 상속 (측정);
테이블 측정 _y2006m01 생성 (
check (logdate = date '2006-01-01'및 logdate <날짜 '2006-02-01')
) 상속 (측정);
아마도 주요 열에 인덱스가 필요할 것입니다 :
Measurement_Y2004M02 (logdate)에서 인덱스 측정 _y2004m02_Logdate 만들기; Measurement_Y2004M03 (logdate)에서 색인 측정 _y2004m03_logdate를 만듭니다. ... Measurement_Y2005M11 (logdate)에서 색인 측정 _y2005M11_LogDate를 만듭니다. Measurement_Y2005M12 (logdate)에서 색인 측정 _y2005M12_Logdate를 만듭니다. 인덱스 측정 _y2006m01_logdate를 측정 _y2006m01 (logdate); 생성합니다.
현재 추가 인덱스를 추가하지 않기로 선택합니다.
데이터가 최신 토토 핫션에만 추가되면, 우리는 데이터를 삽입하는 매우 간단한 규칙을 설정할 수 있습니다. 우리는해야합니다 매달 매월 재정의하여 항상 현재 토토 핫션.
규칙 측정을 만들거나 교체하십시오
측정에 삽입합니다
대신하십시오
측정 _y2006m01 값에 삽입 (new.city_id,
new.logdate,
new.peaktemp,
new.unitsales);
우리는 데이터를 삽입하고 서버를 가질 수 있습니다. 행이있는 토토 핫션을 자동으로 찾습니다 추가해야합니다. 우리는 더 복잡한 세트로 이것을 할 수 있습니다 아래에 표시된 규칙의.
규칙 생성 _insert_y2004m02 AS
측정 값으로 삽입하십시오
(logdate = date '2004-02-01'및 logdate <날짜 '2004-03-01'))
대신하십시오
측정 _y2004m02 값에 삽입 (new.city_id,
new.logdate,
new.peaktemp,
new.unitsales);
...
규칙 측정 _insert_y2005m12를 만듭니다
측정 값으로 삽입하십시오
(logdate = date '2005-12-01'및 logdate <날짜 '2006-01-01'))
대신하십시오
측정 _y2005m12 값에 삽입 (new.city_id,
new.logdate,
new.peaktemp,
new.unitsales);
규칙 측정 _insert_y2006m01을 만듭니다
측정 값으로 삽입하십시오
(logdate = date '2006-01-01'및 logdate <날짜 '2006-02-01'))
대신하십시오
측정 _y2006m01 값에 삽입 (new.city_id,
new.logdate,
new.peaktemp,
new.unitsales);
여기서조항 각 규칙은 정확히와 일치합니다.check토토 핫션에 대한 제약 조건.
우리가 볼 수 있듯이 복잡한 분할 계획에는 상당한 양의 DDL. 위의 예에서는 우리가 될 것입니다 매달 새로운 파티션을 만들므로 글을 쓰는 것이 현명 할 수 있습니다. 필요한 DDL을 자동으로 생성하는 스크립트.
토토 핫셔닝도 a를 사용하여 배열 할 수 있습니다.Union All보기 :
보기 측정을 만듭니다
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;
그러나보기를 재현해야 할 필요성은 추가 단계를 추가합니다. 데이터 세트의 개별 파티션 추가 및 삭제.
일반적으로 처음에 설정된 토토 핫션 세트 테이블을 정의하는 것은 정적을 유지하기위한 것이 아닙니다. 그것은 구식 토토 핫션을 제거하고 싶고 새로운 데이터에 대한 새로운 토토 핫션을 주기적으로 추가하십시오. 가장 중 하나입니다 분할의 중요한 장점은 정확히 그것입니다 이 고통스러운 작업을 거의 실행할 수 있습니다 오히려 토토 핫션 구조를 조작하여 즉시 많은 양의 데이터를 물리적으로 움직이는 것보다.
오래된 데이터를 제거하기위한 가장 간단한 옵션은 단순히 삭제하는 것입니다. 더 이상 필요하지 않은 파티션 :
드롭 테이블 측정 _y2003m02;
이것은 수백만 레코드를 매우 빠르게 삭제할 수 있습니다. 모든 레코드를 개별적으로 삭제할 필요는 없습니다.
종종 바람직한 또 다른 옵션은 분할 된 테이블에서 분할하지만 그 자체로 테이블 :
ALTER 테이블 측정 _Y2003M02 상속 측정 없음;
이것은 데이터에서 추가 작업을 수행 할 수 있습니다. 떨어지기 전에. 예를 들어, 이것은 종종 유용한 시간입니다 사용하여 데이터를 백업하려면COPY, pg_dump또는 유사한 도구. 그것 데이터를 더 작은 것으로 집계하는 데 유용한 시간이 될 수 있습니다. 형식, 기타 데이터 조작을 수행하거나 보고서를 실행합니다.
마찬가지로 새로운 데이터를 처리하기 위해 새 토토 핫션을 추가 할 수 있습니다. 우리 분할 된 테이블에서 빈 토토 핫션을 만들 수 있습니다. 원래 토토 핫션은 위에 만들어졌습니다.
테이블 생성 _y2006m02 (
check (logdate = date '2006-02-01'및 logdate <날짜 '2006-03-01')
) 상속 (측정);
대안으로, 때로는 생성하는 것이 더 편리합니다. 파티션 구조 외부의 새로운 테이블을 나중에 적절한 파티션. 이를 통해 데이터를로드 할 수 있습니다. 점검하고, 그 전에 나타나기 전에 변형되었습니다 분할 된 테이블.
테이블 측정 _y2006m02 생성 (제약 조건을 포함한 기본값을 포함한 측정과 같은); Alter Table Measurement_Y2006M02 추가 제약 조건 Y2006M02 check (logdate = date '2006-02-01'및 logdate <날짜 '2006-03-01'); \ '측정 _y2006m02'에서 측정 _y2006m02 복사 - 아마도 다른 데이터 준비 작업 Alter Table Measurement_Y2006M02 상속 측정;
제약 제외는 쿼리입니다 성능을 향상시키는 최적화 기술 위에서 설명한 방식으로 정의 된 파티션 테이블. 처럼 예 :
SET SERPARTAINT_EXCLUSION = ON; 로그 데이트 = 날짜 '2006-01-01'; 측정에서 count (*)를 선택하십시오.
제약 제외가 없으면 위의 쿼리가 스캔됩니다 의 각 파티션측정테이블. 제약 제외 활성화 된 플래너는 각각의 제약 조건을 조사합니다 파티션이 파티션이 될 필요가 없음을 증명하려고 노력합니다. 줄을 섭취 할 수 없기 때문에 스캔했습니다. 쿼리여기서절. 플래너시기 이것을 증명할 수 있고, 쿼리에서 토토 핫션을 제외합니다. 계획.
당신은를 사용할 수 있습니다설명명령으로 계획의 차이점제약 _exclusionON 및 계획. 이 유형의 테이블 설정에 대한 일반적인 기본 계획은 다음과 같습니다.
SET SERPARTAINT_EXCLUSION = OFF;
로그 데이트 = 날짜 '2006-01-01'에서 측정에서 선택한 수 (*)를 설명하십시오.
쿼리 계획
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
집계 (비용 = 158.66..158.68 행 = 1 너비 = 0)
- Append (비용 = 0.00..151.88 행 = 2715 너비 = 0)
- 측정시 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2006-01-01':: 날짜)
- 측정 _y2004m02 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2006-01-01':: 날짜)
- 측정 _y2004m03 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2006-01-01':: 날짜)
...
- 측정 _y2005m12 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2006-01-01':: 날짜)
- 측정 _y2006m01 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2006-01-01':: 날짜)
파티션 중 일부 또는 전부는 대신 인덱스 스캔을 사용할 수 있습니다. 전체 테이블 순차적 스캔이지만 여기서 요점은 대답하기 위해 이전 파티션을 전혀 스캔 할 필요가 없습니다. 이 쿼리. 제약 제외를 활성화하면 a를 얻습니다 동일하게 제공 할 계획을 크게 줄였습니다 답변:
SET SORMSARTAINT_EXCLUSION = ON;
로그 데이트 = 날짜 '2006-01-01'에서 측정에서 선택한 수 (*)를 설명하십시오.
쿼리 계획
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
집계 (비용 = 63.47..63.48 행 = 1 너비 = 0)
- Append (비용 = 0.00..60.75 행 = 1086 너비 = 0)
- 측정시 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2006-01-01':: 날짜)
- 측정 _y2006m01 측정에서 SEQ 스캔 (비용 = 0.00..30.38 행 = 543 너비 = 0)
필터 : (logdate = '2006-01-01':: 날짜)
제약 제외는에 의해서만 구동됩니다.check존재가 아니라 제약 인덱스. 따라서 인덱스를 정의 할 필요는 없습니다 주요 열. 주어진에 대해 인덱스를 만들어야하는지 여부 토토 핫션은 스캔 쿼리를 기대하는지 여부에 따라 토토 핫션은 일반적으로 토토 핫션의 많은 부분을 스캔합니다. 아니면 작은 부분. 색인은 후자에 도움이 될 것입니다 사례는 아니지만 전자는 아닙니다.
다음 경고는 분할 된 테이블에 적용됩니다 :
현재 모든 것을 확인할 방법이 없습니다check제약 조건은 상호입니다 독점적인. 데이터베이스 디자이너는 치료가 필요합니다.
현재 행을 지정할 간단한 방법은 없습니다. 마스터 테이블에 삽입해서는 안됩니다. 에이check (false)마스터 테이블의 제약 모든 어린이 테이블에 의해 상속 될 것이므로 이 목적으로 사용됩니다. 한 가지 가능성은를 설정하는 것입니다.insert마스터의 트리거 항상 오류가 발생하는 테이블. (또는 그러한 A. 트리거를 사용하여 데이터를 올바른 상태로 리디렉션 할 수 있습니다. 제안 된대로 일련의 규칙을 사용하는 대신 자식 테이블 위에.)
다음 경고는 제약 제외에 적용됩니다 :
제약 제외는 쿼리의 경우에만 작동합니다여기서절에 상수가 포함됩니다. 에이
매개 변수화 된 쿼리는 이후로 최적화되지 않습니다
Planner는 매개 변수 값을 분할 할 수 없습니다
런타임에 선택할 수 있습니다. 같은 이유로"안정"와 같은 기능current_date피해야합니다.
교차 데이터 유형 비교를 피하십시오check플래너와대로 제약 조건 현재 그러한 조건이 거짓을 증명하지 못한다. 예를 들어, 다음 제약 조건이 작동합니다xis정수열이지만 if.xisbigint:
check (x = 1)
abigint열을 사용해야합니다 제약 조건 :
check (x = 1 :: bigint)
문제는에 국한되지 않습니다.bigint데이터 유형 -마다 발생할 수 있습니다 상수의 기본 데이터 유형은 데이터와 일치하지 않습니다. 비교되는 열의 유형. 제공된 쿼리의 교차 데이터 유형 비교는 다음과 같습니다 보통 괜찮아,check조건
마스터 테이블의 모든 파티션에 대한 모든 제약 제약 제외로 고려되므로 많은 수가 있습니다 파티션의 쿼리 계획 시간이 증가 할 가능성이 높습니다 상당히.
여전히 실행해야한다는 것을 잊지 마십시오분석각 토토 핫션에서 개별적으로. 에이 명령
측정 분석;
마스터 테이블 만 처리합니다.