대용량 Insert 기술들
bulk insert(Multi-valued Insert)
데이터가 일정이상 과도하게 insert 되는 경우 인덱싱 문제로 인해 insert 속도가 저하 되었다.
테이블 인덱스 삭제 -> 대용량 Insert -> 테이블 인덱스 재생성
인덱스를 삭제 후 대용량 insert엔 문제가 없었으나 인덱스의 재생성에서 시간이 오래 걸림
PG의 copy문 사용
PG에서 제공하는 copy문을 사용.
1. 임시 테이블 생성 후 대량 Insert(해당 테이블엔 인덱스가 없기 때문에 Insert 속도 향상) 2. 본 테이블의 인덱스 삭제 3. 대용량 insert가 완료된 임시 테이블 copy문을 통해 csv 파일 생성 (copy to) 4. csv를 본 테이블에 전달(copy from) 5. 본 테이블의 인덱스 재생성가장 나은 방법이였으나 5번 인덱스 재생성을 리소스가 과도하게 사용하면서 인덱스 작업이 완료 되기 전까지 다른 작업들이 lock이 걸리는 현상 발생.
이를 우회 하기 위한 방법으로
1. 임시 테이블 생성 후 대량 Insert(해당 테이블엔 인덱스가 없기 때문에 insert 속도 향상) 2. copy문을 통해 본 테이블 데이터를 csv 파일 생성(copy to) 3. 본 테이블 csv 파일을 임시 테이블로 전달(copy from) 4. 임시 테이블 인덱스 생성 5. 본 테이블 drop 6. 임시 테이블명을 본 테이블명으로 rename인덱스 작업 시 다른 작업의 lock 회피를 위해 본 테이블 인덱스 작업을 임시 테이블로 옮김으로서 서비스에 끼치는 영향도는 낮췄으나 퍼포먼스가 너무 저하 됨.
위 3개 기술들의 공통 이슈는 인덱스가 문제였다.
PG가 대용량 insert에 좋은 퍼포먼스를 보여준다 하지만 일정 이상으로 대용량 insert가 되면 인덱싱 때문에 insert가 느려지는 이슈가 발생 하였다. 그래서 대용량 insert 퍼포먼스 향상을 위해 인덱스를 삭제 후 재생성을 필수로 꼽았으나 대용량에선 재생성에 많은 시간이 소요가 되었다.
참고
- https://www.2ndquadrant.com/en/blog/7-best-practice-tips-for-postgresql-bulk-data-loading/
- https://riptutorial.com/ko/postgresql/example/15173/copy를-사용하여-데이터-삽입
- [PostgreSQL] 대량의 Insert를 가장 빠르게 하는 방법 :: 불곰
Partitioning이란?
- Query performance 가 특정 상황에서는 아주 향상될 수 있다. 특히, 대부분의 많이 접근되는 행이 하나나 몇개의 파티셔션에 집중되어 있다면 그렇다. Paritioning 자체가 인덱스 사이즈를 줄이는 것이 가능하고, 때로는 많이 사용하는 부분을 메모리에 모두 올리는 것이 가능할 수 있기 때문이다.
- 하나의 파티션에 큰 부분을 접근하는 쿼리나 업데이트에서, 성능 개선이 가능하다. 인덱스를 타거나 랜덤하게 전체의 테이블에 퍼져있는 데이터를 읽는 대신에 하나의 파티션에서 순차적인 스캔이 가능하기 때문이다.
- 대량의 데이터를 올리거나 삭제하는 것이 파티션을 통째로 다룸으로서(파티션을 새로 생성 또는 통째로 삭제함으로써) 가능하다. 물론 요구사항이 파티션 디자인 시에 계획되었을 때다.
- 잘 사용되지 않는 데이터는 더 저렴하고 상대적으로 느린 저장소에 따로 저장하는 것이 가능하다.
한 테이블에 모든 데이터를 저장 하는 방식이 아닌 데이터 중 특정 값을 기준으로 테이블을 분활 하여 저장하는 방식이다.
간략하게 partitioning 사용 방법을 설명 하자면
마스터 테이블 생성
- 실질적으로 데이터를 저장이 되지 않는다. 역할은 파티션 테이블의 템플릿 테이블 역활을 한다.
- 마스터 테이블에 인덱스 등을 생성하게 되면 마스터 테이블의 파티션 테이블들은 자동으로 해당 인덱스 등이 생성 된다.
마스터 테이블 기준으로 파티션 테이블들을 생성
Postgresql 12 기준 으로 파티션 생성은
Range Partitioning
특정 Column이 가지는 값들의 범위로 Partition을 나눈다. 예를 들면 date값을 기준으로 매달 1일에서 말일까지 범위로 Partition을 나누는 경우이다.
List Partitioning
특정 Column이 가지는 값들의 집합으로 Partition을 나눈다. 예를 들면 location 값을 기준으로 서울, 부산, 인천 등 Data 값들의 List를 기준으로 Partition을 나누는 경우이다.
Hash Partitioning
Partition을 나눌 명확한 방법이 없을 경우 유용하다. 특히 향후 거대해질 것으로 예상되는데 초기단계에서 Partition을 설정하기 어려울 때 더욱 유용하다. 일반적으로 자동으로 증가하는 id 값을 기준으로 Partition을 정의하는 방식으로 사용된다.
특징
- 마스터 테이블 조회만으로 파티션 테이블들의 조회가 가능하다. (이는 저장, 삭제에도 해당 된다.)
- 직접적으로 파티션 테이블에 조회가 가능하므로 최적화가 유연해진다.
- 예로 Range Partitioning인 경우 특정 날짜만 검색 해야 한다면 특정 날짜가 포함되어 있는 파티션 테이블를 직접 사용한다.(삭제, 수정, 조회)
- 대용량 Insert 효율성이 좋다.
- 예로 Range Partitioning이고 파티션 기준이 날짜인 경우면 날짜 기준으로 해당 되는 파티션 테이블에 저장 되기 때문에 효율성이 좋다.
- 마스터 테이블에 attach가 가능하다.
- 마스터 테이블 기준으로 임시 테이블 생성
- 임시 테이블에 대량 Insert
- 임시 테이블을 마스터 테이블에 attach
- attach가 가능 하므로 과도한 작업을 분리 할 수 있다.
- 데이터의 유지보수가 쉬워진다.
- attach와 detach가 가능 하므로 데이터를 insert가 update가 아닌 테이블채로 이동/삭제가 가능하다.
예제
예제에 앞서 Partitioing 생성 시 주의사항으로
- PARTITION BY 키워드에 값은 기본키로 지정된 컬럼이여야 한다.
- 해당 값에 대해선 신중하게 선택해야 한다. 해당 값을 올바르게 선택하지 않으면 파티션 프루닝을 사용하지 못해 partition 테이블 전체를 사용 하는 경우가 발생해서 오히려 퍼포먼스가 떨어진다. 명확한 기준점으론 보긴 어렵지만 조회, 삭제, 수정 등에서 WHERE절에 필수로 들어가는 컬럼을 지정하는게 좋다.
Hash Partitioning
Master 테이블 생성
-- partition hash 부모 테이블 생성
CREATE TABLE public.map_partition (
id BIGSERIAL NOT NULL
, name VARCHAR(50) NOT NULL
, time_stamp timestamp DEFAULT now()
, CONSTRAINT map_partition_pkey PRIMARY KEY (id)
) PARTITION BY HASH(id); -- hash partition의 partition key를 id로 지정.Partition 테이블 생성
CREATE TABLE public.map_partition_0 PARTITION OF public.map_partition FOR VALUES WITH (MODULUS 5, REMAINDER 0);
CREATE TABLE public.map_partition_1 PARTITION OF public.map_partition FOR VALUES WITH (MODULUS 5, REMAINDER 1);
CREATE TABLE public.map_partition_2 PARTITION OF public.map_partition FOR VALUES WITH (MODULUS 5, REMAINDER 2);
CREATE TABLE public.map_partition_3 PARTITION OF public.map_partition FOR VALUES WITH (MODULUS 5, REMAINDER 3);
CREATE TABLE public.map_partition_4 PARTITION OF public.map_partition FOR VALUES WITH (MODULUS 5, REMAINDER 4);쿼리에 대한 설명은 아래를 참고 한다.
CREATE TABLE "partition 테이블명" PARTITION OF "partition의 master 테이블명" FOR VALUES WITH (MODULUS "hash 갯수", REMAINDER "순서");주의사항으론 "순서"는 0부터 시작이며 "hash 갯수" 이상을 초과한 순서를 지정 할 수 없다.
Partition 생성 확인
SELECT TC.table_name, TC.constraint_name, TC.constraint_type, CC.column_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC
WHERE TC.TABLE_NAME LIKE 'map_partition%'
AND TC.TABLE_CATALOG = CC.TABLE_CATALOG
AND TC.TABLE_SCHEMA = CC.TABLE_SCHEMA
AND TC.TABLE_NAME = CC.TABLE_NAME
AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
ORDER BY CC.column_name ASC, TC.table_name ASC;
-- 결과
table_name |constraint_name |constraint_type|column_name|
---------------|--------------------|---------------|-----------|
map_partition |map_partition_pkey |PRIMARY KEY |id |
map_partition_0|map_partition_0_pkey|PRIMARY KEY |id |
map_partition_1|map_partition_1_pkey|PRIMARY KEY |id |
map_partition_2|map_partition_2_pkey|PRIMARY KEY |id |
map_partition_3|map_partition_3_pkey|PRIMARY KEY |id |
map_partition_4|map_partition_4_pkey|PRIMARY KEY |id |결과처럼 master 테이블 인덱스가 partition 테이블에도 동일하게 생성된걸 확인 할 수 있다.
예제 데이터 저장
INSERT INTO public.map_partition(name, time_stamp) VALUES('hash_1', now());
INSERT INTO public.map_partition(name, time_stamp) VALUES('hash_2', now());
INSERT INTO public.map_partition(name, time_stamp) VALUES('hash_3', now());
INSERT INTO public.map_partition(name, time_stamp) VALUES('hash_4', now());
INSERT INTO public.map_partition(name, time_stamp) VALUES('hash_5', now());
INSERT INTO public.map_partition(name, time_stamp) VALUES('hash_6', now());
INSERT INTO public.map_partition(name, time_stamp) VALUES('hash_7', now());
INSERT INTO public.map_partition(name, time_stamp) VALUES('hash_8', now());
INSERT INTO public.map_partition(name, time_stamp) VALUES('hash_9', now());
INSERT INTO public.map_partition(name, time_stamp) VALUES('hash_10', now());
-- 결과
select * from public.map_partition_0;
id|name |time_stamp |
--|------|-------------------|
1|hash_1|2021-01-05 13:56:43|
4|hash_4|2021-01-05 13:56:43|
5|hash_5|2021-01-05 13:56:43|
select * from public.map_partition_1;
id|name |time_stamp |
--|------|-------------------|
2|hash_2|2021-01-05 13:56:43|
3|hash_3|2021-01-05 13:56:43|
select * from public.map_partition_2;
id|name |time_stamp |
--|------|-------------------|
6|hash_6|2021-01-05 13:56:43|
7|hash_7|2021-01-05 13:56:43|
select * from public.map_partition_3;
id|name |time_stamp |
--|------|-------------------|
8|hash_8|2021-01-05 13:56:43|
select * from public.map_partition_4;
id|name |time_stamp |
--|-------|-------------------|
9|hash_9 |2021-01-05 13:56:43|
10|hash_10|2021-01-05 13:56:43|실행계획 확인
EXPLAIN SELECT * FROM public.map_partition WHERE id IN (1, 2, 6);
-- 결과
QUERY PLAN |
---------------------------------------------------------------------------------------|
Append (cost=8.47..46.78 rows=9 width=134) |
-> Bitmap Heap Scan on map_partition_0 (cost=8.47..15.58 rows=3 width=134) |
Recheck Cond: (id = ANY ('{1,2,6}'::bigint[])) |
-> Bitmap Index Scan on map_partition_0_pkey (cost=0.00..8.47 rows=3 width=0)|
Index Cond: (id = ANY ('{1,2,6}'::bigint[])) |
-> Bitmap Heap Scan on map_partition_1 (cost=8.47..15.58 rows=3 width=134) |
Recheck Cond: (id = ANY ('{1,2,6}'::bigint[])) |
-> Bitmap Index Scan on map_partition_1_pkey (cost=0.00..8.47 rows=3 width=0)|
Index Cond: (id = ANY ('{1,2,6}'::bigint[])) |
-> Bitmap Heap Scan on map_partition_2 (cost=8.47..15.58 rows=3 width=134) |
Recheck Cond: (id = ANY ('{1,2,6}'::bigint[])) |
-> Bitmap Index Scan on map_partition_2_pkey (cost=0.00..8.47 rows=3 width=0)|
Index Cond: (id = ANY ('{1,2,6}'::bigint[])) |실행계획 결과에서 Bitmap Heap Scan 부분을 확인 해보면 파티션 프루닝을 통해 id 값 1, 2, 6만 가지고 있는 partition 테이블만 사용한걸 확인 할 수 있다.
만약 여기서 파티션 프루닝을 사용하지 못할 경우엔
-- partition key에 해당되는 컬럼 미포함 조회
EXPLAIN SELECT * FROM public.map_partition WHERE name LIKE 'hash%';
-- 결과
QUERY PLAN |
----------------------------------------------------------------------|
Append (cost=0.00..81.30 rows=10 width=134) |
-> Seq Scan on map_partition_0 (cost=0.00..16.25 rows=2 width=134)|
Filter: ((name)::text ~~ 'hash%'::text) |
-> Seq Scan on map_partition_1 (cost=0.00..16.25 rows=2 width=134)|
Filter: ((name)::text ~~ 'hash%'::text) |
-> Seq Scan on map_partition_2 (cost=0.00..16.25 rows=2 width=134)|
Filter: ((name)::text ~~ 'hash%'::text) |
-> Seq Scan on map_partition_3 (cost=0.00..16.25 rows=2 width=134)|
Filter: ((name)::text ~~ 'hash%'::text) |
-> Seq Scan on map_partition_4 (cost=0.00..16.25 rows=2 width=134)|
Filter: ((name)::text ~~ 'hash%'::text) |
-- 전체 partition 테이블 사용
-- partition key에 해당되는 컬럼 포함 조회
EXPLAIN SELECT * FROM public.map_partition WHERE name LIKE 'hash%' AND id IN (1, 2, 6);
-- 결과
QUERY PLAN |
---------------------------------------------------------------------------------------|
Append (cost=8.47..46.77 rows=3 width=134) |
-> Bitmap Heap Scan on map_partition_0 (cost=8.47..15.58 rows=1 width=134) |
Recheck Cond: (id = ANY ('{1,2,6}'::bigint[])) |
Filter: ((name)::text ~~ 'hash%'::text) |
-> Bitmap Index Scan on map_partition_0_pkey (cost=0.00..8.47 rows=3 width=0)|
Index Cond: (id = ANY ('{1,2,6}'::bigint[])) |
-> Bitmap Heap Scan on map_partition_1 (cost=8.47..15.58 rows=1 width=134) |
Recheck Cond: (id = ANY ('{1,2,6}'::bigint[])) |
Filter: ((name)::text ~~ 'hash%'::text) |
-> Bitmap Index Scan on map_partition_1_pkey (cost=0.00..8.47 rows=3 width=0)|
Index Cond: (id = ANY ('{1,2,6}'::bigint[])) |
-> Bitmap Heap Scan on map_partition_2 (cost=8.47..15.58 rows=1 width=134) |
Recheck Cond: (id = ANY ('{1,2,6}'::bigint[])) |
Filter: ((name)::text ~~ 'hash%'::text) |
-> Bitmap Index Scan on map_partition_2_pkey (cost=0.00..8.47 rows=3 width=0)|
Index Cond: (id = ANY ('{1,2,6}'::bigint[])) |
-- 파티션 프루닝이 올바르게 사용Range Partitioning
Master 테이블 생성
CREATE TABLE public.range_partition (
id BIGSERIAL NOT NULL
, name VARCHAR(50) NOT NULL
, time_stamp timestamp DEFAULT now()
, CONSTRAINT range_partition_pkey PRIMARY KEY (id, time_stamp)
) PARTITION BY RANGE (time_stamp); -- 범위 지정을 위해 날짜 컬럼을 partition key로 설정Partition 테이블 생성
CREATE TABLE public.range_partition_2021_01_01 PARTITION OF public.range_partition FOR VALUES FROM ('2021-01-01 00:00:00') TO ('2021-01-01 23:59:59');
CREATE TABLE public.range_partition_2021_01_02 PARTITION OF public.range_partition FOR VALUES FROM ('2021-01-02 00:00:00') TO ('2021-01-02 23:59:59');
CREATE TABLE public.range_partition_2021_01_03 PARTITION OF public.range_partition FOR VALUES FROM ('2021-01-03 00:00:00') TO ('2021-01-03 23:59:59');
CREATE TABLE public.range_partition_2021_01_04 PARTITION OF public.range_partition FOR VALUES FROM ('2021-01-04 00:00:00') TO ('2021-01-04 23:59:59');
CREATE TABLE public.range_partition_2021_01_05 PARTITION OF public.range_partition FOR VALUES FROM ('2021-01-05 00:00:00') TO ('2021-01-05 23:59:59');Hash Partitioning 생성과 다르게 FROM과 TO 키워드를 사용한다.
CREATE TABLE "partition 테이블명" PARTITION OF "partition의 master 테이블명" FOR VALUES FROM ("범위 시작값") TO ("범위 종료값");Partition 생성 확인
SELECT TC.table_name, TC.constraint_name, TC.constraint_type, CC.column_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC
WHERE TC.TABLE_NAME LIKE 'range_partition%'
AND TC.TABLE_CATALOG = CC.TABLE_CATALOG
AND TC.TABLE_SCHEMA = CC.TABLE_SCHEMA
AND TC.TABLE_NAME = CC.TABLE_NAME
AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
ORDER BY CC.column_name ASC, TC.table_name ASC;
-- 결과
table_name |constraint_name |constraint_type|column_name|
--------------------------|-------------------------------|---------------|-----------|
range_partition |range_partition_pkey |PRIMARY KEY |id |
range_partition_2021_01_01|range_partition_2021_01_01_pkey|PRIMARY KEY |id |
range_partition_2021_01_02|range_partition_2021_01_02_pkey|PRIMARY KEY |id |
range_partition_2021_01_03|range_partition_2021_01_03_pkey|PRIMARY KEY |id |
range_partition_2021_01_04|range_partition_2021_01_04_pkey|PRIMARY KEY |id |
range_partition_2021_01_05|range_partition_2021_01_05_pkey|PRIMARY KEY |id |
range_partition |range_partition_pkey |PRIMARY KEY |time_stamp |
range_partition_2021_01_01|range_partition_2021_01_01_pkey|PRIMARY KEY |time_stamp |
range_partition_2021_01_02|range_partition_2021_01_02_pkey|PRIMARY KEY |time_stamp |
range_partition_2021_01_03|range_partition_2021_01_03_pkey|PRIMARY KEY |time_stamp |
range_partition_2021_01_04|range_partition_2021_01_04_pkey|PRIMARY KEY |time_stamp |
range_partition_2021_01_05|range_partition_2021_01_05_pkey|PRIMARY KEY |time_stamp |Master 테이블의 인덱스 설정 그대로 계승해서 Partition 테이블에 인덱스를 생성한다.
예제 데이터 저장
-- Master 테이블을 이용한 저장
INSERT INTO public.range_partition(name, time_stamp) VALUES('range_20210101', '2021-01-01 09:10:00');
INSERT INTO public.range_partition(name, time_stamp) VALUES('range_20210101', '2021-01-01 23:10:00');
INSERT INTO public.range_partition(name, time_stamp) VALUES('range_20210102', '2021-01-02 09:10:00');
INSERT INTO public.range_partition(name, time_stamp) VALUES('range_20210102', '2021-01-02 23:10:00');
INSERT INTO public.range_partition(name, time_stamp) VALUES('range_20210103', '2021-01-03 13:10:00');
INSERT INTO public.range_partition(name, time_stamp) VALUES('range_20210103', '2021-01-03 23:10:00');
INSERT INTO public.range_partition(name, time_stamp) VALUES('range_20210104', '2021-01-04 03:10:00');
INSERT INTO public.range_partition(name, time_stamp) VALUES('range_20210104', '2021-01-04 18:10:00');
INSERT INTO public.range_partition(name, time_stamp) VALUES('range_20210105', '2021-01-05 00:10:00');
INSERT INTO public.range_partition(name, time_stamp) VALUES('range_20210105', '2021-01-05 12:10:00');
-- Partition 테이블에 직접 저장
INSERT INTO public.range_partition_2021_01_01(name, time_stamp) VALUES('partition_range_20210101', '2021-01-01 11:11:10');
INSERT INTO public.range_partition_2021_01_02(name, time_stamp) VALUES('partition_range_20210102', '2021-01-02 12:12:20');
INSERT INTO public.range_partition_2021_01_03(name, time_stamp) VALUES('partition_range_20210103', '2021-01-03 13:13:30');
INSERT INTO public.range_partition_2021_01_04(name, time_stamp) VALUES('partition_range_20210104', '2021-01-04 14:14:40');
INSERT INTO public.range_partition_2021_01_05(name, time_stamp) VALUES('partition_range_20210105', '2021-01-05 15:15:50');
-- 결과
SELECT * FROM public.range_partition_2021_01_01;
id|name |time_stamp |
--|------------------------|-------------------|
1|range_20210101 |2021-01-01 09:10:00|
2|range_20210101 |2021-01-01 23:10:00|
11|partition_range_20210101|2021-01-01 11:11:10|
SELECT * FROM public.range_partition_2021_01_02;
id|name |time_stamp |
--|------------------------|-------------------|
3|range_20210102 |2021-01-02 09:10:00|
4|range_20210102 |2021-01-02 23:10:00|
12|partition_range_20210102|2021-01-02 12:12:20|
SELECT * FROM public.range_partition_2021_01_03;
id|name |time_stamp |
--|------------------------|-------------------|
5|range_20210103 |2021-01-03 13:10:00|
6|range_20210103 |2021-01-03 23:10:00|
13|partition_range_20210103|2021-01-03 13:13:30|
SELECT * FROM public.range_partition_2021_01_04;
id|name |time_stamp |
--|------------------------|-------------------|
7|range_20210104 |2021-01-04 03:10:00|
8|range_20210104 |2021-01-04 18:10:00|
14|partition_range_20210104|2021-01-04 14:14:40|
SELECT * FROM public.range_partition_2021_01_05;
id|name |time_stamp |
--|------------------------|-------------------|
9|range_20210105 |2021-01-05 00:10:00|
10|range_20210105 |2021-01-05 12:10:00|
15|partition_range_20210105|2021-01-05 15:15:50|실행계획 확인
EXPLAIN SELECT
*
FROM range_partition
WHERE time_stamp >= '2021-01-01 00:00:00'
AND time_stamp <= '2021-01-02 23:59:59';
-- 결과
QUERY PLAN |----------------------------------------------------------------------------------------------------------|
Append (cost=0.00..35.02 rows=4 width=134) |
-> Seq Scan on range_partition_2021_01_01 (cost=0.00..17.50 rows=2 width=134) |
Filter: ((time_stamp >= '2021-01-01 00:00:00'::timestamp without time zone) AND (time_stamp <= '2021-01-02 23:59:59'::timestamp without time zone)) |
-> Seq Scan on range_partition_2021_01_02 (cost=0.00..17.50 rows=2 width=134) |
Filter: ((time_stamp >= '2021-01-01 00:00:00'::timestamp without time zone) AND (time_stamp <= '2021-01-02 23:59:59'::timestamp without time zone)) |파티션 프루닝을 사용하여 지정한 날짜 범위에 해당 되는 partition 테이블만 사용하는것을 확인 할 수 있다.
파티션 프루닝을 사용하지 못하게 된다면
EXPLAIN SELECT
*
FROM range_partition
WHERE name LIKE 'partition_%';
-- 결과
QUERY PLAN |
---------------------------------------------------------------------------------|
Append (cost=0.00..81.30 rows=10 width=134) |
-> Seq Scan on range_partition_2021_01_01 (cost=0.00..16.25 rows=2 width=134)|
Filter: ((name)::text ~~ 'partition_%'::text) |
-> Seq Scan on range_partition_2021_01_02 (cost=0.00..16.25 rows=2 width=134)|
Filter: ((name)::text ~~ 'partition_%'::text) |
-> Seq Scan on range_partition_2021_01_03 (cost=0.00..16.25 rows=2 width=134)|
Filter: ((name)::text ~~ 'partition_%'::text) |
-> Seq Scan on range_partition_2021_01_04 (cost=0.00..16.25 rows=2 width=134)|
Filter: ((name)::text ~~ 'partition_%'::text) |
-> Seq Scan on range_partition_2021_01_05 (cost=0.00..16.25 rows=2 width=134)|
Filter: ((name)::text ~~ 'partition_%'::text) |partition 테이블 전체를 사용하는 것을 확인 할 수 있다.
List Partitioning
Master 테이블 생성
CREATE TABLE public.list_partition (
id BIGSERIAL NOT NULL
, flag VARCHAR(1) NOT NULL
, name VARCHAR(50) NOT NULL
, time_stamp timestamp DEFAULT now()
, CONSTRAINT list_partition_pkey PRIMARY KEY (id, flag)
) PARTITION BY LIST (flag); -- 특정 값의 집합을 저장하는 flag 컬럼을 partition key로 지정Partition 테이블 생성
CREATE TABLE public.list_partition_a PARTITION OF public.list_partition FOR VALUES IN ('A');
CREATE TABLE public.list_partition_b PARTITION OF public.list_partition FOR VALUES IN ('B');
CREATE TABLE public.list_partition_c PARTITION OF public.list_partition FOR VALUES IN ('C');
CREATE TABLE public.list_partition_d PARTITION OF public.list_partition FOR VALUES IN ('D');
CREATE TABLE public.list_partition_e PARTITION OF public.list_partition FOR VALUES IN ('E');List Partitioning 경우에는 IN 키워드를 사용한다.
위 경우는 flag 컬럼은 A, B, C, D, E 값들만 허용 및 partition 기준이 된다. 그 외 다른 값이 들어오게 되면 에러가 발생한다.
CREATE TABLE "partition 테이블명" PARTITION OF "partition의 master 테이블명" FOR VALUES IN ("특정 값");예제 데이터 저장
-- Master 테이블을 이용한 저장
INSERT INTO public.list_partition(name, flag, time_stamp) VALUES('list_a', 'A', now());
INSERT INTO public.list_partition(name, flag, time_stamp) VALUES('list_b', 'B', now());
INSERT INTO public.list_partition(name, flag, time_stamp) VALUES('list_c', 'C', now());
INSERT INTO public.list_partition(name, flag, time_stamp) VALUES('list_d', 'D', now());
INSERT INTO public.list_partition(name, flag, time_stamp) VALUES('list_e', 'E', now());
-- Partition 테이블에 직접 저장
INSERT INTO public.list_partition_a(name, flag, time_stamp) VALUES('partition_list_a', 'A', now());
INSERT INTO public.list_partition_b(name, flag, time_stamp) VALUES('partition_list_b', 'B', now());
INSERT INTO public.list_partition_c(name, flag, time_stamp) VALUES('partition_list_c', 'C', now());
INSERT INTO public.list_partition_d(name, flag, time_stamp) VALUES('partition_list_d', 'D', now());
INSERT INTO public.list_partition_e(name, flag, time_stamp) VALUES('partition_list_e', 'E', now());
-- 결과
select * from public.list_partition_a;
id|flag|name |time_stamp |
--|----|----------------|-------------------|
1|A |list_a |2021-01-05 15:50:26|
6|A |partition_list_a|2021-01-05 15:50:26|
select * from public.list_partition_b;
id|flag|name |time_stamp |
--|----|----------------|-------------------|
2|B |list_b |2021-01-05 15:50:26|
7|B |partition_list_b|2021-01-05 15:50:26|
select * from public.list_partition_c;
id|flag|name |time_stamp |
--|----|----------------|-------------------|
3|C |list_c |2021-01-05 15:50:26|
8|C |partition_list_c|2021-01-05 15:50:26|
select * from public.list_partition_d;
id|flag|name |time_stamp |
--|----|----------------|-------------------|
4|D |list_d |2021-01-05 15:50:26|
9|D |partition_list_d|2021-01-05 15:50:26|
select * from public.list_partition_e;
id|flag|name |time_stamp |
--|----|----------------|-------------------|
5|E |list_e |2021-01-05 15:50:26|
10|E |partition_list_e|2021-01-05 15:50:26|Partition 생성 확인
SELECT TC.table_name, TC.constraint_name, TC.constraint_type, CC.column_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC
WHERE TC.TABLE_NAME LIKE 'list_partition%'
AND TC.TABLE_CATALOG = CC.TABLE_CATALOG
AND TC.TABLE_SCHEMA = CC.TABLE_SCHEMA
AND TC.TABLE_NAME = CC.TABLE_NAME
AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
ORDER BY CC.column_name ASC, TC.table_name ASC;
-- 결과
table_name |constraint_name |constraint_type|column_name|
----------------|---------------------|---------------|-----------|
list_partition |list_partition_pkey |PRIMARY KEY |flag |
list_partition_a|list_partition_a_pkey|PRIMARY KEY |flag |
list_partition_b|list_partition_b_pkey|PRIMARY KEY |flag |
list_partition_c|list_partition_c_pkey|PRIMARY KEY |flag |
list_partition_d|list_partition_d_pkey|PRIMARY KEY |flag |
list_partition_e|list_partition_e_pkey|PRIMARY KEY |flag |
list_partition |list_partition_pkey |PRIMARY KEY |id |
list_partition_a|list_partition_a_pkey|PRIMARY KEY |id |
list_partition_b|list_partition_b_pkey|PRIMARY KEY |id |
list_partition_c|list_partition_c_pkey|PRIMARY KEY |id |
list_partition_d|list_partition_d_pkey|PRIMARY KEY |id |
list_partition_e|list_partition_e_pkey|PRIMARY KEY |id |실행계획 확인
EXPLAIN SELECT * FROM list_partition WHERE flag in ('A', 'E');
-- 결과
QUERY PLAN |
-----------------------------------------------------------------------|
Append (cost=0.00..32.05 rows=10 width=142) |
-> Seq Scan on list_partition_a (cost=0.00..16.00 rows=5 width=142)|
Filter: ((flag)::text = ANY ('{A,E}'::text[])) |
-> Seq Scan on list_partition_e (cost=0.00..16.00 rows=5 width=142)|
Filter: ((flag)::text = ANY ('{A,E}'::text[])) |파티션 프루닝을 사용하여 특정 값(A, E)이 있는 partition 테이블만 사용하는것을 확인 할 수 있다.
파티션 프루닝을 사용하지 못하게 된다면
EXPLAIN SELECT
*
FROM list_partition
WHERE name LIKE 'partition_%';
-- 결과
QUERY PLAN |
-----------------------------------------------------------------------|
Append (cost=0.00..80.05 rows=10 width=142) |
-> Seq Scan on list_partition_a (cost=0.00..16.00 rows=2 width=142)|
Filter: ((name)::text ~~ 'partition_%'::text) |
-> Seq Scan on list_partition_b (cost=0.00..16.00 rows=2 width=142)|
Filter: ((name)::text ~~ 'partition_%'::text) |
-> Seq Scan on list_partition_c (cost=0.00..16.00 rows=2 width=142)|
Filter: ((name)::text ~~ 'partition_%'::text) |
-> Seq Scan on list_partition_d (cost=0.00..16.00 rows=2 width=142)|
Filter: ((name)::text ~~ 'partition_%'::text) |
-> Seq Scan on list_partition_e (cost=0.00..16.00 rows=2 width=142)|
Filter: ((name)::text ~~ 'partition_%'::text) |partition 테이블 전체를 사용하는 것을 확인 할 수 있다.
주요
partition 테이블을 생성 한 후 필히 파티션 프루닝을 확인해서 정상적으로 작동 하는지 확인을 한다.
- 파티션 프루닝파티션 프루닝을 활용하지 못하면 partition을 사용하는 의미가 없다.
무조건 partition을 사용하진 않는다.
- partition은 데이터가 대용량인 경우에 고려하고 대용량이 아닌 경우엔 단일 테이블로 구성한다.
partition key는 무조건 WHERE 절에 들어가는 컬럼으로 지정한다.
- 무조건 where절에 들어가야한다 안그러면 파티션 프루닝파티션 프루닝을 사용하지 못해 개발자&서버가 힘들어진다.
partition 테이블을 생성 시 FOR VALUES 키워드에 설정한 값을 유의해야 한다.
예제의 Range Partitioning 경우 partition 테이블에 설정한 날짜 범위 이외의 데이터는 에러가 난다. 이는 partition 테이블마다 저장 할 수 있는 범위 혹은 값이 지정되어 있기 때문이다. 이와 에러 상황은 모든 partition에 해당 된다.
예제에서 Hash Partitioning외 다른 파티션에선 직접적으로 partition 테이블을 조작 하였지만 Hash Partitioning은 불가능하다. 이유는 설정한 partition key가 자동증가 되는 id로 설정했기 때문이다. 특정 partition 테이블에 저장을 하려 해도 자동 증가 되는 key 값이 해당 partition 테이블에 지정된 범위 혹은 값이 아니면 에러가 나기 때문이다.
-- map_partition_0 테이블에 직접 저장 시도. -- 1차 시도 INSERT INTO public.map_partition_0 (name, time_stamp) VALUES('hash_1', now()); -- 자동 증가된 key 값이 map_partition_0 테이블에 허용할 수 있는 값이 아님 SQL Error [23514]: ERROR: new row for relation "map_partition_0" violates partition constraint Detail: Failing row contains (11, hash_1, 2021-01-05 18:44:06.189923). -- 2차 시도 INSERT INTO public.map_partition_0 (name, time_stamp) VALUES('hash_1', now()); SQL Error [23514]: ERROR: new row for relation "map_partition_0" violates partition constraint Detail: Failing row contains (12, hash_1, 2021-01-05 18:45:39.876258). -- 3차 시도 INSERT INTO public.map_partition_0 (name, time_stamp) VALUES('hash_1', now()); SQL Error [23514]: ERROR: new row for relation "map_partition_0" violates partition constraint Detail: Failing row contains (13, hash_1, 2021-01-05 18:45:39.876258). -- 4차 시도(성공) INSERT INTO public.map_partition_0 (name, time_stamp) VALUES('hash_1', now()); Updated Rows 1 Query INSERT INTO public.map_partition_0 (name, time_stamp) VALUES('hash_1', now()) Finish time Tue Jan 05 18:46:25 KST 2021
비고
v9.6에서는 Partitioning을 하기 위해서 테이블 상속과 트리거 생성을 하는 과정을 거쳤지만 이후 버전에는 사용하지 않고 'PARTITION BY' 와 'PARTITION OF' 키워드를 사용한다.
파티션 프루닝 하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 엑세스 대상에서 제외시키는 기능