PostgreSQL partioning table (24.06.11)

PostgreSQL Partitioning Table

1. 개념 정의

Partitioning Table: 대량의 데이터를 효율적으로 관리하고 조회 성능을 향상시키기 위해 테이블을 작은 단위(파티션)로 나누는 기술이다. 각 파티션은 독립된 테이블처럼 동작하며, 전체 테이블은 이러한 파티션의 집합으로 관리된다.

2. 파티셔닝의 종류

Range Partitioning: 특정 열의 값 범위에 따라 데이터를 나눈다.
- 예시: 날짜 열을 기준으로 월별로 데이터를 분할.

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

CREATE TABLE measurement_y2023m01 PARTITION OF measurement
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

List Partitioning: 특정 열의 값을 기반으로 데이터를 나눈다.
- 예시: 지역 코드에 따라 데이터를 분할.

CREATE TABLE measurement (
    city_id         int,
    logdate         date,
    peaktemp        int,
    unitsales       int
) PARTITION BY LIST (city_id);

CREATE TABLE measurement_north PARTITION OF measurement
    FOR VALUES IN (1, 2, 3);

Hash Partitioning: 해시 함수를 사용하여 데이터를 나눈다.
- 예시: 데이터 균등 분할이 필요할 때 사용.

CREATE TABLE measurement (
    city_id         int,
    logdate         date,
    peaktemp        int,
    unitsales       int
) PARTITION BY HASH (city_id);

CREATE TABLE measurement_part1 PARTITION OF measurement
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

Composite Partitioning: 두 가지 이상의 파티셔닝 방법을 결합하여 데이터를 나눈다.
- 예시: Range와 List를 결합하여 분할.

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

CREATE TABLE measurement_y2023 PARTITION OF measurement
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE measurement_y2023_north PARTITION OF measurement_y2023
    FOR VALUES IN (1, 2, 3);

3. 파티셔닝의 장점

성능 향상: 특정 파티션에만 접근하므로 조회 성능이 향상된다.
관리 용이성: 파티션 단위로 데이터 관리를 할 수 있어 백업, 복구, 유지보수가 용이하다.
디스크 공간 최적화: 파티션별로 저장소를 다르게 설정할 수 있어 디스크 공간 사용을 최적화할 수 있다.
병렬 처리 효율성: 각 파티션이 독립적으로 처리되므로 병렬 처리가 가능하다.

4. 파티셔닝의 단점

설정 복잡성: 초기 설정이 복잡하고 관리가 어렵다.
제약 조건 관리: 파티션 간 제약 조건을 설정하는 데 어려움이 있다.
인덱스 관리: 각 파티션에 대해 별도의 인덱스를 관리해야 한다.

5. 주의 사항

균형 잡힌 파티셔닝: 파티션 크기가 균형을 이루도록 설계해야 한다.
적절한 파티션 키 선택: 데이터의 액세스 패턴을 고려하여 적절한 파티션 키를 선택해야 한다.
주기적 유지보수: 데이터 양의 변화에 따라 파티션을 재조정하는 유지보수가 필요하다.

목표 : 시간에 따라 최근 N일 내의 데이터만 테이블에 남기기.

https://velog.io/@dailylifecoding/postgresql-table-partitioning

https://www.postgresql.org/docs/current/ddl-partitioning.html

1. 파티셔닝을 위한 PK 지표 추가 여부

파티셔닝 키의 선택

기존 테이블의 PK 보존: 기존 테이블의 Primary Key(PK)는 파티셔닝 과정에서 보존할 수 있다. 새로운 파티셔닝 키는 기존 PK에 추가되어야 한다.
파티셔닝 키로 사용할 열: date 단위를 사용하는 것이 일반적이다. 이는 날짜 또는 시간에 기반한 파티셔닝을 위해 필요하다. 예를 들어, created_at 열이 있다면 이를 파티셔닝 키로 사용할 수 있다.

