[PostgreSQL] Database Partitioning

Dark Mode 기능은 꺼주셔야 내용이 잘보입니다😄

 

Database volume에 대한 고민
전체 Database에 모든 데이터를 하나의 테이블에 들어간다면 데이터베이스 관리에 어려움을 겪는다.
이유는 데이터베이스 볼륨 증가로 인해 데이터베이스의 읽기/쓰기에 대한 성능이 감소하며, 데이터베이스의 병목 지점이 되기 때문이다.

 

따라서 데이터를 하나의 테이블에 넣되, 적절히 분할한다면 성능 향상이 될 것이라 판단된다.

 

데이터베이스 성능 향상을 위해 데이터를 분할하여 저장하는 파티셔닝(Partitioning) 혹은 샤딩(Sharding)기법을 사용한다.
위 2가지 기법 모두 거대한 데이터셋을 서브셋으로 분리하여 관리하는 방법 중 파티셔닝(Partitioning)에 대해 정리하고자 한다.

1.  파티셔닝(Partitioning)이란 무엇인가?🤔

파티션(Partition)은 테이블을 논리적으로는 하나이지만, 물리적으로 여러 개로 분리하여 관리하는 데이터베이스의 기능이다.

데이터를 여러 개의 테이블로 분리하여 관리하는 파티션(Partition) 기법을 적용하는 것이 파티셔닝(Partitioning)이라 한다.
특히, 대용량의 테이블을 물리적으로 여러 개의 소규모로 테이블들로 분산하는 것을 목적으로 한다.

물리적인 데이터 분할이 존재하여도 데이터베이스에 접근하는 어플리케이션 입장에서는 이를 인지하지 못한다.
파티셔닝은 DB에서도 중요한 튜닝 기법 중 하나로, 데이터가 너무 커져 조회하는 시간이 길어질 때 또는 관리 용이성, 성능, 가용성 등의 향상을 이유로 활용된다.

 

파티션을 사용하는 이유는 아래와 같다.

• 테이블이 너무 커 인덱스의 크기가 물리적인 메모리보다 훨씬 큰 경우
  • 인덱스가 커질 경우, DML작업(SELECT, INSERT, UPDATE, DELETE)도 함께 느려지게 된다.
  • 인덱스 크기가 물리적으로 데이터베이스가 사용 가능한 메모리 공간보다 크면 영향을 더욱 크게 받는다.

• 데이터 특성상 주기적으로 삭제 작업이 필요한 경우
  • 로그 성격의 이력 데이터는 단기간에 대량으로 누적되고 일정 기간이 지나면 필요치 않게 된다.
  • 시간이 지나면 별도로 아카이빙(archiving) 하거나 백업한 후 삭제한다.
  • 불필요해진 데이터를 백업하거나 삭제하는 작업은 일반 테이블에서는 상당히 고부하 작업이다.
  • 파티셔닝을 통해 파티션을 추가하거나 삭제하는 방식으로 간단히 해결 가능하다.

---

2.  파티셔닝 장단점(Partitioning pros and cons)은?!😊

파티션(Partition)기법은 다음과 같은 장점들을 제공한다.

• 데이터베이스 확장성 향상
  • 신규 데이터 추가될 때 새로운 파티션을 생성하여 데이터 분산이 가능하다.

• 데이터 관리 용이성
  • 파티션별로 독립적으로 관리 가능하다.
  • 파티션별 백업, 복구 인덱스 생성 등의 DB 작업을 개별적으로 수행할 수 있다.
  • 테이블의 파티션 단위로 Disk I/O를 분산하여 경합을 줄이므로 UPDATE 성능을 향상할 수 있다.
• 데이터 보안과 격리 향상
  • 보안이 필요한 데이터를 별도의 파티션으로 분리하여 액세스 제어 및 보안 정책 적용이 가능하다.
• 데이터베이스 성능 향상
  • 특정 Query의 성능 향상이 가능하다.
  • 대용량 Data 쓰기 환경에서 효율적이다.
  • Full Scan에서 데이터 접근 범위를 줄여 성능을 향상시킬 수있다.

그러나, 다음과 같은 단점도 존재한다.

• 파티셔닝된 테이블 사이의 조인(Join) 연산의 복잡성 증가
  • 파티션 키를 사용하지 않는 조인은 전체 파티션을 스캔해야 할 수 있다.
• 데이터가 균등하게 파티셔닝되지 않는 경우, 특정 파티션의 부하 집중
  • 데이터 분산 전략과 로드 밸런싱 메커니즘 고려
• 파티셔닝된 테이블의 특정 행의 데이터 변경은 해당 행이 위치한 파티션을 식별해야 한다.
  • 데이터 변경 관리의 복잡성이 증가

---

3. 파티셔닝 방법(Partitioning Method)은 무엇인가?🧐

파티셔닝의 방법은 크게 2가지로 구분된다.

 

3-1. 수평 파티셔닝(Horizontal Partitioning)

수평 파티셔닝(Horizontal Partitioning)은 행 단위로 분할하는 방법이다.

