PostgreSQL: Partition

PostgreSQL

PostgreSQL: Partition

dewstream 2025. 6. 26. 08:00

728x90

※ PostgreSQL: Partition.

안녕하세요. 듀스트림입니다.

오늘은 PostgreSQL의 파티션에 관한 내용입니다.

파티셔닝은 대규모 데이터베이스 시스템에서 성능 최적화, 데이터 관리 효율성 증대, 백업 및 복구 최적화 등 여러 측면에서 중요한 기능입니다.

그리고 가장 많이 궁금해하시는 오라클처럼 Parttion Exchange 명령은 안됩니다.
우회 방안은 본문의 파티션 관리 부분 확인 부탁드립니다.

1. 파티션 개요

파티셔닝은 큰 테이블을 작은 파티션으로 나누어 데이터를 효율적으로 관리할 수 있게 해줍니다.
파티셔닝된 테이블은 성능 향상뿐만 아니라 데이터 관리 및 유지보수 측면에서도 이점을 제공합니다.

▸ PostgreSQL에서는 다음과 같은 파티셔닝 기법을 지원합니다.

Range Partitioning
List Partitioning
Hash Partitioning

▸ Partition Key는 반드시 Primary Key 또는 Unique Constraint에 포함되어야 합니다.

파티션 구조의 특성
- 파티션 테이블에서 각 파티션은 독립적인 테이블처럼 동작합니다. 파티션 테이블은 실제로 여러 개의 하위 테이블로 나뉘기 때문에 각 파티션의 중복을 방지하려면 전체 테이블 수준에서 고유성을 보장해야 합니다.
- Primary Key 또는 Unique Constraint을 설정할 때 파티션 간 중복을 방지하려면 파티션 키를 제약 조건의 컬럼에 반드시 포함시켜야 합니다.
고유성 보장
- Primary Key는 테이블에서 중복된 값이 존재하지 않도록 보장합니다. 파티션 테이블에서 기본키가 파티션 키를 포함하면 파티션 간에도 동일한 값이 존재하지 않도록 보장할 수 있습니다.
- Unique Constraint도 동일한 방식으로 각 파티션 내에서만 고유성을 보장하는 것이 아니라 전체 테이블에 대해 고유성을 보장하려면 파티션 키를 포함해야만 중복을 방지할 수 있습니다.

▸ 파티션의 장점

성능 향상: 특정 범위나 조건에 맞는 데이터만 조회하므로 쿼리 성능이 향상됩니다. (파티션 키 컬럼 인덱스 필수)
데이터 관리 효율성: 특정 파티션만 백업하거나 삭제하는 등 관리가 용이합니다.

5.12. Table Partitioning

5.12. Table Partitioning # 5.12.1. Overview 5.12.2. Declarative Partitioning 5.12.3. Partitioning Using Inheritance 5.12.4. Partition Pruning 5.12.5. Partitioning and Constraint Exclusion …

www.postgresql.org

2. 제한 사항

Primary Key 및 Unique Constraint
- 파티션 테이블에 Primary Key 또는 Unique Constraint를 설정하려면 파티션 키가 제약 조건의 컬럼에 반드시 포함되어야 합니다.
배제 제약 (Exclusion Constraint)
- 배제 제약을 설정할 때도 파티션 키를 반드시 포함해야 합니다.
- 또한, 제약은 파티션 키의 값을 동등 비교하는 방식으로 설정해야 합니다.
  (예를 들어 &&와 같은 연산자는 사용할 수 없습니다.)
