1) 선언적 파티션선언적 파티션 구성 실습은 postgres 10.X 이상에서만 가능하다. Show 9.* 버전의 경우 테이블 파티션 명령어가 없기 때문에 2) Partitioning Using Inheritance(하단 참조)를 사용해야 한다.
1. 파티션 테이블 생성대형 아이스크림 회사를 위한 데이터베이스를 구축한다고 가정합니다. 이 회사는 매일 최고 기온과 각 지역의 아이스크림 판매량을 저장할 테이블을 다음과 같이 예정이다.
저장해야하는 오래된 데이터의 양을 줄이기 위해 가장 최근 2년 분량의 데이터만 보관하기로 결정했다. 매년 초에 가장 오래된 년도의 데이터를 제거할 것이다. 이 상황에서 파티셔닝을 사용하여 measurement 테이블에 대한 다양한 요구 사항을 모두 충족할 수 있다. 선언적 파티셔닝을 사용1. 파티션 방법은 분할 key로 사용할 열(column) 목록을 포함해야 한다. PARTITION BY RANGE절을 지정하여 테이블을 파티션 된 테이블로 만든다. 아래의 쿼리는 logdate(날짜)를 기준으로 파티션을 설정한다.
2. 각 파티션 정의는 부모 테이블 파티션 방법 및 파티션 키에 해당하는 경계를 지정해야 한다. 새 파티션의 값이 하나 이상의 기존 파티션에 있는 값과 겹치도록 경계를 지정하면 오류가 발생한다. 이렇게 생성된 파티션은 모든 방법으로 일반적인 PostgreSQL 테이블이다. 각 파티션에 대한 테이블 스페이스 및 스토리지 매개 변수를 개별적으로 지정할 수 있다. 위의 예제의 경우 각 파티션은 한 번에 1년의 데이터를 삭제해야하는 요구 사항과 일치하도록 테이블 생성 쿼리는 다음과 같다.
생성후 테이블의 구조. 해당 테이블의 하위에 파티션으로 생성한 테이블이 위치한다.
postgres에서 테이블스페이스는 DB 관리자에 의해 데이터베이스의 객체가 저장될 수 있는 파일 시스템의 경로로 정의된다. 테이블스페이스가 생성되면 데이터베이스 객체에 객체를 생성할 때 이름에 의해서 테이블스페이스가 참조될 수 있다. 장점
만약 물리적으로 다른 경로를 DB에 사용하고자 할 때 테이블스페이스로 지정하여 DB를 확장할 수 있다. 테이블 스페이스는 다음과 같은 DDL 문으로 생성할 수 있으며 해당 테이블스페이스에 태이블을 생성할 수 있다.
테이블 스페이스 생성 확인
3. 분할 된 테이블의 키 열과 원하는 다른 인덱스에 인덱스를 생성한다. 키 인덱스가 꼭 필요한 것은 아니지만 대부분의 시나리오에서 유용하다. 이렇게 하면 각 파티션에 일치하는 인덱스가 자동으로 만들어지며 나중에 만들거나 연결하는 모든 파티션에도 인덱스가 생성된다. 분할된 테이블에 선언된 인덱스 또는 고유 제약 조건은 분할된 테이블과 동일한 방식으로 동작한다. 실제 데이터는 개별 파티션 테이블의 하위 인덱스에 존재한다.
4. postgresql.conf 에서 enable_partition_pruning 구성 매개 변수가 비활성화되어 있지 않은지 확인한다 . 그렇다면 쿼리가 원하는대로 최적화되지 않는다. 위의 예에서는 매년 새 파티션을 작성하므로 필요한 DDL을 자동으로 생성하는 스크립트를 작성하는 것이 좋습니다. 2. insert 테스트
위와 같이 measurement 테이블 조회시 데이터가 추가된 것을 볼 수 있다. 또한 해당 데이터는 measurement_y2021에 들어간것을 확인할 수 있다.
insert시 파티션으로 설정된 칼럼 범위에 포함된 데이터가 아니라면 에러가 발생한다. ex: 파티션의 범위가 2020, 2021, 2022년 이므로 2000년의 데이터는 에러가 발생한다.
3. 파티션 테이블 삭제필요없는 데이터가 축적된 2020년의 테이블(오래된 데이터)을 제거하는 가장 간단한 옵션은 더 이상 필요하지 않은 파티션을 삭제하는 것입니다.
4. 실행 계획 + 파티션 프루닝파티션 프루닝은 선언적으로 파티션 된 테이블은 성능을 향상시키는 쿼리 최적화 기술이다.
파티션 프루닝이 없으면 위의 쿼리는 measurement 테이블 의 각 파티션을 스캔합니다. 파티션을 사용하면 실행계획은 각 파티션의 정의를 검사하고 쿼리의 WHERE 절을 충족하는 행을 포함된 파티션 만을 스캔한다. EXPLAIN 명령과 enable_partition_pruning 구성 매개 변수를 사용하면 파티션 계획과 그렇지 않은 계획의 차이를 표시 할 수 있다. 이 유형의 테이블 설정에 대한 일반적인 최적화되지 않은 계획은 다음과 같습니다.
프루닝은 전체 테이블 순차 스캔 대신 이 쿼리에서는 파티션의 범위에 따라 이전 테이블(2021 이전 테이블)을 전혀 스캔할 필요가 없다는 것입니다. 파티션 정리를 사용하면 동일한 답변을 제공하는 훨씬 효율적인 실행계획을 얻을 수 있다.
파티션 프루닝은 인덱스의 존재가 아니라 파티션 키에 의해 암시적으로 정의 된 제한 조건에 의해서만 구동된다 따라서 키 열에 인덱스를 정의 할 필요는 없다. 지정된 파티션에 대해 인덱스를 작성해야 하는지 여부는 파티션을 스캔하는 쿼리가 따라 다르다. 5. partition by range multiple columns - 복합 칼럼 파티션
복합 칼럼을 파티션을 생성할 경우 해당 범위가 겹칠수 없다. (겹친다면 파티션 생성 시 에러가 발생한다)
데이터의 추가 일 경우 서로 겹치는 데이터를 넣을 경우 ( 두번째로 실행한 insert는 date()의 범위는 첫 번째 테이블인 measurement_y2021_city_1_to_100에 속하지만, city_id는 두 번째 테이블인 measurement_y2022_city_101_to_200에 속한다) 파티셔닝의 레인지의 순서대로 보기 때문에 (logdate 먼저, 체크 후 city_id ) 첫 번째 테이블인 measurement_y2021_city_1_to_100로 들어간다.
6. 선언적 파티셔닝의 한계
PRIMARY KEY or unique 추가를 한다면 아래와 같이 해야 한다
개별 파티션은 백그라운드에서 상속을 사용하여 부모테이블에 연결된다. 선언적으로 분할된 테이블 또는 해당 파티션과 함께 상속의 모든 일반 기능을 사용할 수는 없다. 특히 파티션은 파티션 테이블 이외의 상위 테이블을 가질 수 없으며 파티션 테이블과 일반 테이블에서 상속할 수도 없다. 분할된 테이블과 해당 파티션으로 구성된 파티션 계층은 여전히 상속 계층이므로, 몇 가지 예외를 제외하고 모든 일반 상속 규칙이 적용된다.
2) 상속을 통한 파티션 - Partitioning Using Inheritance
테이블 상속을 사용하여 파티셔닝을 구현할 수 있으며, 다음과 같은 선언적 파티셔닝에서 지원되지 않는 몇 가지 기능을 사용할 수 있다.
모든 "하위" 테이블이 상속되는 "마스터" 테이블을 만든다. 이 테이블에는 데이터가 포함되지 않습니다. 모든 하위 테이블에 동일하게 적용되지 않는 한 이 테이블에 검사 제약 조건을 정의하면 안 된다. 인덱스나 고유한 제약 조건을 정의해도 의미가 없다. 1. 마스터 테이블 생성 마스터 테이블은 위에서 정의된 measurement 테이블이다.
2. 마스터를 상속하는 자식 테이블 생성 각각 마스터 테이블에서 상속되는 여러 "하위"테이블을 작성한다. 일반적으로 이러한 테이블은 마스터에서 상속된 집합에 열을 추가하지 않는다. 선언적 파티셔닝과 마찬가지로 이러한 테이블은 모든 방법으로 일반적인 PostgreSQL 테이블 (또는 외부 테이블)이다 제약 조건으로 인해 다른 자식 테이블에서 허용되는 키 값이 겹치지 않아야 한다.
3. 각 하위 테이블에 대해 키 열에 인덱스 및 원하는 다른 인덱스를 만듭니다.
4. 적절한 트리거 함수를 마스터 테이블에 추가한다. 데이터가 자식에 추가할 수 있도록 간단한 트리거 기능을 사용할 수 있습니다.
트리거 정의는 이전과 동일하다. 각 IF 조건은 자식 테이블의 CHECK 제약 조건과 정확히 일치해야 한다. 5. 데이터를 Insert 해보면 measurement에 모든 데이터가 insert 되고 logdate에 따라서 다시 measurement_y2020, measurement_y2021에 데이터가 등록된 것을 확인할 수 있다.
상속 파티션 시 경고 사항상속을 사용하여 구현된 분할에는 다음 사항을 주의해야 한다.
7. 선언적 파티션 Best Practices쿼리 계획 및 실행 성능이 불량한 설계로 인해 부정적인 영향을 받을 수 있으므로 테이블을 분할하는 방법을 신중하게 선택해야 한다. 가장 중요한 설계 결정 중 하나는 데이터를 분할하는 열(column)이다. 종종 분할된 테이블에서 실행되는 쿼리의 WHERE 절에 가장 흔하게 나타나는 열 또는 열 집합으로 분할하는 것이 최선의 선택이다. 여기서 파티션 바인딩 제약 조건과 호환되는 절은 불필요한 파티션을 제거하는 데 사용될 수 있다. 그러나 기본 키 또는 고유 제약 조건에 대한 요구 사항에 따라 다른 결정을 내려야 할 수도 있다. 파티션 전략을 계획할 때 원치 않는 데이터를 제거하는 것도 고려해야 한다. 전체 파티션은 상당히 빠르게 분리될 수 있으므로 한 번에 제거할 모든 데이터가 단일 파티션에 배치되도록 파티션 전략을 설계하는 것이 유리할 수 있다. 테이블을 분할해야 하는 파티션의 목표 개수를 선택하는 것도 중요한 결정이다. 파티션이 충분하지 않으면 인덱스가 너무 크게 유지되고 데이터 인접성이 저하되어 캐시 적중률이 낮아질 수 있다. 그러나 파티션이 너무 많으면 쿼리 계획 및 실행 중에 쿼리 계획 시간이 길어지고 메모리 사용량이 늘어날 수 있다. 테이블을 분할하는 방법을 선택할 때는 향후 어떤 변경 사항이 발생할지도 고려해야 한다. 예를 들어, 고객당 하나의 파티션을 가지고 있고 현재 소수의 대규모 고객이 있는 경우, 수년 내에 다수의 소규모 고객을 보유하고 있는 경우를 고려해야 한다. 이 경우, LIST로 분할하려고 하기보다는 HASH로 분할하고 합리적인 수의 파티션을 선택하는 것이 더 나을 수 있다. 예를 들어, 고객당 하나의 파티션을 가지고 있고 현재 소수의 대규모 고객이 있는 경우, 수년 내에 다수의 소규모 고객을 보유한다고 가정해보자. 이 경우, LIST로 분할하려고 하고 데이터를 분할하는 것이 실제적인 것보다 RANGE로 분할하여 각각 정해진 수의 고객을 포함하는 합리적인 수의 파티션을 선택하는 것이 더 나을 수 있다. 하위 파티션은 다른 파티션보다 커질 것으로 예상되는 파티션을 더 분할하는 데 유용할 수 있다. 또 다른 옵션은 파티션 키에 여러 열이 있는 범위 분할을 사용하는 것이다. 이 중 어느 것이든 파티션의 수가 과도하게 늘어날 수 있으므로 파티션의 개수를 제한하는 것이 좋을 수도 있다. 쿼리 계획 및 실행 중에 파티션 오버헤드를 고려하는 것이 중요하다. 일반적인 쿼리를 통해 쿼리 계획이 작은 수의 파티션을 제외한 모든 파티션을 제거할 수 있다면 쿼리 실생 계획은 일반적으로 최대 수천 개의 파티션으로 파티션 계층 구조를 상당히 잘 처리할 수 있다. 플래너가 파티션 정리를 수행한 후에도 더 많은 파티션이 남아 있으면 계획 시간은 길어지고 메모리 소비량은 더 많아진다. 이는 UPDATE 및 DELETE 명령의 경우에 특히 해당된다. 많은 수의 파티션을 갖는 것에 대해 우려해야 하는 또 다른 이유는 서버의 메모리 사용량이 시간이 지남에 따라 크게 증가할 수 있으며, 특히 많은 세션이 많은 수의 파티션을 스캔하는 경우 더욱 그렇다. 이는 각 파티션이 해당 파티션에 닿는 각 세션의 로컬 메모리에 메타데이터를 로드해야 하기 때문이다. 데이터 웨어하우스 유형의 워크로드에서는 OLTP 유형의 워크로드보다 더 많은 수의 파티션을 사용하는 것이 합리적이다. 일반적으로 데이터 웨어하우스에서는 쿼리 실행 중에 대부분의 처리 시간이 소요되므로 쿼리 계획 시간은 그다지 중요하지 않다. 이러한 두 가지 워크로드 유형 중 하나를 사용하면 대량의 데이터를 다시 분할하는 작업이 매우 느릴 수 있으므로 올바른 결정을 조기에 내리는 것이 중요하다. 의도된 워크로드의 시뮬레이션은 파티셔닝 전략을 최적화하는 데 종종 유용합니다. 더 많은 파티션이 더 적은 수의 파티션보다 낫다고 가정하거나 그 반대의 경우를 가정하면 안 된다 참고 https://www.postgresql.org/docs/10/ddl-partitioning.html https://www.enterprisedb.com/postgres-tutorials/what-multi-column-partitioning-postgresql-and-how-pruning-occurs https://www.postgresdba.com/bbs/board.php?bo_table=B10&wr_id=64 |