PostgreSQL Partitioning

참고 : https://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

postgres 에서는 파티션 테이블을 테이블의 상속을 통해서 제공해 주고 있다. 부모 테이블을 생성해 준 뒤 이를 사용하여 자식 테이블을 생성해 준다.
(테스트를 위해서 간단한 테이블을 생성해 준다.)

1. 부모 테이블 생성

CREATE TABLE TEST(
  ID NUMERIC NOT NULL
);

부모 테이블을 만들 때는 컬럼 속성외의 조건들은 (FK, PK, INDEX 등) 생성해도 자식 테이블로 전달되지 않는다.

2. 이를 기반으로 자식 테이블을 생성해 준다.

CREATE TABLE TEST_2 (
  CHECK (ID >=1 AND ID <= 2)
) INHERITS (TEST);

CREATE INDEX ON TEST_2 (ID);

CREATE TABLE TEST_3 (
  CHECK (ID >=3 AND ID <= 5)
) INHERITS (TEST);

CREATE INDEX ON TEST_3 (ID);

CHECK 를 통해서 데이터가 insert 될 때 검사를 할 수 있다. 만약 범위에 맞지 않는 데이터가 insert 될 경우 에러가 발생한다.

3. 이제 테스트 데이터를 insert 해보자.

INSERT INTO TEST_2 (ID) VALUES (1);
INSERT INTO TEST_2 (ID) VALUES (2);
INSERT INTO TEST_3 (ID) VALUES (3);
INSERT INTO TEST_3 (ID) VALUES (4);
INSERT INTO TEST_3 (ID) VALUES (5);

만약 TEST 테이블로 insert 한다면 자식 테이블로 자동으로 들어가지 않는다. 꼭 그 데이터가 들어갈 테이블을 지정해서 insert 해줘야 한다. 그리고 위에서 언급했던 것 처럼 범위에 맞지 않는 값이 insert 된다면 에러가 발생한다. 

SELECT * FROM TEST;
/*
2
1
3
4
5
 */

TEST 를 테이블에서는 모든 데이터가 다 보이고 각 테이블을 SELECT 하면 각 테이블의 데이터를 볼 수 있다. (당연히;;)

4. 그럼 plan 을 확인해 볼 차례다.
ID 가 모두 하나의 테이블(TEST_3) 에 있을 경우는 TEST 테이블을 찾고 바로 TEST_3 을 찾는다. (인덱스를 생성해 두었기 때문에 인덱스를 사용한다.)

EXPLAIN SELECT * FROM TEST WHERE ID IN (4, 5);
/*
Append  (cost=0.00..21.67 rows=16 width=32)
  ->  Seq Scan on test  (cost=0.00..2.70 rows=2 width=32)
        Filter: (id = ANY ('{4,5}'::numeric[]))
  ->  Bitmap Heap Scan on test_3  (cost=8.41..18.97 rows=14 width=32)
        Recheck Cond: (id = ANY ('{4,5}'::numeric[]))
        ->  Bitmap Index Scan on test_3_id_idx  (cost=0.00..8.41 rows=14 width=0)
              Index Cond: (id = ANY ('{4,5}'::numeric[]))

 */

ID 다 두 테이블에 모두 있을 경우에는 TEST 테이블을 찾고 TEST_2, TEST_3 을 모두 찾는다. (인덱스를 생성해 두었기 때문에 인덱스를 사용한다.)

EXPLAIN SELECT * FROM TEST WHERE ID IN (2, 5);
/**
Append  (cost=0.00..40.65 rows=30 width=32)
  ->  Seq Scan on test  (cost=0.00..2.70 rows=2 width=32)
        Filter: (id = ANY ('{2,5}'::numeric[]))
  ->  Bitmap Heap Scan on test_2  (cost=8.41..18.97 rows=14 width=32)
        Recheck Cond: (id = ANY ('{2,5}'::numeric[]))
        ->  Bitmap Index Scan on test_2_id_idx  (cost=0.00..8.41 rows=14 width=0)
              Index Cond: (id = ANY ('{2,5}'::numeric[]))
  ->  Bitmap Heap Scan on test_3  (cost=8.41..18.97 rows=14 width=32)
        Recheck Cond: (id = ANY ('{2,5}'::numeric[]))
        ->  Bitmap Index Scan on test_3_id_idx  (cost=0.00..8.41 rows=14 width=0)
              Index Cond: (id = ANY ('{2,5}'::numeric[]))
 */

일반 테이블을 PK 로 검색했을 경우는 아래와 같다.

EXPLAIN SELECT * FROM REVIEW WHERE ID=11;
/**
Index Scan using review_pkey on review  (cost=0.14..8.16 rows=1 width=924)
  Index Cond: (id = '11'::numeric)
 */

파티션을 사용하면 부모테이블을 찾고 자식 테이블로 내려가고 CHECK 조건에 따라서 검색할 자식 테이블을 찾게 된다. 그리고 매번 테이블을 생성해 줘야 하고 데이터가 어떤 테이블로 저장되어야 하는지 찾아줘야 하는 번거로움이 있다. (상단의 참고 사이트나 다른 곳의 예제에서는 db function 과 trigger 로 해결하고 있다.

그리고 만약 WHERE 절에 CHECK 에 해당하지 않는 조건이 붙고 이 조건이 CHECK 에 정의된 제약조건의 영향을 받지 않는다면 전체 자식 테이블을 찾기 때문에 이를 조심해야 한다. (OR 절과 같이)
만약 TEXT 컬럼이 있다고 가정하고 아래와 같이 쿼리를 실행한다면 자식 테이블을 모두 찾는다. (데이터가 하나의 테이블에 있다고 하더라도... 당연..)

SELECT * FROM TEST;
/*
1  1
2  2
3  3
4  4
5  5
 */

EXPLAIN SELECT * FROM TEST WHERE ID IN (4, 5) OR TEXT='5';
/*
Append  (cost=0.00..44.90 rows=25 width=70)
  ->  Seq Scan on test  (cost=0.00..0.00 rows=1 width=70)
        Filter: ((id = ANY ('{4,5}'::numeric[])) OR ((text)::text = '5'::text))
  ->  Seq Scan on test_2  (cost=0.00..22.45 rows=12 width=70)
        Filter: ((id = ANY ('{4,5}'::numeric[])) OR ((text)::text = '5'::text))
  ->  Seq Scan on test_3  (cost=0.00..22.45 rows=12 width=70)
        Filter: ((id = ANY ('{4,5}'::numeric[])) OR ((text)::text = '5'::text))
 */