- 추가로 파티션 키 이외의 컬럼을 포함하고 해당 컬럼들에 대해 원하는 연산자를 사용할 수 있습니다.
BEFORE ROW 트리거
- INSERT에 대한 BEFORE ROW 트리거는 새로운 데이터가 어느 파티션에 삽입될지를 변경할 수 없습니다.
- 즉, 트리거를 통해 데이터를 삽입할 파티션을 변경하는 것은 불가능합니다.
임시 테이블과 영구 테이블의 혼합 금지
- 임시 테이블과 영구 테이블을 동일한 파티션 트리에서 혼합하여 사용할 수 없습니다.
- 파티션 테이블이 영구 테이블이면 그 파티션들도 영구 테이블이어야 하며 임시 테이블이라면 파티션들도 임시 테이블이어야 합니다.
파티션 테이블의 상속 및 열 제한
- 파티션은 상속 관계로 연결되며 상속된 테이블의 열을 수정할 수 없습니다.
- 즉, 파티션에 새로운 열을 추가하거나 수정할 수 없고 파티션은 부모 테이블의 열과 정확히 일치해야 합니다.
제약 조건의 상속
- CHECK 및 NOT NULL 제약은 부모 테이블에서 모든 파티션에 상속됩니다.
- NO INHERIT로 설정된 CHECK 제약은 파티션 테이블에서 사용할 수 없습니다.
- ONLY를 사용하여 제약 조건을 파티션 테이블만 수정할 수 있는 경우는 파티션이 존재하지 않을 때에만 가능합니다
- 파티션이 존재하면 ONLY로는 제약을 수정할 수 없으며 파티션에서 제약을 추가하거나 삭제해야 합니다.
TRUNCATE ONLY
- 파티션 테이블에 대해 TRUNCATE ONLY를 사용할 수 없습니다.
- 파티션이 있을 경우 TRUNCATE ONLY는 오류를 반환합니다.

TRUNCATE
• 테이블 상속 구조에서 부모 테이블을 TRUNCATE하면 기본적으로 자식 테이블까지 모두 비워집니다.
• ONLY 키워드를 사용하면 오직 부모 테이블만 비워지고 자식 테이블은 영향을 받지 않습니다.

5.12. Table Partitioning

5.12. Table Partitioning # 5.12.1. Overview 5.12.2. Declarative Partitioning 5.12.3. Partitioning Using Inheritance 5.12.4. Partition Pruning 5.12.5. Partitioning and Constraint Exclusion …

www.postgresql.org

3. 파티션 기법

PostgreSQL은 복합 파티션을 지원합니다.

3.1 일반 파티셔닝

3.1.1 Range Partitioning

레인지 파티셔닝은 특정 범위에 따라 데이터를 나누는 방식으로 날짜나 시간과 같은 순차적인 값에 대해 주로 사용됩니다.
주의 사항
- 파티션 범위 관리: 파티션 범위가 잘못 설정되면 데이터가 잘못 분배될 수 있습니다.
- 자동 파티션 관리: 자동화된 파티션 추가/삭제를 고려하는 것이 좋습니다. (왜냐면 귀찮아요..)
사용 예시