이를 위해 시간 기반 값을 임베디드 키로 변경해야 한다.

ex)

CREATE TABLE logs (
    id serial PRIMARY KEY,
    message text,
    created_at timestamp #시간 기반 파티셔닝 용 지표
);

----
이를 파티셔닝 테이블로 변경하려면 created_at 열을 파티셔닝 키로 사용할 수 있다.

CREATE TABLE logs (
    id serial,
    message text,
    created_at timestamp,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

2. 파티셔닝 관리 방법

새로운 파티션 추가

시간이 지남에 따라 새로운 파티션을 추가해야 한다. 이를 위해 자동화된 스크립트나 일정한 주기로 파티션을 추가하는 방법을 사용할 수 있다.

자동 스크립트: 크론잡(cron job)이나 스케줄러를 사용하여 주기적으로 새로운 파티션을 생성하는 스크립트를 실행한다.

궁금증

현재 시간 기반 파티셔닝으로 최대 1일 단위로 나뉜다고 가정하자.

만약 검색 범위의 기간이 N일이라면 현재 어떤 방식으로 조회할까? Spring에서 쿼리를 어떻게 전파해야 할까?

답은 : 그냥 원본 논리 테이블에 범위 검색을 통해 검색할 경우, 자동으로 하위 나뉜 파티션에서 조회하고 이를 DB 내에서 집계하여 결과를 보내준다.

tomahawk=# explain select * from tbl_eum_stat_http_uri_1day where generate_time between '2024-06-06' and '2024-06-11';
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.12..14.35 rows=6 width=12670)
   ->  Index Scan using tbl_eum_stat_http_uri_1day_20240606_pkey on tbl_eum_stat_http_uri_1day_20240606 tbl_eum_stat_http_uri_1day_1  (cost=0.12..2.34 rows=1 width=12670)
         Index Cond: ((generate_time >= '2024-06-06 00:00:00'::timestamp without time zone) AND (generate_time <= '2024-06-11 00:00:00'::timestamp without time zone))
   ->  Index Scan using tbl_eum_stat_http_uri_1day_20240607_pkey on tbl_eum_stat_http_uri_1day_20240607 tbl_eum_stat_http_uri_1day_2  (cost=0.25..2.47 rows=1 width=12670)
         Index Cond: ((generate_time >= '2024-06-06 00:00:00'::timestamp without time zone) AND (generate_time <= '2024-06-11 00:00:00'::timestamp without time zone))
   ->  Index Scan using tbl_eum_stat_http_uri_1day_20240608_pkey on tbl_eum_stat_http_uri_1day_20240608 tbl_eum_stat_http_uri_1day_3  (cost=0.12..2.34 rows=1 width=12670)
         Index Cond: ((generate_time >= '2024-06-06 00:00:00'::timestamp without time zone) AND (generate_time <= '2024-06-11 00:00:00'::timestamp without time zone))
   ->  Index Scan using tbl_eum_stat_http_uri_1day_20240609_pkey on tbl_eum_stat_http_uri_1day_20240609 tbl_eum_stat_http_uri_1day_4  (cost=0.12..2.34 rows=1 width=12670)
         Index Cond: ((generate_time >= '2024-06-06 00:00:00'::timestamp without time zone) AND (generate_time <= '2024-06-11 00:00:00'::timestamp without time zone))
   ->  Index Scan using tbl_eum_stat_http_uri_1day_20240610_pkey on tbl_eum_stat_http_uri_1day_20240610 tbl_eum_stat_http_uri_1day_5  (cost=0.25..2.47 rows=1 width=12670)
         Index Cond: ((generate_time >= '2024-06-06 00:00:00'::timestamp without time zone) AND (generate_time <= '2024-06-11 00:00:00'::timestamp without time zone))
   ->  Index Scan using tbl_eum_stat_http_uri_1day_20240611_pkey on tbl_eum_stat_http_uri_1day_20240611 tbl_eum_stat_http_uri_1day_6  (cost=0.12..2.34 rows=1 width=12670)
         Index Cond: ((generate_time >= '2024-06-06 00:00:00'::timestamp without time zone) AND (generate_time <= '2024-06-11 00:00:00'::timestamp without time zone))
