postgres partition 테이블

postgres 의 파티션 테이블

 

파티션 테이블 종류는 oracle과 흡사하다.

 

1. 지원 되는 파티션의 종류

 ○ LIST 파티션

     check 팀명 = 'SALES'

     check 지역 in ('SEOUL','BUSAN','JEJU')

 

 ○ RANGE 파티션

     check 출생년도 >= 1990 and 출생년도 < 1996

 

 ○ COMPOSITE 파티션

     check 지역 in ('SEOUL','BUSAN','JEJU') + check 출생년도 >= 1990 and 출생년도 < 1996

 

 

2. InHerits (상속)

 ○ 부모테이블을 자신의 컬럼으로 저장

 ○ 부보테이블에서 자식테이블 데이터 조회

 ○ 부모테이블이 삭제 되면 자식테이블도 삭제

 

 

3, partition table 생성

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

기본이 되는 테이블(부모)를 우선 만든다.

 

CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement); CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement); CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);

부모테이블과 같은 구조로 자식테이블 생성.

 

CREATE TABLE measurement_y2006m02 (     CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 (     CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) ) INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 (     CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2007m12 (     CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2008m01 (     CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) ) INHERITS (measurement);

자식테이블에 제약 조건을 걸어서 파티션 기능을 하도록 한다.

 

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate); CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate); ... CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate); CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate); CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

파티션 키 컬럼으로 로컬 인덱스 생성

 

CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN     INSERT INTO measurement_y2008m01 VALUES (NEW.*);     RETURN NULL; END; $$ LANGUAGE plpgsql;

데이터를 삽입 하는 함수 생성.

 

CREATE TRIGGER insert_measurement_trigger     BEFORE INSERT ON measurement     FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

트리거를 만들어서 데이터가 입력시 함수를 호출.

 

CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN     IF ( NEW.logdate >= DATE '2006-02-01' AND          NEW.logdate < DATE '2006-03-01' ) THEN         INSERT INTO measurement_y2006m02 VALUES (NEW.*);     ELSIF ( NEW.logdate >= DATE '2006-03-01' AND             NEW.logdate < DATE '2006-04-01' ) THEN         INSERT INTO measurement_y2006m03 VALUES (NEW.*);     ...     ELSIF ( NEW.logdate >= DATE '2008-01-01' AND             NEW.logdate < DATE '2008-02-01' ) THEN         INSERT INTO measurement_y2008m01 VALUES (NEW.*);     ELSE         RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';     END IF;     RETURN NULL; END; $$ LANGUAGE plpgsql;

데이터가 특정 파티션에 들어갈 수 있도록 함수 생성.

 

update 까지 관리 하려면...

 

 

4. Partitioning & Constraint exclusion

 Constraint exclusion 은 쿼리 성능 최적화 기술이다.

SET constraint_exclusion = off; EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';                                           QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate  (cost=158.66..158.68 rows=1 width=0)    ->  Append  (cost=0.00..151.88 rows=2715 width=0)          ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)                Filter: (logdate >= '2008-01-01'::date)          ->  Seq Scan on measurement_y2006m02 measurement  (cost=0.00..30.38 rows=543 width=0)                Filter: (logdate >= '2008-01-01'::date)          ->  Seq Scan on measurement_y2006m03 measurement  (cost=0.00..30.38 rows=543 width=0)                Filter: (logdate >= '2008-01-01'::date) ...          ->  Seq Scan on measurement_y2007m12 measurement  (cost=0.00..30.38 rows=543 width=0)                Filter: (logdate >= '2008-01-01'::date)          ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)                Filter: (logdate >= '2008-01-01'::date)

constraint_exclusion 을 사용하지 않으면, 모든 파티션을 스캔. (파티션 테이블의 이점이 없음.)

 

SET constraint_exclusion = on; EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';                                           QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate  (cost=63.47..63.48 rows=1 width=0)    ->  Append  (cost=0.00..60.75 rows=1086 width=0)          ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)                Filter: (logdate >= '2008-01-01'::date)          ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)                Filter: (logdate >= '2008-01-01'::date)

constraint_exclusion 사용시 제약조건 배제로 필요 파티션만 스캔.

 

 

5. partition 관리

5.1. 파티션 삭제

DROP TABLE measurement_y2006m02;

파티션 테이블 및 데이터 까지 삭제.

 

5.2. 파티션 제거

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

안의 데이터는 유지한체 파티션 연결만 제거.

 

5.3. 파티션 추가

CREATE TABLE measurement_y2008m02 (     CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ) ) INHERITS (measurement);

or

CREATE TABLE measurement_y2008m02   (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS); ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02    CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ); \copy measurement_y2008m02 from 'measurement_y2008m02' -- possibly some other data preparation work ALTER TABLE measurement_y2008m02 INHERIT measurement;

 

 

6. 주의 사항

-  GLOBAL INDEX 부재

-  Local Index 자동 생성 되지 않음.

-  Trigger로 동작하므로, 파티션 추가시 trigger, function을 컴파일 해야 함.

-  DBA의 관리가 중요 (수작업이 많다.)