[SQL] 선언적 파티션 (POSTGRESQL)

postgresql 12 파티셔닝 테이블 매뉴얼을 기반으로 작성한다.

1) 선언적 파티션

선언적 파티션 구성은 postgresql 10이상이서만 가능하다.

10 버전 아래의 경우 파티션 명령어가 없기 때문에, 2) Partitioning Using Inheritance(하단에 설명)를 사용해야 한다.

1. 파티션 테이블 생성

대형 아이스크림 회사를 위한 데이터베이스를 구축한다고 가정합니다. 이 회사는 매일 최고 기온과 각 지역의 아이스크림 판매량을 저장할 테이블을 다음과 같이 예정이다.

CREATE TABLE measurement (
    city_id         int not null,  -- 도시 id
    logdate         date not null, -- 날짜 
    peaktemp        int,           -- 최고 온도
    unitsales       int            -- 판매량
);

저장해야 하는 오래된 데이터의 양을 줄이기 위해 가장 최근 2년 분량의 데이터만 보관하기로 결정했다. 매년 초에 가장 오래된 년도의 데이터를 제거할 것이다. 이 상황에서 파티셔닝을 사용하여 measurement 테이블에 대한 다양한 요구 사항을 모두 충족할 수 있다.

※ 선언적 파티셔닝을 사용

1-1. 파티션 방법은 분할 key로 사용할 열(column) 목록을 포함해야 한다.

PARTITION BY RANGE절을 지정하여 테이블을 파티션 된 테이블로 만든다. 아래의 쿼리는 logdate(날짜)를 기준으로 파티션을 설정한다.

CREATE TABLE measurement (

city_id int not null ,

logdate date not null ,

peaktemp int ,

unitsales int

) PARTITION BY RANGE (logdate); -- 파티션 키를 logdate로 지정

1-2. 각 파티션 정의는 부모 테이블 파티션 방법 및 파티션 키에 해당하는 경계를 지정해야 한다.

새 파티션의 값이 하나 이상의 기존 파티션에 있는 값과 겹치도록 경계를 지정하면 오류가 발생한다. 이렇게 생성된 파티션은 모든 방법으로 일반적인 PostgreSQL 테이블이다. 각 파티션에 대한 테이블 스페이스 및 스토리지 매개 변수를 개별적으로 지정할 수 있다.

위의 예제의 경우 각 파티션은 한 번에 1년의 데이터를 삭제해야 하는 요구 사항과 일치하도록 테이블 생성 쿼리는 다음과 같다.

CREATE TABLE measurement_y2020 PARTITION OF measurement

FOR VALUES FROM ('2020-01-01') TO ('2020-12-31');

CREATE TABLE measurement_y2021 PARTITION OF measurement

FOR VALUES FROM ('2021-01-01') TO ('2021-12-31')

TABLESPACE pg_default;

CREATE TABLE measurement_y2022 PARTITION OF measurement

FOR VALUES FROM ('2022-01-01') TO ('2022-12-31')

TABLESPACE pg_default;

생성후 테이블의 구조. 해당 테이블의 하위에 파티션으로 생성한 테이블이 위치한다.

※ TABLESPACE (ORACLE/ POSTGRESQL 에만 있음)

해당 테이블을 저장할 때 로컬 경로를 지정해서 저장 가능한 기능

postgres에서 테이블스페이스는 DB 관리자에 의해 데이터베이스의 객체가 저장될 수 있는 파일 시스템의 경로로 정의된다. 테이블스페이스가 생성되면 데이터베이스 객체에 객체를 생성할 때 이름에 의해서 테이블스페이스가 참조될 수 있다.

※ 장점

DB가 생성된 볼륨 또는 파티션에 여유공간이 부족할 때 테이블스페이스를 다른 파티션이나 디스크에 생성하여 시스템을 재구성할 때까지 DB를 확장할 수 있다.
데이터베이스 객체의 성능 최적화를 위해 사용할 수 있다는 것이다. 예를 들어 매우 사용량이 많으면서 자주 업데이트되는 인덱

1-3. 분할된 테이블의 키 열과 원하는 다른 인덱스에 인덱스를 생성한다.

키 인덱스가 꼭 필요한 것은 아니지만 대부분의 시나리오에서 유용하다. 이렇게 하면 각 파티션에 일치하는 인덱스가 자동으로 만들어지며 나중에 만들거나 연결하는 모든 파티션에도 인덱스가 생성된다. 분할된 테이블에 선언된 인덱스 또는 고유 제약 조건은 분할된 테이블과 동일한 방식으로 동작한다. 실제 데이터는 개별 파티션 테이블의 하위 인덱스에 존재한다.