(13 rows)

부모 테이블에 쿼리를 호출할 경우 postgresql에서 자동으로 where절 기간에 맞게 내부적으로 n번 select을 하고 union all 해서 보내준다.

일반 사용자 관점에선 단일 테이블에 select 친거하고 차이가 없을 것. 물론 개별 파티션을 직접 지정해서 쿼리 치는것도 가능하다.

실제 계획

기본 테이블을 생성하고 이를 Range Partitioning으로 설정한 후, 하루 단위로 파티션을 나눈다.

원본 테이블 생성

모든 데이터를 포함하는 원본 테이블이 있다.

원본 테이블은 실제 데이터를 포함하지 않으며, 각 파티션 테이블에 대한 메타데이터와 파티셔닝 규칙만을 가지고 있다.
따라서 원본 테이블의 크기는 매우 작다.

파티셔닝 설정

원본 테이블을 시간 값 기반의 PK를 사용하여 PARTITION OF 명령을 통해 여러 개의 파티션으로 나눈다.

실제 데이터는 각 파티셔닝된 테이블(파티션)에 저장된다.
각 파티셔닝된 테이블은 원본 테이블의 데이터 중 특정 범위에 해당하는 데이터를 포함한다.
파티셔닝된 테이블의 크기 합은 원본 테이블에 저장된 전체 데이터의 크기와 동일하다.

파티셔닝 테이블의 데이터 구조

파티셔닝된 테이블은 원본 테이블의 주소만 가지고 있는 것이 아니라, 실제 데이터를 물리적으로 저장한다.
즉, 파티셔닝된 테이블은 원본 테이블의 데이터를 분할하여 저장하는 역할을 한다.

원본 테이블에 인덱스 생성 시 하위 파티션에 적용 여부

원본 테이블에 인덱스를 생성할 경우, 해당 인덱스는 각 하위 파티션에도 동일하게 적용된다. 즉, 원본 테이블에 인덱스를 걸면 하위 파티션 테이블에도 자동으로 인덱스가 생성된다.
자동 생성: Crontab이나 반복 작업을 통해 주기적으로 새로운 파티션을 생성하고 관리한다.

하지만 나의 경우 Socket 기반의 자체 Collector 서버가 존재하기 때문에, 이 서버의 Schedular를 통해 자체적으로 테이블을 생성할 수 있게 하겠다.

대표적인 예시

-- agent info
CREATE TABLE tbl_apm_agent
(
    id         BIGSERIAL,
    name       VARCHAR,
    recent_io  timestamp,
    created_at timestamp,
    PRIMARY KEY (id, created_at),
    UNIQUE (id),  -- 단일 PK값을 FK로 사용하기 위함
    UNIQUE (name, created_at)  -- 파티셔닝 키 포함 : 파티션에서 유니크 제약 조건은 반드시 range 기준 값을 포함해야 한다(여러 분할이 일어나므로)
) PARTITION BY RANGE (created_at);

--.. 이것을 이제 이런 식으로 사용할 수 있을 것이다.
CREATE TABLE tbl_apm_agent_20240101 PARTITION OF tbl_apm_agent
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

궁금증 : Partitioning 단위의 시간은 짧을 수록 좋을까?

PostgreSQL에서 시간 기반 파티셔닝을 할 때 시간 단위를 짧게 잡는 것이 테이블의 튜플 수를 줄여줄 수 있지만, 다음과 같은 단점이 있을 수 있다.