CREATE TABLE sales (
    id serial,
    sale_date DATE NOT NULL,
    amount NUMERIC,
    PRIMARY KEY (id, sale_date)  -- 파티션키를 기본키에 포함
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

CREATE TABLE sales_2025 PARTITION OF sales
    FOR VALUES FROM ('2025-01-01') TO ('2025-12-31');

3.1.2 List Partitioning

리스트 파티셔닝은 특정 값 목록에 따라 데이터를 나누는 방식으로, 주로 지역, 카테고리 등으로 데이터를 분할할 때 사용됩니다.
주의 사항
- 목록 업데이트 관리: 새로운 값이 추가될 때마다 새로운 파티션을 추가해야 합니다.
- 데이터 균형: 리스트 파티셔닝은 특정 값의 데이터가 몰릴 경우 비효율적일 수 있습니다.
사용 예시

CREATE TABLE customer_data (
    id serial,
    region VARCHAR(50),
    name VARCHAR(100),
    PRIMARY KEY (region, id)
) PARTITION BY LIST (region);

CREATE TABLE customer_east PARTITION OF customer_data
    FOR VALUES IN ('East');

CREATE TABLE customer_west PARTITION OF customer_data
    FOR VALUES IN ('West');

3.1.3 Hash Partitioning

해시 파티셔닝은 데이터를 해시 함수로 나누는 방식으로, 데이터를 균등하게 분배할 수 있는 장점이 있습니다.
주의 사항
- 파티션 수와 성능: 해시 파티셔닝은 데이터가 균등하게 분배되지만, 파티션 수가 많아지면 관리가 어려워질 수 있습니다.
사용 예시

CREATE TABLE user_data (
    id serial PRIMARY KEY,
    username VARCHAR(100)
) PARTITION BY HASH (id);

CREATE TABLE user_data_part1 PARTITION OF user_data
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE user_data_part2 PARTITION OF user_data
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE user_data_part3 PARTITION OF user_data
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE user_data_part2 PARTITION OF user_data
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

3.2 복합 파티셔닝

3.2.1 Range-Hash Partitioning

레인지-해시 파티셔닝은 레인지 파티셔닝과 해시 파티셔닝을 결합하여 사용합니다.
예를 들어 날짜 범위로 데이터를 나누고 그 안에서 해시 값을 사용해 더 세분화할 수 있습니다.
사용 예시

CREATE TABLE transaction_data (
    id serial,
    transaction_date DATE NOT NULL,
    customer_id INT NOT NULL,
    PRIMARY KEY (id, transaction_date, customer_id)
) PARTITION BY RANGE (transaction_date); -- transaction_date 기준 레인지 파티셔닝

-- 2025년 레인지-해시
CREATE TABLE transaction_data_2025 PARTITION OF transaction_data
    FOR VALUES FROM ('2025-01-01') TO ('2025-12-31')
    PARTITION BY HASH (customer_id); -- customer_id 기준 해시 파티셔닝

CREATE TABLE transaction_data_2025_part1 PARTITION OF transaction_data_2025
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE transaction_data_2025_part2 PARTITION OF transaction_data_2025
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE transaction_data_2025_part3 PARTITION OF transaction_data_2025
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE transaction_data_2025_part4 PARTITION OF transaction_data_2025
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 2026년 레인지-해시
CREATE TABLE transaction_data_2026 PARTITION OF transaction_data
    FOR VALUES FROM ('2026-01-01') TO ('2026-12-31')
    PARTITION BY HASH (customer_id); -- customer_id 기준 해시 파티셔닝

CREATE TABLE transaction_data_2026_part1 PARTITION OF transaction_data_2026
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE transaction_data_2026_part2 PARTITION OF transaction_data_2026
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE transaction_data_2026_part3 PARTITION OF transaction_data_2026
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE transaction_data_2026_part4 PARTITION OF transaction_data_2026
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

3.2.2 List-Hash Partitioning

리스트-해시 파티셔닝은 리스트를 기준으로 데이터를 나누고 그 안에서 해시 값을 사용해 더 세분화할 수 있습니다.
사용 예시

CREATE TABLE sales_data (
    id serial,
    transaction_date DATE NOT NULL,
    customer_id INT NOT NULL,
    region VARCHAR(50) NOT NULL,
    amount NUMERIC,
    PRIMARY KEY (id, transaction_date, customer_id, region)
) PARTITION BY LIST (region);

CREATE TABLE sales_data_east PARTITION OF sales_data
    FOR VALUES IN ('East')
    PARTITION BY HASH (customer_id);

CREATE TABLE sales_data_east_part1 PARTITION OF sales_data_east
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE sales_data_east_part2 PARTITION OF sales_data_east
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE sales_data_east_part3 PARTITION OF sales_data_east
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE sales_data_east_part4 PARTITION OF sales_data_east
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

3.2.3 List-Range Partitioning

리스트-레인지 파티셔닝은 특정 목록을 기준으로 데이터를 나누고 그 안에서 날짜 범위로 추가적인 파티션을 하는 방식입니다.
사용 예시

CREATE TABLE customer_sales (
    id serial,
    transaction_date DATE NOT NULL,
    customer_id INT NOT NULL,
    region VARCHAR(50) NOT NULL,
    amount NUMERIC,
    PRIMARY KEY (id, transaction_date, region)
) PARTITION BY LIST (region);  -- region을 기준으로 LIST 파티셔닝

CREATE TABLE customer_sales_east PARTITION OF customer_sales
    FOR VALUES IN ('East')  -- region이 East인 데이터만 해당
    PARTITION BY RANGE (transaction_date);  -- transaction_date 기준 RANGE 파티셔닝

-- 2025년 1분기
CREATE TABLE customer_sales_east_2025_q1 PARTITION OF customer_sales_east
    FOR VALUES FROM ('2025-01-01') TO ('2025-03-31');

-- 2025년 2분기
CREATE TABLE customer_sales_east_2025_q2 PARTITION OF customer_sales_east
    FOR VALUES FROM ('2025-04-01') TO ('2025-06-30');

-- 2025년 3분기
CREATE TABLE customer_sales_east_2025_q3 PARTITION OF customer_sales_east
    FOR VALUES FROM ('2025-07-01') TO ('2025-09-30');

-- 2025년 4분기
CREATE TABLE customer_sales_east_2025_q4 PARTITION OF customer_sales_east
    FOR VALUES FROM ('2025-10-01') TO ('2025-12-31');

4. 파티션 관리

네. 파티션 익스체인지는 아래 기능을 활용해서 수동으로 하시면 됩니다.
당연히 교환하려는 파티션의 데이터 형식과 범위는 일치해야 합니다.

4.1 파티션 분리 (DETACH PARTITION)

DETACH PARTITION 명령은 특정 파티션을 테이블에서 분리하는 명령입니다.
이 명령을 사용하면 분리된 파티션은 더 이상 부모 테이블의 일부분이 아니며 독립적인 테이블로 변환됩니다.
사용 예시

-- 테스트용 파티션 생성
CREATE TABLE sales (
    id serial,
    sale_date DATE NOT NULL,
    amount NUMERIC,
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2026 PARTITION OF sales
    FOR VALUES FROM ('2026-01-01') TO ('2026-12-31');

ALTER TABLE sales DETACH PARTITION sales_2026;

4.2 파티션 추가 (ATTACH PARTITION)

ATTACH PARTITION 명령은 기존 파티션을 테이블에 추가하는 명령입니다.
새로운 파티션을 기존 테이블의 파티션 구조에 통합할 수 있습니다.
사용 예시

ALTER TABLE sales ATTACH PARTITION sales_2026 FOR VALUES FROM ('2026-01-01') TO ('2026-12-31');

5. 파티션 테이블 인덱스

5.1 파티션 테이블에서 CREATE INDEX

포인트	설명
자동 재귀	상위 파티션 테이블에 CREATE INDEX ( ONLY 옵션 없이) → 존재·미래 모든 파티션에 동일 정의의 물리 인덱스가 자동 생성됩니다. 부모에는 이를 묶어 주는 파티션 인덱스 메타 오브젝트만 남습니다.
ONLY 사용 시	CREATE INDEX ON ONLY parent … 는 부모에 invalid 인덱스를 걸어 두고, 파티션마다 CREATE INDEX CONCURRENTLY … ; ALTER INDEX parent_idx ATTACH PARTITION … 순으로 붙일 수 있습니다.
글로벌 인덱스 미지원	현재까지는 개별 파티션 수준 인덱스만 지원하며, 파티션 간 범위를 아우르는 글로벌 인덱스는 없습니다.

5.2 ALTER TABLE 구문별 인덱스/락/Re-write 영향

정리 원칙
1. 별도 언급이 없으면 ACCESS EXCLUSIVE 락이 기본입니다.
2. Re-write 필요 여부는 데이터 페이지를 다시 써야 하는가로 구분합니다.

구문	락 수준	테이블/인덱스 Re-wirte	파티션 영향	비고
ADD COLUMNALTER TABLE … ADD COLUMN col type	ACCESS EXCLUSIVE	없음 (카탈로그만 수정) 단, 상수 DEFAULT(PG 11+)도 Re-write 없이 “fast default” 처리	부모에 실행 시 모든 파티션 자동 적용	NULL 허용·상수 DEFAULT만 사용하면 수 ms 내 종료
DROP COLUMN	ACCESS EXCLUSIVE	없음 (컬럼은 attisdropped 표시)	부모에 실행 시 파티션도 삭제	외부 객체 의존성은 CASCADE 필요
RENAME COLUMN/ TABLE	ACCESS EXCLUSIVE	없음	부모→파티션 자동	애플리케이션 코드 동시 배포 필요
SET / DROP DEFAULT	SHARE ROW EXCLUSIVE	없음	부모→파티션	쓰기 트래픽과 공존 가능
SET / DROP NOT NULL	SHARE UPDATE EXCLUSIVE	가능 (NULL 검사 위해 전 테이블 스캔) 단, 유효한 CHECK (column IS NOT NULL) 존재 시 스캔 생략	부모→파티션, 단 파티션만 강화(SET) 가능, 완화(DROP)는 부모에서 수행해야 함
ADD CONSTRAINT UNIQUE / PRIMARY KEY	ACCESS EXCLUSIVE	인덱스 신규 생성 (혹은 USING INDEX)	파티션 테이블은 상위에 직접 PK/UNIQUE 불가 (파티션별 개별 제약 후 ATTACH)	로우수 많으면 CREATE UNIQUE INDEX CONCURRENTLY 후 ADD … USING INDEX 패턴 활용
ALTER COLUMN TYPE (바이너리 호환 × 비호환)	아래 5.3 참조

5.3 컬럼 ALTER TYPE (길이 확대) & 인덱스

(예: varchar(40) → varchar(50))

항목	실제 동작
Re-write 여부	자료형이 동일하고 typmod(길이 제한)만 변경 ⇒ 테이블·인덱스 Re-write 없음, 카탈로그만 갱신
락	여전히 ACCESS EXCLUSIVE → 쓰기·DDL 전면 대기. (테이블에 트랜잭션이 있거나 들어올 경우 블로킹)
파티션 영향	부모 테이블에 실행 시 파티션 전부 카탈로그 수정
인덱스 영향	컬럼이 인덱스 앞부분이라도 바이너리 동일 → 인덱스 재빌드 생략

+ 운영 시 무중단 파티션 테이블 DDL 전략

전략	설명	비고
파티션 롤링 변경	파티션 단위 DETACH → ALTER → ATTACH	서비스 영향 최소, 스크립트 필요
새 컬럼 스왑	ADD COLUMN new_col … → 코드 전환 후 구 컬럼 삭제	코드 수정 부담 있지만 완전 무중단
ALTER … USING INDEX	미리 CONCURRENTLY 로 인덱스 생성 후 Constraints attach	Online 고유성 확보 가능

현재 세션에서 lock_timeout를 임시로 타이트하게 변경해서 장기 블로킹 예방 (예: SET [LOCAL] lock_timeout = '5s')

파티션을 사용하는 가장 큰 이유 중 하나는 파티션 프루닝(Partition Pruning)입니다.

파티션 프루닝은 쿼리에서 필요한 데이터만 특정 파티션에서 조회하여 성능을 최적화하는 기법입니다.
데이터가 고르게 분포되어 있고 파티션의 수가 적당할 경우 쿼리에서 특정 파티션만을 스캔하여 빠르게 결과를 도출할 수 있습니다.

하지만 너무 많은 파티션이 존재하거나 설계 상 문제가 있어 데이터가 고르게 분포되지 않는 경우에는 파티션 프루닝의 효율성이 떨어질 수 있습니다.
그로 인해 실행 계획이 복잡해지고 최적화되지 않은 쿼리가 실행될 가능성이 커집니다. 이러한 상황에서는 오히려 성능이 저하될 수 있습니다.

따라서 파티션의 수와 설계는 매우 중요합니다. 지나치게 많은 파티션을 만들지 않도록 하며 각 파티션에 데이터가 균등하게 분포되도록 관리해야 합니다.

추가로 파티션 갯수가 많을수록 관리의 복잡성이 증가하기 때문에 효율적인 파티셔닝 전략이 필요합니다.

오늘은 여기까지~

728x90

저작자표시 비영리 변경금지 (새창열림)

'PostgreSQL' 카테고리의 다른 글

PostgreSQL: Logical Replication (0)	2025.07.02
PostgreSQL: io_combine_limit (0)	2025.07.01
PostgreSQL: Lock (0)	2025.06.24
PostgreSQL: credcheck (0)	2025.06.17
PostgreSQL: Disk I/O 및 WAL 흐름 (0)	2025.06.11

현재글PostgreSQL: Partition

DewStream

📧: dewstream.kr@gmail.com

250x250

고가용성, 일상, Parameter, 리눅스, life, HA, SQL, 파라미터, essay, 최적화, tuning, 에세이, PostgreSQL, linux, Optimization, 포스트그레스, 튜닝, 일기, PG, Query,

Today :
Yesterday :

DewStream