공부/DATABASE
[PostgreSQL] 파티션 테이블
무는빼주세요
2025. 3. 29. 15:06
개요
postgresql 파티션 테이블 기능 테스트를 진행하였습니다.
postgresql 파티션 테이블 용어 확인 및 정리
파티션 테이블 -> 파티션으로 구성된 테이블 (The partitioned table itself is a “virtual” table having no storage of its own)
파티션 -> 파티션 테이블의 하위 데이터 저장되는 영역 (Instead, the storage belongs to partitions)
특징
선언적 파티셔닝과 상속을 통한 파티셔닝 기법 2가지가 존재합니다.
일반적으로 선언적 파티셔닝은 다른 DBMS와 비슷한 파티셔닝 장단점을 가지고 있다고 생각이 듭니다.
다만 POSTGRESQL 에서 파티셔닝을 사용하였을 때 추가적인 장점은 VACUUM 관련입니다.
Partition 을 삭제하는 과정에서 일반 테이블의 DELETE 작업에 비해 VACUUM 오버헤드를 피할 수 있습니다.
기능
LIST, RANGE, HASH 파티셔닝을 구성할 수 있습니다.
DEFAULT 파티셔닝을 지원하여 조건에 맞지 않는 값들에 대해 처리 가능합니다. (CREATE TABLE sales_default PARTITION OF sales DEFAULT;)
전체 파티션 인덱스 생성
- PostgreSQL 14부터는 파티션 테이블에 인덱스를 정의하면 하위 파티션에도 자동으로 인덱스가 생성됩니다.
제약
파티션 테이블의 Primary Key, UNIQUE 제약 조건을 설정할 때 반드시 파티션 키를 포함해야 합니다.
범위 분할은 NULL 값을 허용하지 않습니다.
CHECK 제약 조건은 개별 파티션에만 적용되며, 파티션 테이블에는 설정할 수 없습니다.
일반 테이블에서 파티션 테이블 전환
A VIEW 를 사용하여 처리 https://www.prefect.io/blog/database-partitioning-prod-postgres-without-downtime
- Postgres에서 원래 테이블의 새 파티션 테이블 복사본을 만듭니다.
- 새 테이블과 이전 테이블을 통합하는 VIEW를 만듭니다. INSERTS는 새 테이블에 들어가고 UPDATES와 DELETES는 두 테이블에 모두 들어갑니다. SELECTS는 두 테이블의 합집합을 반환합니다.
- Postgres에서 이전 테이블과 새 테이블 간에 데이터를 원자적으로 복사하여 행이 한 테이블 또는 다른 테이블에만 존재하고 두 테이블 모두에는 존재하지 않도록 합니다.
- 모든 데이터가 복사된 후 VIEW를 새 파티션 테이블로 바꿉니다.
B 어플리케이션에서 2중 쓰기를 하여 데이터 복사
- 파티션 테이블 만들기 : 먼저 파티션 테이블을 만듭니다.
- 데이터 백필 : COPY 방법을 사용하여 이전 테이블에서 분할된 테이블로 데이터를 전송합니다. PostgreSQL 에 따르면 이 방법은 가장 빠른 대량 데이터 로딩을 제공합니다.
- 이중 쓰기 메커니즘 활성화 : 파티션 테이블과 비파티션 테이블 모두에 대해 이중 쓰기 메커니즘을 활성화합니다.
- 테이블 간 데이터 동기화 :
- 테이블 간 데이터 동기화 프로세스는 각 테이블의 특정 사용 사례에 따라 다릅니다. 이 프로세스는 앱 계층에서 관리되므로 관리성과 유연성이 더 뛰어납니다.
- 트래픽 모니터링 : 전환 단계 동안 트래픽을 면밀히 모니터링합니다. 프로세스가 이전 설정과 유사한 파티션 테이블에서 잘 수행되면 작업 부하의 100%를 파티션 테이블에 롤아웃합니다.
파티션 테이블 인덱스 온라인 생성 방법
--파티션 테이블에 자동으로 인덱스를 추가할 때는 CONCURRENTLY 명령어를 사용할 수가 없다.
--이를 회피하기 위해 postgreSQL 공식 문서에서 아래와 같은 방법으로 진행하도록 가이드를 한다
CREATE INDEX idx_users_email ON ONLY users (email); --ONLY 옵션을 사용할 경우 파티셔닝 테이블에서 하위 파티션에 인덱스를 생성하지 않습니다
CREATE INDEX CONCURRENTLY idx_users_email_1
ON users_1 (email);
ALTER INDEX idx_users_email
ATTACH PARTITION idx_users_email_1;
CREATE INDEX CONCURRENTLY idx_users_email_2
ON users_2 (email);
ALTER INDEX idx_users_email
ATTACH PARTITION idx_users_email_2;
// repeat for all partitions
default 설정의 파티션이 있을 때 스플릿, 파티션 변경 등의 작업에 영향도 확인
--> Default에 들어가 있는 값 범위의 파티션 RANGE는 생성할 수 없습니다
CREATE TABLE minjae_partition_default (LIKE minjae_partition_table including all);
ALTER TABLE minjae_partition_table ATTACH PARTITION minjae_partition1 for values from ('2024-10-31') to ('2024-11-30');
ALTER TABLE minjae_partition_table ATTACH PARTITION minjae_partition2 for values from ('2024-11-30') to ('2024-12-30');
ALTER TABLE minjae_partition_table ATTACH PARTITION minjae_partition3 for values from ('2024-12-31') to ('2025-01-30');
ALTER TABLE minjae_partition_table ATTACH PARTITION minjae_partition_default DEFAULT;
--2025 2월 데이터는 default 파티션에 입력되어 있는 상황
ALTER TABLE minjae_partition_table ATTACH PARTITION minjae_partition4 for values from ('2025-01-31') to ('2025-02-28');
--이때 2월 파티션을 추가하면 에러가 발생함
minjae_test=> ALTER TABLE minjae_partition_table ATTACH PARTITION minjae_partition4 for values from ('2025-01-31') to ('2025-02-28');
ERROR: updated partition constraint for default partition "minjae_partition_default" would be violated by some row
MAX, MINVALUE 기능
--> Default과 마찬가지로 데이터가 들어가 있는 경우 해당 값이 들어가 있는 파티션 RANGE는 생성할 수 없습니다
ALTER TABLE minjae_partition_table DETACH PARTITION minjae_partition_default;
ALTER TABLE minjae_partition_table ATTACH PARTITION minjae_partition4 for values from ('2025-01-31') to (MAXVALUE);
minjae_test=> insert into public.minjae_partition_table (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_04;
schema_name | parent_table | partition_table | partition_condition | reltuples
-------------+------------------------+--------------------+--------------------------------------------------------------------+-----------
public | minjae_partition_table | minjae_partition1 | FOR VALUES FROM ('2024-10-31 00:00:00') TO ('2024-11-30 00:00:00') | 87
public | minjae_partition_table | minjae_partition2 | FOR VALUES FROM ('2024-11-30 00:00:00') TO ('2024-12-30 00:00:00') | 29
public | minjae_partition_table | minjae_partition3 | FOR VALUES FROM ('2024-12-31 00:00:00') TO ('2025-01-30 00:00:00') | 29
public | minjae_partition_table | minjae_partition4 | FOR VALUES FROM ('2025-01-31 00:00:00') TO (MAXVALUE) | 57
--maxvalue에는 2월 3월 데이터가 입력되어 있음
ALTER TABLE minjae_partition_table ATTACH PARTITION minjae_partition5 for values from ('2025-02-28') to ('2025-03-31');
minjae_test=> ALTER TABLE minjae_partition_table ATTACH PARTITION minjae_partition5 for values from ('2025-02-28') to ('2025-03-31');
ERROR: partition "minjae_partition5" would overlap partition "minjae_partition4"
LINE 1: ...TACH PARTITION minjae_partition5 for values from ('2025-02-2...
기존 파티션 RANGE 범위 수정 영향도 확인
--> 기능이 없는 것으로 보입니다. 파티션을 수정하기 위해선 DETACH와 ATTACH 작업으로 진행이 필요합니다.
일단위, 주단위의 동일 사이즈 파티션 테이블에 대하여 부하 차이 확인
| 일 단위 | 주 단위 | |
| INSERT | CPU 70% TPS 약 970 | CPU 약 67% TPS 약 1160 |
| SELECT | CPU 약 89% TPS 약 4,100 | CPU 75 % TPS 약 10,600 |
CREATE TABLE minjae_partition_table_day(
seq bigserial,
col1 int,
col2 int,
created_at timestamp,
constraint minjae_table_day_11_pk primary key (seq, created_at)
) PARTITION BY RANGE (created_at);
create index minjae_table_day_11_nc_col1 on minjae_partition_table_day (col1);
CREATE TABLE minjae_partition_day_0101 PARTITION OF minjae_partition_table_day for values from ('2025-01-01') to ('2025-01-02');
CREATE TABLE minjae_partition_day_0102 PARTITION OF minjae_partition_table_day for values from ('2025-01-02') to ('2025-01-03');
CREATE TABLE minjae_partition_day_0103 PARTITION OF minjae_partition_table_day for values from ('2025-01-03') to ('2025-01-04');
CREATE TABLE minjae_partition_day_0104 PARTITION OF minjae_partition_table_day for values from ('2025-01-04') to ('2025-01-05');
CREATE TABLE minjae_partition_day_0105 PARTITION OF minjae_partition_table_day for values from ('2025-01-05') to ('2025-01-06');
CREATE TABLE minjae_partition_day_0106 PARTITION OF minjae_partition_table_day for values from ('2025-01-06') to ('2025-01-07');
CREATE TABLE minjae_partition_day_0107 PARTITION OF minjae_partition_table_day for values from ('2025-01-07') to ('2025-01-08');
CREATE TABLE minjae_partition_day_0108 PARTITION OF minjae_partition_table_day for values from ('2025-01-08') to ('2025-01-09');
CREATE TABLE minjae_partition_day_0109 PARTITION OF minjae_partition_table_day for values from ('2025-01-09') to ('2025-01-10');
CREATE TABLE minjae_partition_day_0110 PARTITION OF minjae_partition_table_day for values from ('2025-01-10') to ('2025-01-11');
CREATE TABLE minjae_partition_day_0111 PARTITION OF minjae_partition_table_day for values from ('2025-01-11') to ('2025-01-12');
CREATE TABLE minjae_partition_day_0112 PARTITION OF minjae_partition_table_day for values from ('2025-01-12') to ('2025-01-13');
CREATE TABLE minjae_partition_day_0113 PARTITION OF minjae_partition_table_day for values from ('2025-01-13') to ('2025-01-14');
CREATE TABLE minjae_partition_day_0114 PARTITION OF minjae_partition_table_day for values from ('2025-01-14') to ('2025-01-15');
CREATE TABLE minjae_partition_day_0115 PARTITION OF minjae_partition_table_day for values from ('2025-01-15') to ('2025-01-16');
CREATE TABLE minjae_partition_day_0116 PARTITION OF minjae_partition_table_day for values from ('2025-01-16') to ('2025-01-17');
CREATE TABLE minjae_partition_day_0117 PARTITION OF minjae_partition_table_day for values from ('2025-01-17') to ('2025-01-18');
CREATE TABLE minjae_partition_day_0118 PARTITION OF minjae_partition_table_day for values from ('2025-01-18') to ('2025-01-19');
CREATE TABLE minjae_partition_day_0119 PARTITION OF minjae_partition_table_day for values from ('2025-01-19') to ('2025-01-21');
CREATE TABLE minjae_partition_day_0121 PARTITION OF minjae_partition_table_day for values from ('2025-01-21') to ('2025-01-22');
CREATE TABLE minjae_partition_day_0122 PARTITION OF minjae_partition_table_day for values from ('2025-01-22') to ('2025-01-23');
CREATE TABLE minjae_partition_day_0123 PARTITION OF minjae_partition_table_day for values from ('2025-01-23') to ('2025-01-24');
CREATE TABLE minjae_partition_day_0124 PARTITION OF minjae_partition_table_day for values from ('2025-01-24') to ('2025-01-25');
CREATE TABLE minjae_partition_day_0125 PARTITION OF minjae_partition_table_day for values from ('2025-01-25') to ('2025-01-26');
CREATE TABLE minjae_partition_day_0126 PARTITION OF minjae_partition_table_day for values from ('2025-01-26') to ('2025-01-27');
CREATE TABLE minjae_partition_day_0127 PARTITION OF minjae_partition_table_day for values from ('2025-01-27') to ('2025-01-28');
CREATE TABLE minjae_partition_day_0128 PARTITION OF minjae_partition_table_day for values from ('2025-01-28') to ('2025-01-29');
CREATE TABLE minjae_partition_day_0129 PARTITION OF minjae_partition_table_day for values from ('2025-01-29') to ('2025-01-30');
CREATE TABLE minjae_partition_day_0201 PARTITION OF minjae_partition_table_day for values from ('2025-02-01') to ('2025-02-02');
CREATE TABLE minjae_partition_day_0202 PARTITION OF minjae_partition_table_day for values from ('2025-02-02') to ('2025-02-03');
CREATE TABLE minjae_partition_day_0203 PARTITION OF minjae_partition_table_day for values from ('2025-02-03') to ('2025-02-04');
CREATE TABLE minjae_partition_day_0204 PARTITION OF minjae_partition_table_day for values from ('2025-02-04') to ('2025-02-05');
CREATE TABLE minjae_partition_day_0205 PARTITION OF minjae_partition_table_day for values from ('2025-02-05') to ('2025-02-06');
CREATE TABLE minjae_partition_day_0206 PARTITION OF minjae_partition_table_day for values from ('2025-02-06') to ('2025-02-07');
CREATE TABLE minjae_partition_day_0207 PARTITION OF minjae_partition_table_day for values from ('2025-02-07') to ('2025-02-08');
CREATE TABLE minjae_partition_day_0208 PARTITION OF minjae_partition_table_day for values from ('2025-02-08') to ('2025-02-09');
CREATE TABLE minjae_partition_day_0209 PARTITION OF minjae_partition_table_day for values from ('2025-02-09') to ('2025-02-10');
CREATE TABLE minjae_partition_day_0210 PARTITION OF minjae_partition_table_day for values from ('2025-02-10') to ('2025-02-11');
CREATE TABLE minjae_partition_day_0211 PARTITION OF minjae_partition_table_day for values from ('2025-02-11') to ('2025-02-12');
CREATE TABLE minjae_partition_day_0212 PARTITION OF minjae_partition_table_day for values from ('2025-02-12') to ('2025-02-13');
CREATE TABLE minjae_partition_day_0213 PARTITION OF minjae_partition_table_day for values from ('2025-02-13') to ('2025-02-14');
CREATE TABLE minjae_partition_day_0214 PARTITION OF minjae_partition_table_day for values from ('2025-02-14') to ('2025-02-15');
CREATE TABLE minjae_partition_day_0215 PARTITION OF minjae_partition_table_day for values from ('2025-02-15') to ('2025-02-16');
CREATE TABLE minjae_partition_day_0216 PARTITION OF minjae_partition_table_day for values from ('2025-02-16') to ('2025-02-17');
CREATE TABLE minjae_partition_day_0217 PARTITION OF minjae_partition_table_day for values from ('2025-02-17') to ('2025-02-18');
CREATE TABLE minjae_partition_day_0218 PARTITION OF minjae_partition_table_day for values from ('2025-02-18') to ('2025-02-19');
CREATE TABLE minjae_partition_day_0219 PARTITION OF minjae_partition_table_day for values from ('2025-02-19') to ('2025-02-21');
CREATE TABLE minjae_partition_day_0221 PARTITION OF minjae_partition_table_day for values from ('2025-02-21') to ('2025-02-22');
CREATE TABLE minjae_partition_day_0222 PARTITION OF minjae_partition_table_day for values from ('2025-02-22') to ('2025-02-23');
CREATE TABLE minjae_partition_day_0223 PARTITION OF minjae_partition_table_day for values from ('2025-02-23') to ('2025-02-24');
CREATE TABLE minjae_partition_day_0224 PARTITION OF minjae_partition_table_day for values from ('2025-02-24') to ('2025-02-25');
CREATE TABLE minjae_partition_day_0225 PARTITION OF minjae_partition_table_day for values from ('2025-02-25') to ('2025-02-26');
CREATE TABLE minjae_partition_day_0226 PARTITION OF minjae_partition_table_day for values from ('2025-02-26') to ('2025-02-27');
CREATE TABLE minjae_partition_day_0227 PARTITION OF minjae_partition_table_day for values from ('2025-02-27') to ('2025-02-28');
CREATE TABLE minjae_partition_day_0301 PARTITION OF minjae_partition_table_day for values from ('2025-03-01') to ('2025-03-02');
CREATE TABLE minjae_partition_day_0302 PARTITION OF minjae_partition_table_day for values from ('2025-03-02') to ('2025-03-03');
CREATE TABLE minjae_partition_day_0303 PARTITION OF minjae_partition_table_day for values from ('2025-03-03') to ('2025-03-04');
CREATE TABLE minjae_partition_day_0304 PARTITION OF minjae_partition_table_day for values from ('2025-03-04') to ('2025-03-05');
CREATE TABLE minjae_partition_day_0305 PARTITION OF minjae_partition_table_day for values from ('2025-03-05') to ('2025-03-06');
CREATE TABLE minjae_partition_day_0306 PARTITION OF minjae_partition_table_day for values from ('2025-03-06') to ('2025-03-07');
CREATE TABLE minjae_partition_day_0307 PARTITION OF minjae_partition_table_day for values from ('2025-03-07') to ('2025-03-08');
CREATE TABLE minjae_partition_day_0308 PARTITION OF minjae_partition_table_day for values from ('2025-03-08') to ('2025-03-09');
CREATE TABLE minjae_partition_day_0309 PARTITION OF minjae_partition_table_day for values from ('2025-03-09') to ('2025-03-10');
CREATE TABLE minjae_partition_day_0310 PARTITION OF minjae_partition_table_day for values from ('2025-03-10') to ('2025-03-11');
CREATE TABLE minjae_partition_day_0311 PARTITION OF minjae_partition_table_day for values from ('2025-03-11') to ('2025-03-12');
CREATE TABLE minjae_partition_day_0312 PARTITION OF minjae_partition_table_day for values from ('2025-03-12') to ('2025-03-13');
CREATE TABLE minjae_partition_day_0313 PARTITION OF minjae_partition_table_day for values from ('2025-03-13') to ('2025-03-14');
CREATE TABLE minjae_partition_day_0314 PARTITION OF minjae_partition_table_day for values from ('2025-03-14') to ('2025-03-15');
CREATE TABLE minjae_partition_day_0315 PARTITION OF minjae_partition_table_day for values from ('2025-03-15') to ('2025-03-16');
CREATE TABLE minjae_partition_day_0316 PARTITION OF minjae_partition_table_day for values from ('2025-03-16') to ('2025-03-17');
CREATE TABLE minjae_partition_day_0317 PARTITION OF minjae_partition_table_day for values from ('2025-03-17') to ('2025-03-18');
CREATE TABLE minjae_partition_day_0318 PARTITION OF minjae_partition_table_day for values from ('2025-03-18') to ('2025-03-19');
CREATE TABLE minjae_partition_day_0319 PARTITION OF minjae_partition_table_day for values from ('2025-03-19') to ('2025-03-21');
CREATE TABLE minjae_partition_day_0321 PARTITION OF minjae_partition_table_day for values from ('2025-03-21') to ('2025-03-22');
CREATE TABLE minjae_partition_day_0322 PARTITION OF minjae_partition_table_day for values from ('2025-03-22') to ('2025-03-23');
CREATE TABLE minjae_partition_day_0323 PARTITION OF minjae_partition_table_day for values from ('2025-03-23') to ('2025-03-24');
CREATE TABLE minjae_partition_day_0324 PARTITION OF minjae_partition_table_day for values from ('2025-03-24') to ('2025-03-25');
CREATE TABLE minjae_partition_day_0325 PARTITION OF minjae_partition_table_day for values from ('2025-03-25') to ('2025-03-26');
CREATE TABLE minjae_partition_day_0326 PARTITION OF minjae_partition_table_day for values from ('2025-03-26') to ('2025-03-27');
CREATE TABLE minjae_partition_day_0327 PARTITION OF minjae_partition_table_day for values from ('2025-03-27') to ('2025-03-28');
CREATE TABLE minjae_partition_day_0328 PARTITION OF minjae_partition_table_day for values from ('2025-03-28') to ('2025-03-29');
CREATE TABLE minjae_partition_day_0329 PARTITION OF minjae_partition_table_day for values from ('2025-03-29') to ('2025-03-30');
CREATE TABLE minjae_partition_table_week(
seq bigserial,
col1 int,
col2 int,
created_at timestamp,
constraint minjae_table_week_11_pk primary key (seq, created_at)
) PARTITION BY RANGE (created_at);
create index minjae_test_week_11_nc_col1 on minjae_partition_table_week (col1);
CREATE TABLE minjae_partition_week_0101 PARTITION OF minjae_partition_table_week for values from ('2025-01-01') to ('2025-01-08');
CREATE TABLE minjae_partition_week_0102 PARTITION OF minjae_partition_table_week for values from ('2025-01-08') to ('2025-01-15');
CREATE TABLE minjae_partition_week_0103 PARTITION OF minjae_partition_table_week for values from ('2025-01-15') to ('2025-01-22');
CREATE TABLE minjae_partition_week_0104 PARTITION OF minjae_partition_table_week for values from ('2025-01-22') to ('2025-01-30');
CREATE TABLE minjae_partition_week_0201 PARTITION OF minjae_partition_table_week for values from ('2025-02-01') to ('2025-02-08');
CREATE TABLE minjae_partition_week_0202 PARTITION OF minjae_partition_table_week for values from ('2025-02-08') to ('2025-02-15');
CREATE TABLE minjae_partition_week_0203 PARTITION OF minjae_partition_table_week for values from ('2025-02-15') to ('2025-02-22');
CREATE TABLE minjae_partition_week_0204 PARTITION OF minjae_partition_table_week for values from ('2025-02-22') to ('2025-02-28');
CREATE TABLE minjae_partition_week_0301 PARTITION OF minjae_partition_table_week for values from ('2025-03-01') to ('2025-03-08');
CREATE TABLE minjae_partition_week_0302 PARTITION OF minjae_partition_table_week for values from ('2025-03-08') to ('2025-03-15');
CREATE TABLE minjae_partition_week_0303 PARTITION OF minjae_partition_table_week for values from ('2025-03-15') to ('2025-03-22');
CREATE TABLE minjae_partition_week_0304 PARTITION OF minjae_partition_table_week for values from ('2025-03-22') to ('2025-03-30');
insert into public.minjae_partition_table_day (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_01;
insert into public.minjae_partition_table_day (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_04;
insert into public.minjae_partition_table_day (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_05;
insert into public.minjae_partition_table_week (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_01;
insert into public.minjae_partition_table_week (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_04;
insert into public.minjae_partition_table_week (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_05;
CREATE TABLE minjae_partition_week_0201 PARTITION OF minjae_partition_table_week for values from ('2025-02-01') to ('2025-02-08');
CREATE TABLE minjae_partition_week_0202 PARTITION OF minjae_partition_table_week for values from ('2025-02-08') to ('2025-02-15');
CREATE TABLE minjae_partition_week_0203 PARTITION OF minjae_partition_table_week for values from ('2025-02-15') to ('2025-02-22');
CREATE TABLE minjae_partition_week_0204 PARTITION OF minjae_partition_table_week for values from ('2025-02-22') to ('2025-02-28');
--r6g.xlarge
--2025-03-24-15:20 start
pgbench minjae_test -h HOST -p 5432 -U DBA -c 2 -j 2 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
insert into public.minjae_partition_table_day (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_01;
\q
pgbench minjae_test -h HOST -p 5432 -U DBA -c 2 -j 2 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
insert into public.minjae_partition_table_day (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_04;
\q
pgbench minjae_test -h HOST -p 5432 -U DBA -c 2 -j 2 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
insert into public.minjae_partition_table_day (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_05;
\q
--2025-03-24-15:35 end (cpu 약 70% tps 약 970)
--2025-03-24-15:50 start
pgbench minjae_test -h HOST -p 5432 -U DBA -c 2 -j 1 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
insert into public.minjae_partition_table_week (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_01;
\q
pgbench minjae_test -h HOST -p 5432 -U DBA -c 2 -j 1 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
insert into public.minjae_partition_table_week (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_04;
\q
pgbench minjae_test -h HOST -p 5432 -U DBA -c 2 -j 1 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
insert into public.minjae_partition_table_week (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_05;
\q
--2025-03-24-16:05 end (cpu 약 67% tps 약 1160)
--81건
SELECT count(*) FROM minjae_partition_table_week WHERE created_at >= '2025-02-01 00:00:00' and created_at < '2025-03-01 00:00:00'; (4개 파티션)
--81건
SELECT count(*) FROM minjae_partition_table_day WHERE created_at >= '2025-02-01 00:00:00' and created_at < '2025-03-01 00:00:00'; (30개 파티션)
--2025-03-24-16:30 start
pgbench minjae_test -h HOST -p 5432 -U DBA -c 4 -j 4 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
SELECT * FROM minjae_partition_table_week WHERE created_at >= '2025-02-01 00:00:00' and created_at < '2025-03-01 00:00:00';
\q
--2025-03-24-16:45 end (cpu 약 75% tps 약 10,600)
--2025-03-24-17:00 start
pgbench minjae_test -h HOST -p 5432 -U DBA -c 4 -j 4 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
SELECT * FROM minjae_partition_table_day WHERE created_at >= '2025-02-01 00:00:00' and created_at < '2025-03-01 00:00:00';
\q
--2025-03-24-17:15 end (cpu 약 89% tps 약 4,100)
PostgreSQL 에서 timestamp 일 때 소수점이 어느 정도 넘어가면 다음 파티션으로 생기는지 확인 필요
--> 23:59:59.9999995 부터 다음 날짜로 데이터가 생성 됩니다
create table partition_date_test (
col1 bigserial
, col2 timestamp,
constraint partition_date_test_pk primary key (col1, col2)
)PARTITION BY RANGE (col2) ;
truncate table partition_date_test;
TRUNCATE TABLE
insert into partition_date_test (col2) values ('2024-12-31 23:59:59.9999994');
analyze partition_date_test;
INSERT 0 1
ANALYZE
schema_name | parent_table | partition_table | partition_condition | reltuples
-------------+---------------------+-----------------------+--------------------------------------------------------------------+-----------
public | partition_date_test | partition_date_test_1 | FOR VALUES FROM ('2024-12-31 00:00:00') TO ('2025-01-01 00:00:00') | 1
public | partition_date_test | partition_date_test_2 | FOR VALUES FROM ('2025-01-01 00:00:00') TO ('2025-01-02 00:00:00') | 0
insert into partition_date_test (col2) values ('2024-12-31 23:59:59.9999995');
INSERT 0 1
analyze partition_date_test;
ANALYZE
schema_name | parent_table | partition_table | partition_condition | reltuples
-------------+---------------------+-----------------------+--------------------------------------------------------------------+-----------
public | partition_date_test | partition_date_test_1 | FOR VALUES FROM ('2024-12-31 00:00:00') TO ('2025-01-01 00:00:00') | 1
public | partition_date_test | partition_date_test_2 | FOR VALUES FROM ('2025-01-01 00:00:00') TO ('2025-01-02 00:00:00') | 1
select * from partition_date_test;
col1 | col2
------+----------------------------
18 | 2024-12-31 23:59:59.999999
19 | 2025-01-01 00:00:00
각 작업의 영향도 요약
| 작업 | 명령어 (RANGE 기준) | 영향도 | 비고 |
| 파티션 추가 | ALTER TABLE '테이블명' ATTACH PARTITION '파티션명' FOR VALUES FROM () TO () | SHARE UPDATE EXCLUSIVE (DML 쿼리의 경우 락 없이 처리) |
CREATE TABLE 파티션명 (LIKE 테이블명 including all, check (파티션범위)); ALTER TABLE '테이블명' ATTACH PARTITION '파티션명' FOR VALUES FROM () TO () alter table 파티션명 drop constraint if exists 제약조건 위 순서로 작업을 진행하여야 ATTACH 작업 중 SCAN 이 발생하지 않습니다 (제약조건을 파티션 범위와 동일하게 생성하는 과정 필수) |
| 파티션 추가 | CREATE TABLE '테이블명' PARTITION OF '파티션명' FOR VALUES FROM () TO () | ACCESS EXCLUSIVE (테이블 접근이 불가) |
|
| 파티션 제거 | ALTER TABLE '테이블명' DETACH PARTITION '파티션명' CONCURRENTLY | SHARE UPDATE EXCLUSIVE (DML 쿼리의 경우 락 없이 처리) |
CONCURRENTLY 명령어 미 사용시 ACCESS EXCLUSIVE |
| 컬럼 추가 | ALTER TABLE '테이블명' ADD COLUMN '컬럼명' 데이터 타입 | 영향도 없음 (메타데이터 수정) | 정적인 default value를 포함한 컬럼 추가에 대하여 성능 개선 (메타데이터 수정) |
| 컬럼 삭제 | ALTER TABLE '테이블명' DROP COLUMN '컬럼명' | 영향도 없음 (메타데이터 수정) |
--12개 파티션
select now();
ALTER TABLE minjae_partition_table_week ADD COLUMN varchar_col varchar(20) default 'test';
select now();
now
------------------------------
2025-03-24 08:28:16.74131+00
(1 row)
ALTER TABLE
now
-------------------------------
2025-03-24 08:28:16.798202+00
(1 row)
select now();
ALTER TABLE minjae_partition_table_week DROP COLUMN varchar_col;
select now();
now
-------------------------------
2025-03-24 08:34:30.457411+00
(1 row)
ALTER TABLE
now
-------------------------------
2025-03-24 08:34:30.479693+00
(1 row)
--컬럼 추가 삭제간 특이사항 보이지 않음
--82개 파티션
select now();
ALTER TABLE minjae_partition_table_day ADD COLUMN varchar_col varchar(20) default 'test';
select now();
now
-------------------------------
2025-03-24 08:39:49.789918+00
(1 row)
ALTER TABLE
now
-------------------------------
2025-03-24 08:39:49.845396+00
(1 row)
select now();
ALTER TABLE minjae_partition_table_day DROP COLUMN varchar_col;
select now();
now
-------------------------------
2025-03-24 08:40:30.551134+00
(1 row)
ALTER TABLE
now
-------------------------------
2025-03-24 08:40:30.587959+00
(1 row)
--컬럼 추가 삭제간 특이사항 보이지 않음
--파티션 테이블 생성
CREATE TABLE minjae_table_01(
seq bigserial,
col1 int,
col2 int,
created_at timestamp,
constraint minjae_table_01_pk primary key (seq, created_at)
) PARTITION BY RANGE (created_at);
create index minjae_test_01_nc_col1 on minjae_table_01 (col1);
CREATE TABLE minjae_table_partition01 PARTITION OF minjae_table_01 for values from ('2024-10-31') to ('2024-11-30');
CREATE TABLE minjae_table_partition02 PARTITION OF minjae_table_01 for values from ('2024-11-30') to ('2024-12-30');
CREATE TABLE minjae_table_partition03 PARTITION OF minjae_table_01 for values from ('2024-12-31') to ('2025-01-30');
CREATE TABLE minjae_table_11(
seq bigserial,
col1 int,
col2 int,
created_at timestamp,
constraint minjae_table_11_pk primary key (seq, created_at)
) PARTITION BY RANGE (created_at);
create index minjae_test_11_nc_col1 on minjae_table_11 (col1);
CREATE TABLE minjae_table_partition11 (LIKE minjae_table_11 including all, check (created_at >='2024-10-31' and created_at <'2024-11-30'));
CREATE TABLE minjae_table_partition12 (LIKE minjae_table_11 including all, check (created_at >='2024-11-30' and created_at <'2024-12-30'));
CREATE TABLE minjae_table_partition13 (LIKE minjae_table_11 including all, check (created_at >='2024-12-31' and created_at <'2025-01-30'));
ALTER TABLE minjae_table_11 ATTACH PARTITION minjae_table_partition11 for values from ('2024-10-31') to ('2024-11-30');
ALTER TABLE minjae_table_11 ATTACH PARTITION minjae_table_partition12 for values from ('2024-11-30') to ('2024-12-30');
ALTER TABLE minjae_table_11 ATTACH PARTITION minjae_table_partition13 for values from ('2024-12-31') to ('2025-01-30');
--파티션 상태 조회
SELECT
nm.nspname AS schema_name,
parent.relname AS parent_table,
child.relname AS partition_table,
pg_get_expr(child.relpartbound, child.oid) AS partition_condition
FROM pg_partitioned_table p
JOIN pg_class parent ON p.partrelid = parent.oid
JOIN pg_namespace nm ON parent.relnamespace = nm.oid
LEFT JOIN pg_inherits i ON parent.oid = i.inhparent
LEFT JOIN pg_class child ON i.inhrelid = child.oid
LEFT JOIN pg_partitioned_table cp ON child.oid = cp.partrelid -- 자식 테이블이 다시 부모일 경우
ORDER BY schema_name, parent_table, partition_table;
--결과 값
schema_name | parent_table | partition_table | partition_condition
-------------+-----------------+--------------------------+--------------------------------------------------------------------
public | minjae_table_01 | minjae_table_partition01 | FOR VALUES FROM ('2024-10-31 00:00:00') TO ('2024-11-30 00:00:00')
public | minjae_table_01 | minjae_table_partition02 | FOR VALUES FROM ('2024-11-30 00:00:00') TO ('2024-12-30 00:00:00')
public | minjae_table_01 | minjae_table_partition03 | FOR VALUES FROM ('2024-12-31 00:00:00') TO ('2025-01-30 00:00:00')
public | minjae_table_11 | minjae_table_partition11 | FOR VALUES FROM ('2024-10-31 00:00:00') TO ('2024-11-30 00:00:00')
public | minjae_table_11 | minjae_table_partition12 | FOR VALUES FROM ('2024-11-30 00:00:00') TO ('2024-12-30 00:00:00')
public | minjae_table_11 | minjae_table_partition13 | FOR VALUES FROM ('2024-12-31 00:00:00') TO ('2025-01-30 00:00:00')
-- 파티션 인덱스, PK 등 정상 추가 확인
CREATE TABLE minjae_test_input_01 (
col1 int,
col2 int,
created_at timestamp
);
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,01, '2025-01-01 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,02, '2025-01-02 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,03, '2025-01-03 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,04, '2025-01-04 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,05, '2025-01-05 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,06, '2025-01-06 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,07, '2025-01-07 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,08, '2025-01-08 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,09, '2025-01-09 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,10, '2025-01-10 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,11, '2025-01-11 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,12, '2025-01-12 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,13, '2025-01-13 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,14, '2025-01-14 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,15, '2025-01-15 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,16, '2025-01-16 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,17, '2025-01-17 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,18, '2025-01-18 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,19, '2025-01-19 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,20, '2025-01-20 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,21, '2025-01-21 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,22, '2025-01-22 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,23, '2025-01-23 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,24, '2025-01-24 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,25, '2025-01-25 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,26, '2025-01-26 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,27, '2025-01-27 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,28, '2025-01-28 00:00:00');
INSERT INTO minjae_test_input_01 (col1,col2,created_at) VALUES (2501,29, '2025-01-29 00:00:00');
CREATE TABLE minjae_test_input_02 (
col1 int,
col2 int,
created_at timestamp
);
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,01, '2024-12-01 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,02, '2024-12-02 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,03, '2024-12-03 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,04, '2024-12-04 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,05, '2024-12-05 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,06, '2024-12-06 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,07, '2024-12-07 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,08, '2024-12-08 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,09, '2024-12-09 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,10, '2024-12-10 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,11, '2024-12-11 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,12, '2024-12-12 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,13, '2024-12-13 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,14, '2024-12-14 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,15, '2024-12-15 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,16, '2024-12-16 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,17, '2024-12-17 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,18, '2024-12-18 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,19, '2024-12-19 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,20, '2024-12-20 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,21, '2024-12-21 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,22, '2024-12-22 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,23, '2024-12-23 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,24, '2024-12-24 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,25, '2024-12-25 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,26, '2024-12-26 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,27, '2024-12-27 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,28, '2024-12-28 00:00:00');
INSERT INTO minjae_test_input_02 (col1,col2,created_at) VALUES (2412,29, '2024-12-29 00:00:00');
CREATE TABLE minjae_test_input_03 (
col1 int,
col2 int,
created_at timestamp
);
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,01, '2024-11-01 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,02, '2024-11-02 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,03, '2024-11-03 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,04, '2024-11-04 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,05, '2024-11-05 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,06, '2024-11-06 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,07, '2024-11-07 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,08, '2024-11-08 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,09, '2024-11-09 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,10, '2024-11-10 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,11, '2024-11-11 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,12, '2024-11-12 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,13, '2024-11-13 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,14, '2024-11-14 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,15, '2024-11-15 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,16, '2024-11-16 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,17, '2024-11-17 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,18, '2024-11-18 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,19, '2024-11-19 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,20, '2024-11-20 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,21, '2024-11-21 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,22, '2024-11-22 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,23, '2024-11-23 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,24, '2024-11-24 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,25, '2024-11-25 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,26, '2024-11-26 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,27, '2024-11-27 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,28, '2024-11-28 00:00:00');
INSERT INTO minjae_test_input_03 (col1,col2,created_at) VALUES (2411,29, '2024-11-29 00:00:00');
insert into public. minjae_table_01 (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_01;
insert into public. minjae_table_01 (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_02;
insert into public. minjae_table_01 (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_03;
insert into public. minjae_table_11 (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_01;
insert into public. minjae_table_11 (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_02;
insert into public. minjae_table_11 (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_03;
--단일 포인트 조회로 파티션 프루닝 확인
EXPLAIN ANALYZE SELECT * FROM minjae_table_01 WHERE created_at = '2024-11-07 00:00:00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using minjae_table_partition01_pkey on minjae_table_partition01 minjae_table_01 (cost=0.15..20.01 rows=8 width=24) (actual time=0.010..0.011 rows=1 loops=1)
Index Cond: (created_at = '2024-11-07 00:00:00'::timestamp without time zone)
Planning Time: 1.506 ms
Execution Time: 0.035 ms
EXPLAIN ANALYZE SELECT * FROM minjae_table_01 WHERE created_at = '2024-12-07 00:00:00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using minjae_table_partition02_pkey on minjae_table_partition02 minjae_table_01 (cost=0.15..20.01 rows=8 width=24) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: (created_at = '2024-12-07 00:00:00'::timestamp without time zone)
Planning Time: 0.117 ms
Execution Time: 0.035 ms
EXPLAIN ANALYZE SELECT * FROM minjae_table_01 WHERE created_at = '2025-01-07 00:00:00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using minjae_table_partition03_pkey on minjae_table_partition03 minjae_table_01 (cost=0.15..20.01 rows=8 width=24) (actual time=0.013..0.014 rows=1 loops=1)
Index Cond: (created_at = '2025-01-07 00:00:00'::timestamp without time zone)
Planning Time: 0.114 ms
Execution Time: 0.033 ms
--단일 포인트 조회로 파티션 프루닝 확인
EXPLAIN ANALYZE SELECT * FROM minjae_table_11 WHERE created_at = '2024-11-07 00:00:00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using minjae_table_partition11_pkey on minjae_table_partition11 minjae_table_11 (cost=0.15..20.01 rows=8 width=24) (actual time=0.013..0.014 rows=1 loops=1)
Index Cond: (created_at = '2024-11-07 00:00:00'::timestamp without time zone)
Planning Time: 0.124 ms
Execution Time: 0.037 ms
EXPLAIN ANALYZE SELECT * FROM minjae_table_11 WHERE created_at = '2024-12-07 00:00:00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using minjae_table_partition12_pkey on minjae_table_partition12 minjae_table_11 (cost=0.15..20.01 rows=8 width=24) (actual time=0.012..0.014 rows=1 loops=1)
Index Cond: (created_at = '2024-12-07 00:00:00'::timestamp without time zone)
Planning Time: 0.114 ms
Execution Time: 0.034 ms
EXPLAIN ANALYZE SELECT * FROM minjae_table_11 WHERE created_at = '2025-01-07 00:00:00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using minjae_table_partition13_pkey on minjae_table_partition13 minjae_table_11 (cost=0.15..20.01 rows=8 width=24) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: (created_at = '2025-01-07 00:00:00'::timestamp without time zone)
Planning Time: 0.140 ms
Execution Time: 0.036 ms
--전체 범위 조회 실행계획 확인
EXPLAIN ANALYZE SELECT * FROM minjae_table_01 WHERE created_at BETWEEN '2024-11-07 00:00:00' AND '2025-12-07 00:00:00';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.15..71.92 rows=24 width=24) (actual time=0.020..0.065 rows=81 loops=1)
-> Index Scan using minjae_table_partition01_pkey on minjae_table_partition01 minjae_table_01_1 (cost=0.15..23.93 rows=8 width=24) (actual time=0.019..0.027 rows=23 loops=1)
Index Cond: ((created_at >= '2024-11-07 00:00:00'::timestamp without time zone) AND (created_at <= '2025-12-07 00:00:00'::timestamp without time zone))
-> Index Scan using minjae_table_partition02_pkey on minjae_table_partition02 minjae_table_01_2 (cost=0.15..23.93 rows=8 width=24) (actual time=0.009..0.016 rows=29 loops=1)
Index Cond: ((created_at >= '2024-11-07 00:00:00'::timestamp without time zone) AND (created_at <= '2025-12-07 00:00:00'::timestamp without time zone))
-> Index Scan using minjae_table_partition03_pkey on minjae_table_partition03 minjae_table_01_3 (cost=0.15..23.93 rows=8 width=24) (actual time=0.008..0.014 rows=29 loops=1)
Index Cond: ((created_at >= '2024-11-07 00:00:00'::timestamp without time zone) AND (created_at <= '2025-12-07 00:00:00'::timestamp without time zone))
Planning Time: 0.199 ms
Execution Time: 0.097 ms
--전체 범위 조회 실행계획 확인
EXPLAIN ANALYZE SELECT * FROM minjae_table_11 WHERE created_at BETWEEN '2024-11-07 00:00:00' AND '2025-12-07 00:00:00';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.15..71.92 rows=24 width=24) (actual time=0.025..0.072 rows=81 loops=1)
-> Index Scan using minjae_table_partition11_pkey on minjae_table_partition11 minjae_table_11_1 (cost=0.15..23.93 rows=8 width=24) (actual time=0.025..0.034 rows=23 loops=1)
Index Cond: ((created_at >= '2024-11-07 00:00:00'::timestamp without time zone) AND (created_at <= '2025-12-07 00:00:00'::timestamp without time zone))
-> Index Scan using minjae_table_partition12_pkey on minjae_table_partition12 minjae_table_11_2 (cost=0.15..23.93 rows=8 width=24) (actual time=0.010..0.016 rows=29 loops=1)
Index Cond: ((created_at >= '2024-11-07 00:00:00'::timestamp without time zone) AND (created_at <= '2025-12-07 00:00:00'::timestamp without time zone))
-> Index Scan using minjae_table_partition13_pkey on minjae_table_partition13 minjae_table_11_3 (cost=0.15..23.93 rows=8 width=24) (actual time=0.008..0.014 rows=29 loops=1)
Index Cond: ((created_at >= '2024-11-07 00:00:00'::timestamp without time zone) AND (created_at <= '2025-12-07 00:00:00'::timestamp without time zone))
Planning Time: 0.159 ms
Execution Time: 0.107 ms
--부분 범위 조회로 파티션 프루닝 확인
EXPLAIN ANALYZE SELECT * FROM minjae_table_01 WHERE created_at BETWEEN NOW() - INTERVAL '2 months' AND NOW();
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.16..71.94 rows=24 width=24) (actual time=0.016..0.043 rows=51 loops=1)
Subplans Removed: 1
-> Index Scan using minjae_table_partition02_pkey on minjae_table_partition02 minjae_table_01_1 (cost=0.16..23.94 rows=8 width=24) (actual time=0.015..0.021 rows=22 loops=1)
Index Cond: ((created_at >= (now() - '2 mons'::interval)) AND (created_at <= now()))
-> Index Scan using minjae_table_partition03_pkey on minjae_table_partition03 minjae_table_01_2 (cost=0.16..23.94 rows=8 width=24) (actual time=0.012..0.017 rows=29 loops=1)
Index Cond: ((created_at >= (now() - '2 mons'::interval)) AND (created_at <= now()))
Planning Time: 0.204 ms
Execution Time: 0.085 ms
--부분 범위 조회로 파티션 프루닝 확인
EXPLAIN ANALYZE SELECT * FROM minjae_table_11 WHERE created_at BETWEEN '2024-12-07 00:00:00' AND NOW();
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.15..47.95 rows=16 width=24) (actual time=0.028..0.055 rows=52 loops=1)
-> Index Scan using minjae_table_partition12_pkey on minjae_table_partition12 minjae_table_11_1 (cost=0.15..23.93 rows=8 width=24) (actual time=0.027..0.034 rows=23 loops=1)
Index Cond: ((created_at >= '2024-12-07 00:00:00'::timestamp without time zone) AND (created_at <= now()))
-> Index Scan using minjae_table_partition13_pkey on minjae_table_partition13 minjae_table_11_2 (cost=0.15..23.93 rows=8 width=24) (actual time=0.010..0.015 rows=29 loops=1)
Index Cond: ((created_at >= '2024-12-07 00:00:00'::timestamp without time zone) AND (created_at <= now()))
Planning Time: 0.130 ms
Execution Time: 0.094 ms
--> 웹 검색 중 파티션 프루닝이 실행 계획 단계에서만 작성이 되어 변수로 입력되면 프루닝을 못한다는 내용이 있어서 테스트 및 공식 문서를 확인하였습니다.
실행 파티션 프루닝은 주어진 쿼리의 계획 중 뿐만 아니라 실행 중에도 수행할 수 있습니다. 이는 절에 쿼리 계획 시 값을 알 수 없는 표현식이 포함된 경우 더 많은 파티션을 프루닝할 수 있으므로 유용합니다.
https://www.postgresql.org/docs/14/ddl-partitioning.html
--작업간 트랜잭션 설정으로 LOCK 확인
pgbench minjae_test -h HOST -p 5432 -U DBA -c 1 -j 1 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
insert into public. minjae_table_01 (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_03;
\q
pgbench minjae_test -h HOST -p 5432 -U DBA -c 1 -j 1 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
SELECT * FROM minjae_table_01 WHERE created_at = '2024-11-07 00:00:00';
\q
--CREATE TABLE ... PARTITION OF 파티션 증설 minjae_table_01 대상
BEGIN;
CREATE TABLE minjae_table_partition10 PARTITION OF minjae_table_01 for values from ('2024-09-30') to ('2024-10-30');
select l.locktype, t.relname, pid, mode, granted, waitstart
from pg_locks l
left join pg_class t on l.relation = t.oid
locktype | relname | pid | mode | granted | waitstart
---------------+-----------------------------------+------+---------------------+---------+-------------------------------
object | | 595 | AccessShareLock | t |
object | | 595 | AccessShareLock | t |
relation | minjae_table_01 | 595 | AccessExclusiveLock | t | --> 테이블에 AccessExclusiveLock 발생
relation | | 595 | AccessExclusiveLock | t |
relation | | 595 | AccessExclusiveLock | t |
relation | | 595 | ShareLock | t |
relation | | 595 | AccessExclusiveLock | t |
transactionid | | 595 | ExclusiveLock | t |
virtualxid | | 595 | ExclusiveLock | t |
relation | pg_class | 5481 | AccessShareLock | t |
relation | pg_class_oid_index | 5481 | AccessShareLock | t |
relation | pg_class_relname_nsp_index | 5481 | AccessShareLock | t |
relation | pg_class_tblspc_relfilenode_index | 5481 | AccessShareLock | t |
relation | pg_locks | 5481 | AccessShareLock | t |
virtualxid | | 5481 | ExclusiveLock | t |
relation | minjae_table_01 | 5571 | RowExclusiveLock | f | 2025-02-07 01:42:44.453936+00
virtualxid | | 5571 | ExclusiveLock | t |
relation | minjae_table_01 | 5578 | AccessShareLock | f | 2025-02-07 01:42:44.537307+00
virtualxid | | 5578 | ExclusiveLock | t |
COMMIT;
schema_name | parent_table | partition_table | partition_condition
-------------+-----------------+--------------------------+--------------------------------------------------------------------
public | minjae_table_01 | minjae_table_partition01 | FOR VALUES FROM ('2024-10-31 00:00:00') TO ('2024-11-30 00:00:00')
public | minjae_table_01 | minjae_table_partition02 | FOR VALUES FROM ('2024-11-30 00:00:00') TO ('2024-12-30 00:00:00')
public | minjae_table_01 | minjae_table_partition03 | FOR VALUES FROM ('2024-12-31 00:00:00') TO ('2025-01-30 00:00:00')
public | minjae_table_01 | minjae_table_partition10 | FOR VALUES FROM ('2024-09-30 00:00:00') TO ('2024-10-30 00:00:00')
입력 부하
progress: 10.0 s, 281.0 tps, lat 3.549 ms stddev 1.345, 0 failed
progress: 20.0 s, 271.3 tps, lat 3.684 ms stddev 2.022, 0 failed
progress: 30.0 s, 274.5 tps, lat 3.640 ms stddev 1.900, 0 failed
progress: 40.0 s, 263.7 tps, lat 3.791 ms stddev 2.139, 0 failed
progress: 50.0 s, 265.5 tps, lat 3.764 ms stddev 2.387, 0 failed
progress: 60.0 s, 146.8 tps, lat 3.914 ms stddev 2.239, 0 failed
progress: 70.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed --> 파티션 추가 시작 시점
progress: 80.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 90.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 100.0 s, 72.2 tps, lat 61.368 ms stddev 1547.412, 0 failed --> 파티션 추가 종료 시점
progress: 110.0 s, 279.0 tps, lat 3.582 ms stddev 2.348, 0 failed
progress: 120.0 s, 274.5 tps, lat 3.640 ms stddev 1.734, 0 failed
조회 부하
progress: 10.0 s, 5.8 tps, lat 169.789 ms stddev 19.764, 0 failed
progress: 20.0 s, 5.8 tps, lat 171.915 ms stddev 14.342, 0 failed
progress: 30.0 s, 5.3 tps, lat 189.752 ms stddev 40.280, 0 failed
progress: 40.0 s, 5.5 tps, lat 183.218 ms stddev 19.490, 0 failed
progress: 50.0 s, 5.2 tps, lat 190.423 ms stddev 15.729, 0 failed
progress: 60.0 s, 0.7 tps, lat 200.364 ms stddev 20.012, 0 failed
progress: 70.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed --> 파티션 추가 시작 시점
progress: 80.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 90.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed --> 파티션 추가 종료 시점
progress: 100.0 s, 3.7 tps, lat 1316.012 ms stddev 6730.570, 0 failed
progress: 110.0 s, 5.1 tps, lat 194.274 ms stddev 17.538, 0 failed
progress: 120.0 s, 4.7 tps, lat 211.558 ms stddev 39.837, 0 failed
--작업간 트랜잭션 설정으로 LOCK 확인
pgbench minjae_test -h HOST -p 5432 -U DBA -c 1 -j 1 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
insert into public. minjae_table_11 (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_03;
\q
pgbench minjae_test -h HOST -p 5432 -U DBA -c 1 -j 1 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
SELECT * FROM minjae_table_11 WHERE created_at = '2024-11-07 00:00:00';
\q
--ALTER TABLE ~~ ATTACH PARTITION ~~ FOR VALUES FROM A TO B 파티션 증설
--작업간 트랜잭션 설정으로 LOCK 확인 minjae_table_11 대상
BEGIN;
CREATE TABLE minjae_table_partition00 (LIKE minjae_table_11 including all, check (created_at >='2024-09-30' and created_at <'2024-10-30'));
locktype | relname | pid | mode | granted | waitstart
---------------+-----------------------------------+------+--------------------------+---------+-----------
object | | 595 | AccessShareLock | t |
object | | 595 | AccessShareLock | t |
relation | minjae_table_11 | 595 | AccessShareLock | t | --> 테이블에 AccessShareLock 발생
relation | | 595 | AccessExclusiveLock | t |
relation | | 595 | AccessExclusiveLock | t |
relation | | 595 | ShareLock | t |
relation | | 595 | AccessExclusiveLock | t |
transactionid | | 595 | ExclusiveLock | t |
virtualxid | | 595 | ExclusiveLock | t |
relation | pg_class | 5481 | AccessShareLock | t |
relation | pg_class_oid_index | 5481 | AccessShareLock | t |
relation | pg_class_relname_nsp_index | 5481 | AccessShareLock | t |
relation | pg_class_tblspc_relfilenode_index | 5481 | AccessShareLock | t |
relation | pg_locks | 5481 | AccessShareLock | t |
virtualxid | | 5481 | ExclusiveLock | t |
relation | minjae_table_11 | 5794 | RowExclusiveLock | t |
relation | minjae_table_11_seq_seq | 5794 | RowExclusiveLock | t |
relation | minjae_table_partition11 | 5794 | RowExclusiveLock | t |
relation | minjae_test_input_03 | 5794 | AccessShareLock | t |
transactionid | | 5794 | ExclusiveLock | t |
virtualxid | | 5794 | ExclusiveLock | t |
relation | minjae_table_11 | 5802 | AccessShareLock | t |
relation | minjae_table_partition11 | 5802 | AccessShareLock | t |
relation | minjae_table_partition11_col1_idx | 5802 | AccessShareLock | t |
relation | minjae_table_partition11_pkey | 5802 | AccessShareLock | t |
virtualxid | | 5802 | ExclusiveLock | t |
relation | minjae_table_partition11 | 6038 | ShareUpdateExclusiveLock | t |
relation | minjae_table_partition11_col1_idx | 6038 | AccessShareLock | t |
relation | minjae_table_partition11_pkey | 6038 | AccessShareLock | t |
virtualxid | | 6038 | ExclusiveLock | t |
ALTER TABLE minjae_table_11 ATTACH PARTITION minjae_table_partition00 for values from ('2024-09-30') to ('2024-10-30');
locktype | relname | pid | mode | granted | waitstart
---------------+-----------------------------------+------+--------------------------+---------+-----------
object | | 595 | AccessShareLock | t |
object | | 595 | AccessShareLock | t |
relation | minjae_table_11 | 595 | ShareUpdateExclusiveLock | t | --> 테이블에 ShareUpdateExclusiveLock 발생
relation | minjae_table_11 | 595 | AccessShareLock | t | --> 테이블에 AccessShareLock 발생
relation | | 595 | AccessExclusiveLock | t |
relation | | 595 | AccessExclusiveLock | t |
relation | | 595 | ShareLock | t |
relation | | 595 | AccessExclusiveLock | t |
transactionid | | 595 | ExclusiveLock | t |
virtualxid | | 595 | ExclusiveLock | t |
relation | pg_class | 5481 | AccessShareLock | t |
relation | pg_class_oid_index | 5481 | AccessShareLock | t |
relation | pg_class_relname_nsp_index | 5481 | AccessShareLock | t |
relation | pg_class_tblspc_relfilenode_index | 5481 | AccessShareLock | t |
relation | pg_locks | 5481 | AccessShareLock | t |
virtualxid | | 5481 | ExclusiveLock | t |
relation | minjae_table_11 | 5794 | RowExclusiveLock | t |
relation | minjae_table_11_seq_seq | 5794 | RowExclusiveLock | t |
relation | minjae_table_partition11 | 5794 | RowExclusiveLock | t |
relation | minjae_test_input_03 | 5794 | AccessShareLock | t |
transactionid | | 5794 | ExclusiveLock | t |
virtualxid | | 5794 | ExclusiveLock | t |
relation | minjae_table_11 | 5802 | AccessShareLock | t |
relation | minjae_table_partition11 | 5802 | AccessShareLock | t |
relation | minjae_table_partition11_col1_idx | 5802 | AccessShareLock | t |
relation | minjae_table_partition11_pkey | 5802 | AccessShareLock | t |
virtualxid | | 5802 | ExclusiveLock | t |
ALTER TABLE minjae_table_partition00 DROP CONSTRAINT IF EXISTS minjae_table_partition00_created_at_check;
locktype | relname | pid | mode | granted | waitstart
---------------+-----------------------------------+------+--------------------------+---------+-----------
object | | 595 | AccessExclusiveLock | t |
object | | 595 | AccessShareLock | t |
object | | 595 | AccessShareLock | t |
relation | minjae_table_11 | 595 | ShareUpdateExclusiveLock | t | --> 테이블에 ShareUpdateExclusiveLock 발생
relation | minjae_table_11 | 595 | AccessShareLock | t | --> 테이블에 AccessShareLock 발생
relation | | 595 | AccessExclusiveLock | t |
relation | | 595 | AccessExclusiveLock | t |
relation | | 595 | ShareLock | t |
relation | | 595 | AccessExclusiveLock | t |
transactionid | | 595 | ExclusiveLock | t |
virtualxid | | 595 | ExclusiveLock | t |
relation | pg_class | 5481 | AccessShareLock | t |
relation | pg_class_oid_index | 5481 | AccessShareLock | t |
relation | pg_class_relname_nsp_index | 5481 | AccessShareLock | t |
relation | pg_class_tblspc_relfilenode_index | 5481 | AccessShareLock | t |
relation | pg_locks | 5481 | AccessShareLock | t |
virtualxid | | 5481 | ExclusiveLock | t |
relation | minjae_table_11 | 5794 | RowExclusiveLock | t |
relation | minjae_table_11_seq_seq | 5794 | RowExclusiveLock | t |
relation | minjae_table_partition11 | 5794 | RowExclusiveLock | t |
relation | minjae_test_input_03 | 5794 | AccessShareLock | t |
transactionid | | 5794 | ExclusiveLock | t |
virtualxid | | 5794 | ExclusiveLock | t |
relation | minjae_table_11 | 5802 | AccessShareLock | t |
relation | minjae_table_partition11 | 5802 | AccessShareLock | t |
relation | minjae_table_partition11_col1_idx | 5802 | AccessShareLock | t |
relation | minjae_table_partition11_pkey | 5802 | AccessShareLock | t |
virtualxid | | 5802 | ExclusiveLock | t |
COMMIT;
schema_name | parent_table | partition_table | partition_condition
-------------+-----------------+--------------------------+--------------------------------------------------------------------
public | minjae_table_01 | minjae_table_partition01 | FOR VALUES FROM ('2024-10-31 00:00:00') TO ('2024-11-30 00:00:00')
public | minjae_table_01 | minjae_table_partition02 | FOR VALUES FROM ('2024-11-30 00:00:00') TO ('2024-12-30 00:00:00')
public | minjae_table_01 | minjae_table_partition03 | FOR VALUES FROM ('2024-12-31 00:00:00') TO ('2025-01-30 00:00:00')
public | minjae_table_01 | minjae_table_partition10 | FOR VALUES FROM ('2024-09-30 00:00:00') TO ('2024-10-30 00:00:00')
public | minjae_table_11 | minjae_table_partition00 | FOR VALUES FROM ('2024-09-30 00:00:00') TO ('2024-10-30 00:00:00')
public | minjae_table_11 | minjae_table_partition11 | FOR VALUES FROM ('2024-10-31 00:00:00') TO ('2024-11-30 00:00:00')
public | minjae_table_11 | minjae_table_partition12 | FOR VALUES FROM ('2024-11-30 00:00:00') TO ('2024-12-30 00:00:00')
public | minjae_table_11 | minjae_table_partition13 | FOR VALUES FROM ('2024-12-31 00:00:00') TO ('2025-01-30 00:00:00')
입력 부하
progress: 10.0 s, 282.9 tps, lat 3.524 ms stddev 1.754, 0 failed
progress: 20.0 s, 260.3 tps, lat 3.840 ms stddev 2.056, 0 failed
progress: 30.0 s, 240.6 tps, lat 4.154 ms stddev 2.280, 0 failed
progress: 40.0 s, 250.3 tps, lat 3.993 ms stddev 2.210, 0 failed
progress: 50.0 s, 243.5 tps, lat 4.105 ms stddev 2.678, 0 failed
progress: 60.0 s, 240.3 tps, lat 4.160 ms stddev 2.892, 0 failed
progress: 70.0 s, 255.1 tps, lat 3.918 ms stddev 2.463, 0 failed
progress: 80.0 s, 252.4 tps, lat 3.960 ms stddev 2.596, 0 failed
progress: 90.0 s, 246.1 tps, lat 4.061 ms stddev 2.507, 0 failed
progress: 100.0 s, 247.7 tps, lat 4.035 ms stddev 2.735, 0 failed
progress: 110.0 s, 250.0 tps, lat 3.998 ms stddev 2.739, 0 failed
progress: 120.0 s, 250.1 tps, lat 3.996 ms stddev 2.651, 0 failed
progress: 130.0 s, 268.5 tps, lat 3.723 ms stddev 2.087, 0 failed
progress: 140.0 s, 262.1 tps, lat 3.814 ms stddev 2.124, 0 failed
progress: 150.0 s, 253.0 tps, lat 3.946 ms stddev 2.769, 0 failed
progress: 160.0 s, 257.0 tps, lat 3.892 ms stddev 2.561, 0 failed
progress: 170.0 s, 252.9 tps, lat 3.952 ms stddev 2.441, 0 failed
progress: 180.0 s, 254.4 tps, lat 3.929 ms stddev 2.245, 0 failed
progress: 190.0 s, 266.1 tps, lat 3.756 ms stddev 1.859, 0 failed
progress: 200.0 s, 262.9 tps, lat 3.801 ms stddev 1.934, 0 failed
progress: 210.0 s, 257.7 tps, lat 3.877 ms stddev 2.515, 0 failed
progress: 220.0 s, 261.3 tps, lat 3.827 ms stddev 1.924, 0 failed
progress: 230.0 s, 266.3 tps, lat 3.753 ms stddev 2.244, 0 failed
progress: 240.0 s, 252.1 tps, lat 3.964 ms stddev 2.685, 0 failed
progress: 250.0 s, 276.1 tps, lat 3.620 ms stddev 1.688, 0 failed
progress: 260.0 s, 261.6 tps, lat 3.821 ms stddev 1.947, 0 failed
progress: 270.0 s, 269.8 tps, lat 3.704 ms stddev 2.109, 0 failed
progress: 280.0 s, 254.9 tps, lat 3.922 ms stddev 2.182, 0 failed
progress: 290.0 s, 256.9 tps, lat 3.889 ms stddev 2.089, 0 failed
progress: 300.0 s, 265.7 tps, lat 3.762 ms stddev 1.969, 0 failed
progress: 310.0 s, 262.2 tps, lat 3.813 ms stddev 2.225, 0 failed
progress: 320.0 s, 271.1 tps, lat 3.686 ms stddev 1.878, 0 failed
progress: 330.0 s, 267.4 tps, lat 3.738 ms stddev 1.660, 0 failed
progress: 340.0 s, 263.8 tps, lat 3.789 ms stddev 1.909, 0 failed
progress: 350.0 s, 254.3 tps, lat 3.930 ms stddev 2.385, 0 failed
progress: 360.0 s, 253.8 tps, lat 3.938 ms stddev 2.108, 0 failed
progress: 370.0 s, 260.7 tps, lat 3.834 ms stddev 2.160, 0 failed
progress: 380.0 s, 272.3 tps, lat 3.670 ms stddev 1.892, 0 failed
progress: 390.0 s, 253.4 tps, lat 3.944 ms stddev 2.339, 0 failed
progress: 400.0 s, 272.0 tps, lat 3.675 ms stddev 1.797, 0 failed
progress: 410.0 s, 262.6 tps, lat 3.806 ms stddev 2.125, 0 failed
progress: 420.0 s, 268.3 tps, lat 3.724 ms stddev 2.002, 0 failed
progress: 430.0 s, 278.9 tps, lat 3.583 ms stddev 1.805, 0 failed
progress: 440.0 s, 267.1 tps, lat 3.743 ms stddev 2.045, 0 failed
progress: 450.0 s, 270.0 tps, lat 3.701 ms stddev 1.941, 0 failed
progress: 460.0 s, 267.9 tps, lat 3.730 ms stddev 1.992, 0 failed
progress: 470.0 s, 268.4 tps, lat 3.724 ms stddev 1.955, 0 failed
progress: 480.0 s, 264.3 tps, lat 3.782 ms stddev 3.421, 0 failed
progress: 490.0 s, 267.0 tps, lat 3.743 ms stddev 1.969, 0 failed
progress: 500.0 s, 270.9 tps, lat 3.689 ms stddev 2.316, 0 failed
progress: 510.0 s, 254.9 tps, lat 3.921 ms stddev 2.086, 0 failed
progress: 520.0 s, 271.4 tps, lat 3.682 ms stddev 1.977, 0 failed
progress: 530.0 s, 264.5 tps, lat 3.778 ms stddev 2.435, 0 failed
progress: 540.0 s, 260.8 tps, lat 3.833 ms stddev 2.005, 0 failed
progress: 550.0 s, 284.9 tps, lat 3.508 ms stddev 1.577, 0 failed
progress: 560.0 s, 248.9 tps, lat 4.016 ms stddev 2.341, 0 failed
progress: 570.0 s, 258.5 tps, lat 3.866 ms stddev 2.406, 0 failed
progress: 580.0 s, 258.0 tps, lat 3.873 ms stddev 2.144, 0 failed
progress: 590.0 s, 267.1 tps, lat 3.742 ms stddev 1.954, 0 failed
progress: 600.0 s, 253.7 tps, lat 3.940 ms stddev 2.667, 0 failed
progress: 610.0 s, 264.0 tps, lat 3.786 ms stddev 2.075, 0 failed
progress: 620.0 s, 268.2 tps, lat 3.726 ms stddev 2.006, 0 failed
progress: 630.0 s, 259.2 tps, lat 3.853 ms stddev 2.381, 0 failed
progress: 640.0 s, 251.2 tps, lat 3.981 ms stddev 2.527, 0 failed
progress: 650.0 s, 258.4 tps, lat 3.869 ms stddev 2.510, 0 failed
progress: 660.0 s, 262.3 tps, lat 3.810 ms stddev 1.984, 0 failed
조회 부하
progress: 10.0 s, 132.4 tps, lat 7.525 ms stddev 3.326, 0 failed
progress: 20.0 s, 74.5 tps, lat 13.435 ms stddev 3.990, 0 failed
progress: 30.0 s, 54.9 tps, lat 18.203 ms stddev 3.723, 0 failed
progress: 40.0 s, 42.8 tps, lat 23.350 ms stddev 4.026, 0 failed
progress: 50.0 s, 32.3 tps, lat 30.985 ms stddev 10.547, 0 failed
progress: 60.0 s, 29.7 tps, lat 33.618 ms stddev 5.277, 0 failed
progress: 70.0 s, 25.6 tps, lat 38.969 ms stddev 6.647, 0 failed
progress: 80.0 s, 21.9 tps, lat 45.726 ms stddev 11.487, 0 failed
progress: 90.0 s, 20.3 tps, lat 49.165 ms stddev 6.375, 0 failed
progress: 100.0 s, 18.5 tps, lat 54.298 ms stddev 7.288, 0 failed
progress: 110.0 s, 15.8 tps, lat 63.152 ms stddev 15.488, 0 failed
progress: 120.0 s, 15.0 tps, lat 66.580 ms stddev 9.322, 0 failed
progress: 130.0 s, 13.9 tps, lat 71.671 ms stddev 11.551, 0 failed
progress: 140.0 s, 12.6 tps, lat 79.768 ms stddev 19.605, 0 failed
progress: 150.0 s, 12.2 tps, lat 81.517 ms stddev 12.334, 0 failed
progress: 160.0 s, 11.7 tps, lat 85.741 ms stddev 9.938, 0 failed
progress: 170.0 s, 10.4 tps, lat 95.959 ms stddev 16.984, 0 failed
progress: 180.0 s, 10.2 tps, lat 98.324 ms stddev 13.585, 0 failed
progress: 190.0 s, 9.8 tps, lat 102.340 ms stddev 18.808, 0 failed
progress: 200.0 s, 8.7 tps, lat 114.165 ms stddev 23.864, 0 failed
progress: 210.0 s, 8.3 tps, lat 119.793 ms stddev 28.994, 0 failed
progress: 220.0 s, 8.4 tps, lat 120.192 ms stddev 14.195, 0 failed
progress: 230.0 s, 7.5 tps, lat 132.430 ms stddev 35.818, 0 failed
progress: 240.0 s, 7.8 tps, lat 129.161 ms stddev 14.092, 0 failed
progress: 250.0 s, 7.2 tps, lat 137.823 ms stddev 19.019, 0 failed
progress: 260.0 s, 6.7 tps, lat 150.011 ms stddev 29.096, 0 failed
progress: 270.0 s, 6.7 tps, lat 149.123 ms stddev 27.370, 0 failed
progress: 280.0 s, 6.5 tps, lat 152.403 ms stddev 16.484, 0 failed
progress: 290.0 s, 6.0 tps, lat 169.194 ms stddev 35.818, 0 failed
progress: 300.0 s, 6.0 tps, lat 164.033 ms stddev 14.970, 0 failed
progress: 310.0 s, 6.0 tps, lat 169.176 ms stddev 20.904, 0 failed
progress: 320.0 s, 5.4 tps, lat 182.149 ms stddev 36.106, 0 failed
progress: 330.0 s, 5.5 tps, lat 182.337 ms stddev 19.992, 0 failed
progress: 340.0 s, 5.5 tps, lat 182.494 ms stddev 13.074, 0 failed
progress: 350.0 s, 4.7 tps, lat 212.003 ms stddev 54.665, 0 failed
progress: 360.0 s, 5.1 tps, lat 197.586 ms stddev 15.576, 0 failed
progress: 370.0 s, 5.0 tps, lat 195.766 ms stddev 12.833, 0 failed
progress: 380.0 s, 4.7 tps, lat 218.371 ms stddev 34.072, 0 failed
progress: 390.0 s, 4.5 tps, lat 220.750 ms stddev 28.896, 0 failed
progress: 400.0 s, 4.6 tps, lat 218.619 ms stddev 13.093, 0 failed
progress: 410.0 s, 3.9 tps, lat 253.790 ms stddev 68.824, 0 failed
progress: 420.0 s, 4.3 tps, lat 234.149 ms stddev 23.663, 0 failed
progress: 430.0 s, 4.1 tps, lat 237.696 ms stddev 17.783, 0 failed
progress: 440.0 s, 4.0 tps, lat 257.048 ms stddev 52.937, 0 failed
progress: 450.0 s, 4.0 tps, lat 249.190 ms stddev 20.375, 0 failed
progress: 460.0 s, 3.9 tps, lat 256.816 ms stddev 20.992, 0 failed
progress: 470.0 s, 3.4 tps, lat 289.870 ms stddev 89.190, 0 failed
progress: 480.0 s, 3.8 tps, lat 263.689 ms stddev 21.441, 0 failed
progress: 490.0 s, 3.6 tps, lat 278.778 ms stddev 26.017, 0 failed
progress: 500.0 s, 3.5 tps, lat 288.334 ms stddev 62.222, 0 failed
progress: 510.0 s, 3.4 tps, lat 284.088 ms stddev 28.817, 0 failed
progress: 520.0 s, 3.5 tps, lat 294.676 ms stddev 47.302, 0 failed
progress: 530.0 s, 3.2 tps, lat 310.062 ms stddev 68.462, 0 failed
progress: 540.0 s, 3.4 tps, lat 296.983 ms stddev 21.595, 0 failed
progress: 550.0 s, 3.2 tps, lat 301.494 ms stddev 14.994, 0 failed
progress: 560.0 s, 3.1 tps, lat 327.566 ms stddev 69.420, 0 failed
progress: 570.0 s, 3.1 tps, lat 324.021 ms stddev 36.959, 0 failed
progress: 580.0 s, 3.1 tps, lat 321.440 ms stddev 19.110, 0 failed
progress: 590.0 s, 2.8 tps, lat 365.168 ms stddev 114.787, 0 failed
progress: 600.0 s, 3.0 tps, lat 327.184 ms stddev 23.677, 0 failed
--파티션 DETACH 영향도 확인
--VACUMM 정보
schema_name | table_name | total_tuple | live_tuple | dead_tupple | live_tuple_rate | dead_tuple_rate | n_mod_since_analyze | n_ins_since_vacuum | autovacuum | autoanalyze | total_relation_size | relation_size
-------------+--------------------------+-------------+------------+-------------+-----------------+-----------------+---------------------+--------------------+-------------------------------+-------------------------------+---------------------+---------------
public | minjae_table_partition01 | 2984984 | 2984984 | 0 | 100.00 | 0.00 | 42804 | 394255 | 2025-02-07 01:42:34.266874+00 | 2025-02-07 01:44:01.149771+00 | 257 MB | 149 MB
public | minjae_table_partition03 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 40 kB | 8192 bytes
public | minjae_table_partition02 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 40 kB | 8192 bytes
public | minjae_table_partition13 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 40 kB | 8192 bytes
public | minjae_table_partition11 | 5034027 | 5034027 | 0 | 100.00 | 0.00 | 201724 | 849903 | 2025-02-07 01:54:42.563542+00 | 2025-02-07 01:56:08.321846+00 | 434 MB | 251 MB
public | minjae_table_partition12 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 40 kB | 8192 bytes
public | minjae_test_input_01 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 8192 bytes | 8192 bytes
public | minjae_test_input_02 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 8192 bytes | 8192 bytes
public | minjae_test_input_03 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 8192 bytes | 8192 bytes
public | minjae_test_input_00 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 8192 bytes | 8192 bytes
--작업간 트랜잭션 설정으로 LOCK 확인
pgbench minjae_test -h HOST -p 5432 -U DBA -c 1 -j 1 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
insert into public. minjae_table_01 (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_02;
\q
pgbench minjae_test -h HOST -p 5432 -U DBA -c 1 -j 1 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
SELECT * FROM minjae_table_01 WHERE created_at = '2024-12-07 00:00:00';
\q
--CREATE TABLE ... PARTITION OF 파티션 증설 minjae_table_partition01 대상
BEGIN;
ALTER TABLE minjae_table_01 DETACH PARTITION minjae_table_partition01
locktype | relname | pid | mode | granted | waitstart
---------------+-----------------------------------+-------+---------------------+---------+-------------------------------
relation | minjae_table_01 | 595 | AccessExclusiveLock | t |
relation | minjae_table_partition01 | 595 | AccessExclusiveLock | t |
relation | minjae_table_partition01_col1_idx | 595 | AccessExclusiveLock | t |
relation | minjae_table_partition01_pkey | 595 | AccessExclusiveLock | t |
transactionid | | 595 | ExclusiveLock | t |
virtualxid | | 595 | ExclusiveLock | t |
relation | pg_class | 5481 | AccessShareLock | t |
relation | pg_class_oid_index | 5481 | AccessShareLock | t |
relation | pg_class_relname_nsp_index | 5481 | AccessShareLock | t |
relation | pg_class_tblspc_relfilenode_index | 5481 | AccessShareLock | t |
relation | pg_locks | 5481 | AccessShareLock | t |
virtualxid | | 5481 | ExclusiveLock | t |
relation | minjae_table_01 | 14953 | RowExclusiveLock | f | 2025-02-07 04:18:56.00306+00
virtualxid | | 14953 | ExclusiveLock | t |
relation | minjae_table_01 | 14959 | AccessShareLock | f | 2025-02-07 04:18:56.001326+00
virtualxid | | 14959 | ExclusiveLock | t |
COMMIT;
schema_name | parent_table | partition_table | partition_condition
-------------+-----------------+--------------------------+--------------------------------------------------------------------
public | minjae_table_01 | minjae_table_partition02 | FOR VALUES FROM ('2024-11-30 00:00:00') TO ('2024-12-30 00:00:00')
public | minjae_table_01 | minjae_table_partition03 | FOR VALUES FROM ('2024-12-31 00:00:00') TO ('2025-01-30 00:00:00')
public | minjae_table_01 | minjae_table_partition10 | FOR VALUES FROM ('2024-09-30 00:00:00') TO ('2024-10-30 00:00:00')
public | minjae_table_11 | minjae_table_partition11 | FOR VALUES FROM ('2024-10-31 00:00:00') TO ('2024-11-30 00:00:00')
public | minjae_table_11 | minjae_table_partition12 | FOR VALUES FROM ('2024-11-30 00:00:00') TO ('2024-12-30 00:00:00')
public | minjae_table_11 | minjae_table_partition13 | FOR VALUES FROM ('2024-12-31 00:00:00') TO ('2025-01-30 00:00:00')
입력 부하
progress: 10.0 s, 254.4 tps, lat 3.920 ms stddev 1.650, 0 failed
progress: 20.0 s, 89.3 tps, lat 4.045 ms stddev 2.297, 0 failed --> 파티션 삭제 시작 시점
progress: 30.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 40.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 50.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 60.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed --> 파티션 완료 시작 시점
progress: 70.0 s, 181.3 tps, lat 31.134 ms stddev 1141.968, 0 failed
progress: 80.0 s, 238.2 tps, lat 4.196 ms stddev 2.406, 0 failed
조회 부하
progress: 10.0 s, 184.6 tps, lat 5.396 ms stddev 2.857, 0 failed
progress: 20.0 s, 2.0 tps, lat 8.780 ms stddev 1.612, 0 failed --> 파티션 삭제 시작 시점
progress: 30.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 40.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 50.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
progress: 60.0 s, 11.9 tps, lat 418.633 ms stddev 4437.463, 0 failed --> 파티션 삭제 완료 시점
progress: 70.0 s, 86.8 tps, lat 11.525 ms stddev 3.659, 0 failed
progress: 80.0 s, 59.4 tps, lat 16.820 ms stddev 4.434, 0 failed
schema_name | table_name | total_tuple | live_tuple | dead_tupple | live_tuple_rate | dead_tuple_rate | n_mod_since_analyze | n_ins_since_vacuum | autovacuum | autoanalyze | total_relation_size | relation_size
-------------+--------------------------+-------------+------------+-------------+-----------------+-----------------+---------------------+--------------------+-------------------------------+-------------------------------+---------------------+---------------
public | minjae_table_partition03 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 40 kB | 8192 bytes
public | minjae_table_partition02 | 308473 | 308473 | 0 | 100.00 | 0.00 | 0 | 0 | 2025-02-07 04:20:15.987923+00 | 2025-02-07 04:20:16.168639+00 | 27 MB | 15 MB
public | minjae_table_partition13 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 40 kB | 8192 bytes
public | minjae_table_partition01 | 2984984 | 2984984 | 0 | 100.00 | 0.00 | 42804 | 394255 | 2025-02-07 01:42:34.266874+00 | 2025-02-07 01:44:01.149771+00 | 257 MB | 149 MB
public | minjae_table_partition11 | 5034027 | 5034027 | 0 | 100.00 | 0.00 | 201724 | 849903 | 2025-02-07 01:54:42.563542+00 | 2025-02-07 01:56:08.321846+00 | 434 MB | 251 MB
public | minjae_table_partition12 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 40 kB | 8192 bytes
public | minjae_test_input_01 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 8192 bytes | 8192 bytes
public | minjae_test_input_02 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 8192 bytes | 8192 bytes
public | minjae_test_input_03 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 8192 bytes | 8192 bytes
public | minjae_test_input_00 | 29 | 29 | 0 | 100.00 | 0.00 | 29 | 29 | | | 8192 bytes | 8192 bytes
-- 테이블이 삭제 되는건 아님 단지 파티션에서 떨어져 나감
-- 테이블 DROP 을 통해서 VACUUM 미 발생으로 데이터 생명 주기 처리 가능
--DETACH CONCURRENTLY
pgbench minjae_test -h HOST -p 5432 -U DBA -c 1 -j 1 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
insert into public. minjae_table_11 (col1, col2, created_at) select col1, col2, created_at from minjae_test_input_02;
\q
pgbench minjae_test -h HOST -p 5432 -U DBA -c 1 -j 1 -t 10000000 --progress 10 --report-per-command --verbose-errors --no-vacuum --file=/dev/stdin <<'\q'
SELECT * FROM minjae_table_11 WHERE created_at = '2024-12-07 00:00:00';
\q
--작업간 트랜잭션 설정으로 LOCK 확인 minjae_table_partition11 대상
BEGIN;
ALTER TABLE minjae_table_11 DETACH PARTITION minjae_table_partition11 CONCURRENTLY;
minjae_test=*> ALTER TABLE minjae_table_11 DETACH PARTITION minjae_table_partition11 CONCURRENTLY;
ERROR: ALTER TABLE ... DETACH CONCURRENTLY cannot run inside a transaction block
CONCURRENTLY 는 TRANSCATION 처리가 불가능
상당히 빠른 속도로 처리가 되고 입력, 조회 부하 테스트 간 TPS 하락하는 부분을 찾지 못하였습니다.
--파티션 테이블 컬럼 추가, 삭제
파티션 부모 테이블에 컬럼 추가 및 삭제가 가능하며
각 하위 파티션 테이블에는 컬럼 추가 삭제 작업이 불가능 합니다.
nx_dbadmin=> alter table minjae_table_01 add column col3 varchar(10);
ALTER TABLE
nx_dbadmin=> select * from minjae_table_01;
seq | col1 | col2 | created_at | col3
-----+------+------+------------+------
(0 rows)
nx_dbadmin=> alter table minjae_table_partition01 add column col4 varchar(10);
ERROR: cannot add column to a partition
nx_dbadmin=> alter table minjae_table_partition03 add column col4 varchar(10);
ERROR: cannot add column to a partition
nx_dbadmin=> alter table minjae_table_partition03 drop column col2;
ERROR: cannot drop inherited column "col2"
PG_PARTMAN Extension
pg_partman은 파티션 관리를 쉽게 처리해주는 Extension 입니다.
→ 간단한 함수를 사용하여 파티셔닝 생성 및 삭제를 진행함.
아래와 같이 파티셔닝 생성 가능
SELECT partman.create_parent(
p_parent_table := 'public.xxxx',
p_control := 'my_date',
p_type := 'range',
p_interval := '1 days',
p_premake := 2,
p_start_partition := (CURRENT_TIMESTAMP - '10 days'::interval)::text
);
--각 옵션별 기능
p_parent_table : 파티션 테이블
p_control : 파티션 생성시 기준이 될 컬럼명
p_type : 파티셔닝 타입
- list : 지역, 상태, 카테고리 등 비연속적인 데이터.
- range : 날짜, 시간, 숫자 등 연속적인 데이터.
p_interval : 각 파티션의 범위
p_premake : 미리 생성해둘 파티션의 갯수. 기본값은 4. 현재 날짜 기준으로 파티션이 생성된다.
내부적으로 실행되는 구문은 아래와 같습니다.
생성 : ALTER TABLE ~~ ATTACH PARTITION ~~ FOR VALUES FROM (%L) TO (%L)
삭제 : ALTER TABLE ~~ DETACH PARTITION ~~
(https://github.com/pgpartman/pg_partman)
많은 의견을 준 팀원분들 덕에 더욱 완성도 높은 테스트를 진행할 수 있었다.