Database partition in postgresql

Postgresql를 사용하면서 대용량 데이터를 insert, select, delete할 일이 있었다. 간단한 OLAP 작업이긴 하나 row oriented rdbms를 사용해야 했고, select와 delete에 대해 최적화를 진행해야 했다. 이를 위해 사용했던 partition 기능을 정리해둔다.

참고 문서: Postgres partition

Partition

Partition은 하나의 logical table을 작은 physical table로 나누는 것을 의미한다. 다음과 같은 특징을 가진다.

장점

• Query performance가 드라마틱하게 올라간다. 테이블에 원하는 row만 접근해도 되기 때문이다.
• Bulk load와 delete가 partition 추가와 삭제로 간단하게 구현될 수 있다. DROP TABLE같은 operation은 bulk delete operation보다 훨씬 빠르다.

단점(한계점)

• unique나 primary key constraint를 걸때, partition key는 expression이나 function call을 포함해서는 안된다. 또한, constraint column들은 partition key column을 포함해야 한다.

  CREATE TABLE measurement (
      city_id         int unique not null,
      logdate         date not null,
      peaktemp        int,
      unitsales       int
  ) PARTITION BY RANGE (logdate);
  

  ex2) 다음 케이스는 가능하다. unique constraint가 logdate를 포함하기 때문이다.
• CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int, unique (city_id, logdate) ) PARTITION BY RANGE (logdate);
• ex1) 다음 케이스는 불가능하다. city_id에 걸린 unique 속성이 partition key인 logdate를 포함하지 않기 때문이다.
• 전체 partition 테이블을 span하는 exclusive constraint를 설정할 수 없다. 각각의 leaf partition에 대해서만 가능하다.

위의 단점으로 인해 외부 테이블에서 partition 테이블에 대해 foreign key를 걸기 어렵다. Parent table 기준으로 foreign key를 설계했다가(ex: city_id) 해당 column이 unique constraint가 걸려있지 않기 때문에 unique column 전체에 대해 foreign key를 걸거나(ex: city_id, logdate), 아예 foreign key를 걸지 않는 경우도 있다.

Postgresql에서는 3가지 partition type을 제공한다.

• Range partition
• List partition
• Hash partition: modulus와 remainder로 나눠 remainder에 따라 partition을 구성한다.

Partition 생성

원본 테이블(partition의 부모가 되는 logical 테이블)은 다음과 같이 설정해야 한다.

AS-IS

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

TO-BE

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

Partition은 다음과 같이 생성할 수 있다.

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

Partition 조회

Partition은 일반적인 테이블과 동일하게 생각할 수 있다. SELECT 문을 원본 테이블(logical table), Partition에 대해 각각 적용 가능하다.

-- partition 없이
SELECT COUNT(*) FROM measurement m where m.logdate between timestamp '2006-02-01' and timestamp '2006-03-01';

-- partition과 함께
SELECT COUNT(*) FROM measurement_y2006m02 m;

[테스트 환경]

• Partition 수: 180개
• Partition별 row수: 약 126만

Partition 없이 하면 1m 35s인 작업이 partition 사용하면 438ms 걸렸다. 200배 넘게 차이가 난 것이다.

Partition 삭제

간단하게 Partition 테이블을 삭제해줄 수 있다.

DROP TABLE measurement_y2006m02;