파티션 관리의 복잡성 증가:
- 시간 단위를 짧게 잡으면 파티션의 수가 많아지게 된다. 예를 들어, 1분 단위로 파티셔닝하면 하루에 1440개의 파티션이 생긴다. 이 많은 파티션을 관리하는 것은 상당히 복잡할 수 있다.
- 예시: 1분 단위로 파티셔닝된 테이블에서 특정 시간 범위의 데이터를 삭제하려면 수백 개의 파티션을 일일이 관리해야 할 수 있다.
인덱스 및 메타데이터 오버헤드 증가:
- 각 파티션은 자체 인덱스를 가지고 있으며, PostgreSQL은 각 파티션에 대한 메타데이터를 유지해야 한다. 파티션 수가 많아질수록 이러한 인덱스 및 메타데이터의 크기가 커지고, 이는 시스템의 오버헤드를 증가시킬 수 있다.
- 예시: 1시간 단위로 파티셔닝된 테이블에 인덱스를 추가하면, 하루에 24개의 인덱스를 유지해야 하므로 인덱스 생성 및 유지 관리에 필요한 시간이 증가할 수 있다.
쿼리 성능 저하 가능성:
- 짧은 시간 단위로 파티셔닝하면 특정 시간 범위를 조회할 때 여러 파티션을 스캔해야 할 수 있다. 이는 쿼리 성능을 저하시킬 수 있다.
- 예시: 한 달 동안의 데이터를 조회하는 경우 1시간 단위로 파티셔닝된 테이블에서는 720개의 파티션을 스캔해야 할 수 있다. 이 경우 쿼리 성능이 저하될 수 있다.
중복 데이터 문제:
- 짧은 시간 단위로 파티셔닝하면 동일한 데이터가 여러 파티션에 중복될 수 있다. 이는 스토리지 공간을 낭비하고 데이터 일관성을 유지하기 어렵게 만든다.
- 예시: 1분 단위로 파티셔닝된 테이블에서 특정 이벤트가 10분 동안 발생한 경우, 이 이벤트는 10개의 파티션에 중복 저장될 수 있다.

따라서, 최적의 시간 단위를 선택하려면 다음과 같은 요소들을 고려해야 한다:

데이터의 양: 데이터가 너무 많다면 짧은 시간 단위로 파티셔닝하여 각 파티션의 크기를 줄이는 것이 좋다.
쿼리 패턴: 주로 어떤 시간 범위의 데이터를 조회하는지 분석하여 그에 맞는 시간 단위를 설정한다.
데이터 유지 관리: 데이터 삭제, 백업 등의 유지 관리 작업이 용이하도록 적절한 시간 단위를 선택한다.

결론적으로, 너무 짧은 시간 단위로 파티셔닝하면 관리의 복잡성, 인덱스 및 메타데이터 오버헤드, 쿼리 성능 저하, 중복 데이터 등의 문제가 발생할 수 있다. 반면, 너무 긴 시간 단위로 파티셔닝하면 파티셔닝의 이점을 충분히 누리지 못할 수 있다. 최적의 시간 단위를 선택하기 위해서는 데이터의 특성과 사용 패턴을 신중히 분석해야 한다.

짧은 시간 단위로 파티셔닝할 때의 주요 단점은 다음과 같다

쿼리 성능 저하: 파티션 수가 많아질수록 쿼리 플래너의 오버헤드가 증가하고, 많은 파티션을 스캔해야 하는 경우 성능 저하가 발생할 수 있다.
중복 데이터 문제: 동일한 데이터가 여러 파티션에 중복 저장될 가능성이 있으며, 이는 데이터 일관성 유지 및 데이터 관리의 복잡성을 증가시킨다.

실제 실행 결과 예시

예제 데이터 스키마

