[SQL] Inheritance 파티션 (POSTGRESQL)

상속을 통한 파티션 - Partitioning Using Inheritance

9.* 이하 버전에서 쓰던 파티션 방식, 하지만 명시적 파티션보다 파티션 조건을 트리거를 통해 명시함으로써 더 많은 기능으로 활용할 수 있다.

테이블 상속을 사용하여 파티셔닝을 구현할 수 있으며, 다음과 같은 선언적 파티셔닝에서 지원되지 않는 몇 가지 기능을 사용할 수 있다.

• 선언적 파티션은 파티션 테이블과 원본 테이블이 정확히 동일한 열을 가져야 하지만 테이블 상속을 사용하면 자식 테이블에 부모에 열을 달리 할 수 있다
• 테이블 상속은 다중 상속을 허용한다
• 선언적 파티셔닝은 범위, 목록 및 해시 파티셔닝만 지원하는 반면 테이블 상속을 통해 사용자가 선택한 방식으로 데이터를 나눌 수 있다. 그러나 제약 조건 제외가 자식 테이블을 효과적으로 제거할 수 없으면 쿼리 성능이 저하될 수 있다.
• 일부 작업에서는 테이블 상속을 사용할 때 보다 선언적 파티셔닝을 사용할 때 더 강력한 잠금이 필요하다. 예를 들어 파티션을 ACCESS EXCLUSIVE 테이블에서 파티션을 제거하려면 상위 테이블에 대한 ACCESS EXCLUSIVE 잠금을 가져와야 하는 반면, SHARE UPDATE EXCLUSIVE 잠금은 일반 상속의 경우 충분하다

모든 "하위" 테이블이 상속되는 "마스터" 테이블을 만든다. 이 테이블에는 데이터가 포함되지 않습니다. 모든 하위 테이블에 동일하게 적용되지 않는 한 이 테이블에 검사 제약 조건을 정의하면 안 된다. 인덱스나 고유한 제약 조건을 정의해도 의미가 없다.

 

1. 마스터 테이블 생성

마스터 테이블은 위에서 정의된 measurement 테이블이다.

1 2 3 4 5 6

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

cs

 

2. 마스터를 상속하는 자식 테이블 생성

각각 마스터 테이블에서 상속되는 여러 "하위"테이블을 작성한다. 일반적으로 이러한 테이블은 마스터에서 상속된 집합에 열을 추가하지 않는다. 선언적 파티셔닝과 마찬가지로 이러한 테이블은 모든 방법으로 일반적인 PostgreSQL 테이블 (또는 외부 테이블)이다.

제약 조건으로 인해 다른 자식 테이블에서 허용되는 키 값이 겹치지 않아야 한다.

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE measurement_y2020 (     CHECK ( logdate >= DATE '2020-01-01' AND logdate <= DATE '2020-12-31' ) ) INHERITS (measurement);   CREATE TABLE measurement_y2021 (     CHECK ( logdate >= DATE '2021-01-01' AND logdate <= DATE '2021-12-31' ) ) INHERITS (measurement);   CREATE TABLE measurement_y2022 (     CHECK ( logdate >= DATE '2022-01-01' AND logdate < DATE '2022-12-31' ) ) INHERITS (measurement);

 

3. 각 하위 테이블에 대해 키 열에 인덱스 및 원하는 다른 인덱스를 만듭니다.

1 2 3

CREATE INDEX measurement_y2020_logdate ON measurement_y2020 (logdate); CREATE INDEX measurement_y2021_logdate ON measurement_y2021 (logdate); CREATE INDEX measurement_y2022_logdate ON measurement_y2022 (logdate);

cs

 

4. 적절한 트리거 함수를 마스터 테이블에 추가한다. 데이터가 자식에 추가할 수 있도록 간단한 트리거 기능을 사용할 수 있습니다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

-- 트리거 함수 생성 CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN     IF ( NEW.logdate >= DATE '2020-01-01' AND          NEW.logdate <= DATE '2020-12-31' ) THEN         INSERT INTO measurement_y2020 VALUES (NEW.*);     ELSIF ( NEW.logdate >= DATE '2021-01-01' AND             NEW.logdate <= DATE '2021-12-31' ) THEN         INSERT INTO measurement_y2021 VALUES (NEW.*);     ELSIF ( NEW.logdate >= DATE '2022-01-01' AND             NEW.logdate <= DATE '2022-12-31' ) THEN         INSERT INTO measurement_y2022 VALUES (NEW.*);     ELSE         RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';     END IF;     RETURN NULL; END; $$ LANGUAGE plpgsql;     -- 트리거 함수 연결 CREATE TRIGGER measurement_trigger     BEFORE INSERT ON measurement     FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

cs

트리거 정의는 이전과 동일하다. 각 IF 조건은 자식 테이블의 CHECK 제약 조건과 정확히 일치해야 한다.

5. 데이터를 Insert 해보면 measurement에 모든 데이터가 insert 되고 logdate에 따라서 다시 measurement_y2020, measurement_y2021에 데이터가 등록된 것을 확인할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12

insert into measurement values (1, date('2021-06-30'), 1, 1);   insert into measurement values (2, date('2020-04-10'), 2, 2);     select * from measurement;   select * from measurement_y2020;   select * from measurement_y2021;

cs

 

상속 파티션 시 경고 사항

상속을 사용하여 구현된 분할에는 다음 사항을 주의해야 한다.

