PostgreSQL 파티셔닝과 성능 최적화 간단히 실습

PostgreSQL에서 대규모 데이터를 효율적으로 관리하고 쿼리 성능을 최적화하기 위해 파티셔닝을 활용하는 방법을 단계별로 테이블을 생성하고 데이터를 삽입하며 성능 비교를 수행한다.

 

초기 설정 및 데이터 생성

먼저 practice_org라는 테이블을 생성하고 여기에 랜덤 데이터를 삽입한다. 그런 다음 인덱스를 생성한다.

-- PostgreSQL에 접속
psql -U postgres

-- 테이블 생성
CREATE TABLE practice_org (
    id serial NOT NULL,
    g int NOT NULL
);

-- 랜덤 데이터 삽입
INSERT INTO practice_org(g)
SELECT floor(random() * 100)
FROM generate_series(0, 10000000);

-- 인덱스 생성
CREATE INDEX practice_org_index ON practice_org(g);

-- 테이블 구조 확인
\d practice_org;

-- 특정 값의 데이터 카운트 조회
SELECT COUNT(*) FROM practice_org WHERE g = 30;

-- 쿼리 성능 분석
EXPLAIN ANALYZE SELECT COUNT(*) FROM practice_org WHERE g = 30;
EXPLAIN ANALYZE SELECT COUNT(*) FROM practice_org WHERE g BETWEEN 30 AND 35;

 

파티셔닝 된 테이블 생성

practice_parts라는 파티셔닝 된 테이블을 생성하고 이를 여러 파티션으로 나눈다.

-- 파티셔닝된 테이블 생성
CREATE TABLE practice_parts (
    id serial NOT NULL,
    g int NOT NULL
) PARTITION BY RANGE(g);

-- 파티션 테이블 생성
-- LIKE 절과 INCLUDING INDEXES 옵션을 사용하여 기존 테이블 구조와 인덱스를 동일하게 복사
CREATE TABLE g0035 (LIKE practice_parts INCLUDING INDEXES);
CREATE TABLE g3560 (LIKE practice_parts INCLUDING INDEXES);
CREATE TABLE g6080 (LIKE practice_parts INCLUDING INDEXES);
CREATE TABLE g80100 (LIKE practice_parts INCLUDING INDEXES);

-- 생성된 파티션 테이블을 특정 값의 범위에 맞게 메인 파티셔닝 테이블에 연결
ALTER TABLE practice_parts ATTACH PARTITION g0035 FOR VALUES FROM (0) TO (35);
ALTER TABLE practice_parts ATTACH PARTITION g3560 FOR VALUES FROM (35) TO (60);
ALTER TABLE practice_parts ATTACH PARTITION g6080 FOR VALUES FROM (60) TO (80);
ALTER TABLE practice_parts ATTACH PARTITION g80100 FOR VALUES FROM (80) TO (100);

-- 테이블 구조 확인
\d practice_parts;
\d g0035;
\d g3560;
\d g6080;
\d g80100;

파티션을 추가하기 전과 추가한 후의 Number 숫자가 달라진 것을 볼 수 있다.

 

파티션 테이블 변화 모습 확인

 

데이터 이동 및 인덱스 생성

기존 테이블의 데이터를 파티셔닝 된 테이블로 이동하고 테이블에 인덱스를 생성한다.

-- 데이터 이동
INSERT INTO practice_parts SELECT * FROM practice_org;

-- 데이터 카운트 및 최대값 조회
SELECT COUNT(*) FROM practice_parts;
SELECT MAX(g) FROM practice_parts;

-- 파티션이 되어서 값이 찍히는것을 볼 수 있다.
SELECT COUNT(*) FROM g0035;
SELECT MAX(g) FROM g0035;
SELECT COUNT(*) FROM g3560;
SELECT MAX(g) FROM g3560;

-- 인덱스 생성
CREATE INDEX practice_parts_idx ON practice_parts(g);

-- 파티셔닝된 테이블 및 파티션 인덱스 확인
\d practice_parts;
\d g0035;

g열에 인덱스가 추가된 것을 확인할 수 있다.

 

각 파티션 테이블에도 인덱스가 추가된 것을 확인할 수 있다.

 

성능 비교 및 최적화

파티셔닝 된 테이블과 기존 테이블의 쿼리 성능을 비교하고 파티션 프루닝 기능을 테스트를 통해 데이터 접근을 확인한다.

-- 파티셔닝된 테이블 쿼리 성능 분석
EXPLAIN ANALYZE SELECT COUNT(*) FROM practice_parts WHERE g = 30;

-- 기존 테이블 쿼리 성능 분석
EXPLAIN ANALYZE SELECT COUNT(*) FROM practice_org WHERE g = 30;

비교해 보았을 때 g = 30인경우 g0035의 파티션을 사용하는 것을 볼 수 있다.

-- 테이블 및 인덱스 크기 확인
SELECT pg_relation_size(oid), relname
FROM pg_class
ORDER BY pg_relation_size(oid) DESC;

 

파티셔닝 된 테이블은 데이터 양이 많은 경우에도 특정 범위의 데이터에 대해 빠르게 접근할 수 있어 성능이 향상된다.

특히 인덱스 크기가 작아지므로 데이터 선택 과정에서 성능이 개선된다.

기존 테이블로 파티셔닝 되지 않은 원본 데이터를 포함

practice_org의 크기: 약 345MB

practice_org_index의 크기: 약 66MB

 

practice_parts의 첫 번째 파티션으로 g 값이 0에서 35 사이인 데이터를 포함

g0035의 크기: 약 120MB

g0035_g_idx: 약 24MB

이처럼 각 파티션의 인덱스 크기가 작아져 쿼리 성능이 향상된다.

-- 파티션 프루닝 설정 확인
SHOW ENABLE_PARTITION_PRUNING;

-- 파티션 프루닝 비활성화
SET enable_partition_pruning = off;
EXPLAIN SELECT COUNT(*) FROM practice_parts WHERE g = 30;

-- 파티션 프루닝 활성화
SET enable_partition_pruning = on;
EXPLAIN SELECT COUNT(*) FROM practice_parts WHERE g = 30;

파티션 프루닝 활성화

파티션 프루닝 비활성화

비활성화 시 모든 파티션을 검색한다. 쿼리 조건과 상관없이 모든 파티션을 읽어야 하므로 성능이 저하된다.

그래서 파티션 프루닝 기능 활성화를 통해 불필요한 파티션을 제거하고 필요한 데이터만을 빠르게 조회할 수 있다.

 

결론

PostgreSQL의 파티셔닝 기능을 활용하면 대규모 데이터를 효율적으로 관리하고 쿼리 성능을 최적화할 수 있다.

파티션 프루닝 기능을 통해 불필요한 파티션을 제거하고 필요한 데이터만을 빠르게 조회할 수 있다.