최근에 Postgresql에서 지원하는 "Table Partitioning"으로 성능을 향상시켰던 내용에 대해 기록하려고 한다.
테이블 형식
table_nameXXX
- 1,2번째 XX는 01~12까지의 달로 정의
- 3번째 X는 일별로 0, 1, 2의 값으로,
00<=X<10 : 0
10<=X<20 : 1
20<=X : 2
로 정의한다. - table_name010 ~ table_name122 까지의 모든 데이터를 합치는 VIEW가 존재한다.
문제점
1. 이름만 다르고 같은 컬럼, 같은 데이터 형식을 가지는 테이블 다수 존재
아래와 같이 이름만 다른 TABLE이 36개가 존재.
CREATE TABLE TABLE_NAME010(
test_column1 int not null,
test_column2 varchar(20),
test_column3 TIMESTAMP
)
CREATE TABLE TABLE_NAME011(
test_column1 int not null,
test_column2 varchar(20),
test_column3 TIMESTAMP
)
...
CREATE TABLE TABLE_NAME122(
test_column1 int not null,
test_column2 varchar(20),
test_column3 TIMESTAMP
)2. 특정 기간 조회 시, 성능 저하.
01월 01일 ~ 01월 09일 까지의 데이터를 조회하는 경우에는 table_name010만 조회하면 된다.
하지만 01월 01일 ~ 06월 15일 까지의 데이터를 조회하는 경우에는 VIEW를 통해 조회하게 되는데,
이때에는 불필요한 날짜의 데이터도 검색 조건에 포함되기에
성능 상의 불리한 점이 따르게 된다.
3. 데이터 삽입 시, 타겟 테이블로 인한 유연성 및 확장성 저하.
table_nameXXX를 날짜에 따라 테이블을 선정하는 로직이 필수적이므로
데이터 삽입 시에 유연성 및 확장성이 떨어짐Table Partitioning 이란
-
논리적으로 하나의 큰 테이블을 작은 물리적 조각으로 분할하는 것
-
즉, 논리 테이블인 <table_name> 하위에
<table_name010>,
<table_name011>,
<table_name012>,
..,
<table_name122>
의 물리 테이블이 존재하는 형태를 말함.
Table Partitioning 적용 예시
- 기존 테이블 구성
sequence 36개 + table 36 개 + index 36개 + view 1개
CREATE SEQUENCE table_name010_seq;
...
CREATE SEQUENCE table_name122_seq;
CREATE TABLE table_name010 (
test_column1 bigint DEFAULT nextval('table_name010_seq'::regclass) NOT NULL,
test_column2 TIMESTAMP WITHOUT TIME ZONE,
test_column3 varchar(20)
)
...
CREATE TABLE table_name122 (
test_column1 bigint DEFAULT nextval('table_name122_seq'::regclass) NOT NULL,
test_column2 TIMESTAMP WITHOUT TIME ZONE,
test_column3 varchar(20)
)
CREATE INDEX table_name010_idx ON table_name010 USING btree (test_column2, test_column3);
...
CREATE INDEX table_name122_idx ON table_name122 USING btree (test_column2, test_column3);
CREATE VIEW view_table_name AS
SELECT
table_name010.test_column1,
table_name010.test_column2,
table_name010.test_column3,
FROM table_name010
UNION ALL
...
SELECT
table_name122.test_column1,
table_name122.test_column2,
table_name122.test_column3,
FROM table_name122- Table Partitioning 적용 구성
sequence 1개 + table 1개 + index 1개
CREATE SEQUENCE table_name_seq;
CREATE TABLE table_name (
test_column1 bigint DEFAULT nextval('table_name_seq'::regclass) NOT NULL,
test_column2 TIMESTAMP WITHOUT TIME ZONE,
test_column3 varchar(20)
)
CREATE INDEX table_name_idx ON table_name USING btree (test_column2, test_column3);비교 테스트
- 테스트 사양
Memory: 4G
Core: 4
HardDisk: 100G
Postgresql: v14 - 시간은 Excution Time 기준(EXPLAIN ANALYZE)
| 컬럼 수(개) | 데이터 건수(억) | 테이블 용량(GB) | 전체 조회(ms) | 전체 조회 -조건1(ms) | 전체 조회 -조건2(ms) | 전체 조회 -조건1&2(ms) | |
|---|---|---|---|---|---|---|---|
| 기존 테이블 | 11 | 1 | 15 | 16912.144 | 36087.519 | 15369.527 | 3815.623 |
| 파티셔닝 테이블 | 11 | 1 | 15 | 14299.575 | 12531.403 | 4429.395 | 1547.478 |
| 차이(배수) | 1.18 | 2.87 | 3.46 | 2.46 | |||
| 기존 테이블 | 11 | 2 | 30 | 35999.988 | 147653.382 | 36862.338 | 19355.966 |
| 파티셔닝 테이블 | 11 | 2 | 29 | 28435.075 | 108417.686 | 17207.650 | 5488.973 |
| 차이(단위: 배수) | 1.26 | 1.36 | 2.14 | 3.52 | |||
| 파티셔닝 테이블 | 11 | 3 | 44 | 51762.145 | 193981.513 | 25561.909 | 4315.061 |
| 파티셔닝 테이블 | 11 | 3.5 | 51 | 54400.472 | 216787.001 | 32574.542 | 5273.092 |
기존 형태보다 파티셔닝이 적용된 형태에서의 성능이 우세한 점을 확인 가능
또한
- TABLE 수: 2001 -> 391
- VIEW 수: 107 -> 61
와 같이
수가 줄어든만큼 TABLE, VIEW 등이 간단해져 유지보수성 향상됨
Table Partitioning을 통한 장점 및 얻은 이점
1. 테이블 별 각각의 SEQUENCE 대신 논리테이블에 하나의 SEQUENCE를 사용
2. VIEW 대신 논리테이블을 사용
3. 논리테이블에 직접 INSERT 함으로써, 유연성 및 유지보수 용이성 향상
4. 자주, 많이 접근하는 부분에서 해당하는 파티션만 스캔하므로 성능 향상
5. 분산 데이터 및 대용량 데이터 처리에 유리함
6. 데이터 관리가 편함참고
- 테이블 분리 함수
CREATE OR REPLACE FUNCTION public.get_table_by_date(tb_name VARCHAR(30))
RETURNS VARCHAR AS $$
DECLARE
current_dt DATE := '2000-01-01';
end_date DATE := '2000-12-31';
common_range VARCHAR(2);
BEGIN
WHILE current_dt <= end_date LOOP
common_range := (SELECT TO_CHAR(current_dt, 'MM'));-- 달
-- TABLE
EXECUTE FORMAT('CREATE TABLE IF NOT EXISTS %s PARTITION OF %s FOR VALUES FROM (%L, 01) TO (%L, 10)',-- 01 <= x < 10
tb_name || common_range || '0',
tb_name,
common_range,
common_range);
EXECUTE FORMAT('CREATE TABLE IF NOT EXISTS %s PARTITION OF %s FOR VALUES FROM (%L, 10) TO (%L, 20)',-- 10 <= x < 20
tb_name || common_range || '1',
tb_name,
common_range,
common_range);
EXECUTE FORMAT('CREATE TABLE IF NOT EXISTS %s PARTITION OF %s FOR VALUES FROM (%L, 20) TO (%L, 32)',-- 20 <= x < 32
tb_name || common_range || '2',
tb_name,
common_range,
common_range);
current_dt := current_dt + INTERVAL '1 MONTH';
END LOOP;
RETURN 'SUCCESS';
END;
$$ LANGUAGE plpgsql;REF
https://www.postgresql.org/docs/14/ddl-partitioning.html
hello world:)