CREATE TABLE tbl_apm_hist_db_client_connections_wait_time
(
    id                   BIGSERIAL,                       --pk
    created_at            timestamp,                       --pk, partitioning 기준 값
    agent_id             BIGINT,                          --fk1
    metric_id            bigint,                          --fk2
    metric_created_at    timestamp,                       --fk2
    start_time_unix_nano bigint,
    time_unix_nano       bigint,
    attributes           jsonb,
    count                bigint,
    sum                  double precision,
    bucket_counts        bigint[],
    explicit_bounds      double precision[],
    min                  double precision,
    max                  double precision,
    PRIMARY KEY (id, created_at),                  --Range PK
    foreign key (agent_id) references tbl_apm_agent (id), -- AgentID Fk
    foreign key (metric_id, metric_created_at) REFERENCES tbl_apm_metric (id, created_at)
) PARTITION BY RANGE (created_at);

Quartz를 통해 자동으로 수집되는 데이터의 시간 단위로 파티션을 분할하였다. (timestamp = ‘created_at’)

이에 맞춰 실행할 경우를 살펴보자.

 explain     select
                metric_id,
                start_time_unix_nano,
                time_unix_nano,
                count,
                sum,
                array_to_string(bucket_counts,
                                ',') as bucket_counts,
                array_to_string(explicit_bounds,
                                ',') as explicit_bounds,
                min,
                max,
                attributes->>'pool.name'
            FROM
                tbl_apm_hist_db_client_connections_wait_time
            WHERE
                created_at BETWEEN to_timestamp(1718256796049410188 / 1000000000) AND to_timestamp(1718256896049410188 / 1000000000)
              AND agent_id = 1
;
                                                                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tbl_apm_hist_db_client_connections_wait_time_2024061314_pkey on tbl_apm_hist_db_client_connections_wait_time_2024061314 tbl_apm_hist_db_client_connections_wait_time  (cost=0.15..8.38 rows=1 width=152)
   Index Cond: ((created_at >= '2024-06-13 14:33:16+09'::timestamp with time zone) AND (created_at <= '2024-06-13 14:33:16+09'::timestamp with time zone))
   Filter: (agent_id = 1)
(3 rows)

해당 쿼리의 EXPLAIN 결과는 다음과 같다:

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tbl_apm_hist_db_client_connections_wait_time_2024061314_pkey on tbl_apm_hist_db_client_connections_wait_time_2024061314 tbl_apm_hist_db_client_connections_wait_time  (cost=0.15..8.38 rows=1 width=152)
   Index Cond: ((created_at >= '2024-06-13 14:33:16+09'::timestamp with time zone) AND (created_at <= '2024-06-13 14:33:16+09'::timestamp with time zone))
   Filter: (agent_id = 1)
(3 rows)

이 결과를 통해 다음 사항을 확인할 수 있다:

Index Scan: tbl_apm_hist_db_client_connections_wait_time_2024061314_pkey 인덱스를 사용하여 스캔하고 있다. 이는 인덱스를 제대로 사용하고 있다는 의미이다.
Index Cond: created_at 컬럼에 대한 조건으로 created_at >= '2024-06-13 14:33:16+09'와 created_at <= '2024-06-13 14:33:16+09'를 사용하고 있다. 이는 파티션 키로 사용된 created_at이 조건에 잘 적용되었음을 나타낸다.
Filter: agent_id = 1에 대한 추가 필터링을 적용하고 있다.
먼저 '파티션' 기준으로 조회 후(시간에 맞는 파티션 테이블을 찾은 후)
여기서 PK로 조회

QUERY PLAN에서 나오는 순서는 쿼리 실행 계획의 논리적인 순서이지, 실제 실행 순서를 나타내는 것은 아니다.

이 쿼리는 created_at을 기준으로 파티셔닝된 테이블에서 인덱스를 잘 타고 있으며, 성능 상의 문제가 없다. 파티션 키와 인덱스를 모두 활용하여 효율적으로 데이터를 조회하고 있다.