가장 일반적으로 분산 저장 기술에서 파티셔닝은 수평 분할을 의미한다.

• 데이터의 특정 열 값을 기준으로 데이터를 분할
• 각 파티션에는 특정 범위 또는 조건에 해당하는 행들이 포함

 

3-2. 수직 파티셔닝(Vertical Partitioning)

수직 파티셔닝(Vertical Partitioning)은 데이터를 열 단위로 분할하는 방법이다.

• 테이블의 열들이 논리적 혹은 물리적으로 분할되어 각 파티션에는 특정 열 또는 열 그룹으로 포함
• 주로 DB에서 자주 사용치 않은 열이나 크기가 큰 열을 분리하여 저장 공간을 절약하고, 액세스 성능을 개선하는데 사용

---

4.  파티셔닝 기법(Partitioning Type)은?! 🧐

PostgreSQL을 기준으로 3가지 기본 파티셔닝 형식을 기본적으로 지원한다.
파티셔닝 예시는 범위 파티셔닝으로 작성할 예정이다.

 

4-1. 범위 파티셔닝(Range Partitioning)

    테이블의 키 열 또는 열 집합에 의해 정의된 범위로 분할되고, 다른 파티션에 할당된 값 범위 간에 겹치지 않는다.

 

    범위 파티셔닝은 다음과 같은 용도로 사용될 경우 적합하다.

    ① 범위 기반으로 데이터를 여러 파티션에 균등히 나눌 수 있는 경우

    ② 파티션 키 위주로 검색이 자주 실행되는 경우

 

-- 테이블 생성 users_range
CREATE TABLE users_range(
    id bigint,
    nickname varchar(20),
    gender char(1),
    wrk_date timestamp not null
    PRIMARY KEY(id, wrk_date)
) PARTITION BY range(wrk_date);



-- 7월부터 9월까지의 파티셔닝 된 테이블을 다음과 같이 생성한다.
CREATE TABLE users_range_2023_07 PARTITION OF users_range FOR VALUES FROM('2023-07-01 00:00:00') TO('2023-08-01 00:00:00');
CREATE TABLE users_range_2023_08 PARTITION OF users_range FOR VALUES FROM('2023-08-01 00:00:00') TO('2023-09-01 00:00:00');
CREATE TABLE users_range_2023_09 PARTITION OF users_range FOR VALUES FROM('2023-09-01 00:00:00') TO('2023-10-01 00:00:00');


-- 임의 데이터 5개 INSERT
study=# insert into users_range values (1, 'hide', 'M', '2023-07-23')
                                    , (2, 'jeo', 'F', '2023-08-02')
                                    , (3, 'lee', 'F', '2023-08-11')
                                    , (4, 'hyuk', 'M', '2023-09-04')
                                    , (5, 'seo', 'F', '2023-08-28');

INSERT 0 5

study=# select * from users_range ;
 id | nickname | gender |       wrk_date
----+----------+--------+---------------------
  1 | hide     | M      | 2023-07-23 00:00:00
  2 | jeo      | F      | 2023-08-02 00:00:00
  3 | lee      | F      | 2023-08-11 00:00:00
  4 | hyuk     | M      | 2023-09-04 00:00:00
  5 | seo      | F      | 2023-08-28 00:00:00
(5 rows)


study=# select * from users_range_2023_07 ;
 id | nickname | gender |       wrk_date
----+----------+--------+---------------------
  1 | hide     | M      | 2023-07-23 00:00:00
(1 row)


study=# select * from users_range_2023_08 ;
 id | nickname | gender |       wrk_date
----+----------+--------+---------------------
  2 | jeo      | F      | 2023-08-02 00:00:00
  3 | lee      | F      | 2023-08-11 00:00:00
  5 | seo      | F      | 2023-08-28 00:00:00
(3 rows)


study=# select * from users_range_2023_09 ;
 id | nickname | gender |       wrk_date
----+----------+--------+---------------------
  4 | hyuk     | M      | 2023-09-04 00:00:00
(1 row)

 

4-2. 리스트 파티셔닝(List Partitioning)

    각 분할 영역에 나타나는 키 값을 명시적으로 나열하여 분할된다.

   

    리스트 파티셔닝은 다음과 같은 용도로 사용될 경우 적합하다.

    ① 파티션 키 값이 코드 값이나 카테고리와 같이 고정적인 경우

    ② 키 값이 연속되지 않고 정렬 순서와 관계없이 파티션을 해야하는 경우

    ③ 파티션 키 값을 기준으로 레코드의 건수가 균일하고 검색 조건에 파티션 키가 자주 사용되는 경우

 

-- 테이블 생성 users_list
CREATE TABLE users_list(
    id bigint,
    nickname varchar(20),
    gender char(1)
) PARTITION BY LIST(gender);



-- value가 'M'인 경우, users_list_m 파티션으로, 'F'인 경우, users_list_f 파티션 테이블로
CREATE TABLE users_list_m PARTITION OF users_list FOR VALUES IN('M');
CREATE TABLE users_list_f PARTITION OF users_list FOR VALUES IN('F');


