포스트그레SQL기본 지원 테이블 파티셔닝. 이 섹션에서는 이유와 방법을 설명합니다. 데이터베이스 설계의 일부로 분할을 구현합니다.
파티셔닝은 논리적으로 하나의 큰 것을 분할하는 것을 의미합니다. 테이블을 더 작은 물리적 조각으로 나눕니다. 파티셔닝은 다음을 제공할 수 있습니다. 여러 가지 이점:
다음에 대한 쿼리 성능이 크게 향상될 수 있습니다. 특정 종류의 쿼리.
업데이트 성능도 향상될 수 있습니다. 테이블의 인덱스가 테이블의 인덱스보다 작습니다. 전체 데이터 세트가 됩니다. 인덱스가 더 이상 맞지 않는 경우 메모리에서 쉽게 읽기 및 쓰기 작업을 수행할 수 있습니다. 인덱스는 점점 더 많은 디스크 액세스를 사용합니다.
대량 삭제는 간단히 하나를 제거하여 수행할 수 있습니다. 해당 요구 사항이 계획된 경우 사설 토토 사이트의 사설 토토 사이트 디자인.드롭 테이블이다 대량보다 훨씬 빠릅니다.삭제, ~에게 이어지는 내용은 아무 말도 하지 마세요진공오버헤드.
자주 사용하지 않는 데이터를 더 저렴하고 느린 데이터로 마이그레이션할 수 있습니다. 저장 매체.
이 혜택은 일반적으로 테이블이 있을 때만 가치가 있습니다. 그렇지 않으면 매우 클 것입니다. 테이블이 있는 정확한 지점 애플리케이션에 따라 파티셔닝의 이점을 누릴 수 있습니다. 경험상 테이블의 크기는 데이터베이스 서버의 물리적 메모리를 초과합니다.
현재,PostgreSQL테이블 상속을 통한 파티셔닝을 지원합니다. 각 파티션 단일 상위 테이블의 하위 테이블로 생성되어야 합니다. 는 상위 테이블 자체는 일반적으로 비어 있습니다. 그것은 단지 존재한다 전체 데이터 세트를 나타냅니다. 당신은 익숙해야합니다 상속(참조토토 결과 PostgreSQL : 문서 : 8.1 : 상속) 파티셔닝을 구현하기 전에.
다음 형태의 파티셔닝을 구현할 수 있습니다.PostgreSQL:
테이블은 다음과 같이 분할되어 있습니다."범위"키 열 또는 집합으로 정의됨 값 범위 간에 겹치지 않는 열 다른 사설 토토 사이트에 할당됩니다. 예를 들어 날짜 범위 또는 식별자 범위별로 분할 특정 비즈니스 개체.
테이블은 다음을 명시적으로 나열하여 분할되었습니다. 키 값은 각 파티션에 나타납니다.
해시 파티셔닝은 현재 지원되지 않습니다.
분할된 테이블을 설정하려면 다음을 수행하십시오:
만들기"마스터"테이블, 모든 사설 토토 사이트이 상속됩니다.
이 테이블에는 데이터가 포함되지 않습니다. 검사를 정의하지 마세요. 당신이 의도하지 않는 이상 이 테이블에 대한 제약 조건이 적용됩니다. 모든 사설 토토 사이트에 동일하게 적용됩니다. 아무 의미가 없습니다 이에 대한 인덱스 또는 고유 제약 조건을 정의하고, 어느 쪽이든.
여러 개 생성"아이"테이블 각각은 마스터 테이블에서 상속됩니다. 일반적으로 이러한 테이블은 상속된 집합에 어떤 열도 추가하지 않습니다. 주인님.
하지만 우리는 하위 테이블을 사설 토토 사이트으로 참조할 것입니다. 그들은 모든 면에서 정상입니다PostgreSQL테이블.
정의할 사설 토토 사이트 테이블에 테이블 제약조건을 추가하세요. 각 사설 토토 사이트에 허용되는 키 값입니다.
전형적인 예는 다음과 같습니다:
확인( x = 1 ) CHECK( 카운티 IN( '옥스퍼드셔', '버킹엄셔', '워릭셔' )) 확인( 콘센트 ID = 100 AND 콘센트 ID < 200 )
제약조건이 다음이 없음을 보장하는지 확인하세요. 서로 다른 키 값 사이의 중복이 허용됩니다. 파티션. 흔히 저지르는 실수는 범위 제한을 설정하는 것입니다. 이렇게:
확인( 콘센트 ID 100~200 사이) 확인(200~300 사이의 콘센트 ID)
어느 사설 토토 사이트이 명확하지 않기 때문에 이것은 잘못된 것입니다. 키 값 200이 속해 있습니다.
범위 간 구문에는 차이가 없습니다. 그리고 목록 분할; 그 용어는 설명적이다 만.
각 파티션에 대해 키에 인덱스를 생성하십시오 열뿐만 아니라 원하는 기타 인덱스도 포함됩니다. (키 인덱스는 꼭 필요한 것은 아니지만 대부분의 경우 시나리오가 도움이 됩니다. 핵심 가치를 다음과 같이 설정하려는 경우 고유한 경우 항상 고유한 또는 각 파티션에 대한 기본 키 제약 조건입니다.)
선택적으로 리디렉션할 규칙 또는 트리거를 정의하세요. 마스터 테이블을 적절하게 수정 사설 토토 사이트.
다음을 확인하세요.constraint_exclusion구성 매개변수가 다음에서 활성화되었습니다.postgresql.conf. 이것이 없으면 쿼리는 원하는 대로 최적화되지 않습니다.
예를 들어, 우리가 다음을 위한 데이터베이스를 구축한다고 가정해 보겠습니다. 대형 아이스크림 회사. 회사는 최고 기온을 측정합니다. 매일매일, 지역별 아이스크림 판매도 함께 진행됩니다. 개념적으로 우리는 다음과 같은 테이블을 원합니다.
테이블 측정 생성(
city_id int는 null이 아닙니다.
logdate 날짜가 null이 아닙니다.
피크 온도 정수,
단위 판매 정수
);
우리는 대부분의 쿼리가 지난 주에만 액세스한다는 것을 알고 있습니다. 이 테이블의 주요 사용 이후 월별 또는 분기별 데이터 관리를 위한 온라인 보고서를 준비하는 것입니다. 줄이기 위해 저장해야 하는 오래된 데이터의 양에 따라 최근 3년간의 데이터를 보관합니다. 시작 부분에 매달 가장 오래된 달의 데이터가 삭제됩니다.
이 상황에서 우리는 파티셔닝을 사용하여 다음을 충족할 수 있습니다. 측정 테이블에 대한 우리의 다양한 요구 사항을 모두 충족합니다. 위에 설명된 단계에 따라 파티셔닝을 설정할 수 있습니다. 다음과 같이:
마스터 테이블은 다음과 같습니다.측정테이블, 다음과 같이 정확하게 선언됨 위.
다음으로 각 활성 월에 대해 하나의 파티션을 생성합니다.
CREATE TABLE Measurement_yy04mm02 ( ) 상속(측정); CREATE TABLE Measurement_yy04mm03 ( ) INHERITS (측정값); ... CREATE TABLE Measurement_yy05mm11 ( ) INHERITS (측정값); CREATE TABLE Measurement_yy05mm12 ( ) INHERITS (측정값); CREATE TABLE 측정_yy06mm01( ) 상속(측정);
각 사설 토토 사이트은 그 자체로 완전한 테이블입니다 맞습니다. 하지만 그들은에서 정의를 상속받습니다.측정테이블.
이것은 우리의 문제 중 하나인 오래된 데이터 삭제를 해결합니다. 각각 이번 달에 우리가 해야 할 일은 다음을 수행하는 것뿐입니다.드롭 테이블가장 오래된 하위 테이블에 그리고 새 달의 데이터에 대한 새 하위 테이블을 만듭니다.
우리는 겹치지 않는 테이블 제약 조건을 추가해야 합니다. 테이블 생성 스크립트는 다음과 같습니다.
테이블 생성 측정_yy04mm02(
CHECK( logdate = DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) 상속(측정);
CREATE TABLE 측정_yy04mm03(
CHECK ( logdate = DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) 상속(측정);
...
CREATE TABLE 측정_yy05mm11(
CHECK( logdate = DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
) 상속(측정);
CREATE TABLE 측정_yy05mm12(
CHECK( logdate = DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
) 상속(측정);
CREATE TABLE 측정_yy06mm01(
CHECK ( logdate = DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
) 상속(측정);
아마도 키 열에도 색인이 필요할 것입니다:
측정 인덱스 생성 측정_yy04mm02_logdate ON 측정_yy04mm02(로그 날짜); 측정_yy04mm03_logdate ON 측정 INDEX 생성_yy04mm03(로그 날짜); ... 측정_yy05mm11_logdate ON 측정_yy05mm11(로그 날짜)에 대한 INDEX 생성; 측정_yy05mm12_logdate ON 측정_yy05mm12(로그 날짜)에 대한 INDEX 생성; 측정_yy06mm01_logdate ON 측정 INDEX 생성_yy06mm01(로그 날짜);
우리는 현재 더 이상 색인을 추가하지 않기로 결정했습니다.
데이터가 최신 파티션에만 추가된다면, 데이터를 삽입하는 매우 간단한 규칙을 설정할 수 있습니다. 우리는 항상 다음을 가리키도록 매달 이를 재정의합니다. 현재 파티션.
measurement_current_partition AS 규칙 생성 또는 교체
측정에 삽입 시
대신에
측정_yy06mm01 값에 삽입( NEW.city_id,
NEW.logdate,
NEW.피크온도,
NEW.unitsales );
우리는 데이터를 삽입하고 서버를 갖고 싶을 수도 있습니다 행이 있는 사설 토토 사이트을 자동으로 찾습니다. 추가되어야 합니다. 우리는 더 복잡한 세트로 이것을 할 수 있습니다 아래와 같은 규칙이 적용됩니다.
규칙 만들기 Measurement_insert_yy04mm02 AS
측정 위치에 삽입 중
( logdate = DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
대신에
측정_yy04mm02 값에 삽입( NEW.city_id,
NEW.logdate,
NEW.피크온도,
NEW.unitsales );
...
측정 규칙 만들기_insert_yy05mm12 AS
측정 위치에 삽입 중
( logdate = DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
대신에
측정_yy05mm12 값에 삽입( NEW.city_id,
NEW.logdate,
NEW.피크온도,
NEW.unitsales );
측정 규칙 만들기_insert_yy06mm01 AS
측정 위치에 삽입 중
( logdate = DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
대신에
측정_yy06mm01 값에 삽입( NEW.city_id,
NEW.logdate,
NEW.피크온도,
NEW.unitsales );
참고하세요어디절 각 규칙은 정확히 일치합니다.확인사설 토토 사이트에 대한 제약 조건입니다.
우리가 볼 수 있듯이 복잡한 파티션 구성에는 다음이 필요할 수 있습니다. substantial amount of DDL. 위의 예에서 우리는 매달 새로운 파티션을 생성하므로 다음과 같이 작성하는 것이 현명할 수 있습니다. 필요한 DDL을 자동으로 생성하는 스크립트입니다.
다음 주의사항이 적용됩니다:
현재 모든 내용을 확인할 수 있는 방법은 없습니다.확인제약조건은 상호 독점. 데이터베이스 디자이너의 주의가 필요합니다.
현재 해당 행을 지정하는 간단한 방법은 없습니다 마스터 테이블에 삽입하면 안 됩니다. 갑확인(거짓)마스터 테이블의 제약조건 모든 하위 테이블에 상속되므로 이 목적으로 사용됩니다. 한 가지 가능성은 다음을 설정하는 것입니다.삽입 중마스터에서 트리거 항상 오류가 발생하는 테이블입니다. (또는 이러한 트리거를 사용하여 데이터를 적절한 위치로 리디렉션할 수 있습니다. 제안된 대로 일련의 규칙을 사용하는 대신 하위 테이블 위.)
파티션은 다음을 사용하여 정렬할 수도 있습니다.유니온 올보기:
다음으로 측정 보기 생성
선택 * 측정_yy04mm02에서
UNION ALL SELECT * FROM 측정_yy04mm03
...
UNION ALL SELECT * FROM 측정_yy05mm11
UNION ALL SELECT * FROM 측정_yy05mm12
UNION ALL SELECT * FROM 측정_yy06mm01;
그러나 제약 조건 제외는 현재 다음에 대해 지원되지 않습니다. 이러한 방식으로 정의된 분할된 테이블입니다. 또한, 뷰를 다시 생성하면 추가 및 삭제에 추가 단계가 추가됩니다. 데이터 세트의 개별 파티션.
제약조건 제외쿼리입니다 성능을 향상시키는 최적화 기술 위에서 설명한 방식으로 정의된 분할 테이블입니다. 다음과 같이 예:
SET Constraint_exclusion = 켜짐; SELECT 개수(*) FROM 측정 WHERE logdate = DATE '2006-01-01';
제약조건 제외가 없으면 위 쿼리는 스캔합니다. 의 각 파티션측정테이블. 제약조건 제외 포함 활성화되면 플래너는 각 제약 조건을 검사합니다. 분할하고 분할이 필요하지 않다는 것을 증명하려고 노력하십시오. 조건을 충족하는 행을 포함할 수 없기 때문에 스캔되었습니다. 쿼리어디절. 기획자는 언제 이를 증명할 수 있으면 쿼리에서 사설 토토 사이트을 제외합니다. 계획.
당신은 다음을 사용할 수 있습니다설명명령을 내리다 다음 계획의 차이점을 보여주세요.constraint_exclusion켜져 있고 꺼진 계획도 있습니다. 이러한 유형의 테이블 설정에 대한 일반적인 기본 계획은 다음과 같습니다.
SET Constraint_exclusion = 꺼짐;
EXPLAIN SELECT count(*) FROM 측정 WHERE logdate = DATE '2006-01-01';
쿼리 계획
---------------------------------------------------------------------
집계(비용=158.66..158.68행=1 너비=0)
- 추가(비용=0.00..151.88행=2715너비=0)
- 측정 시 Seq 스캔(비용=0.00..30.38행=543너비=0)
필터: (logdate = '2006-01-01'::date)
- 측정 시 Seq 스캔_yy04mm02 측정(비용=0.00..30.38 행=543 너비=0)
필터: (logdate = '2006-01-01'::date)
- 측정 시 Seq 스캔_yy04mm03 측정(비용=0.00..30.38행=543너비=0)
필터: (logdate = '2006-01-01'::date)
...
- 측정 시 Seq 스캔_yy05mm12 측정(비용=0.00..30.38행=543너비=0)
필터: (logdate = '2006-01-01'::date)
- 측정 시 Seq 스캔_yy06mm01 측정(비용=0.00..30.38행=543너비=0)
필터: (로그 날짜 = '2006-01-01'::날짜)
파티션의 일부 또는 전체가 인덱스 스캔을 대신 사용할 수 있습니다. 전체 테이블 순차 스캔이 있지만 여기서 중요한 점은 응답하기 위해 이전 파티션을 전혀 스캔할 필요가 없습니다. 이 쿼리. 제약 조건 제외를 활성화하면 다음과 같은 결과를 얻습니다. 동일한 서비스를 제공하는 대폭 축소된 계획 대답:
SET Constraint_exclusion = 켜기;
EXPLAIN SELECT count(*) FROM 측정 WHERE logdate = DATE '2006-01-01';
쿼리 계획
---------------------------------------------------------------------
집계(비용=63.47..63.48행=1너비=0)
- 추가(비용=0.00..60.75행=1086너비=0)
- 측정 시 Seq 스캔(비용=0.00..30.38행=543너비=0)
필터: (logdate = '2006-01-01'::date)
- 측정 시 Seq 스캔_yy06mm01 측정(비용=0.00..30.38행=543너비=0)
필터: (로그 날짜 = '2006-01-01'::날짜)
제약조건 제외는 다음에 의해서만 구동됩니다.확인제약조건이 존재하지 않음 인덱스. 따라서 인덱스를 정의할 필요가 없습니다. 키 열. 특정 인덱스에 대해 인덱스를 생성해야 하는지 여부 사설 토토 사이트은 스캔하는 쿼리를 예상하는지 여부에 따라 달라집니다. 사설 토토 사이트은 일반적으로 사설 토토 사이트의 많은 부분을 스캔합니다. 아니면 단지 작은 부분일 뿐이죠. 색인은 후자에 도움이 될 것입니다 경우는 있지만 전자는 아닙니다.
다음 주의사항이 적용됩니다:
제약조건 제외는 쿼리가 다음과 같은 경우에만 작동합니다.어디에서절에 상수가 포함되어 있습니다. 에이
매개변수화된 쿼리는 최적화되지 않습니다.
플래너는 매개변수 값이 어떤 사설 토토 사이트인지 알 수 없습니다.
런타임 시 선택할 수 있습니다. 같은 이유로,"안정적"다음과 같은 함수CURRENT_DATE피해야 합니다. 가입
다른 테이블의 열에 대한 사설 토토 사이트 키는
최적화되어 있습니다.
다음에서 교차 데이터 유형 비교를 피하십시오.확인제약조건, 계획자가 원하는대로 현재 그러한 조건이 거짓임을 증명하는 데 실패했습니다. 예를 들어, 다음 제약 조건은 다음과 같은 경우에 작동합니다.x은정수열, 그러나 if는 아님x은비긴트:
확인( x = 1 )
abigint열을 사용해야 합니다. 다음과 같은 제약 조건:
확인( x = 1::bigint )
문제는 다음에 국한되지 않습니다.비긴트데이터 유형 — 이는 다음과 같은 경우에 발생할 수 있습니다. 상수의 기본 데이터 유형이 데이터와 일치하지 않습니다. 비교되는 열의 유형입니다. 제공된 쿼리의 교차 데이터 유형 비교는 다음과 같습니다. 일반적으로 괜찮습니다. 하지만 해당 지역에는 없습니다.확인조건.
업데이트그리고삭제마스터 테이블에 대한 명령은 다음을 수행합니다 현재 제약 조건 제외를 수행하지 않습니다.
마스터 테이블의 모든 사설 토토 사이트에 대한 모든 제약 조건 제약 조건 제외를 위해 고려되므로 숫자가 큽니다. 사설 토토 사이트으로 인해 쿼리 계획 시간이 늘어날 가능성이 높습니다. 상당히.
아직도 달려야 한다는 것을 잊지 마세요분석각 파티션에 개별적으로. 에이 다음과 같은 명령
측정 분석;
마스터 테이블만 처리합니다.