CREATE INDEX ON measurement (logdate);

1-4. postgresql.conf 에서 enable_partition_pruning 구성 매개 변수가 비활성화되어 있지 않은지 확인

쿼리가 원하는대로 최적화되지 않는다.
enable_partition_pruning = on 상태에서는 2022년 테이블 하나만 스캔한다.

2. insert 테스트

insert into measurement

values (1, date('2021-06-30'), 1, 1);

select * from measurement;

위와 같이 measurement 테이블 조회 시 데이터가 추가된 것을 볼 수 있다.

또한 해당 데이터는 measurement_y2021에 들어간 것을 확인할 수 있다.

select * from measurement_y2021;

insert시 파티션으로 설정된 칼럼 범위에 포함된 데이터가 아니라면 에러가 발생한다.

ex: 파티션의 범위가 2020, 2021, 2022년 이므로 2000년의 데이터는 에러가 발생한다.

# 에러 코드. date가 파티셔닝의 범위에서 벗어남

insert into measurement

values (9999, date('2000-12-30'), 9999, 9999);

3. 파티션 테이블 삭제

필요없는 데이터가 축적된 2020년의 테이블(오래된 데이터)을 제거하는 가장 간단한 옵션은 더 이상 필요하지 않은 파티션을 삭제하는 것입니다.

DROP TABLE measurement_y2020;

4. 실행 계획 + 파티션 프루닝

파티션 프루닝은 선언적으로 파티션 된 테이블은 성능을 향상하는 쿼리 최적화 기술이다.

SET enable_partition_pruning = on; -- default

SELECT count(*)FROM measurement

WHERE logdate >=DATE '2022-01-01';

Colored by Color Scripter

파티션 프루닝이 없으면 위의 쿼리는 measurement 테이블 의 각 파티션을 스캔합니다. 파티션을 사용하면 실행계획은 각 파티션의 정의를 검사하고 쿼리의 WHERE 절을 충족하는 행을 포함된 파티션 만을 스캔한다.

▶ enable_partition_pruning = off 에서는 파티션 테이블을 모두 스캔한다.

SET enable_partition_pruning = off;

EXPLAIN

SELECT count(*)FROM measurement

WHERE logdate >=DATE '2022-01-01';

QUERY PLAN

-----------------------------------------------------------------------------------

QUERY PLAN

Append (cost=0.00..37.85 rows=420 width=100) (actual time=0.016..0.017 rows=1 loops=1)

-> Seq Scan on measurement_y2021_city_1_to_100 measurement_1 (cost=0.00..17.88 rows=210 width=100) (actual time=0.011..0.012 rows=0 loops=1)

Filter: (logdate >= '2022-01-30'::date)

Rows Removed by Filter: 2

-> Seq Scan on measurement_y2022_city_101_to_200 measurement_2 (cost=0.00..17.88 rows=210 width=100) (actual time=0.004..0.004 rows=1 loops=1)

Filter: (logdate >= '2022-01-30'::date)

Colored by Color Scripter

▶ enable_partition_pruning = off에서는 파티션 테이블을 모두 스캔한다.

SET enable_partition_pruning = on;

EXPLAIN

SELECT count(*)FROM measurement

WHERE logdate >=DATE '2022-01-01';

QUERY PLAN

-----------------------------------------------------------------------------------

QUERY PLAN

Seq Scan on measurement_y2022_city_101_to_200 measurement (cost=0.00..17.88 rows=210 width=100) (actual time=0.009..0.010 rows=1 loops=1)

Filter: (logdate >= '2022-01-30'::date)

5. partition by range multiple columns - 복합 칼럼 파티션

CREATE TABLE measurement (

city_id int not null ,

logdate date not null ,

peaktemp int ,

unitsales int ,

name char(20) not null

) PARTITION BY RANGE (logdate, city_id);

CREATE TABLE measurement_y2021_city_1_to_100 PARTITION OF measurement

FOR VALUES FROM ('2021-01-01', 1) TO ('2021-12-31', 100)

TABLESPACE pg_default;

CREATE TABLE measurement_y2022_city_101_to_200 PARTITION OF measurement