#이전 쿼리와 다르게 1~ 2시간 정도의 간격 범위를 스캔해야 한다면?
 explain     select
                metric_id,
                start_time_unix_nano,
                time_unix_nano,
                count,
                sum,
                array_to_string(bucket_counts,
                                ',') as bucket_counts,
                array_to_string(explicit_bounds,
                                ',') as explicit_bounds,
                min,
                max,
                attributes->>'pool.name'
            FROM
                tbl_apm_hist_db_client_connections_wait_time
            WHERE
                created_at BETWEEN to_timestamp(1718256796049410188 / 1000000000) AND to_timestamp(1718262196049410188 / 1000000000)
              AND agent_id = 1
; 
                                                                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..23.18 rows=62 width=152)
   ->  Seq Scan on tbl_apm_hist_db_client_connections_wait_time_2024061314 tbl_apm_hist_db_client_connections_wait_time_1  (cost=0.00..14.48 rows=61 width=152)
         Filter: ((created_at >= '2024-06-13 14:33:16+09'::timestamp with time zone) AND (created_at <= '2024-06-13 16:03:16+09'::timestamp with time zone) AND (agent_id = 1))
   ->  Index Scan using tbl_apm_hist_db_client_connections_wait_time_2024061315_pkey on tbl_apm_hist_db_client_connections_wait_time_2024061315 tbl_apm_hist_db_client_connections_wait_time_2  (cost=0.15..8.38 rows=1 width=152)
         Index Cond: ((created_at >= '2024-06-13 14:33:16+09'::timestamp with time zone) AND (created_at <= '2024-06-13 16:03:16+09'::timestamp with time zone))
         Filter: (agent_id = 1)

만약 시간 범주를 늘린다면 두 개의 파티션을 동시에 조회함을 확인할 수 있다.
이는 파티션이 ‘1시간 기준’으로 나뉘어져 있기에 발생한다.

'프로젝트 > APM Web Server 개발' 카테고리의 다른 글

[트랜잭션, EM]네이티브 쿼리 임시 테이블 미적용 문제 (24.06.17) (0)	2024.09.26
(in 연산 → join) 및 커스텀 NativeQuery (24.06.14) (1)	2024.09.26
JSONB 타입 다뤄보기 - 1 (24.05.22) (0)	2024.09.26
대규모 쿼리 최적화 1차 시도 (IN 방지..) (24.05.21) (0)	2024.09.26

초보자의 개발일지

PostgreSQL partioning table (24.06.11)

PostgreSQL Partitioning Table

1. 개념 정의

2. 파티셔닝의 종류

3. 파티셔닝의 장점

4. 파티셔닝의 단점

5. 주의 사항

목표 : 시간에 따라 최근 N일 내의 데이터만 테이블에 남기기.

1. 파티셔닝을 위한 PK 지표 추가 여부

파티셔닝 키의 선택

2. 파티셔닝 관리 방법

새로운 파티션 추가

궁금증

실제 계획

원본 테이블 생성

파티셔닝 설정

파티셔닝 테이블의 데이터 구조

궁금증 : Partitioning 단위의 시간은 짧을 수록 좋을까?

실제 실행 결과 예시

'프로젝트 > APM Web Server 개발' 카테고리의 다른 글

티스토리툴바

PostgreSQL partioning table (24.06.11)

PostgreSQL Partitioning Table

1. 개념 정의

2. 파티셔닝의 종류

3. 파티셔닝의 장점

4. 파티셔닝의 단점

5. 주의 사항

목표 : 시간에 따라 최근 N일 내의 데이터만 테이블에 남기기.

1. 파티셔닝을 위한 PK 지표 추가 여부

파티셔닝 키의 선택

2. 파티셔닝 관리 방법

새로운 파티션 추가

궁금증

실제 계획

원본 테이블 생성

파티셔닝 설정

파티셔닝 테이블의 데이터 구조

궁금증 : Partitioning 단위의 시간은 짧을 수록 좋을까?

실제 실행 결과 예시

'프로젝트 > APM Web Server 개발' 카테고리의 다른 글

'프로젝트/APM Web Server 개발' Related Articles

티스토리툴바