• 모든 CHECK 제약 조건이 상호 배타적임을 확인하는 자동 방법은 없다. 자식 테이블을 생성하고 관련 개체를 직접 작성하거나 수정하는 것보다 자식 테이블을 생성 및 수정하는 코드를 만드는 것이 더 안전하다.
• 인덱스와 외래 키 제약 조건은 상속 자식이 아닌 단일 테이블에 적용되므로 몇 가지 주의 사항이 있다.
• 자식 파티션에  UPDATE / CHECK 제약 조건을 추가하려 한다면 실패하게 된다. 이러한 경우를 처리해야 하는 경우 하위 테이블에 적합한 업데이트 트리거를 배치할 수 있지만 구조 관리가 훨씬 복잡해진다.
• 수동 VACUUM 또는 ANALYZE 명령을 사용하는 경우 각 하위 테이블에서 개별적으로 명령을 실행해야 한다

 

7. 선언적 파티션 Best Practices

쿼리 계획 및 실행 성능이 불량한 설계로 인해 부정적인 영향을 받을 수 있으므로 테이블을 분할하는 방법을 신중하게 선택해야 한다.

가장 중요한 설계 결정 중 하나는 데이터를 분할하는 열(column)이다. 종종 분할된 테이블에서 실행되는 쿼리의 WHERE 절에 가장 흔하게 나타나는 열 또는 열 집합으로 분할하는 것이 최선의 선택이다. 여기서 파티션 바인딩 제약 조건과 호환되는 절은 불필요한 파티션을 제거하는 데 사용될 수 있다. 그러나 기본 키 또는 고유 제약 조건에 대한 요구 사항에 따라 다른 결정을 내려야 할 수도 있다. 파티션 전략을 계획할 때 원치 않는 데이터를 제거하는 것도 고려해야 한다. 전체 파티션은 상당히 빠르게 분리될 수 있으므로 한 번에 제거할 모든 데이터가 단일 파티션에 배치되도록 파티션 전략을 설계하는 것이 유리할 수 있다.

 

테이블을 분할해야 하는 파티션의 목표 개수를 선택하는 것도 중요한 결정이다. 파티션이 충분하지 않으면 인덱스가 너무 크게 유지되고 데이터 인접성이 저하되어 캐시 적중률이 낮아질 수 있다. 그러나 파티션이 너무 많으면 쿼리 계획 및 실행 중에 쿼리 계획 시간이 길어지고 메모리 사용량이 늘어날 수 있다. 테이블을 분할하는 방법을 선택할 때는 향후 어떤 변경 사항이 발생할지도 고려해야 한다.

예를 들어, 고객당 하나의 파티션을 가지고 있고 현재 소수의 대규모 고객이 있는 경우, 수년 내에 다수의 소규모 고객을 보유하고 있는 경우를 고려해야 한다. 이 경우, LIST로 분할하려고 하기보다는 HASH로 분할하고 합리적인 수의 파티션을 선택하는 것이 더 나을 수 있다.

예를 들어, 고객당 하나의 파티션을 가지고 있고 현재 소수의 대규모 고객이 있는 경우, 수년 내에 다수의 소규모 고객을 보유한다고 가정해 보자. 이 경우, LIST로 분할하려고 하고 데이터를 분할하는 것이 실제적인 것보다 RANGE로 분할하여 각각 정해진 수의 고객을 포함하는 합리적인 수의 파티션을 선택하는 것이 더 나을 수 있다.

하위 파티션은 다른 파티션보다 커질 것으로 예상되는 파티션을 더 분할하는 데 유용할 수 있다. 또 다른 옵션은 파티션 키에 여러 열이 있는 범위 분할을 사용하는 것이다. 이 중 어느 것이든 파티션의 수가 과도하게 늘어날 수 있으므로 파티션의 개수를 제한하는 것이 좋을 수도 있다.

 

쿼리 계획 및 실행 중에 파티션 오버헤드를 고려하는 것이 중요하다. 일반적인 쿼리를 통해 쿼리 계획이 작은 수의 파티션을 제외한 모든 파티션을 제거할 수 있다면 쿼리 실생 계획은 일반적으로 최대 수천 개의 파티션으로 파티션 계층 구조를 상당히 잘 처리할 수 있다. 플래너가 파티션 정리를 수행한 후에도 더 많은 파티션이 남아 있으면 계획 시간은 길어지고 메모리 소비량은 더 많아진다. 이는 UPDATE 및 DELETE 명령의 경우에 특히 해당된다. 많은 수의 파티션을 갖는 것에 대해 우려해야 하는 또 다른 이유는 서버의 메모리 사용량이 시간이 지남에 따라 크게 증가할 수 있으며, 특히 많은 세션이 많은 수의 파티션을 스캔하는 경우 더욱 그렇다. 이는 각 파티션이 해당 파티션에 닿는 각 세션의 로컬 메모리에 메타데이터를 로드해야 하기 때문이다.

데이터 웨어하우스 유형의 워크로드에서는 OLTP 유형의 워크로드보다 더 많은 수의 파티션을 사용하는 것이 합리적이다. 일반적으로 데이터 웨어하우스에서는 쿼리 실행 중에 대부분의 처리 시간이 소요되므로 쿼리 계획 시간은 그다지 중요하지 않다. 이러한 두 가지 워크로드 유형 중 하나를 사용하면 대량의 데이터를 다시 분할하는 작업이 매우 느릴 수 있으므로 올바른 결정을 조기에 내리는 것이 중요하다. 의도된 워크로드의 시뮬레이션은 파티셔닝 전략을 최적화하는 데 종종 유용합니다. 더 많은 파티션이 더 적은 수의 파티션보다 낫다고 가정하거나 그 반대의 경우를 가정하면 안 된다

출처: https://uiandwe.tistory.com/1318 [조아하는모든것]