[PostgreSQL] 월별로 DB 파티셔닝 예제

출처

주문 기록에 대해서 수십만건의 많은 데이터가 발행 돼 기존 기록 때문에 조회하는데 시간이 너무 많이 소요 돼 성능을 최적화 하는 방법을 찾는 중 아래와 같이 유용한 글을 읽고 퍼왔다.

https://jaimemin.tistory.com/1913

[PostgreSQL] 월별로 DB 파티셔닝 예제

개요

제가 담당하고 있는 파트에서 매일 몇 만 건씩 데이터가 쌓이는 DB가 있었습니다.

DB에 너무 많은 데이터들이 쌓일 경우 조회 성능이 떨어질 수 있으므로 월별 DB 파티셔닝을 진행했고 진행할 때 관련 내용이 별로 없어 삽질을 많이 했었습니다.

따라서 저는 여러분들이 저같이 삽질을 하는 일이 없도록 이번 게시글에서 DB 파티셔닝 샘플 예제를 공유해보겠습니다.

 

* DB 파티셔닝을 진행하더라도 기존에 작성한 CRUD 쿼리를 수정할 필요가 없습니다!

 

전체척인 프로세스

이미 존재하는 DB를 파티셔닝 하기 위해서는 아래의 프로세스를 거쳐야 합니다.

• 기존 DB명 변경
• 기존 DB 내 primary key, foreign key와 같은 제약조건 해제
• 기존 DB와 동일한 조건의 새로운 DB를 기존 DB명과 동일하게 생성 (이때, PARTITION BY RANGE 키워드를 사용하여 월별로 파티셔닝 진행)
• 신규로 생성한 DB의 owner를 바꿔야 한다면 owner 변경
• DBA의 요구사항에 맞게 향후 몇 년 치의 월별 파티션 테이블 생성
• 조회 성능을 위해 월별로 파티셔닝 한 테이블마다 인덱스 생성
• 기존 DB의 데이터들을 신규로 생성한 DB에 삽입
• 기존 DB 삭제를 위해 시퀀스 owner를 none으로 변경 (OWNED BY NONE)
• 기존 DB 삭제
  • 기존 DB에 종속적인 VIEW가 있다면 해당 VIEW를 DROP 하고 신규 DB에 종속적인 VIEW를 다시 생성

 

예시 DB

tbl_example
(
    id bigint NOT NULL DEFAULT nextval('tbl_example_seq'::regclass),
    created_at timestamp without time zone,
    created_by character varying(255) COLLATE pg_catalog."default",
    modified_at timestamp without time zone,
    modified_by character varying(255) COLLATE pg_catalog."default",
    sample_message character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT tbl_example_pkey PRIMARY KEY (id)
)

 

예시 DB를 기준으로 프로세스 진행

 

1. 기존 DB명 변경

 

ALTER TABLE tbl_example RENAME TO tbl_example_temp;

 

2. 기존 DB 내 primary key, foreign key와 같은 제약조건 해제

 

ALTER TABLE tbl_exampe_temp DROP CONSTRAINT tbl_example_pkey;

 

3. 기존 DB와 동일한 조건의 새로운 DB를 기존 DB명과 동일하게 생성 (이때, PARTITION BY RANGE 키워드를 사용하여 월별로 파티셔닝 진행)

 