-- 임의 데이터 5개 Insert
study=# insert into users_list values (1, 'hide', 'M')
                                   , (2, 'jeo', 'F')
                                   , (3, 'lee', 'F')
                                   , (4, 'hyuk', 'M')
                                   , (5, 'seo', 'F');

INSERT 0 5

study=# select * from users_list ;
 id | nickname | gender
----+----------+--------
  1 | hide     | M
  2 | jeo      | F
  3 | lee      | F
  4 | hyuk     | M
  5 | seo      | F
(5 rows)

study=# select * from users_list_m ;
 id | nickname | gender
----+----------+--------
  1 | hide     | M
  4 | hyuk     | M
(2 rows)

study=# select * from users_list_f ;
 id | nickname | gender
----+----------+--------
  2 | jeo      | F
  3 | lee      | F
  5 | seo      | F
(3 rows)

 

4-3. 해시 파티셔닝(Hash Partitioning)

    각 파티션에 대한 계수와 나머지를 지정하여 분할되고, 각 파티션은 지정된 계수로 나눈 파티션 키의 해시 값이 지정된 나머지를 생성하는 행을 보유한다.

 

 

    해시 파티셔닝은 다음과 같은 용도로 사용될 경우 적합하다.

    ① 레인지 파티셔닝 / 리스트 파티셔닝 2가지로도 데이터를 균등하게 나누기 어려운 경우

    ② 테이블의 모든 레코드가 비슷한 사용 빈도를 보이지만 테이블이 너무 커서 파티션을 적용해야하는 경우

---

5. 선언적 파티셔닝 (Declarative Partitioning)은 또 뭐지? 😳

PostgreSQL을 사용하면 테이블이 파티션으로 나뉜다고 선언할 수 있으며, 분할된 테이블을 분할 테이블 이라 한다.

선언에는 위에서 설명한 분할 방법 과 분할 키로 사용할 열 또는 표현식 목록이 포함된다.

 

분할된 테이블 자체는 자체 스토리지가 없는 가상테이블이다.그 대신 스토리지는 파티션 된 테이블과 연결된 일반 테이블인 partitions에 속하며,

각 파티션은 파티션 경계 로 정의된 데이터의 하위 집합을 저장한다.

 

분할된 테이블에 삽입된 모든 행은 파티션 키 열의 값에 따라 적절한 파티션으로 라우팅되며,

행의 파티션 키를 Update 시, 원래 파티션의 파티션 경계를 더 이상 충족하지 않는 경우 행이 다른 파티션으로 이동된다.

 

5-1. 선언적 파티셔닝 예시 (Declarative Partitioning Example)

    1. 분할 방법( 이 경우 RANGE )과 분할 키로 사용할 열 목록을 포함하는 PARTITION BY 절을 지정하여

    measurement 테이블을 분할된 테이블로 만든다.

-- 측정 논리 테이블 생성
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
);

comment on table measurement is '측정 테이블';
comment on column measurement.city_id is '도시_id';
comment on column measurement.logdate is 'log일자';
comment on column measurement.peaktemp is '최고온도';
comment on column measurement.unitsales is '지역별 아이스크림 판매량';

    2. 파티션 생성은 월별로 나누고, 한달이 지난 후 데이터를 삭제해야 한다는 요구사항이 있을 시, 다음과 같은 쿼리를 사용하면 된다.

CREATE TABLE measurement_y2023m07 PARTITION OF measurement
    FOR VALUES FROM ('2023-07-01') TO ('2023-08-01');
CREATE TABLE measurement_y2023m08 PARTITION OF measurement
    FOR VALUES FROM ('2023-08-01') TO ('2023-09-01');
CREATE TABLE measurement_y2023m09 PARTITION OF measurement
    FOR VALUES FROM ('2023-09-01') TO ('2023-10-01');

    3. 파티션 유지보수로서 한달이 지난 후 7월 데이터를 삭제해야한다면 아래와 같이 제거하면 된다.

-- 가장 간단한 옵션은 더 이상 필요하지 않은 파티션을 삭제하는 것
DROP TABLE measurement_y2023m07;

-- 또 다른 옵션은 분할된 테이블에서 파티션을 제거하지만 그 자체로 테이블로 액세스할 수 있는 권한을 유지하는 것
ALTER TABLE measurement DETACH PARTITION measurement_y2023m07;

이상으로 Database Partitioning의 개념에 대해 작성해보았다.

확실히 눈으로 익힌 것보다는 직접 예제로 실습하면서 하니 파티셔닝에 대해 더욱 쉽게 이해된다.

 

이상한 점이나 잘못 작성된 내용은 댓글로 작성해주시면 수정하겠습니다.

---

📜 REFRENCE

 

5.11. Table Partitioning

 

PostgreSQL - 5.11. 테이블 파티셔닝 PostgreSQL은 기본 테이블 파티셔닝을 지원합니다.

 

[PostgreSQL] Table Partitioning (테이블 파티셔닝)

 

[PostgreSQL] DB 파티셔닝(Partitioning) 정의 및 예제

 

데이터베이스 파티셔닝과 샤딩