FOR VALUES FROM ('2022-01-01', 101) TO ('2022-12-31', 200)

TABLESPACE pg_default;

Colored by Color Scripter

복합 칼럼을 파티션을 생성할 경우 해당 범위가 겹칠수 없다. (겹친다면 파티션 생성 시 에러가 발생한다)

insert into measurement

values (1, date('2021-06-30'), 1, 1, 'name1');

insert into measurement

values (101, date('2021-06-30'), 1, 1, 'name2');

insert into measurement

values (1, date('2022-12-30'), 1, 1, 'name3');

데이터의 추가 일 경우 서로 겹치는 데이터를 넣을 경우 ( 두 번째로 실행한 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로 들어간다.

select * from measurement_y2021_city_1_to_100

6. 선언적 파티셔닝의 한계

분할된 테이블의 고유한 제약 조건(즉, 기본 키)에는 모든 파티션 키 열이 포함되어야 한다. 이 제한은 제약 조건을 구성하는 개별 인덱스가 자체 파티션 내에서만 고유성을 직접 적용할 수 있기 때문에 존재하며, 따라서 파티션 구조 자체는 서로 다른 파티션에 중복되지 않도록 보장해야 한다
INTERT에서 행 트리거를 시작하기 전에 새 행의 최종 대상이 되는 파티션은 변경할 수 없다.
유니크 키는 언제나 파티션의 기준이 되는 칼럼이 포함되어야 한다. 그렇지 않으면 아래의 에러가 발생한다.

CREATE TABLE measurement (

id bigint primary key,

city_id int not null ,

logdate date not null ,

peaktemp int ,

unitsales int ,

name char(20) not null

) PARTITION BY RANGE (logdate);

[0A000] ERROR: unique constraint on partitioned table must include all partitioning columns

Detail: PRIMARY KEY constraint on table "measurement" lacks column "logdate" which is part of the partition key.

PRIMARY KEY or unique 추가를 한다면 아래와 같이 해야 한다

CREATE TABLE measurement (

city_id int not null ,

logdate date not null ,

peaktemp int ,

unitsales int ,

name char(20) not null,

-- CONSTRAINT pk_logdata_city_id PRIMARY KEY (logdate, city_id), -- primary key 추가시

-- CONSTRAINT unique_logdata_city_id unique (logdate, city_id) -- unique key 추가시

) PARTITION BY RANGE (logdate);

Colored by Color Scripter

개별 파티션은 백그라운드에서 상속을 사용하여 부모테이블에 연결된다. 선언적으로 분할된 테이블 또는 해당 파티션과 함께 상속의 모든 일반 기능을 사용할 수는 없다. 특히 파티션은 파티션 테이블 이외의 상위 테이블을 가질 수 없으며 파티션 테이블과 일반 테이블에서 상속할 수도 없다.

분할된 테이블과 해당 파티션으로 구성된 파티션 계층은 여전히 상속 계층이므로, 몇 가지 예외를 제외하고 모든 일반 상속 규칙이 적용된다.

파티션은 부모에 없는 열을 가질 수 없다. CREATE TABLE을 사용하여 파티션을 만들 때 열을 지정할 수 없으며 ALTER TABLE을 사용하여 사실상 파티션에 열을 추가할 수도 없다. 열이 부모와 정확히 일치하는 경우에만 ALTER TABLE... ATTACH PARTITION을 사용하여 테이블을 파티션으로 추가할 수 있다.
분할 테이블의 CHECK 및 NOT NULL 제약 조건 모두 항상 모든 파티션에 상속된다. 상위 테이블에 동일한 제약 조건이 있는 경우 파티션 열에 NOT NULL 제약 조건을 놓을 수 없다.
파티션이 없는 한 파티션 테이블에만 제약 조건을 추가하거나 삭제하는 데만 사용한다. 파티션이 있는 경우만 사용하면 오류가 발생한다. 대신 파티션 자체에 대한 제약 조건을 추가하고 상위 테이블에 없는 경우 해당 제약 조건을 삭제할 수 있다.

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

'Java' 카테고리의 다른 글

인덱스(Index)란? (0)	2022.05.31
[SQL] Inheritance 파티션 (POSTGRESQL) (0)	2022.05.20
Apache 로드밸런싱 + 세션 클러스터링 (0)	2022.05.18
Tomcat에 Context 추가하기 (0)	2022.04.29
[Linux] 크론탭, Crontab 설정 (0)	2022.04.25