CREATE TABLE IF NOT EXISTS tbl_example
(
    id bigint NOT NULL DEFAULT nextval('tbl_example_seq'::regclass),
    created_at timestamp without time zone,
    created_by character varying(255) COLLATE pg_catalog."default",
    modified_at timestamp without time zone,
    modified_by character varying(255) COLLATE pg_catalog."default",
    sample_message character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT tbl_example_pkey PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

 

* 파티셔닝 기준으로 한 칼럼은 반드시 PRIMARY KEY에 포함되어야 합니다.

 

4. 신규로 생성한 DB의 owner를 바꿔야 한다면 owner 변경

 

ALTER TABLE tbl_example OWNER to example_owner

 

 

5. DBA의 요구사항에 맞게 향후 몇 년 치의 월별 파티션 테이블 생성

 

CREATE TABLE tbl_example_2021_09 PARTITION OF tbl_example FOR VALUES FROM('2021-09-01 00:00:00') TO ('2021-10-01 00:00:00');
CREATE TABLE tbl_example_2021_10 PARTITION OF tbl_example FOR VALUES FROM('2021-10-01 00:00:00') TO ('2021-11-01 00:00:00');
CREATE TABLE tbl_example_2021_11 PARTITION OF tbl_example FOR VALUES FROM('2021-11-01 00:00:00') TO ('2021-12-01 00:00:00');
CREATE TABLE tbl_example_2021_12 PARTITION OF tbl_example FOR VALUES FROM('2021-12-01 00:00:00') TO ('2022-01-01 00:00:00');

CREATE TABLE tbl_example_2022_01 PARTITION OF tbl_example FOR VALUES FROM('2022-01-01 00:00:00') TO ('2022-02-01 00:00:00');
CREATE TABLE tbl_example_2022_02 PARTITION OF tbl_example FOR VALUES FROM('2022-02-01 00:00:00') TO ('2022-03-01 00:00:00');
CREATE TABLE tbl_example_2022_03 PARTITION OF tbl_example FOR VALUES FROM('2022-03-01 00:00:00') TO ('2022-04-01 00:00:00');
CREATE TABLE tbl_example_2022_04 PARTITION OF tbl_example FOR VALUES FROM('2022-04-01 00:00:00') TO ('2022-05-01 00:00:00');
CREATE TABLE tbl_example_2022_05 PARTITION OF tbl_example FOR VALUES FROM('2022-05-01 00:00:00') TO ('2022-06-01 00:00:00');
CREATE TABLE tbl_example_2022_06 PARTITION OF tbl_example FOR VALUES FROM('2022-06-01 00:00:00') TO ('2022-07-01 00:00:00');
CREATE TABLE tbl_example_2022_07 PARTITION OF tbl_example FOR VALUES FROM('2022-07-01 00:00:00') TO ('2022-08-01 00:00:00');
CREATE TABLE tbl_example_2022_08 PARTITION OF tbl_example FOR VALUES FROM('2022-08-01 00:00:00') TO ('2022-09-01 00:00:00');
CREATE TABLE tbl_example_2022_09 PARTITION OF tbl_example FOR VALUES FROM('2022-09-01 00:00:00') TO ('2022-10-01 00:00:00');
CREATE TABLE tbl_example_2022_10 PARTITION OF tbl_example FOR VALUES FROM('2022-10-01 00:00:00') TO ('2022-11-01 00:00:00');
CREATE TABLE tbl_example_2022_11 PARTITION OF tbl_example FOR VALUES FROM('2022-11-01 00:00:00') TO ('2022-12-01 00:00:00');
CREATE TABLE tbl_example_2022_12 PARTITION OF tbl_example FOR VALUES FROM('2022-12-01 00:00:00') TO ('2023-01-01 00:00:00');

// 중략

 

 

6. 조회 성능을 위해 월별로 파티셔닝 한 테이블마다 인덱스 생성

 

* 수정: 2021.09.21

* DBA분이 각 파티션에 대해서 말고 모 테이블에 대해서만 인덱스를 생성하면 파티셔닝 한 테이블에도 인덱스가 자동 생성된다고 하셔서 모 테이블에 대해서만 인덱스를 생성했습니다.

 

CREATE INDEX index_tbl_example_2021_09_id ON tbl_example_2021_09(id);
CREATE INDEX index_tbl_example_2021_09_created_at ON tbl_example_2021_09(created_at);

CREATE INDEX index_tbl_example_2021_10_id ON tbl_example_2021_10(id);
CREATE INDEX index_tbl_example_2021_10_created_at ON tbl_example_2021_10(created_at);

CREATE INDEX index_tbl_example_2021_10_id ON tbl_example_2021_11(id);
CREATE INDEX index_tbl_example_2021_10_created_at ON tbl_example_2021_11(created_at);

CREATE INDEX index_tbl_example_2021_10_id ON tbl_example_2021_12(id);
CREATE INDEX index_tbl_example_2021_10_created_at ON tbl_example_2021_12(created_at);

// 중략

 

 

7. 기존 DB의 데이터들을 신규로 생성한 DB에 삽입

 

INSERT INTO tbl_example (
    SELECT id, created_at, created_by, modified_at, modified_by, sample_message
    FROM tbl_example_temp
    WHERE created_at >= '2021-09-01 00:00:00'
);

 

8. 기존 DB 삭제를 위해 시퀀스 owner를 none으로 변경 (OWNED BY NONE)

 

ALTER SEQUENCE tbl_example_seq OWNED BY NONE;

 

9. 기존 DB 삭제를 위한 VIEW 제거 후 재생성

 

DROP VIEW tbl_example_view001;

CREATE VIEW tbl_example_view001
AS
SELECT tbl_example.id,
    tbl_example.created_at,
    tbl_example.created_by,
    tbl_example.modified_at,
    tbl_example.modified_by,
    tbl_example.sample_message
FROM tbl_example;

 

9.1 생성한 VIEW에 대해 권한 부여

 

https://jaimemin.tistory.com/1922?category=1084044

 

9.2 기존 DB 삭제

 

DROP TABLE tbl_example_temp;